[EMAIL PROTECTED] 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 _______________________________________________ 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
