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

Re: [Bacula-devel] small mysql patch


Marc Cousin wrote:
> 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)

Interesting.  I find that hard to believe.  Yet I'm not tempted to 
verify these figures with some simple tests.

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

Care, yes.

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

We should not be having orphans.

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

You are right.  That is not what I'm suggesting.

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

dbcheck should never find problems.  If it does, there is something 
wrong with the code/database.

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