Saminnet-Search Article Wiki Forum Piwigo SNS Cloud vtiger Sugar
MySQL InnoDB buffer

MySQL InnoDB buffer

You can configure multipleツinnodb_buffer_pool_instancesツin MySQL, to increase read/write threads. To further improve InnoDB performance, you can increase the InnoDB buffer pool size, and you can divide the InnoDB buffer pool into multiple regions.

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

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窶冲 you?

MariaDB/MySQL 5.5.4 introduces new configuration settings for the InnoDB storage engine. This can greatly improve MySQL窶冱 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_sizevalue:


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:

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 setツ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.

To enable multiple buffer pool instances, set the innodb_buffer_pool_instances configuration option to a value greater than 1 (the default) 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窶况e 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 instancesNotes
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

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


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


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? When 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.

Optimizing InnoDB Disk I/O

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,ツ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.

Protip, don窶冲 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 implications of the changes. Then, if necessary, make additional changes to the configuration.

Convert MyISAM to InnoDB tables for WordPress using a plugin
For WordPress, I created a plugin to convert MyISAM tables to InnoDB, that now is incorporated into theツVevida OptimizerWordPress plugin. The Vevida Optimizer plugin extends the automatic update feature already present in WordPress.

The WordPress core updates can be switched on or off, themes and translations can be automatically updated, and the plugin updates can be configured on a per-plugin basis.

5 Extra tips for MySQL performance tuning

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 server.

Note: 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 :-)

#1: No MySQL server is 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. 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 窶徼hread 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窶冲 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 :)

#2: Query Cache

Note: MySQL query cache deprecated

MySQL query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0. 窶督

TheツMySQL Query Cacheツis a very important optimization setting. It does that what the name implies: caching queries (query results) in memory.

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.

The query cache can be useful in an environment where you have tables that do not change very often and for which the server receives many identical queries. This is a typical situation for many Web servers that generate many dynamic pages based on database content.

The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.

You can verify whether MySQL Query Cache is enabled using the following statement:

mysql> SHOW STATUS LIKE 'q%';
| Variable_name           | Value     |
| Qcache_free_blocks      | 0         |
| Qcache_free_memory      | 0         |
| Qcache_hits             | 0         |
| Qcache_inserts          | 0         |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 0         |
| Qcache_queries_in_cache | 0         |
| Qcache_total_blocks     | 0         |
| Queries                 | 277915656 |
| Questions               | 4         |

In this particular example, query cache is disabled, but the database server does support it. Check with:

mysql> SHOW VARIABLES LIKE 'have_query_cache';
| Variable_name    | Value |
| have_query_cache | YES   |

By properly configuring MySQL query cache, you can lower the amount of disk I/O reads. MySQL stores the text result of a SELECT statement 窶 or query 窶 in memory. When the exact same query is executed again, the result is easily fetched from the cache, instead of MySQL having to plow through the tables again.

Starting from MySQL verison 5.1.27, they query cache is also available for perpared statements, and since 5.1.21 there is prepared statement with parameter markers support.

As explained below, don窶冲 set the query cache value too high. In the following example, the query cache is set to only 128 MB for a server with 4 GB RAM:

mysql> SHOW STATUS LIKE 'q%';
| Variable_name           | Value    |
| Qcache_free_blocks      | 7311     |
| Qcache_free_memory      | 23478512 |
| Qcache_hits             | 12593712 |
| Qcache_inserts          | 3400932  |
| Qcache_lowmem_prunes    | 2442420  |
| Qcache_not_cached       | 343758   |
| Qcache_queries_in_cache | 26438    |
| Qcache_total_blocks     | 72135    |
| Queries                 | 20129501 |
| Questions               | 4        |

As you can see, it still has 26438 cached SELECT statements or queries. Pretty neat :)

MySQL Query Cache caveats!
When going over your MySQLツmy.cnfツserver configuration, avoid setting a too large query cache value! Set a lower-ish value like 100 窶 200 MB. A too highツquery_cache_sizeツwill degrade server performance dramatically.

Here is what Oracle MySQL has to say about the query cache in theirツMySQL reference manual:

Be cautious about sizing the query cache excessively large, which increases the overhead required to maintain the cache, possibly beyond the benefit of enabling it. Sizes in tens of megabytes are usually beneficial. Sizes in the hundreds of megabytes might not be.

#3: Query Cache flush

To avoid fragmentation of the available query cache memory, you have toツflush MySQL窶冱 query cacheツfrom time to time.

Don窶冲 worry, this doesn窶冲 delete the cache as explained in the linked post.

#4: Calculate, calculate, calculate!

You can calculate whether or not your MySQL server is performing optimal. The following calculations are also in myツOptimize WordPress hostingツpost, but I窶冤l repeat them here for you, to offer you an as complete guide as possible.

In a MySQL nutshell:

  • Query cache hit ratio:ツQcache_hits / (Qcache_hits + Com_select)
  • query_cache_size: first determine the average query size:
    (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
  • Query Cache fragmentation:ツQcache_free_blocks =~ Qcache_total_blocks / 2, ifツqcache_free_blocksツis about equal toツqcache_total_blocks / 2,ツthen you suffer from query cache fragmentation.

If theツQcache_lowmem_prunesツvalue increases rapidly, then you have to increaseツquery_cache_sizeツin yourツmy.cnfツserver configuration.

#5: Miscellaneous MySQL configuration settings

A few words on some miscellaneous configuration settings.

#5.1: tmp_table_size / 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.

#5.2: 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 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窶冱 performance!

But never (ever, ever) over-optimize! Please don窶冲 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 your old MyISAM tables to InnoDB.


0 #1 Guest 2018-11-27 20:16
Very energetic article, I enjoyed that a lot.
Will there be a part 2?

Also visit my homepage: shop
design concept

Articles by Date

TweetTweet Share on LinkedInShare on LinkedIn Share on Google+Google+ Submit to RedditReddit Publish on WordPress WordPress Send emailSend email