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

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


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.


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