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

Re: [Bacula-devel] small mysql patch


On Monday 01 September 2008 11:30:58 Marc Cousin wrote:
> 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 ...

Thanks.  I just added those notes to the script.

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