MySQL InnoDB performance improvement: InnoDB buffer pool instances - Updated!

Are you running into MySQL load problems? InnoDB Buffer Pool optimization is an important step in tuning MySQL servers for a heavy InnoDB workload. Configuring innodb_buffer_pool_instances and increase read/write I/O threads. Dividing the InnoDB buffer pool into multiple instances can improve Disk I/O. And by doing so, you run your database & server more efficiently and faster. Here is a little help for you.
Published on Friday, 10 June 2016
innodb linux mysql optimization query_cache

All for more InnoDB Disk I/O performance on MySQL 5.5+.

Tune MySQL InnoDB buffer pool instances and size for heavy workloads

Tuning MySQL servers is an ever ongoing process. Every new MySQL version brings new configuration settings you can use to improve its performance. As a MySQL DBA you want your database server and databases to perform better than well, don't you?

MariaDB/MySQL 5.5.4 introduces new configuration settings for the InnoDB storage engine. This can greatly improve MySQL's InnoDB performance, both in read and write operations.

One of those settings is innodb_buffer_pool_instances. The innodb_buffer_pool_instances divides the InnoDB buffer pool into separate instances. Dividing your buffer pool into separate instances can improve concurrency, by reducing contention as different threads read and write to cached pages. Multiple buffer pool instances are configured using the innodb_buffer_pool_instances configuration option.

You might also want to adjust the innodb_buffer_pool_size value:

The larger the InnoDB buffer pool, the more InnoDB acts like an in-memory database. It reads data from disk once and then accesses the data from memory during subsequent reads. Buffer pool size is configured using the innodb_buffer_pool_size configuration option.

Back to increasing InnoDB Buffer Pool instances with innodb_buffer_pool_instances.

The innodb_buffer_pool_instances divides the InnoDB buffer pool in a number of regions.

The number of regions that the InnoDB buffer pool is divided into. For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency, by reducing contention as different threads read and write to cached pages. Each page that is stored in or read from the buffer pool is assigned to one of the buffer pool instances randomly, using a hashing function. Each buffer pool manages its own free lists, flush lists, LRUs, and all other data structures connected to a buffer pool, and is protected by its own buffer pool mutex.

This option takes effect only when you set the innodb_buffer_pool_size to a size of 1 gigabyte or more. The total size you specify is divided among all the buffer pools. For best efficiency, specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1 gigabyte.

In MySQL versions prior to 5.5.4 this was not configurable and thus set to just one instance. Now you can increase innodb_buffer_pool_size, and you can divide the InnoDB buffer pool into multiple regions by setting innodb_buffer_pool_instances to 2, 3, 4 or 8. As long as innodb_buffer_pool_size is set high enough, and you have enough memory available in your MySQL database server. This increases InnoDB read/write threads.

To enable multiple buffer pool instances, set the innodb_buffer_pool_instances configuration option to a value greater than 1 (the default) and up to 64 (the maximum).

For example, you can set innodb_buffer_pool_size to 6 GB and innodb_buffer_pool_instances to 4 in your my.cnf MySQL configuration file:

; InnoDB buffer pool size in bytes. The primary value to adjust on a database server, 
; can be set up to 80% of the total memory in these environments
innodb_buffer_pool_size = 6000M

;  If innodb_buffer_pool_size is set to more than 1GB, innodb_buffer_pool_instances

; divides the InnoDB buffer pool into this many instances.
innodb_buffer_pool_instances = 4

In this example, I've used an innodb_buffer_pool_size of 6000M (6 GB), so there is 1500M available per innodb_buffer_pool_instance, which is more than the minimum 1 GB. As a rule of thumb, set your innodb_buffer_pool_size to approximately 70 - 80% of the RAM available.

Innodb_buffer_pool_instances defaults

Various MySQL versions have different innodb_buffer_pool_instances default values, here is an overview - listing - for you:

MySQL version # InnoDB buffer pool instances Notes
MySQL 5.5 (<= 5.5.4) 1 not configurable
MySQL 5.5 1 -
MySQL 5.6 (<= 5.6.5) 1 -
MySQL 5.6 (>= 5.6.6) 8 or 1 if innodb_buffer_pool_size < 1GB
MySQL >= 5.7 8 or 1 if innodb_buffer_pool_size < 1GB
MariaDB 10 (<= MariaDB 10.0.3) 1 -
MariaDB 10 (>= MariaDB 10.0.4) 8 -
MariaDB 10.5.1 - Deprecated and ignored
MariaDB 10.6.0 - Removed

