On Tue, 29 Jul 2003, paul POULAIN wrote:
If anyone has a magic sql statement to improve mySQL insert (maybe chapter 10 of mySQL doc could be helpfull) feel free to suggest... I suggest two ideas : * drop indexes (except pk) at beginning & rebuild them at end * lock table at beginning & unlock at end (LOCK TABLES XXX WRITE;)
I'd be interested to see how these actually work, but we do a lot of stuff with MySQL without doing either of those things and we don't have these sorts of problems. Every day we use Perl to transform several gigabytes of SQL into gigabytes of other SQL and MySQL has rarely been the bottle neck. There are a few things we do habitually that might make a difference to the original poster and for future development: + bulk inserts should be done as insert delayed to reduce the amount of I/O contention. I believe (but I'm too lazy to confirm) that "insert delayed" works less hard on maintaining the indexes as well. But whether it does now or if that's just a potential feature, bulk things should be flagged as such so MySQL can try to do the right thing. This makes a huge difference - a similar effect to what dropping indexes did for Oracle. (Of course I haven't touched Oracle since Oracle7 came out... tragedy. :) + configure MySQL properly. I get a bit miffed about this one because people on certain other mailling lists often flame MySQL for underperforming on the cheap fast hardware available easily these days. It's pretty simple to take one of the sample my.cnf files that comes with the RPM distribution and pick one that sounds similar to your machine. It can make a huge difference to let the caches grow. Since even good memory is pretty cheaps these days the last three database servers I've built have all had 4G of RAM so MySQL could be opened up full bore. But to return to Koha, a pointer to the appropriate MySQL documentation and the mod_perl guide would probably be a good start on a performance section. -- </chris> The death of democracy is not likely to be an assassination from ambush. It will be a slow extinction from apathy, indifference, and undernourishment. -Robert Maynard Hutchins, educator (1899-1977)