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