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

Inspired by the great presentations at the Magento Developers Paradise 2011 I’ve been following some of the code created there on GitHub and some of the presenters own projects and here’s 7 great Magento projects you should be following on GitHub:

Magento GitHub Sites

1. 2Boys1Shop / Twoboysoneshop_Configr

Multi-shop configurator shown at MDP 2011

https://github.com/2Boys1Shop/Twoboysoneshop_Configr

2. alistairstead / MageTool

Tool to quickly create now Mage extension frameworks

https://github.com/alistairstead/MageTool

3. dankocherga / MTool

Auto creates files for new Magento module

https://github.com/dankocherga/MTool
https://github.com/dankocherga/MTool/wiki/Manual

4. jirafe / magento-plugin

Magento plugin for Jirafe stats

https://github.com/jirafe/magento-plugin/wiki/Overview

5. firegento / firegento-dynamiccategory

Dynamic category

https://github.com/firegento/firegento-dynamiccategory

6. More Vanish options

http://www.kalenyuk.com.ua/magento-performance-optimization-with-varnish-cache-47.html
http://jirafeinc.tumblr.com/post/6392764424/varnish-caching-with-magento

7. Firegento

FireGento extension with core functionality for debugging Magento

https://github.com/firegento/firegento

The latest version of PHP 5.3 deprecates the ‘ereg’ function, which generates errors that break programs such as TimThumb, the automatic image thumbnail generator.

To fix this, replace the existing ‘ereg’ expressions with alternative functions as per below:

Replace:

if (ereg(‘http://’, $src) == true) {

With:

if (strpos (strtolower ($src), ‘http://’) !== false || strpos (strtolower ($src), ‘https://’) !== false) {

Replace:

if (ereg($site, $url_info[‘host’]) == true) {

With:

if (strpos (strtolower ($url_info[‘host’]), $site) !== false) {

and you are all good to go :->

References:

http://code.google.com/p/timthumb/
http://devthought.com/2009/06/09/fix-ereg-is-deprecated-errors-in-php-53/