On Mon, 08 Nov 2010, Gavin McCullagh wrote: > We seem to have the correct indexes on the file table. I've run optimize > table > and it still takes 14 minutes to build the tree on one of our bigger clients. > We have 51 million entries in the file table.
I thought I should give some mroe concrete information: I don't suppose this is news to anyone but here's the mysql slow query log to correspond: # Time: 101111 14:24:49 # u...@host: bacula[bacula] @ localhost [] # Query_time: 1139.657646 Lock_time: 0.000471 Rows_sent: 4263403 Rows_examined: 50351037 SET timestamp=1289485489; 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 (9944,9950,9973,9996) UNION ALL SELECT JobTDate, PathId, FilenameId FROM BaseFiles JOIN File USING (FileId) JOIN Job ON (BaseJobId = Job.JobId) WHERE BaseFiles.JobId IN (9944,9950,9973,9996) ) AS tmp GROUP BY PathId, FilenameId ) AS T1 WHERE (Job.JobId IN ( SELECT DISTINCT BaseJobId FROM BaseFiles WHERE JobId IN (9944,9950,9973,9996)) OR Job.JobId IN (9944,9950,9973,9996)) 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; I've spent some time with the mysqltuner.pl script but to no avail thus far. There's 6GB RAM so it suggests a key buffer size of >4GB which I've set at 4.1GB. This is an Ubuntu Linux server running MySQL v5.1.41. The mysql data is on an MD software RAID 1 array on 7200rpm SATA disks. The tables are MyISAM (which I had understood to be quicker than innodb in low concurrency situations?). The tuner script is suggesting I should disable innodb as we're not using it which I will do though I wouldn't guess that will make a massive difference. There are no fragmented tables currently. Gavin ------------------------------------------------------------------------------ Centralized Desktop Delivery: Dell and VMware Reference Architecture Simplifying enterprise desktop deployment and management using Dell EqualLogic storage and VMware View: A highly scalable, end-to-end client virtualization framework. Read more! http://p.sf.net/sfu/dell-eql-dev2dev _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
