Arno Lehmann schrieb: > 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.
Thanks a lot Arno. May I ask you too, how long an interactive restore of a big filesystem takes to build the directory tree? -- Kind regards Christoph ________________________________________________________________________ Christoph Litauer lita...@uni-koblenz.de Uni Koblenz, Computing Center, http://www.uni-koblenz.de/~litauer Postfach 201602, 56016 Koblenz Fon: +49 261 287-1311, Fax: -100 1311 PGP-Fingerprint: F39C E314 2650 650D 8092 9514 3A56 FBD8 79E3 27B2 ------------------------------------------------------------------------------ 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
