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

Re: [Bacula-devel] small mysql patch

On Monday 01 September 2008 08:51:48 Kern Sibbald wrote:
> Hello,
> Thanks for the patch.  I see no problem applying it as it is a comment,
> which is a suggestion.  However, as Eric has said, it may not be such a
> good idea for users to turn these indexes on since, as he points out, they
> will probably leave them.
> I like Eric's suggestion that dbcheck automatically (possibly with an
> option, or by requesting permission) create and then delete the indexes
> that are needed for speed.
> Or perhaps we can speed up dbcheck itself.  The current SQL was written by
> me and is *far* from optimal.  Marc Cousin has rewritten the dbcheck sql in
> a script file in <bacula-source>/examples/database/dbcheck.sql, which he
> says runs *much* faster.  In the short run, I would recommend trying out
> this script.  In the long run, when some developer steps forward, or we
> finish our 3.0.0 work, I would like to integrate his ideas into dbcheck.
> Please see more notes below ...

The script version won't work better with mysql without indexes.

The reason is that the script works with global queries instead of many small 
queries like dbcheck. So PostgreSQL can optimise the query by building hash 
joins or merge joins.

Mysql can't do that (last time I checked, at least ...), and will do nested 
loops between job and file for instance. And without the missing indexes, 
mysql will still be as slow as with dbcheck, as you'll more or less have 
thousands of full scans on the job table (where postgresql will do only a few 
to build its hash).

So for dbcheck with mysql, there is no other solution than adding the missing 
indexes (but adding and dropping them just for the dbcheck is a good option).

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