The functionality described below was disabled in MariaDB 10.5, and removed in MariaDB 10.6, as the original reasons for for splitting the buffer pool have mostly gone away.

But there is more!

InnoDB read and write I/O threads in MySQL

Besides innodb_buffer_pool_instances, you can also increase the number of InnoDB read I/O threads and write I/O threads. These are configured with innodb_write_io_threads and innodb_read_io_threads.

Both settings default to 4 threads. We can increase these to, for example, 8:

; Number of I/O threads for writes
innodb_write_io_threads = 8
; Number of I/O threads for reads
innodb_read_io_threads = 8

innodb_read_io_threads

The number of I/O threads for read operations in InnoDB. The default value is 4.

innodb_write_io_threads

The number of I/O threads for write operations in InnoDB. The default value is 4.

When should you increase the number of innodb_read_io_threads? If you see more than 64 × innodb_read_io_threads pending read requests in SHOW ENGINE INNODB STATUS, you might gain by increasing the value of innodb_read_io_threads.

In the following example check out Pending normal aio reads, and aio writes. The values are 0 so no optimization is required:

MariaDB [(none)]> show engine innodb status;
...
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: complete io for buf page (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 1; buffer pool: 1
2055485 OS file reads, 15045015 OS file writes, 11313333 OS fsyncs
2.90 reads/s, 16384 avg bytes/read, 79.75 writes/s, 35.85 fsyncs/s

You can also inspect GLOBAL STATUS for the requested values:

MariaDB [(none)]> SHOW GLOBAL STATUS like "Innodb_data_pending_%";
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Innodb_data_pending_fsyncs | 0     |
| Innodb_data_pending_reads  | 0     |
| Innodb_data_pending_writes | 0     |
+----------------------------+-------+

Calculate a good InnoDB innodb_log_file_size

As a rough rule of thumb, you can make the log big enough that it can hold at most an hour or so of logs. That’s generally plenty of data for InnoDB to work with; an hour’s worth is more than enough so that it can reorder the writes to use sequential I/O during the flushing and checkpointing process.

Percona - How to calculate a good InnoDB log file size

It is important to set a proper InnoDB log file size in your my.cnf. You need to make the logs (there are two) are big enough to hold at most an hour of so of logs. That is more than enough so that it can reorder the writes to use sequential I/O during the flushing and checkpointing process.

You need to do some calculations and monitoring before you can set its value. Check the recommended innodb_log_file_size with the following command:

mysql -e 'show engine innodb status\G' | awk ' BEGIN { } /Log sequence number/ {st=$4 } /Last checkpoint at/ { ed=$4; print (st-ed)/1024/1024 } '

If the Checkpoint Age is getting close to Async point, consider to increasing innodb_log_file_size by 20% and check again. To make the above mysql command more understandable, execute the following statements on your MySQL shell/prompt:

MariaDB [(none)]> pager grep seq;
PAGER set to 'grep seq'
MariaDB [(none)]> show engine innodb status \G select sleep(60); show engine innodb status \G
Log sequence number 4588508004683
1 row in set (0.001 sec)

1 row in set (1 min 0.001 sec)

Log sequence number 4588514420341
1 row in set (0.001 sec)

MariaDB [(none)]> pager
Default pager wasn't set, using stdout.
MariaDB [(none)]> SELECT (4588514420341 - 4588508004683) * 60 / 1024 / 1024;
+----------------------------------------------------+
| (4588514420341 - 4588508004683) * 60 / 1024 / 1024 |
+----------------------------------------------------+
|                                       367.10689545 |
+----------------------------------------------------+
1 row in set (0.000 sec)

The outcome (~368) is the amount of MB's you need to be able to hold in your InnoDB log files. Set the InnoDB log file size using innodb_log_file_size in your my.cnf configuration file accordingly.

A second method to calculate the innodb_log_file_size is by querying information_schema.global_status:

SELECT
  innodb_os_log_written_per_minute*60
    AS estimated_innodb_os_log_written_per_hour,
  CONCAT(ROUND(innodb_os_log_written_per_minute*60/1024/1024, 1), 'MB')
    AS estimated_innodb_os_log_written_per_hour_mb
FROM
  (SELECT SUM(value) AS innodb_os_log_written_per_minute FROM (
    SELECT -VARIABLE_VALUE AS value
      FROM INFORMATION_SCHEMA.GLOBAL_STATUS
      WHERE VARIABLE_NAME = 'innodb_os_log_written'
    UNION ALL
    SELECT SLEEP(60)
      FROM DUAL
    UNION ALL
    SELECT VARIABLE_VALUE
      FROM INFORMATION_SCHEMA.GLOBAL_STATUS
      WHERE VARIABLE_NAME = 'innodb_os_log_written'
  ) s1
) s2
;

Its result is something like:

+------------------------------------------+---------------------------------------------+
| estimated_innodb_os_log_written_per_hour | estimated_innodb_os_log_written_per_hour_mb |
+------------------------------------------+---------------------------------------------+
|                                  9093120 | 8.7MB                                       |
+------------------------------------------+---------------------------------------------+

If both calculations have approximately the same amount as result you know you're on the right track :) .

After changing innodb_log_file_size you have to restart mysqld, and don't forget to backup and delete the current log files ib_logfile0 and ib_logfile1 first.

If you fail to delete the current logfiles first MySQL won't start and errors out:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 16777216 bytes!
111118 11:38:54 [ERROR] Plugin 'InnoDB' init function returned error.
111118 11:38:54 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

Setting a good innodb_log_file_size value will also resolve the MySQL error:

InnoDB: ERROR: the age of the last checkpoint is 9433951,
InnoDB: which exceeds the log group capacity 9433498. InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.

The error message is caused by the InnoDB log files being too small according to the my.cnf configuration. The default setting is that the file (the files, there are two…) may have a maximum size of 5 MB. You configure this with the server variable innodb_log_file_size in the MySQL configuration file my.cnf.

Update 1 Jun 2016: Percona's Vadim Tkachenko posted an interesting article called "What is a big innodb_log_file_size?". The article dives pretty deep into the inner workings of InnoDB and its logs.

You may find more information about InnoDB logs and checkpoints online in Dynamic InnoDB Redo Log and The top 5 MySQL performance variables.

Optimizing InnoDB Disk I/O

Starting from MariaDB 10.0, the default number of innodb_buffer_pool_instances is 8. This means you have to configure your innodb_buffer_pool_size to at least 8 GB, see the defaults above. Use this to your advantage.

If you follow the best practices for database design and the tuning techniques for SQL operations, but your database is still slowed by heavy disk I/O activity, explore these low-level techniques related to disk I/O. If the Unix top tool or the Windows Task Manager shows that the CPU usage percentage with your workload is less than 70%, your workload is probably disk-bound.

  • Increase buffer pool size
  • Adjust the flush method
  • Configure a threshold for operating system flushes
  • Use fdatasync() instead of fsync()
  • Use a noop or deadline I/O scheduler with native AIO on Linux
  • Use direct I/O on Solaris 10 for x86_64 architecture
  • Use raw storage for data and log files with Solaris 2.6 or later
  • Use additional storage devices
  • Consider non-rotational storage
  • Increase I/O capacity to avoid backlogs
  • Lower I/O capacity if flushing does not fall behind
  • Store system tablespace files on Fusion-io devices
  • Disable logging of compressed pages

See MySQL's Optimizing InnoDB Disk I/O documentation.

Don't over optimize: never make too many configuration changes at once. After changing one or two settings, let the server run for a few days so you can learn the effects of the changes. Then, if necessary, make additional changes to the configuration.

Extra tips for MySQL performance tuning

More tips? Yes!

Besides optimizing InnoDB for a high-performance workload, there is more you can do to tune MySQL server and database performance. Here are some extra MySQL configuration tips for you. Some information might be outdated and obsolete but may hold valuable information for tuning your MySQL servers. Some settings may be ignored if you're only using InnoDB as storage engine (tip: convert MyISAM tables to InnoDB).

This is a translation and rewrite of my older Dutch post "MySQL performance en optimalisatie tips", which is now deleted and links to here. Just in case you were wondering why you arrived here instead of the Dutch post after clicking a link :-) Some tips might be outdated.

