Tuning MySQL | News | PHPro
What's new in our world

Tuning MySQL

16 February 2012

When tuning mysql you will have to edit it's configuration. 
The Mysql configuration can be found in the /etc/mysql/my.cnf file.
Before you start editing the the configuration file it is probably a good idea to know how well the Mysql server is running at the moment. That's where the mysqlreport script comes in.

More information on interpreting the output generated by mysqlreport can be found on: http://hackmysql.com/mysqlreportguide


mysqlreport --user USER –password you will be prompted for a password.


You want to tune your mysql configuration for a magento store.
Since magento uses the innodb storage engine, the below trimmed output from mysqlreport is quite important.

__ InnoDB Buffer Pool __________________________________________________

Usage 31.98M of 32.00M %Used: 99.95 
Read hit 99.57%
  Free            1            %Total: 0.05
  Data            2.00k    97.85 %Drty: 0.00
  Misc            43          2.10
  Latched      0            0.00
Reads          3.67M    275.5/s
  From file    15.64k   1.2/s 0.43
  Ahead Rnd   689    0.1/s
  Ahead Sql       28    0.0/s
Writes           18.62k  1.4/s
Flushes          3.56k   0.3/s
Wait Free              0   0/s

In the above displayed output you can see 99.95 % of the innodb_buffer_size_pool is in use.

In my.cnf you can find a variable called innodb_buffer_pool_size which specifies the size of the buffer pool. If your buffer pool is small and you have sufficient memory, making the pool larger can improve performance by reducing the amount of disk I/O needed as queries access innodb tables.

In this case we would have to increase the buffer pool size.