MySQL Forums
Forum List  »  InnoDB

Re: Need help to improve innodb performance
Posted by: Rick James
Date: March 29, 2009 11:00PM

Turn off HyperThreading -- The two cores fight each other over the shared cache. But that does not matter, since you are not CPU bound.

Batch your inserts -- either by periodically building a .csv file and doing LOAD, or by using multi-row INSERTs. Even 100 rows at a time would probably give you a 10x improvement.

Another thought (in your case) is to batch things through a MEMORY table -- insert into it, then periodically do
INSERT INTO innodb_table SELECT * FROM memory_table;

Minimize the number of keys on the InnoDB table. In particular, see if you can get away with just an AUTO_INCREMENT PRIMARY KEY. (For reporting, build summary table.)

Let's review these:
SHOW VARIABLES LIKE 'innodb%';

64-bit MySQL on 64-bit OS, right? innodb_buffer_pool_size is at least 13GB?

Shrink the data. Use MEDIUMINT instead of INT when possible, etc.

Do you have TEXT or BLOB fields? Compress them before handing them to MySQL. This will cut down on disk I/O.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Need help to improve innodb performance
3031
March 29, 2009 11:00PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.