[Koha] data import question

[Christopher Hicks]

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)