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

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


Kern Sibbald wrote:
> Hello,
> 
> Why didn't you also add the indexes for other databases such as PostgreSQL and 
> SQLite3?

For PostgreSQL, he said it did not have the performance issues that 
MySQL had.  I suspect PostgreSQL can use some other index that MySQL 
cannot.  See his NOTE below.

> 
> 
> Please go to:  www.bacula.org -> FSFE License
> 
> Print out two copies of the license, fill in the fields as described in the 
> notes, sign it, send the two copies by mail to the Zurich address, then 
> notify me by email that you have done so.
> 
> At that point, I can integrate your patch.
> 
> Kern
> 
> On Tuesday 09 September 2008 15:30:18 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?
> 
> 
> 
> -------------------------------------------------------------------------
> 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


-- 
Dan Langille

BSDCan - The Technical BSD Conference : http://www.bsdcan.org/
PGCon  - The PostgreSQL Conference:     http://www.pgcon.org/

-------------------------------------------------------------------------
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.