Magento loves using lots of mySQL processes, so therefore mySQL needs to finely tuned to achieve this.

We ran some benchmarking tests today using mysqlreport as the benchmarking tool and as you can see from the results below, mySQL caching following some of the tips in the articles below can really make a difference

1. First test

As you can see with basic caching turned on and no massive tuning we get a cache insert to prune rate of 1.31:1 and no real cache hits

query_cache_size = 8M
table_cache = 128
innodb_buffer_pool_size = 8M

__ Query Cache _________________________________________________________
Memory usage 5.89M of 8.00M %Used: 73.58
Block Fragmnt 14.66%
Hits 1.21M 157.7/s
Inserts 389.92k 50.7/s
Insrt:Prune 1.31:1 11.9/s
Hit:Insert

2. Second test with recommendations from mysqltuner.pl and mysqlreport-3.5

Bigger cache better results

query_cache_size = 32M
table_cache = 512
innodb_buffer_pool_size = 256M

__ Query Cache _________________________________________________________
Memory usage 23.79M of 32.00M %Used: 74.33
Block Fragmnt 12.43%
Hits 3.50M 184.3/s
Inserts 681.81k 35.9/s
Insrt:Prune 2.55:1 21.8/s
Hit:Insert 5.13:1

Hit versus Insert of 5.13 to 1 – now that should make the world a much faster place 🙂

References:

http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/
http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/
http://www.techiecorner.com/45/turn-on-mysql-query-cache-to-speed-up-mysql-query-performance/
http://webcache.googleusercontent.com/search?q=cache:wVQfPOsS3t0J:www.debianhelp.co.uk/mysqlperformance.htm+mysql+optomise&cd=10&hl=en&ct=clnk&gl=uk&client=firefox-a&source=www.google.co.uk