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

[Bacula-devel] small dbcheck patch for mysql


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?


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