Top My.CNF Optimizations for Innodb
These are the four parameters which will have the most affect on the performance of MySQL 5.1x and Innodb. If you are suffering from poor performance, try changing the following settings. Remember, each individual situation will vary, and in many cases, the actual design of your queries will have more to do with your overall performance than any system tuning tricks. The best tool I have found to capture, review, and analyse query performance for MySQL is Jet Profiler for MySQL. If you need help optimizing your queries, let us know.
innodb_buffer_pool_size =8G
Set this to ~80% of free server memory. For example, if you have a dedicated MySQL server with 10G, set to 8G
innodb_flush_log_at_trx_commit =0
Setting this to 0 will have a huge performance improvement, however your data is at somewhat more if you have a hardware failure
sync_binlog =0
Setting this to 0 will have a huge performance improvement, however your data is at somewhat more if you have a hardware failure
On many systems, this will provide a performance improvement. However, this can actually have a negative affect, so make sure you test appropriately.