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

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


Hello Yuri,

I have now applied your patch and committed it to the SVN.  I made a few
minor changes to reduce the number of #ifdefs in the body of the code.  I 
would appreciate it if you would test the new code and let me know.

Best regards,

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


This mailing list archive is a service of Copilot Consulting.