InnoDB Performance OptimizationI recently stumbled upon a post that Peter Zaitsev published back in 2007 titled “Innodb Performance Optimization Basics.” It’s a great post and reading it inspired me to examine what’s changed in the nearly six years that have followed in terms of MySQL, Percona Server – as well as in all of the other now-available infrastructures.

And a lot has in fact changed! In this post I am going to highlight most of the InnoDB parameters critical for InnoDB – specifically from a performance perspective. I’m a support engineer and I can tell you that Percona Support gets many questions related to the right sizing of basic InnoDB parameters.
So hopefully this post will help others with similar questions and issues.

For larger datasets, nowadays memory counted in hundreds of giga- and even in terabytes is not surprising. MySQL requires significant memory amounts in order to provide optimal performance. By caching hot datasets, indexes, and ongoing changes, InnoDB is able to provide faster response times and utilize disk IO in a much more optimal way. From a CPU standpoint, faster processors with many cores provide better throughput. CPUs with 32/64 cores or more are becoming common now, and the latest MySQL versions are able to utilize them much better then before. In terms of storage, SSD disks are replacing traditional spindles with great success, offering the best performance for the money. RAID 10 is still the most recommended level for most workloads, but first make sure your RAID controller is able to utilize the SSD drive’s performance and will not become the actual bottleneck. There are also many PCI-e Flash drives out there if you need even more IOPS.

Operating System:
Linux is the most common operating system for high performance MySQL servers. Make sure to use modern filesystems, like EXT4 or XFS on Linux, combined with the most recent kernel. Each of them has it’s own limits and advantages: for example XFS is fast in deleting large files, while EXT4 can provide better performance on fast SSD drives. Benchmark before you decide. Check this blog post to see how EXT4 can outperform XFS. You can use noatime and nodiratime options if you’re using innodb_file_per_table and a lot of tables though benefit of these is minor. The default I/O scheduler in Linux is Completely Fair Queuing (CFQ), while Noop/Deadline will be much better in most cases.. Setting swappiness to zero is generally recommended for the MySQL dedicated host, which will lower the tendency of swapping. Make sure the MySQL host does not run out of memory. Swapping is bad for MySQL and defeats the purpose of caching in memory. To learn more about swapping, check this blog post

MySQL Innodb Settings
From 5.5 InnoDB is the default engine, so these parameters are even more important for performance than before. The most important ones are:

  • innodb_buffer_pool_size: InnoDB relies heavily on the buffer pool and should be set correctly, so be sure to allocate enough memory to it. Typically a good value is 70%-80% of available memory. More precisely, if you have RAM bigger than your dataset setting it bit larger should be appropriate with that keep in account of your database growth and re-adjust innodb buffer pool size accordingly. Further, there is improvement in code for InnoDB buffer scalability if you are using Percona Server 5.1 or Percona Server 5.5 You can read more about it here.
  • innodb_buffer_pool_instances: Multiple innodb buffer pools introduced in InnoDB 1.1 and MySQL 5.5. In MySQL 5.5 the default value for it was 1 which is changed to 8 as new default value in MySQL 5.6. Minimum innodb_buffer_pool_instances should be lie between 1 (minimum) & 64 (maximum). Enabling innodb_buffer_pool_instances is useful in highly concurrent workload as it may reduce contention of the global mutexes.
  • Dump/Restore Buffer Pool: This feature speed up restarts by saving and restoring the contents of the buffer pool. This feature is first introduced in Percona Server 5.5 you can read about it here. Also Vadim benchmark this feature You can read more about it in this post. Oracle MySQL also introduced it in version 5.6, To automatically dump the database at startup and shutdown set innodb_buffer_pool_dump_at_shutdown & innodb_buffer_pool_load_at_startup parameters to ON.
  • innodb_log_file_size: Large enough InnoDB transaction logs are crucial for good, stable write performance. But also larger log files means that recovery process will slower in case of crash. However this is not such big issue since great improvements in 5.5. Default value has been changed in MySQL 5.6 to 50 MB from 5 MB (old default), but it’s still too small size for many workloads. Also, in MySQL 5.6, if innodb_log_file_size is changed between restarts then MySQL will automatically resize the logs to match the new desired size during the startup process. Combined log file size is increased to almost 512 GB in MySQL 5.6 from 4 GB. To get the optimal logfile size please check this blog post.
  • innodb_log_buffer_size: Innodb writes changed data record into lt’s log buffer, which kept in memory and it saves disk I/O for large transactions as it not need to write the log of changes to disk before transaction commit. 4 MB – 8 MB is good start unless you write a lot of huge blobs.
  • innodb_flush_log_at_trx_commit: When innodb_flush_log_at_trx_commit is set to 1 the log buffer is flushed on every transaction commit to the log file on disk and provides maximum data integrity but it also has performance impact. Setting it to 2 means log buffer is flushed to OS file cache on every transaction commit. The implication of 2 is optimal and improve performance if you are not concerning ACID and can lose transactions for last second or two in case of OS crashes.
  • innodb_thread_concurrency: With improvements to the InnoDB engine, it is recommended to allow the engine to control the concurrency by keeping it to default value (which is zero). If you see concurrency issues, you can tune this variable. A recommended value is 2 times the number of CPUs plus the number of disks. It’s dynamic variable means it can set without restarting MySQL server.
  • innodb_flush_method: DIRECT_IO relieves I/O pressure. Direct I/O is not cached, If it set to O_DIRECT avoids double buffering with buffer pool and filesystem cache. Given that you have hardware RAID controller and battery-backed write cache.
  • innodb_file_per_table: innodb_file_per_table is ON by default from MySQL 5.6. This is usually recommended as it avoids having a huge shared tablespace and as it allows you to reclaim space when you drop or truncate a table. Separate tablespace also benefits for Xtrabackup partial backup scheme.

