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

Re: [Bacula-devel] small mysql patch

On Monday 01 September 2008 02:38:30 Dan Langille wrote:
> Eric Bollengier wrote:
> > Hello Yuri,
> >
> > Adding indexes will probably speed up dbcheck, but it will slow down the
> > attribute insertion process and grow up the database.
> Do we have statistics on the performance slowdown?

I don't have some for bacula, or postgresql. I have some for mysql (from 'The 
Art Of SQL', by Stephane Faroult):
Comparing with a naked table, speed insertion is:
55% with just a PK
15% with a PK + an index
Around 10% with a PK + 3 indexes
(figures are similar with Oracle in the book)

> Seriously, not adding Foreign Keys or indexes because they slow things
> down is not a good decision.  Databases deal with indexes all the time.

Yes, but I've seen several apps getting extremely slow because they were 
over-indexed. And they weren't dealing with as high a insert/select ratio as 
bacula. Adding indexes in software like bacula, that does that many updates, 
must be done with care.

I agree with you about foreign keys from a theoretical point of view (never 
put garbage in a database, and the indexes associated with foreign keys are 
useful for queries anyway).
But having a dedicated index for every query (that's extreme, but I've seen 
it) isn't good. I know that's not what you're proposing, I just mean that 
what's important is to balance between the cost of storing the rows and the 
cost of retrieving the rows. In bacula, for most users, most of the rows 
won't ever be retrieved (at least, I hope so for them :) ).

So I think the dedicated indexes for dbcheck should be created and dropped by 
dbcheck if they aren't there : they would be wasting cpu, disk space and io 
the rest of the time, for no gain. And building an extra index on demand is 
much cheaper than maintaining it. The only problem is that building an index 
get  a full lock on the table in certain databases (so no backups during this 

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.