NYCPHP Meetup

[Carlos A Hoyos]

talk-bounces at lists.nyphp.org wrote on 02/28/2007 11:39:25 AM:

> There was some intense discussion last night at TGIF about MySQL tuning
> and some problems my group has been having with inserts into InnoDB
> tables.  I was asked to provide our my.cnf for review.

Indeed it was. Funny how things seem more interesting at that venue ;-)

>
> We are doing a big data import and the problem was inserts were taking
> up to 20 seconds on a table of 8 Million rows, which didn't seem right
> as we have tables in excess of 25 million rows in the same database.
> There were two indexes: Primary and a compound index of two string
> columns (32bit String MD5 + varchar50).  The combination has a
> cardinality of about 400,000.  Dropping the indexes before we started
> our big import sped thing up considerably.  We now drop the index, run
> the big import, and then rebuild the indexes.  This solution doesn't
> really address the real problem, which I haven't quite figured out.  Any
> further guidance is appreciated.
How are you loading the data? When doing bulk loads, mysql is usually smart
to load everything first and then rebuild the indexes. But unique indexes
are checked at once.

Some tips that have worked for me:
- If you're exporting the data from another source, try if possible to
export it sorted by your primary key.
- Increase key_buffer so that your index will fit on it.

Carlos Hoyos