[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.
> 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
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
Bacula-devel mailing list
This mailing list archive is a service of Copilotco.