25.03.2014 22:23, Jari Fredriksson kirjoitti: > 25.03.2014 17:04, Kern Sibbald kirjoitti: >> Hello, >> >> Aside from tuning as recommended by Gary, did you add any new indexes >> recently? Often users a new indexes because they speed up some >> particular operation, but because of the complexities of SQL, those >> indexes can seriously slow down other queries such as batch insert >> which is what I imagine is taking the time in your case. >> >> Best regards, >> Kern >> > No, I did not add any indices but just recreated the database by using > script from 5.0.3. The software version in CentOS is 5.0.0. but I could > not find that from bacula.org repository.
Appeared that the bacula script created the tables as MyISAM. I converted to InnoDB, tweaked some MySQL parameters, and not this seems to be OK again. Seems there is really a difference between MyISAM and InnoDB. >> On 14-03-24 10:19 AM, Jari Fredriksson wrote: >>> 26.02.2014 12:00, Jari Fredriksson kirjoitti: >>>> MySQL process keeps on running at 100% CPU and is processing >>>> >>>> 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 (7088,7196,7204,7260) UNION ALL SELECT JobTDate, PathId, >>>> FilenameId FROM BaseFiles JOIN File USING (FileId) JOIN Job ON >>>> (BaseJobId = Job.JobId) WHERE BaseFiles.JobId IN (7088,7196,7204,7260) ) >>>> AS tmp GROUP BY PathId, FilenameId ) AS T1 WHERE (Job.JobId IN ( SELECT >>>> DISTINCT BaseJobId FROM BaseFiles WHERE JobId IN (7088,7196,7204,7260)) >>>> OR Job.JobId IN (7088,7196,7204,7260)) 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 >>>> >>>> My setup has worked flawlessly for years, and now suddenly this. >>>> >>>> This is on CentOS 6.5 with Version: 5.0.0 (26 January 2010) >>>> >>>> Any thoughts, what might cause this? >>>> >>>> >>> I dropped the database, recretead it again. The problems stays! I wonder >>> if 5.0.0. has some inherent problem into it, that is the version in >>> CentOS 6.5. >>> >>> Irritating. >>> >>> >>> >>> >>> >>> ------------------------------------------------------------------------------ >>> Learn Graph Databases - Download FREE O'Reilly Book >>> "Graph Databases" is the definitive new guide to graph databases and their >>> applications. Written by three acclaimed leaders in the field, >>> this first edition is now available. Download your free book today! >>> http://p.sf.net/sfu/13534_NeoTech >>> >>> >>> _______________________________________________ >>> Bacula-users mailing list >>> Bacula-users@lists.sourceforge.net >>> https://lists.sourceforge.net/lists/listinfo/bacula-users > > > > ------------------------------------------------------------------------------ > Learn Graph Databases - Download FREE O'Reilly Book > "Graph Databases" is the definitive new guide to graph databases and their > applications. Written by three acclaimed leaders in the field, > this first edition is now available. Download your free book today! > http://p.sf.net/sfu/13534_NeoTech > > > _______________________________________________ > Bacula-users mailing list > Bacula-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/bacula-users -- jarif.bit
signature.asc
Description: OpenPGP digital signature
------------------------------------------------------------------------------
_______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
