Sebastian Stark wrote:
I noticed that when doing a backup mysql seems to be using most of
the time doing SELECTs:
SELECT FilenameId FROM Filename WHERE
Name='trainNeckarArray.sh.o1087190.27859' |
When I show the processlist several times in a row it is nearly
always SELECTing some filename in the Filename table. Mysql is using
~100% CPU so this seems to be the bottleneck in this case.
As seen from the sample, bacula uses dynamic SQL-statements, where it
inserts the filename in the statement and sends it to server. That way the
server has to parse the statement every time again and again, it's pretty
costly process.
I don't know prepared statements works in MySQL, PgSQL or others, I have
done it in native Oracle, and Jdbc only. In Jdbc it goes like this:
PreparedStatement stmt = connection.prepareStatement("SELECT FilenameId FROM
Filename WHERE Name=?");
for (int i=0; i < lastFile; i++)
{
stmt.setString(1, filesNames[i]);
ResultSet rs = stmt.executeQuery();
}
In real world, the two latter rows are put inside the loop, and the SQL
statement is sent to server only once, parsed there only once, and executed
from a "cursor cache".
It's really heavy stuff for the server send a separate SQL statement for
each query. I'm pretty sure that at least MySQL and PgSQL can do natively
prepared statements fine. They should always be used when possible.
SQL has never been the strong point in Bacula, but there are reasons like
portability for it. I believe that by using UnixODBC or like for the
portability, and coding the SQL with caution the end result would be much
more portable, and even faster.
Using native API's do not pay, if they are not used with optimum ways.
-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems? Stop! Download the new AJAX search engine that makes
searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
