-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
FYI
- -------- Original Message --------
Subject: Re: [Bacula-users] SOLVED?: Dead slow backups with bacula 5.0,
mysql and accurate
Date: Sat, 20 Feb 2010 13:56:31 -0800
From: Steve Ellis <el...@brouhaha.com>
To: bacula-us...@lists.sourceforge.net
References: <4b7eec0c.8070...@brouhaha.com> <4b7f4104.8040...@wpi.edu>
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
- --
Dan Langille
BSDCan - The Technical BSD Conference : http://www.bsdcan.org/
PGCon - The PostgreSQL Conference: http://www.pgcon.org/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.13 (FreeBSD)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkuAYpUACgkQCgsXFM/7nTyGkgCfcyWO+3mYEtgqD2brS4RxmbFS
uqcAnikq+hF7ncd7RyClxnWqk2W0YCSK
=J4hj
-----END PGP SIGNATURE-----
------------------------------------------------------------------------------
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-us...@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
------------------------------------------------------------------------------
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-devel mailing list
Bacula-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-devel
