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
_______________________________________________
New York PHP Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com
Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php
