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 time). ------------------------------------------------------------------------- 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 [email protected] https://lists.sourceforge.net/lists/listinfo/bacula-devel
