Return to Answer

#EPILOGUE

###SUGGESTION #4

You should increase the innodb_log_buffer_size to 64M since the default is 8M. A bigger log buffer may increase InnoDB write I/O performance.

#EPILOGUE

#SUGGESTIONS

Get rid of indexes you do not use. Look over your application code and see if your queries use all the indexes. You may want to look into pt-index_usage to let it suggest what indexes are not being used.

Don't forget that importing 10 million rows in a single transaction will pile up everything into one rollback segment and fill up the UNDO space in ibdata1.

#SUGGESTIONS

Get rid of indexes you do not use. Look over your application code and see if your queries use all the indexes. You may want to look into pt-index-usage to let it suggest what indexes are not being used.

#ALTERNATIVE

You could create a table called monster_csv as a MyISAM table with no indexes and do this:

CREATE TABLE monster_csv ENGINE=MyISAM AS SELECT * FROM monster WHERE 1=2;
ALTER TABLE monster RENAME monster_old;
CREATE TABLE monster LIKE monster_old;
ALTER TABLE monster DROP INDEX `party_id`;

Import your data into monster_csv. Then, use mysqldump to create another import

mysqldump -t -uroot -p mydb monster_csv | sed 's/monster_csv/monster/g' > data.sql

The mysqldump file data.sql will extended INSERT commands importing 10,000-20,000 rows at a time.

Now, just load the mysqldump

mysql -uroot -p mydb < data.sql

Finally, get rid of the MyISAM table

DROP TABLE monster_csv;