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@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users