[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [Bacula-devel] small dbcheck patch for mysql


Sorry to jump in late here... but if both of
a) the dbceck takes a long time w/o the index
b) the index adds quickly, then the dbcheck runs quickly

I would have to agree with Kern's earlier suggestion that perhaps 
somebody should look at the SQL code in dbcheck to optimize it.  This 
also has the added benefit of not adding more db specific code, 
especially given the course the mysql Inc.

Aren't there any SQL gurus interested in diving into it?

    Bob

Dan Langille wrote:
> Yuri Timofeev wrote:
>> Hi
>>
>> I once again explored speed dbcheck for MySQL and received steady results.
>>
>> Servers configurations :
>> production server : Fedora Core 6, 2.6.22.9-61.fc6,
>> bacula-mysql-2.2.8-2, mysql-5.0.27
>>    about 7M records in File table.
>>
>> test server : Fedora Core 8, 2.6.25.10-47.fc8, bacula-mysql-2.2.8-2,
>> mysql-5.0.45-6
>>    about 3M records in File table.
>>
>> Tests were conducted on both servers
>>
>> NOTE: In PostgreSQL does not have problems. It may the MySQL not have
>> very good optimizer.
>>
>> 1. Table File, Path have are only indexes, described in
>> src/cats/make_*_tables.in
>>    dbcheck worked was slow with option:
>>
>>    9) Check for orphaned Path records
>>
>> I waited for 30-40 minutes and kill dbcheck, since I knew that it can
>> work very long - 3 days or more.
>>
>> In any case:
>> (time spent at creating the index + work dbcheck + removal index) less
>> than (work time dbcheck without index)
>>
>> After the creation index (index creation took 20 minutes):
>> CREATE INDEX idxPI ON File (PathId);
>> dbcheck began to work much faster - approximately 5-10 min.
>>
>>
>> 2. Continue.
>>    dbcheck worked was slow with option:
>>
>>    10) Check for orphaned Filename records
>>
>> After the creation index:
>> CREATE INDEX idxFI  ON File (FilenameId);
>> dbcheck began to work much faster - approximately 5 min.
>>
>>
>>
>>
>>
>> So I made a patch for dbcheck for MySQL (patch may require a small
>> changes, but overall this patch is ready).
>>
>> Algorithm:
>> - when you specify "9) Check for orphaned Path records" or "10) Check
>> for orphaned Filename records" :
>> - verified the existence of the "one-column" index on the column
>> PathId (or FilenameId)
>> - if the index does not exist, then asked : "Create temporary index? (yes/no):"
>> - after that dbcheck working as usual
>> - temporary index removed.
>>
>> The patch is attached.
>>
>> What would you say?
> 
> Separate from the patch: suggest running some large backups, with and 
> without the index.  See the difference in the times for MySQL.  That may 
> help some people decide whether or not they want to run with the index 
> all the time.
> 
> 

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Bacula-devel mailing list
Bacula-devel@xxxxxxxxxxxxxxxxxxxxx
https://lists.sourceforge.net/lists/listinfo/bacula-devel


This mailing list archive is a service of Copilot Consulting.