Detailed analysis of memory consumption of MySQL 8.0

Time:2021-11-30

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

In the production environment, memory alarms are often encountered. Before dealing with these alarms, you need to know where MySQL consumes the most memory, so as to intuitively judge how much memory your MySQL service occupies and how to reduce MySQL’s memory consumption.

In the MySQL configuration file, the two most commonly used memory related parameters are InnoDB_ buffer_ pool_ size、innodb_ log_ buffer_ Size, let’s look at these two parameters.

1、innodb_buffer_pool_size

This parameter defines the size of the buffer pool. You may be familiar with it. 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 size of the buffer pool configured for MySQL 8.0 is 128MB. Generally, if it is a single machine and single instance, there is no other business, The official MySQL configuration size is 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 plenty of memory, you can set multiple InnoDB buffer pool instances. You can adjust the number of instances using the following parameters:


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 cached data in memory before InnoDB storage engine writes redo log to disk. The default is 16MB. After this value is increased, the redo log of large transactions can be saved before the transaction is committed. If your update, delete and insert operations affect a large number of rows, you need to consider increasing this value.

Here’s the point:

In the operating system, the memory occupied by MySQL is not only related to the above two memory configuration parameters. Generally, when calculating the memory occupied by mysql, we will add the following four values:

1、innodb_buffer_pool_size

2、key_ buffer_ size   (this parameter is usually the key parameter for 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, you can see that your MySQL occupies far more memory than InnoDB_ buffer_ pool_ Another key factor you need to consider is whether the number of connections exceeds the standard. Once the number of connections is too high, the memory consumed by the above 3 and 4 parts will be very large.

Of course, the factors listed above are the main memory consumption factors of MySQL. In addition, you can check the official documents for other memory consumption:

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

In the above documents, we also introduce how to use performance_ Schema to monitor the memory usage of MySQL. I’ll mention the whole process here. Please refer to the official document for details and parameter introduction.

1. Check

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, which are divided into several categories. You must remember to filter the parameters you are interested in). For example, we search the memory / InnoDB related parameters to 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 start statistics to 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 click performance_ Memory of schema database_ summary_ global_ by_ event_ View the memory statistics in the name table.


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 aggregation results of each category according to 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 details, see the official documentation.

The above is a detailed analysis of the memory consumption of MySQL 8.0. For more information about the memory consumption of MySQL 8.0, please pay attention to other relevant articles of developeppaer!

Recommended Today

JS generate guid method

JS generate guid method https://blog.csdn.net/Alive_tree/article/details/87942348 Globally unique identification(GUID) is an algorithm generatedBinaryCount Reg128 bitsNumber ofidentifier , GUID is mainly used in networks or systems with multiple nodes and computers. Ideally, any computational geometry computer cluster will not generate two identical guids, and the total number of guids is2^128In theory, it is difficult to make two […]