Summary of MySQL 8.0 memory-related parameters

Theoretically, the memory used by MySQL = global shared memory + max_connections × thread-specific memory.

That is: innodb_buffer_pool_size + innodb_log_buffer_size + thread_cache_size + table_open_cache + table_definition_cache + key_buffer_size + max_connections * (thread_stack + sort_buffer_size + join_buffer_size + read_buffer_size + read_rnd_buffer_size + binlog_cache_size + tmp_table_size)

Below we classify the global memory parameters and thread-specific parameters and briefly introduce the functions of the relevant parameters.

Global shared memory

innodb_buffer_pool_size

The innodb_buffer_pool_size parameter is one of the most important parameters for the MySQL database. Its effect on the InnoDB storage engine is similar to the effect of the Key Buffer Cache on the MyISAM storage engine. The main difference is that the InnoDB Buffer Pool not only caches index data, but also caches table data, and caches it completely according to the data structure information in the data file. This is similar to the database buffer cache in Oracle SGA. Therefore, the Buffer pool size found in SHOW ENGINE innodb status should be multiplied by 16K.

The hit rate of InnoDB Buffer Pool can be calculated by (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%.

innodb_change_buffering

Change buffering is a new feature added in MySQL 5.5. Change buffering is an enhancement of insert buffer. Insert buffer is only valid for insert, while change buffering is valid for insert, delete, update (delete+insert), and purge. When modifying the data of an index block (secondary index), if the index block does not exist in the buffer pool, the modified information will be cached in the change buffer. When the required index block is read into the buffer pool through index scanning, it will be merged with the modified information in the change buffer and then written back to disk when appropriate.

The purpose is to reduce the performance loss caused by random IO. To put it simply: convert random IO into sequential IO as much as possible. SSDs are prevalent nowadays. When the random access and sequential access performance on SSDs are almost the same, the change buffering feature will not bring much performance improvement. However, for cheap mechanical hard drives, this parameter can still help improve performance.

Change buffering is controlled by the parameter innodb_change_buffering:

• all: buffer inserts, delete-marking operations, and purges.
• none: Do not buffer any operations.
• inserts: Buffer insert operations.
• deletes: Buffer delete-marking operations.
• changes: Buffer both inserts and delete-marking.
• purges: Buffer the physical deletion operations that happen in the background.
  

Note that this memory is allocated in the Innodb buffer pool and does not need to be counted when calculating the total memory.

innodb_change_buffer_max_size

Indicates the maximum percentage of the change buffer in the buffer pool, the default is 25% and the maximum is 50%. If there are serious inserts, updates and active deletes in the system, increase max_size; for pure reporting systems that do not change data, you can reduce the parameter value.

innodb_log_buffer_size

This is the buffer used by the InnoDB storage engine's transaction log. In order to improve performance, the information is first written to the Innofb Log Buffer. When the corresponding conditions set by the innodb_flush_log_trx_commit parameter are met (or the log buffer is full), the log is written to the file (or synchronized to the disk). The innodb_flush_log_trx_commit parameter can be set to 0, 1, or 2, as explained below:

• 0: The data in the log buffer will be written to the logfile once per second, and the file system will be synchronized to the disk at the same time. However, the commit of each transaction will not trigger any flushing of the log buffer to the log file or the file system to the disk. This mode is the fastest but less secure. The crash of the mysqld process will cause the loss of all transaction data in the previous second.
• 1: When each transaction is committed, the data in the log buffer will be written to the logfile, and the synchronization from the file system to the disk will also be triggered. This mode is the safest, but also the slowest.
• 2: Transaction commit triggers the refresh of the log buffer to the logfile, but does not trigger the synchronization of the disk file system to the disk. This mode is faster and safer than 0. All transaction data in the last second may be lost only when the operating system crashes or the system loses power.
  

thread_cache_size

The thread pool cache size is used to cache the current thread after the client disconnects, so that a new connection request can be quickly responded to without creating a new thread. This can greatly improve the efficiency of creating connections, especially for applications that use short connections. The hit rate of the connection thread cache can be calculated by (Connections - Threads_created) / Connections * 100%. You can also adjust the thread pool size appropriately through the following MySQL status values:

mysql> show global status like 'Thread%';
+-------------------+------+
| Variable_name | Value |
+-------------------+------+
| Threads_cached | 2 |
| Threads_connected | 1 |
| Threads_created | 3 |
| Threads_running | 2 |
+-------------------+------+
4 rows in set (0.01 sec)

When Threads_cached decreases but Threads_connected never decreases, and Threads_created continues to increase, you can appropriately increase the size of thread_cache_size.

table_open_cache

table_open_cache specifies the size of the table cache, which is used to cache the file handle information of the table file. When our client program submits a Query to MySQL, MySQL needs to obtain a table file handle information for each table involved in the Query. If there is no Table Cache, MySQL will have to frequently open and close files, which will undoubtedly have a certain impact on system performance. Whenever MySQL accesses a table, if there is space in the table buffer, the table will be opened and placed in it, so that the table contents can be accessed faster. Note that what is set here is the number of table file handle information that can be cached, not the size of the memory space.

By checking the status values ​​Open_tables and Opened_tables during peak times, you can decide whether you need to increase the value of table_open_cache. Open_tables is the number of currently open tables, and Opened_tables is the number of all opened tables. Note that you cannot blindly set table_open_cache to a large value. If the value is too large, it will exceed the shell's file descriptors (check with ulimit -n), resulting in insufficient file descriptors, unstable performance or connection failure. If you find that open_tables is equal to table_open_cache, and opened_tables is growing, then you need to increase the value of table_open_cache (the above status values ​​can be obtained through SHOW GLOBAL STATUS LIKE 'Open%tables'). If the value of Open_tables is close to the value of table_cache, and Opened_tables is still increasing, it means that MySQL is releasing the cached table to accommodate the new table. At this time, you may need to increase the value of table_cache. For most situations, suitable values ​​are:

• Open_tables / Opened_tables >= 0.85
• Open_tables / table_cache <= 0.95
  

It is recommended to run the MySQL database in a production environment for a period of time, and then adjust the parameter value to be larger than the value of Opened_tables, and ensure that it is still slightly larger than Opened_tables under extreme conditions of high load.

table_definition_cache

table_definition_cache is similar to table_open_cache. The former caches frm files. The document does not explain the latter. It should be ibd/MYI/MYD.

Status value:

Open_table_definitions: The number of table definition files.frm cached

Opened_table_definitions: The total number of frm files that have been cached in history

key_buffer_size

key_buffer_size specifies the size of the index buffer, which determines the speed of index processing, especially the speed of index reading. By checking the status values ​​Key_read_requests and Key_reads, you can know whether the key_buffer_size setting is reasonable. The ratio key_reads / key_read_requests should be as low as possible, at least 1:100, 1:1000 is better (the above status value can be obtained using SHOW STATUS LIKE 'key_read%'). key_buffer_size only works for MyISAM tables. Even if you do not use MyISAM tables, but the internal temporary disk tables are MyISAM tables, you should use this value. You can use the check status value created_tmp_disk_tables to get details.

max_connections

The maximum number of MySQL connections. Increasing this value increases the number of file descriptors required by mysqld. If the server has a large number of concurrent connection requests, it is recommended to increase this value to increase the number of parallel connections. Of course, this is based on the machine's ability to support it. Because if there are more connections, MySQL will provide a connection buffer for each connection, which will consume more memory. Therefore, you should adjust the value appropriately and not blindly increase the value. If the value is too small, ERROR 1040: Too many connections will often appear. You can use the 'conn%' wildcard to view the number of connections in the current state to determine the size of the value. max_used_connections / max_connections * 100% (ideal value ≈ 85%) If max_used_connections is the same as max_connections, then max_connections is set too low or exceeds the server load limit. If it is lower than 10%, it is set too high.

Thread/session/connection exclusive memory

binlog_cache_size

The memory allocated for each session is used to store the binary log cache during the transaction process, which can improve the efficiency of recording bin-log. The default value is 32K. If there are no large transactions and DML is not very frequent, you can set it smaller. If the transactions are large and numerous, and DML operations are frequent, you can increase it appropriately.

The usage of binlog_cache_size in the database can be viewed as follows: Binlog_cache_disk_use indicates the number of times temporary files are used to cache binary logs due to insufficient memory designed by our binlog_cache_size; Binlog_cache_use indicates the number of times binlog_cache_size is used for caching

tmp_table_size and max_heap_table_size

tmp_table_size specifies the maximum size of the internal memory temporary table, which must be allocated for each thread. (The actual limit is the minimum of tmp_table_size and max_heap_table_size.) If the in-memory temporary table exceeds the limit, MySQL will automatically convert it to a disk-based MyISAM table and store it in the specified tmpdir directory, by default:

mysql> show variables like "tmpdir";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir | /tmp/ |
+---------------+-------+

When optimizing query statements, avoid using temporary tables. If it is unavoidable, make sure that these temporary tables are stored in memory. If necessary, and you have a lot of group by statements, and you have a lot of memory, increase the value of tmp_table_size (and max_heap_table_size). This variable does not apply to user-created memory tables.

You can compare the total number of internal disk-based temporary tables and the total number of temporary tables created in memory (Created_tmp_disk_tables and Created_tmp_tables). The general ratio is:

Created_tmp_disk_tables/Created_tmp_tables<5%

max_heap_table_size defines the size of the memory table that the user can create. This value is used to calculate the maximum row value of the memory table. This variable supports dynamic changes, i.e. set @max_heap_table_size = xxx.

The above is the detailed summary of MySQL 8.0 memory-related parameters. For more information about MySQL 8.0 memory parameters, please pay attention to other related articles on 123WORDPRESS.COM!