Re: [Bacula-users] Very slow interactive restore

Christoph Litauer schrieb:
> 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?
> 

Seems as if I found the reason: I had been running version 3.0.2 which
uses a comlicated sql statement as the above to build the directory
tree. Version 3.0.3 uses more but simpler sql queries ...

I read the release notes for this version once again but couldn't find
any hint regarding that fix.

-- 
Kind regards
Christoph
________________________________________________________________________
Christoph Litauer                  litauer AT uni-koblenz DOT 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


------------------------------------------------------------------------------
Join us December 9, 2009 for the Red Hat Virtual Experience,
a free event focused on virtualization and cloud computing. 
Attend in-depth sessions from your desk. Your couch. Anywhere.
http://p.sf.net/sfu/redhat-sfdev2dev
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users