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.