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

[Bacula-devel] small dbcheck patch for mysql


I once again explored speed dbcheck for MySQL and received steady results.

Servers configurations :
production server : Fedora Core 6,,
bacula-mysql-2.2.8-2, mysql-5.0.27
   about 7M records in File table.

test server : Fedora Core 8,, bacula-mysql-2.2.8-2,
   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
   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).

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

with best regards

Attachment: dbcheck.path
Description: Binary data

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
Bacula-devel mailing list

This mailing list archive is a service of Copilot Consulting.