At 11:39 AM -0500 2/28/07, 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.
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
That's the type of question I would ask the MySQL list.
List-Archive: <http://lists.nyphp.org/pipermail/mysql>
List-Post: <mailto:[email protected]>
List-Help: <mailto:[EMAIL PROTECTED]>
List-Subscribe: <http://lists.nyphp.org/mailman/listinfo/mysql>,
<mailto:[EMAIL PROTECTED]>
tedd
--
-------
http://sperling.com http://ancientstones.com http://earthstones.com
_______________________________________________
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
