Improving MySQL Performance with Advanced InnoDB Settings

We have discussed how to configure InnoDB for high performance a while ago, however, we have not yet discussed how we can improve MySQL performance while making use of advanced InnoDB settings. This blog post should provide a little more insight into this topic.

InnoDB Explained

Before we actually dive deeper into InnoDB settings, we should probably understand the basics: InnoDB is a storage engine for MySQL, MariaDB, and Percona Server. The engine was known as InnoDB Plugin, which requires setup and installation of the plugion. Until the release of MySQL 5.5.5, InnoDB is no longer a plugin and is now part of the MySQL package as one of the supported storage engines for MySQL. Since the release of MySQL 5.6,  InnoDB has become the default storage engine – it’s a general-purpose storage engine that balances high reliability and high performance. The key advantages of InnoDB include supporting row-level locking, foreign keys and following the ACID (Atomicity Consistency Isolation Durability) model – ACID is a set of properties that are intended to guarantee data validity despite errors, power failures and other issues. InnoDB has an extensive list of variables and some of these help improve performance especially on the type of hardware and available resources of your database server. Among these are:

• innodb_data_file_path is the file where data from InnoDB tables is stored.
• innodb_buffer_pool_size is a memory buffer that InnoDB uses to cache data and indexes of its tables.
• innodb_log_file_size depicts the size of InnoDB log files. The larger innodb_log_file_size is, the longer the recovery time you need in case of a crash.
• innodb_log_buffer_size is used by InnoDB to write to the log files on disk.
• innodb_flush_log_at_trx_commit controls the balance between performance and ACID compliance. The default value is 1, which helps keep InnoDB ACID compliant – turning innodb_flush_log_at_trx_commit to 2 gets a very fast write speed, but up to one second’s worth of transactions can be lost.

There are also advanced InnoDB settings that can be set to improve MySQL performance even further. We will take a look into them now.

Advanced InnoDB Settings

As already noted above, InnoDB has advanced settings that can be used to further improve the performance of it (we will not list absolutely all of them, but the settings that are listed should give you a pretty good idea of just how powerful InnoDB really is):

• InnoDB can be disabled – if you want to disable InnoDB, simply modify the my.cnf file and add skip-innodb under the [mysqld] section. After that, restart your MySQL server – InnoDB should now be disabled. Alternatively, you can make use of the –innodb option: setting it to OFF disables the engine. Though do note that these options are deprecated as of MySQL 5.7.5.
• InnoDB also provides a configurable locking mechanism that can improve performance of SQL statements that add rows to tables with AUTO_INCREMENT columns: auto-increment modes can be configured at startup by using the innodb_autoinc_lock_mode option. The option has three settings for specifying the lock mode – the lock mode can be 0 (“traditional”), 1 (“consecutive”) or 2 (“interleaved”). Values offer performance depending on the type of database insert. For short, 0 offers compatibility with older versions of MySQL and Innodb. Value of 1 offers more safety and deterministic approach for statement-based replication (SBR). Whereas value of 2 has the more scalable and fastest lock mode yet rows inserted by any given statement may not be consecutive. Refer to the MySQL documentation for more information.
• InnoDB gives you the ability to divide the buffer pool into multiple segments (the feature is only available from MySQL 5.5) – the innodb_buffer_pool_instances setting enables you to improve MySQL’s scalability on machines that run multiple cores. By default, the value of this setting is 1 if the innodb_buffer_pool_size is less than 1GB and 8 otherwise: the number specifies the number of regions that the InnoDB buffer pool is divided into. This setting can be used to engage more cores, we will explain how later on.
• InnoDB offers four transaction isolation levels (tx_isolation in < 5.7 but transaction_isolation in version 5.7 onwards): READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE: these isolation levels are the “I” in the ACID acronym:
  • When READ UNCOMMITTED is in use, one transaction may see uncommitted changes by another transaction. This isolation level allows dirty reads.
  • When READ COMMITTED is in use, you can rest assured that any data that was read was committed at the moment it was read.
  • When REPEATABLE READ is in use, a higher isolation level is in use. In addition to everything that is guaranteed by the READ COMMITTED level, it also guarantees that any data that already got read cannot change.
  • When SERIALIZABLE is in use, an even higher isolation level is in use. In addition to everything that is guaranteed by the REPEATABLE READ isolation level, it also guarantees that no new data can be seen by subsequent reads.
• InnoDB also allows you to define the overall I/O capacity available to InnoDB by modifying the innodb_io_capacity variable. The value of this variable should be set to approximately the number of IOPS that the system can perform per second: when setting the value of this parameter, keep in mind that values around 100 are more appropriate for HDDs, while SSDs might benefit from higher values. The innodb_io_capacity_max variable can also be of assistance: this variable allows InnoDB to flush more aggressively, meaning that the rate of I/O operations might exceed the limit defined by innodb_io_capacity – in such situations, operations will not exceed the value defined by the innodb_io_capacity_max variable.
• InnoDB also allows you to control how many background threads are available for I/O operations: the number of I/O threads allocated to read operations can be controlled by the innodb_read_io_threads variable while the number of I/O threads allocated to write operations can be controlled by the innodb_write_io_threads variable. The default value for both of these parameters is 4 and the maximum allowed value is 64.
• InnoDB has the ability to turn certain InnoDB warnings into errors: to do so, simply set the innodb_strict_mode variable to ON: this variable affects the handling of syntax errors for CREATE TABLE, ALTER TABLE and CREATE INDEX operations: disabling this variable might solve “Row size too large” errors. To disable strict mode, set innodb_strict_mode to OFF.
• InnoDB can be somewhat protected against full table scans interfering with data cached in the buffer pool by increasing the innodb_old_blocks_time variable. The minimum value for this setting is 0, the default value is 1000.
• If you run maintenance operations on InnoDB tables that contain FULLTEXT indexes, consider turning the innodb_optimize_fulltext_only variable to ON – after this variable is enabled, the OPTIMIZE TABLE query should run faster because it will skip the reorganization of data in the table. Note that this setting is intended to only be enabled temporarily so you might want to turn it off once the optimization is finished.
• To start InnoDB in read-only mode, enable the innodb_read_only setting. When this setting is enabled, you can query InnoDB tables where the MySQL data directory is on read-only media.

Making InnoDB Engage More Cores

You can also make InnoDB engage more cores by taking advantage of its multithreading capabilities: surprisingly, this is not very hard to achieve – you just need to modify a couple of settings. Here’s how to do that:

1. Leave the innodb_thread_concurrency option at its default value 0. By doing so you let InnoDB decide the best number of concurrency tickets (they determine the number of threads that can concurrently enter InnoDB) to open for a given MySQL instance setup. For MariaDB starting 10.5, it’s mark as deprecated so it would make sense for MySQL to set this to 0 as computing resources have been sophisticated compared to the early days of MySQL.
2. Once the innodb_thread_concurrency option is set to 0, set both innodb_read_io_threads and innodb_write_io_threads to their maximum values of 64. This should engage more cores.

Summary

To summarize, InnoDB is an extremely powerful storage engine. The performance of this storage engine is directly impacted by the settings that this engine is using. So if you want to improve the performance of your MySQL instance, make sure to keep in mind at least a few of the tips mentioned in this article. Tuning the settings regarding the engine and using them when appropriate, doing it so should give you an advantage.