Adjusting MySQL 8.0 Memory Parameters

So you’ve just added some more memory to your MySQL server; now what? If you’ve been around the MySQL block for a while, you know that nothing is automatically changed to take advantage of this new system RAM. Let’s have a look at a few parameters you would want to adjust.

InnoDB Parameters

innodb_buffer_pool_size

The InnoDB buffer pool is “…the memory area that holds cached InnoDB data for both tables and indexes.” This parameter is probably the #1 tuning parameter in MySQL. If your buffer pool is too small, then InnoDB must spend extra CPU/Disk time, loading, and unloading pages in/out of memory. This is time better spent executing your queries.

The default size of this cache is 128MB; woefully small for any serious database. Increasing the size of this cache allows more frequently accessed pages to remain in memory for the fastest access. Obviously, you do not need a buffer pool which is larger than your overall dataset. You also do not need all of your data to fit into the buffer pool. It is extremely rare for a database system to utilize/access 100% of all data, all the time.

The screenshot below from Percona Monitoring and Management 2.0 gives us some insight into the sizing of our buffer pool. Without going over everything, we can see the percentage size of our buffer pool as related to how much data we have (73.14%), and we can see how often a cache request is missed and must fetch from the disk. Ideally, we want this miss-rate to be as low as possible (0.00% as shown).

You can change InnoDB’s buffer pool size as an online operation, and persist the configuration change to disk:

mysql> SET PERSIST innodb_buffer_pool_size=402653184;

You can monitor the resize process by watching the MySQL error log, and by inspecting the ‘innodb_buffer_pool_resize_status’ variable.

innodb_flush_method

This parameter controls how InnoDB will open, and flush tablespace and data files. There is no direct impact on MySQL itself with this parameter but changing it to the recommended value can ease the memory pressure of the underlying filesystem. The default for this parameter is ‘fsync’ which uses buffered IO to manage the opened files. Buffered IO incurs memory usage within the filesystem and other low-level kernel operations.

Because InnoDB uses its own methods for controlling data safety (ie: redo logging, doublewrite buffer), caching/buffering this data at the file level is unnecessary. We can tell InnoDB not to do this additional caching by setting this parameter to O_DIRECT. Changing this parameter in your my.cnf will require MySQL restart.

innodb_numa_interleave

For systems with large amounts of memory, (ie: > 128GB), we recommend turning on NUMA interleaving. Without going into too much detail, enabling this parameter configures memory allocation to be ‘interleaved’ across the various CPU-Memory channels. This helps “even out” memory allocations so that one CPU does not become a memory bottleneck. Have a look at Jeremy’s NUMA analysis for a more detailed explanation. Changing this parameter in your my.cnf will require MySQL restart.

Temporary Tables

MySQL 8.0 changed the way temporary tables are managed. In previous versions, we used the variables tmp_table_size and max_heap_table_size. If an in-memory temporary table grew larger than the lower of these two values, MySQL would convert the table to an on-disk temporary table. These two variables controlled the limit on a per-temp-table basis. So, if you set both of these to 1GB, and 50 nasty queries came in, MySQL could allocate and use up to 50GB of additional memory.

While these two parameters still exist in 8.0, they are directed towards temporary tables that use the MEMORY storage engine. The new TempTable engine is more efficient and became the new default.

The setting temptable_max_ram controls how much is available to ALL temporary tables before converting to an on-disk table.  Looking at our 50 queries example earlier, say each one needs 256MB of memory. With the default setting of 1GB, the first 4 queries would be stored in memory, and the remaining 46 queries would immediately go to disk as InnoDB temporary tables.

You can track how often temporary tables are created on disk as a guide on increasing temptable_max_ram to reduce the number of such disk-based tables.

Global Buffers

MySQL has lots of small internal buffers used for various aspects like thread stack memory, read_buffer_size for sequential table scans, read_rnd_buffer_size for sorting reads, etc. Peter Zaitsev’s Optimal MySQL Memory post explains a few of these in more detail, especially if you are still using MyISAM tables. (You are? Why?)

Each time a connection is established to MySQL, that connection receives its own set of various buffers. If you were previously limited on the number of maximum connections, having more available memory would allow you to increase this capacity by changing max_connections.

Consider increasing sort_buffer_size to improve performance for queries using ORDER BY or GROUP BY clauses that cannot be improved with a query optimization or additional indexing. This parameter is best changed on a session-by-session basis and NOT globally.

If you have lots of tables, increasing table_definition_cache can help speed up the opening of tables.

Summary

This post covers a few essential MySQL 8.0 parameters you would consider changing after adding more memory to a MySQL server. Just be extra careful not to allocate too much memory to MySQL, lest you be subject to the dreaded OOM Killer.

One last parameter to mention is the new innodb_dedicated_server. By setting this value to ‘ON’ you give control to MySQL over four key InnoDB parameters: innodb_buffer_pool_size, innodb_log_file_size, innodb_log_files_in_group, and innodb_flush_method. You can view the manual page to see the various cut-offs and calculations that MySQL makes depending on how much memory is available to your server. This parameter could be the ‘easy button’ you were looking for all along.

by Matthew Boehm via Percona Database Performance Blog