No two MySQL servers are the same

When optimizing MySQL database servers, keep in mind that no server is equal to another. Settings that work well on one server, may degrade performance on a second, simply because workload differs. If you manage multiple servers with its configuration under version control (e.g almost -or exactly- the same MySQL configuration for all servers), choose what works best on all servers.

To determine what you can improve, you first need to know how the server performs now. You can use some MySQL commands for this on your MySQL cli (data comes from my very old post).

mysql> SHOW STATUS LIKE '%key_read%';
+-------------------+-------------+
| Variable_name     | Value       |
+-------------------+-------------+
| Key_read_requests | 11810240259 |
| Key_reads         | 9260357     |
+-------------------+-------------+

These two variables and values relate to the configured key_buffer_size

In this old example, the database server has 4 GB of RAM and a configured key_buffer_size of 512 MB. The ratio (Key_read_requests / Key_reads) is approximately 1/1275, which is good but the key_buffer_size value may be increased to 768 MB. Even though this is not yet necessary.

mysql> SHOW STATUS LIKE 'thread%';
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| Threads_cached    | 0       |
| Threads_connected | 76      |
| Threads_created   | 6234040 |
| Threads_running   | 2       |
+-------------------+---------+

These Threads_* variable values show you there are currently 76 connected threads, of which only 2 are really running a thread (executing a statement). This means 74 connections are idle.

