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