Optimize InnoDB

Increasing the InnoDB buffer pool

InnoDB maintains a cache memory, called the buffer pool. With the Parameter innodb_buffer_pool_size you can define the size of this pool. On a dedicated database server, this pool can occupy up to 80% of total RAM. The higher you set this value, the less disk accesses are required to access the table data. But set the value not too large, since it can otherwise lead to bottlenecks.


The required size can be calculated with the following query:

SQL: Calculating innodb_buffer_pool_size
  1. SELECT CONCAT(ROUND(KBS/POWER(1024,
  2. IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
  3. SUBSTR(' KMG',IF(PowerOf1024<0,0,
  4. IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
  5. FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
  6. WHERE engine='InnoDB') A,
  7. (SELECT 3 PowerOf1024) B;

Transaction log

By default, the parameter innodb_flush_log_at_trx_commit is set to the value 1. In this case the transaction log is written after every commit to the hard disk. Because disk accesses are very slow, this costs plenty of speed. If you set the value to 2, then the commits are cached and delayed writing to the hard drive to 1x a second. This is a big time for hard disks and therefore the speed increases by a multiple.

Increasing the Autoextend value

The Parameter innodb_autoextend_increment should optimally 1-5% of the available disk space to be great (max value: 1000M). The lower the value the greater is the fragmentation. If you want to change this value, then shutdown the MySQL server before and make sure that that there are no information on outstanding transactions in the log (/var/lib/mysql/ibdata1). Copy the old log files to a safe place in case something goes wrong during the shutdown and you need the files to restore the tablespace. Then delete the old log files from the log file directory (/var/lib/mysql/ibdata1). After changing the parameter, you can restart the MySQL server. There are then automatically created new log files.

Log file

The innodb_log_file_size parameter should not exceed 25% of the size of innodb_buffer_pool_size. Large logs are faster in the normal mode. However, the recovery will take longer if an error occurs.

MySQL Performance Tuning Primer Script

A further help to optimize MySQL is the script tuning primer.sh by Matthew Montgomery (http://day32.com/MySQL/).


Download the script via wget http://www.day32.com/MySQL/tuning-primer.sh to your server and execute it.