Here you can also see that there is no "thread cache" set up for MySQL: Threads_cached | 0

You can use the MySQL Server System variable thread_cache_size to configure how many threads must be cached by MySQL. This is one of those configuration settings that, probably, provides the least performance gain, but still...

Don't set this one too high, somewhere between 20 and 40 is often good enough:

thread_cache_size = 20

When you execute the previous statement again, the values will be:

mysql> SHOW STATUS LIKE 'thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 14    |
| Threads_connected | 98    |
| Threads_created   | 2896  |
| Threads_running   | 1     |
+-------------------+-------+

You now have 14 threads cached :)

Miscellaneous MySQL configuration settings

A few words on some miscellaneous configuration settings.

Enforce InnoDB storage engine

I'm sure you don't want to support old MyISAM tables for too long, and certainly don't want newly created tables to be of that storage engine. In MariaDB/MySQL, you can enforce an specific storage engine in the server my.cnf configuration file:

enforce_storage_engine = InnoDB

This enforces InnoDB, see enforce_storage_engine in MariaDB's documentation for more information. The following query lists all MyISAM tables from the information_schema metadata:

select table_schema as database_name, table_name from information_schema.tables where engine = 'MyISAM' and table_type = 'BASE TABLE' and table_schema not in ('information_schema', 'sys', 'performance_schema','mysql') order by table_schema, table_name;

tmp_table_size and max_heap_table_size

The default tmp_table_size and max_heap_table_size values are 16M. These two have to be equal in size! It sets the maximum size for internal in-memory tables, resulting in less creation of temporarily MyISAM tables on the file system. That in return, results in less disk I/O.

join_buffer_size

The join_buffer_size sets a maximum buffer size for plain index scans, range index scans and joins without indices (and therefore perform full table scans). Keep this one low, 1M for example.

MySQL database optimization with indices

Use Diagnostics for improvements

It is important to frequently run diagnostics and/or look up diagnostic data (for example in your information_schema table). Percona has a lot of information about some key metrics:

Monitor your InnoDB Buffer Pool performance

Monitor the InnoDB Buffer Pool performance, for example in a Zabbix template. Retrieving data from information_schema.global_status and information_schema.global_variables gives you valuable information and metrics for your monitoring.

Show how effectively the buffer pool is serving reads (InnoDB Buffer Pool hit ratio):

innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_reads) * 100

MySQL tuning, the conclusion

Tuning MySQL and the InnoDB storage engine is an important step in further optimizing your hosting environment. Every new MySQL version brings new settings to improve your MySQL configuration, so be sure to read those changelogs.

In this article we went over InnoDB Buffer Pool Size and InnoDB Buffer Pool Instances. Setting these properly greatly improves your MySQL server's performance!

But never (ever, ever) over-optimize! Please don't make too many configuration changes at once. Make one or two and restart mysqld. After monitoring your system for a few days, running with the new configuration, you have data available to further optimize other MySQL settings.

With InnoDB being the default storage engine, you also have to make sure you make use of this storage engine in MySQL. Therefore it is important to convert old MyISAM tables to InnoDB. And in this post you learned that InnoDB Buffer Pool optimization is an important step in tuning MySQL servers for a heavy InnoDB workload. Configuring innodb_buffer_pool_instances and increase read/write I/O threads. Dividing the InnoDB buffer pool into multiple instances can improve Disk I/O. And by doing so, you run your database & server more efficiently and faster.