Hi, 24.11.2009 08:59, Christoph Litauer wrote: > Christoph Litauer schrieb: >> Jesper Krogh schrieb: >>> Christoph Litauer wrote: >>>> Thanks! One last question (hopefully): How big is /var/lib/mysql/ibdata1? >>> 282GB on ext3 >>> >> Dear Jesper, >> >> in the meantime I made a test setup - not successfull 'til now regarding >> the performance. What I forgot to ask: What mysql-DB version are you >> running? >> > > And another demand, please: > > Could you - or someone else - please select any JobId and execute the > following (my)sql-statement: > > mysql>EXPLAIN SELECT Path.Path, Filename.Name, File.FileIndex, > File.JobId, File.LStat > FROM ( > SELECT max(FileId) as FileId, PathId, FilenameId > FROM ( > SELECT FileId, PathId, FilenameId > FROM File > WHERE JobId IN (<insert your JobId here>) > ) AS F GROUP BY PathId, FilenameId > ) AS Temp JOIN Filename ON (Filename.FilenameId = Temp.FilenameId) JOIN > Path ON (Path.PathId = Temp.PathId) JOIN File ON (File.FileId = > Temp.FileId) WHERE File.FileIndex > 0 ORDER BY JobId, FileIndex ASC > > Please post the result. Thanks in advance!
Sure... > mysql> EXPLAIN SELECT Path.Path, Filename.Name, File.FileIndex, File.JobId, > File.LStat FROM ( SELECT max(FileId) as FileId, PathId, FilenameId FROM ( > SELECT FileId, PathId, FilenameId FROM File WHERE JobId IN (11902)) AS F > GROUP BY PathId, FilenameId ) AS Temp JOIN Filename ON (Filename.FilenameId = > Temp.FilenameId) JOIN Path ON (Path.PathId = Temp.PathId) JOIN File ON > (File.FileId =Temp.FileId) WHERE File.FileIndex > 0 ORDER BY JobId, FileIndex > ASC; > +----+-------------+------------+--------+---------------+---------+---------+-----------------+-------+---------------------------------+ > | id | select_type | table | type | possible_keys | key | key_len > | ref | rows | Extra | > +----+-------------+------------+--------+---------------+---------+---------+-----------------+-------+---------------------------------+ > | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL > | NULL | 60905 | Using temporary; Using filesort | > | 1 | PRIMARY | Path | eq_ref | PRIMARY | PRIMARY | 4 > | Temp.PathId | 1 | | > | 1 | PRIMARY | Filename | eq_ref | PRIMARY | PRIMARY | 4 > | Temp.FilenameId | 1 | | > | 1 | PRIMARY | File | eq_ref | PRIMARY | PRIMARY | 8 > | Temp.FileId | 1 | Using where | > | 2 | DERIVED | <derived3> | ALL | NULL | NULL | NULL > | NULL | 60905 | Using temporary; Using filesort | > | 3 | DERIVED | File | ref | JobId,JobId_2 | JobId_2 | 4 > | | 52471 | | > +----+-------------+------------+--------+---------------+---------+---------+-----------------+-------+---------------------------------+ > 6 rows in set (6.99 secs) This is a MyISAM catalog with 14776513 Files, 1163114 FileNames, and 198492 Paths. Machine is a Dual-Core Opteron with 2GB RAM and a decent disk subsystem. MySQL is not exactly configured for maximum performance. Hope this helps you, Arno -- Arno Lehmann IT-Service Lehmann Sandstr. 6, 49080 Osnabrück www.its-lehmann.de ------------------------------------------------------------------------------ Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users