Detailed analysis of MySQL 8.0 memory consumption

When MySQL 8.0 is started, various buffers and caches will be configured to improve database performance. If we configure the MySQL 8.0 service on a server, the memory of this server will be shared by the operating system, MySQL 8.0 service, and other applications at the same time.

In a production environment, memory alarms are often encountered. Before handling these alarms, you need to know where MySQL itself consumes the most memory. This way, you can more intuitively determine how much memory your MySQL service occupies and how to reduce the memory consumption of MySQL itself.

In the MySQL configuration file, the two most commonly used memory-related parameters are innodb_buffer_pool_size and innodb_log_buffer_size. Let's take a look at these two parameters.

1. innodb_buffer_pool_size

This parameter defines the size of the buffer pool, which you may be familiar with. The contents of the buffer pool include InnoDB tables, indexes, and other auxiliary buffers. The size of the buffer pool has a great impact on the performance of the MySQL system. By default, the buffer pool size configured in MySQL 8.0 is 128MB. Normally, if it is a single machine and single instance with no other business, then the official MySQL recommendation is to configure the size between 50% and 75% of the system memory. Of course, if you have other applications deployed on your server, you need to reduce this ratio as appropriate to free up memory.

If your operating system has sufficient memory, you can set up multiple InnoDB buffer pool instances. You can use the following parameters to adjust the number of instances:

mysql> show variables like '%innodb_buffer_pool_instances%';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 1 |
+------------------------------+-------+
1 row in set (0.00 sec)

2. innodb_log_buffer_size

This parameter defines the maximum size of data cached in memory before the InnoDB storage engine writes the redo log to disk. The default is 16MB. After this value is increased, large transactions do not need to write redo logs to disk before the transaction is committed. If your update, delete, and insert operations affect a large number of rows, then you should consider increasing this value.

Here comes the point:

In the operating system, the memory occupied by MySQL is not only related to the above two memory configuration parameters. Usually, when we calculate the memory occupied by MySQL, we use the following four values ​​to add together:

1. innodb_buffer_pool_size

2. key_buffer_size (this parameter is usually the key parameter of the memory occupied by the myisam table)

3. max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) (these three are connection-level buffers)

4. max_connections*2MB

So when you use the top command to see that the memory occupied by your MySQL is far more than innodb_buffer_pool_size, another key factor you need to consider is whether the number of connections exceeds the limit. Once the number of connections is too high, the memory consumed by the above two parts 3 and 4 will be very large.

Of course, the above are the main factors that occupy MySQL memory. In addition, for other memory consumption, you can check the official documentation:

https://dev.mysql.com/doc/refman/8.0/en/memory-use.html

The above document also introduces how to use performance_schema to monitor MySQL memory usage. Here I will mention the whole process. For detailed details and parameter introduction, please refer to the official document.

1. View

performance_schema.setup_instruments

In this table, find the name of the memory variable you are interested in (direct search, there are more than 490 results, divided into several major categories, be sure to filter the parameters you are interested in). For example, we search for memory/innodb related parameters, which represent the memory occupied by the innodb storage engine. The results are as follows:

mysql> SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory/innodb%';
+-------------------------------------------+---------+-------+-------------------+------------+---------------+
| NAME | ENABLED | TIMED | PROPERTIES | VOLATILITY | DOCUMENTATION |
+-------------------------------------------+---------+-------+-------------------+------------+---------------+
| memory/innodb/adaptive hash index | YES | NULL | | 0 | NULL |
| memory/innodb/log and page archiver | YES | NULL | | 0 | NULL |
| memory/innodb/buf_buf_pool | YES | NULL | global_statistics | 0 | NULL |
| memory/innodb/buf_stat_per_index_t | YES | NULL | | 0 | NULL |
| memory/innodb/clone | YES | NULL | | 0 | NULL |
| memory/innodb/dict_stats_bg_recalc_pool_t | YES | NULL | | 0 | NULL |
| memory/innodb/dict_stats_index_map_t | YES | NULL | | 0 | NULL |
| memory/innodb/dict_stats_n_diff_on_level | YES | NULL | | 0 | NULL |
| memory/innodb/other | YES | NULL | | 0 | NULL |
| memory/innodb/partitioning | YES | NULL | | 0 | NULL |
| memory/innodb/row_log_buf | YES | NULL | | 0 | NULL |
| memory/innodb/row_merge_sort | YES | NULL | | 0 | NULL |
| memory/innodb/std | YES | NULL | | 0 | NULL |
| memory/innodb/trx_sys_t::rw_trx_ids | YES | NULL | | 0 | NULL |
| memory/innodb/undo::Tablespaces | YES | NULL | | 0 | NULL |
| memory/innodb/ut_lock_free_hash_t | YES | NULL | | 0 | NULL |
| memory/innodb/api0api | YES | NULL | | 0 | NULL |
| memory/innodb/api0misc | YES | NULL | | 0 | NULL |
| memory/innodb/btr0btr | YES | NULL | | 0 | NULL |

2. Write relevant parameters in the configuration file and enable statistics. Taking memory/innodb/row_log_buf as an example, the configuration file is modified as follows:

performance-schema-instrument='memory/innodb/row_log_buf=COUNTED'

3. Start the instance and view the memory statistics in the memory_summary_global_by_event_name table of the performance_schema database.

SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/innodb/row_log_buf'\G

Of course, you can also view the aggregate results of each category based on the results in the sys table, as follows:

mysql> SELECT SUBSTRING_INDEX(event_name,'/',2) AS
  code_area, FORMAT_BYTES(SUM(current_alloc))
  AS current_alloc
  FROM sys.x$memory_global_by_current_bytes
  GROUP BY SUBSTRING_INDEX(event_name,'/',2)
  ORDER BY SUM(current_alloc) DESC;
+---------------------------+---------------+
| code_area | current_alloc |
+---------------------------+---------------+
| memory/innodb | 843.24 MiB |
| memory/performance_schema | 81.29 MiB |
| memory/mysys | 8.20 MiB |
| memory/sql | 2.47 MiB |
| memory/memory | 174.01 KiB |
| memory/myisam | 46.53 KiB |
| memory/blackhole | 512 bytes |
| memory/federated | 512 bytes |
| memory/csv | 512 bytes |
| memory/vio | 496 bytes |
+---------------------------+---------------+

For more detailed information, see the official documentation.

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