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. 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
Setting this to 0 will have a huge performance improvement, however your data is at somewhat more if you have a hardware failure
innodb_flush_method=O_DIRECT
On many systems, this will provide a performance improvement. However, this can actually have a negative affect, so make sure you test appropriately.
On many systems, this will provide a performance improvement. However, this can actually have a negative affect, so make sure you test appropriately.
Related articles
- MySQL – Disabling Binary Logging for INSERT and UPDATE (prodromus.com)