NYCPHP Meetup

[Kenneth Downs]

Hans C. Kaspersetz wrote:
> 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.
>
> 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.
>

This is a pretty standard approach to large loads for all database 
vendors, MS SQL, DB/2, Oracle, Postgres, etc.

The slowdown comes from the fact that you are updating not just the 
table but the indexes.  The indexes in particular tend to become 
unbalanced quickly and they start to chew up a lot of time.

The standard approaches are:

1)  As done above, drop indexes before load
2)  Break up load to go row-at-a-time.  This is still slow, but it 
causes the loading program to be a "good citizen" and not choke the 
database or require dropping indexes.

-- 
Kenneth Downs
Secure Data Software, Inc.
www.secdat.com / www.andromeda-project.org
Office: 631-689-7200   Cell: 631-379-0010

::Think you may have a problem with programming? Ask yourself this 
::question: do you worry about how to throw away a garbage can?