Unfortunately, this index creates lots of performance problem during batch insert session due to index bloat. This is not a good idea to advise it :(
http://sourceforge.net/apps/wordpress/bacula/2009/09/28/performance-issue-with-a-useless-index-on-postgresql/ Le Samedi 20 Février 2010 23:30:45, Dan Langille a écrit : > 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 ------------------------------------------------------------------------------ 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
