|
Re: [Bacula-users] SOLVED?: Dead slow backups with bacula 5.0, mysql and accurate
2010-02-20 17:13:38
On 2/19/2010 5:55 PM, Frank Sweetser wrote:
The best way to get more data about what's going on is to use the 'explain'
mysql command. First, get the complete SQL query that's taking too long to
run by using the 'show processlist full' command - that way the results won't
get truncated.
Then, run the query manually, but prefixed with the 'explain' command:
explain SELECT Path.Path, Filename.Name, ...
This should give you more data about exactly how mysql is going about
executing the query, which should hopefully in turn point to why it's taking
so ridiculously long and how that might be fixed.
Thanks, Frank, for the tip. I tried exactly what you said, and found
at least one helpful index addition. Although, actually, even doing
the explain took so long that I gave up and reviewed the
make_mysql_tables script, which provided a possible clue, which I
tried, and it not only made the explain go faster, but also resolved my
horribly slow backup issue. make_mysql_tables suggests to add INDEX
(FilenameId, PathId) on the File table if verifies are too slow--it
also recommends several other indices, all of which I already had
(PathId, FilenameId and JobId). I ran this sql query:
CREATE INDEX FilenameId_2 ON File (FilenameId, PathId);
Which took quite a while (maybe 20-30 minutes?)....
Then, using the full query from 'mysqladmin -v processlist' with
'explain' to tell me about how mysql would run the query (sorry about
the width below, may wrap unpleasantly):
mysql> explain SELECT Path.Path, Filename.Name,
Temp.FileIndex, Temp.JobId, LStat, MD5 FROM ( SELECT FileId, Job.JobId
AS JobId, FileIndex, File.PathId AS PathId, File.FilenameId AS
FilenameId, LStat, MD5 FROM Job, File, ( SELECT MAX(JobTDate) AS
JobTDate, PathId, FilenameId FROM ( SELECT JobTDate, PathId, FilenameId
FROM File JOIN Job USING (JobId) WHERE File.JobId IN
(13275,13346,13350) UNION ALL SELECT JobTDate, PathId, FilenameId FROM
BaseFiles JOIN File USING (FileId) JOIN Job ON (BaseJobId =
Job.JobId) WHERE BaseFiles.JobId IN (13275,13346,13350) ) AS tmp GROUP
BY PathId, FilenameId ) AS T1 WHERE (Job.JobId IN ( SELECT DISTINCT
BaseJobId FROM BaseFiles WHERE JobId IN (13275,13346,13350)) OR
Job.JobId IN (13275,13346,13350)) AND T1.JobTDate = Job.JobTDate AND
Job.JobId = File.JobId AND T1.PathId = File.PathId AND T1.FilenameId =
File.FilenameId ) AS Temp JOIN Filename ON (Filename.FilenameId =
Temp.FilenameId) JOIN Path ON (Path.PathId = Temp.PathId) WHERE
FileIndex > 0 ORDER BY Temp.JobId, FileIndex ASC;
+----+--------------------+------------+--------+----------------------------------------------+--------------+---------+-------------------------+--------+---------------------------------+
| id | select_type | table | type |
possible_keys | key | key_len |
ref | rows | Extra |
+----+--------------------+------------+--------+----------------------------------------------+--------------+---------+-------------------------+--------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL |
NULL | NULL | NULL |
NULL | 256855 | Using where; Using filesort |
| 1 | PRIMARY | Path | eq_ref |
PRIMARY | PRIMARY | 4 |
Temp.PathId | 1 | |
| 1 | PRIMARY | Filename | eq_ref |
PRIMARY | PRIMARY | 4 |
Temp.FilenameId | 1 | |
| 2 | DERIVED | <derived3> | ALL |
NULL | NULL | NULL |
NULL | 256855 | |
| 2 | DERIVED | File | ref |
JobId,PathId,FilenameId,JobId_2,FilenameId_2 | FilenameId_2 | 8 |
T1.FilenameId,T1.PathId | 8 | Using where |
| 2 | DERIVED | Job | eq_ref |
PRIMARY | PRIMARY | 4 |
bacula.File.JobId | 1 | Using where |
| 6 | DEPENDENT SUBQUERY | NULL | NULL |
NULL | NULL | NULL |
NULL | NULL | no matching row in const table |
| 3 | DERIVED | <derived4> | ALL |
NULL | NULL | NULL |
NULL | 259176 | Using temporary; Using filesort |
| 4 | DERIVED | Job | range |
PRIMARY | PRIMARY | 4 |
NULL | 3 | Using where |
| 4 | DERIVED | File | ref |
JobId,JobId_2 | JobId_2 | 4 |
bacula.Job.JobId | 35941 | Using index |
| 5 | UNION | NULL | NULL |
NULL | NULL | NULL |
NULL | NULL | no matching row in const table |
| NULL | UNION RESULT | <union4,5> | ALL |
NULL | NULL | NULL |
NULL | NULL | |
+----+--------------------+------------+--------+----------------------------------------------+--------------+---------+-------------------------+--------+---------------------------------+
12 rows in set (16.83 sec)
Afterwards, I attempted to run an incremental backup with Accurate on,
and it works pretty much the same as it used to for me back with 3.0.3.
-se
|
------------------------------------------------------------------------------
Download Intel® Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev _______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
|
|