Along with that, there are lot of enhancements for InnoDB, specifically in Percona Server 5.5 and in Oracle MySQL 5.6. Persistent optimizer statistics is one of the features first introduced in Percona Server 5.5 that requires the enabling of the innodb_use_sys_stats_table in XtraDB. You can read more about it here. This feature is now included in Oracle MySQL 5.6, too. In MySQL 5.6 persistent stats are stored in two new tables: mysql.innodb_index_stats and mysql.innodb_table_stats. Through this query plans are much more accurate and consistent. You can read more about it in documentation. Also Percona Server 5.5 introduced a Thread Pool feature which is ported from MariaDB. You can read more about it in this documentation. On a related note, I recommend reading this blog post from Vadim on the Thread Pool feature.

Percona Server free and open source. An enhanced drop in Oracle MySQL replacement and some of the mentioned features are only applicable to Percona Server.

There are bunch of other options which you may want to tune but in this post we focus only InnoDB specifically.

Application tuning for Innodb:
Especially when coming from a MyISAM background, there will be some changes you would like to make with your application. First make sure you’re using transactions when doing updates, both for sake of consistency and to get better performance. Next if your application has any writes be prepared to handle deadlocks which may happen. Third you should review your table structure and see how you can get advantage of Innodb properties – clustering by primary key, having primary key in all indexes (so keep primary key short), fast lookups by primary keys (try to use it in joins), large unpacked indexes (try to be easy on indexes).

We covered almost all basic and important InnoDB parameters, OS related tweaking and hardware for optimal MySQL server performance. By setting all mentioned variables appropriately certainly help to boost overall MySQL server performance.

More Resources


eBooks (free to download)

Database Tools

Newest Most Voted
Inline Feedbacks
View all comments

Would you say that a value of innodb_flush_log_at_trx_commit = 1 is the best option when using MySQL multi-master replication (either natively or with XtraDB cluster?)

Peter (Stig) Edwards

Probably not “basic” but maybe worth mentioning in passing. Under hardware changes: CPU frequency scaling / (CPU) power profiles, with many cores the vendor bios defaults may not be suitable for mysqld with high performance requirements. NUMA architecture considerations. Fusion-io atomic writes. Under OS: transparent huge pages, especially when mysqld is using newer memory allocators.


I’ve found innodb_adaptive_hash_index_partitions also is very useful in concurrent workloads (at least in 5.5)


Hello, and thank you for all this great posts,
I have a sever running mainly InnoDb
3 Magento stores ~ 20 Gb (InnoDb)
1 Dbmail ~ 150Gb (InnoDb)
20 Websites with MyIsam database ~ 1Gb

My server:
vendor_id : GenuineIntel
cpu family : 6
model : 42
model name : Intel(R) Core(TM) i5-2500 CPU @ 3.30GHz
stepping : 7
microcode : 0x28
cpu MHz : 1600.000
cache size : 6144 KB
Memory : 16Gb ddrIII
Hdd : 1800 Gb raid 10

my.cnf :
innodb_additional_mem_pool_size = 16M
innodb_log_buffer_size = 16M
innodb_buffer_pool_size = 4G
innodb_log_file_size = 512M
innodb_use_sys_malloc =0
max_user_connections = 20
max_connections = 30
wait_timeout = 10
interactive_timeout = 5
connect_timeout = 2
query_cache_size = 512M
query_cache_limit = 4M
join_buffer_size = 128M
thread_cache_size = 16k
table_cache = 3072
key_buffer_size = 64M
sort_buffer_size = 32M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
low_priority_updates = 1
concurrent_insert = ALWAYS
innodb_buffer_pool_instances = 4
query_cache_min_res_unit = 1K
open-files-limit = 8192
innodb_lock_wait_timeout = 60
innodb_thread_concurrency = 0
innodb_write_io_threads = 64
innodb_read_io_threads = 64
myisam_repair_threads = 1
bulk_insert_buffer_size = 64M
tmp_table_size = 64M
transaction_isolation = REPEATABLE-READ
thread_stack = 240K
table_open_cache = 3072
back_log = 50
table_definition_cache = 3072
tmp_table_size = 128M
max_heap_table_size = 128M
slow_query_log = 1
long_query_time = 3
slow_query_log_file = /var/log/mariadb-slow.log
myisam_sort_buffer_size= 256M
symbolic-links = 0
max_allowed_packet = 128M
thread_concurrency = 4

