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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-devel
