NYCPHP Meetup

[Hans C. Kaspersetz]

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.

Hans K

The machine is a Dual 2.8Ghz Xeon with 4GB Ram.

---  Start my.cnf -------
[mysqld]
innodb_data_home_dir =
innodb_data_file_path = /foo/mysql/data/ibdata1:100M:autoextend
set-variable = innodb_buffer_pool_size=2000M
set-variable = innodb_additional_mem_pool=500M
set-variable = innodb_log_file_size=150M
set-variable = innodb_log_buffer_size=8M

#innodb_flush_log_at_trx_commit=1

socket=/var/lib/mysql/mysql.sock
datadir=/foo/mysql/data/

[client]
socket=/var/lib/mysql/mysql.sock
#EOF