the server is performing well, you notice I don’t use •innodb_flush_method: DIRECT_IO because after I put this the server slows down a lot, I didn’t use also: •innodb_file_per_table because I canno’t shut down server now to dump/restore tables.
So my question is: what can I do now to tune up more (speed up the server)?
Thank you.


i think innodb-purge-threads is worth to mention here. In versions prior to 5.5 this purge function was part of the responsibility of the InnoDB master thread. A high load on the server that dirtied a lot of pages would force the master thread to spend most of its time flushing and therefore no purging would get done and vice a versa.
In 5.5 there is an option innodb-purge-threads=[0,1] to create a dedicated thread that purges asynchronously if there are UNDO logs that need to be removed. In 5.6 we have the same parameters as 5.5 except that innodb-purge-threads can now be between 0 and 32.
it is usually better to have a single dedicated purge thread unless you are using partitions. With multiple tables the purge threads come into their own and can purge records in parallel with minimal contention.



We have a sever running a website Joomla 2.5. We are facing frequent mysql crashes that occur when website has medium visitor load like a few thousands. It appears that mysql consumes more memory and is killed by OOM killer. Currently if I look for memory consumption for mysql using top command, it says that mysql process is consuming 17% of memory. Normally the server shows upto 45% of total memory used. But when mysql crashes, system memory is recorded to be between 70% to 95% or sometimes 100%.

I have a feeling that its mysql memory settings (I think we have set huge values for different variables) which cause it to consume that much memory at peak traffic hours. Or it is the myisam tables in Joomla 2.5 that are causing it to crash due to locking of tables. Please suggest your thought.

Our server:
CPU GenuineIntel, Intel(R) Xeon(R)CPU E5620 @ 2.40GHzcpu MHz : 1600.000
RAM : 1Gb
Hdd : 50Gb
OS Ubuntu 10.04.4 LTS

my.cnf :
max_connections = 250
thread_cache_size = 8
table_cache =
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
key_buffer = 16M
read_buffer_size = 131072
join_buffer_size = 131072
connect_timeout = 10
max_connect_errors = 10
max_allowed_packet = 16777216
tmp_table_size = 16M




along with innodb_flush_log_at_trx_commit:.. sync_binlog , innodb double write buffer are other variable to consider if not worrying much about crashes and data loss


While reading the comments, I noticed that you have a problem with your Joomla website crashing because of MySQL. I think before anything, you will need a much more powerful server. You have 1 GB of RAM when you need at least 16 GB of RAM to serve thousand of visitors every day. You will also need an SSD drive as Joomla causes MySQL to write to a lot of temporary files (see the issue we faced here when a client was switch from SSD to a larger HDD: )

Additionally, there are some core optimizations on a Joomla website that you must do in order to handle the load. Joomla 2.5 and up (currently we are at version 3.5.1) are much heavier on the database than Joomla 1.5, and, without core optimizations, you will soon run into MySQL issues regardless of how much memory you have on your server (if you have a large website with high traffic). This is caused by the fact that some queries are run twice (one for the data selection and one for the the count of the data in the data selection), a DoS magnet search engine (searching on large websites puts a huge pressure on the server), and a must-have and always-growing assets table – to name some issues. You will need to a huge optimization in the core for a large website to tolerate a substantial amount of traffic. Additionally, you will need to switch to Sphinx or Solr for searches, and, you must do some cleanup on the assets table every day (in a cron job).

Joomla 2.5 and 3.5 are very powerful, but that comes at a price: performance. You will need to modify the core in order to have the best of both worlds. We currently use MyISAM for the large Joomla websites that we manage (not for all the tables though, for the session table we use the MEMORY), and we are doing that for performance reasons (we had some serious performance problems with InnoDB). Nevertheless, we will follow the tips here and will hopefully report back positively after switching the first website.


Hi Irfan,
I have one query in percona xtradb cluster. We have a setup of 6 node cluster, in which we are using one as a master node and others as a slave nodes(only for reading). I want to ask like if we use sync_binlog=0 and innodb_flush_log_at_trx_commit = 2. will it increase some performance on reading nodes. what will be the effect of these settings for select queries. some times we face lot of spikes due to multiple select queries.