October 20, 2014

InnoDB performance optimization basics (redux)

InnoDB performance optimization basicsI 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.

Hardware:
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).

Conclusion:
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.

About Muhammad Irfan

Muhammad Irfan is vastly experienced in LAMP Stack. Prior to joining Percona Support, he worked in the role of MySQL DBA & LAMP Administrator, maintained high traffic websites, and worked as a Consultant. His professional interests focus on MySQL scalability and on performance optimization.

Comments

  1. mig5 says:

    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?)

  2. Peter (Stig) Edwards says:

    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.

  3. Oleksii says:

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

  4. Mihai says:

    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.

  5. aftab says:

    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.

  6. Ridda says:

    Hello,

    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

    innodb_buffer_pool_size=512M
    innodb_additional_mem_pool_size=40M
    innodb_log_buffer_size=40M
    innodb_thread_concurrency=2
    innodb_flush_log_at_trx_commit=1

    Regards,
    Ridda.

Speak Your Mind

*