MySQL (innodb) performance tuning

Written by Gionatan Danti on . Posted in Linux & Unix

User Rating:  / 5
PoorBest 

Conclusions

At the end of this analysis, it is clear that a major performance improvement can be obtained by correctly sizing innodb_buffer_pool_size: going from the default value (8 MB) to a reasonable one (2 GB) increase database performance by as much as 10 times. On dedicated servers, mysql documentation recommend to set this value to ~80% of available memory, while on shared server you had to use 80% (or less) of mysqld maximum allowed memory.

Also pay attention to query cache settings: while on these benchmarks enabling it has no good effects, in specific circumstances it can led to significant performance gains.

Speaking about write speed, if you can tolerate an one-second data loss in the event of complete system crash / failure, you can set trx_commit to 2. In this manner you will be able to squeeze noticeably better write performance, at the cost of losing ACID compliance (in essence: in exchange of lower reliability).

I hope that this reading was interesting for you. If you wish, you can write me to This email address is being protected from spambots. You need JavaScript enabled to view it.

Have a nice day!

Comments   

 
#1 David van Enckevort 2012-06-09 00:25
Nice tests. Another good source of information you could mention is the MySQL Performance blog by the people from Percona: http://www.mysqlperformanceblog.com/
 
 
#2 Gionatan Danti 2012-06-11 15:54
Sure. It is a very renowned blog!

Thank you for posting the link here :)
 
 
#3 ykyuen 2012-07-26 11:23
Thanks for your blog post. it is very helpful and the content is easy to follow.

=D
 

You have no rights to post comments