Detailed explanation of viewing mode of MySQL memory usage

Preface

This article mainly introduces the related contents about MySQL memory usage and views, and shares them for your reference and study. The following words are not much to say, let's take a look at the detailed introduction

Used version: MySQL 5.7

Official documents

In performance_schema, the following table records memory usage

mysql> show tables like '%memory%summary%';
+-------------------------------------------------+
| Tables_in_performance_schema (%memory%summary%) |
+-------------------------------------------------+
| memory_summary_by_account_by_event_name  |
| memory_summary_by_host_by_event_name  |
| memory_summary_by_thread_by_event_name  |
| memory_summary_by_user_by_event_name  |
| memory_summary_global_by_event_name  |
+-------------------------------------------------+

Each memory statistics table has the following statistics columns:

* COUNT_ALLOC, COUNT_FREE: Total number of calls to memory allocation and free functions

* SUM_NUMBER_OF_BYTES_ALLOC, SUM_NUMBER_OF_BYTES_FREE: Total byte size of allocated and freed memory blocks

* CURRENT_COUNT_USED: This is a convenient column equal to COUNT_ALLOC-COUNT_FREE

* CURRENT_NUMBER_OF_BYTES_USED: Statistical size of memory blocks currently allocated but not freed. This is a convenient column equal to SUM_NUMBER_OF_BYTES_ALLOC-SUM_NUMBER_OF_BYTES_FREE

* LOW_COUNT_USED, HIGH_COUNT_USED: Low and high water mark corresponding to CURRENT_COUNT_USED column

* LOW_NUMBER_OF_BYTES_USED, HIGH_NUMBER_OF_BYTES_USED: Low and high water mark corresponding to CURRENT_NUMBER_OF_BYTES_USED column

Memory statistics allow TRUNCATE TABLE statements. The following behavior occurs when using the truncate statement:

* Typically, an truncate operation resets the baseline data of the statistics (i.e., empties the previous data), but does not modify the current state of server, such as memory allocation. That is, the truncate memory statistics table does not release allocated memory

* Reset the COUNT_ALLOC and COUNT_FREE columns and restart counting (equal to memory statistics with reset values as baseline data)

* SUM_NUMBER_OF_BYTES_ALLOC and SUM_NUMBER_OF_BYTES_FREE column resets are similar to COUNT_ALLOC and COUNT_FREE column resets

* LOW_COUNT_USED and HIGH_COUNT_USED will be reset to CURRENT_COUNT_USED column values

* LOW_NUMBER_OF_BYTES_USED and HIGH_NUMBER_OF_BYTES_USED will be reset to CURRENT_NUMBER_OF_BYTES_USED column values

* Additionally, the truncate statement is implicitly executed on a memory statistic table or memory_summary_global_by_event_name by account, host, user, or thread classification when truncate is executed on its dependent accounts, hosts, users tables

Simply put, memory can be monitored according to users, hosts, threads, accounts and global dimensions. At the same time, the library sys also formats these tables one step further, which makes it very easy for users to observe the memory overhead of each object:

mysql> select event_name,current_alloc from sys.memory_global_by_current_bytes limit 10;
+-----------------------------------------------------------------------------+---------------+
| event_name         | current_alloc |
+-----------------------------------------------------------------------------+---------------+
| memory/performance_schema/events_statements_history_long   | 13.66 MiB |
| memory/performance_schema/events_statements_history_long.sqltext  | 9.77 MiB |
| memory/performance_schema/events_statements_history_long.tokens  | 9.77 MiB |
| memory/performance_schema/events_statements_summary_by_digest.tokens | 9.77 MiB |
| memory/performance_schema/table_handles     | 9.06 MiB |
| memory/performance_schema/events_statements_summary_by_thread_by_event_name | 8.67 MiB |
| memory/sql/String::value       | 6.02 MiB |
| memory/performance_schema/memory_summary_by_thread_by_event_name  | 5.62 MiB |
| memory/performance_schema/events_statements_summary_by_digest  | 4.88 MiB |
| memory/sql/TABLE        | 4.35 MiB |
+-----------------------------------------------------------------------------+---------------+

By default, performance_schema only counts the memory cost of performance_schema. Depending on your MySQL installation code area may include performance_schema, sql, client, innodb, myisam, csv, memory, blackhole, archive, partition, and others.

Check whether the memory monitoring related to innodb is turned on, but it is not turned on by default

mysql> SELECT * FROM performance_schema.setup_instruments
 -> WHERE NAME LIKE '%memory%';

+--------------------------------------------------------------------------------+---------+-------+
| NAME          | ENABLED | TIMED |
+--------------------------------------------------------------------------------+---------+-------+
| memory/performance_schema/mutex_instances     | YES | NO |
| memory/performance_schema/rwlock_instances     | YES | NO |
| memory/performance_schema/cond_instances     | YES | NO |
| memory/performance_schema/file_instances     | YES | NO |
| memory/performance_schema/socket_instances     | YES | NO |
| memory/performance_schema/metadata_locks     | YES | NO |
| memory/performance_schema/file_handle      | YES | NO |
| memory/performance_schema/accounts      | YES | NO |
| memory/performance_schema/events_waits_summary_by_account_by_event_name | YES | NO |
| memory/performance_schema/events_stages_summary_by_account_by_event_name | YES | NO |
| memory/performance_schema/events_statements_summary_by_account_by_event_name | YES | NO |
| memory/performance_schema/events_transactions_summary_by_account_by_event_name | YES | NO |
| memory/performance_schema/memory_summary_by_account_by_event_name  | YES | NO |
| memory/performance_schema/events_stages_summary_global_by_event_name  | YES | NO |
| memory/performance_schema/events_statements_summary_global_by_event_name | YES | NO |
| memory/performance_schema/memory_summary_global_by_event_name   | YES | NO |
| memory/performance_schema/hosts      | YES | NO |
| memory/performance_schema/events_waits_summary_by_host_by_event_name  | YES | NO |
| memory/performance_schema/events_stages_summary_by_host_by_event_name  | YES | NO |
| memory/performance_schema/events_statements_summary_by_host_by_event_name | YES | NO |
| memory/performance_schema/events_transactions_summary_by_host_by_event_name | YES | NO |

You can narrow the scope through conditions:

mysql> SELECT * FROM performance_schema.setup_instruments
 WHERE NAME LIKE '%memory/innodb%';
+-------------------------------------------+---------+-------+
| NAME     | ENABLED | TIMED |
+-------------------------------------------+---------+-------+
| memory/innodb/adaptive hash index  | NO | NO |
| memory/innodb/buf_buf_pool  | NO | NO |
| memory/innodb/dict_stats_bg_recalc_pool_t | NO | NO |
| memory/innodb/dict_stats_index_map_t | NO | NO |
| memory/innodb/dict_stats_n_diff_on_level | NO | NO |
| memory/innodb/other   | NO | NO |
| memory/innodb/row_log_buf   | NO | NO |
| memory/innodb/row_merge_sort  | NO | NO |
| memory/innodb/std    | NO | NO |
| memory/innodb/trx_sys_t::rw_trx_ids | NO | NO |

Memory monitoring of all possible objects. Therefore, you also need to make the following settings:

mysql> update performance_schema.setup_instruments set enabled = 'yes' where name like 'memory%';
Query OK, 306 rows affected (0.00 sec)
Rows matched: 376 Changed: 306 Warnings: 0

However, this method of opening memory statistics online is only valid for newly added memory objects, and the settings will be restored after restarting the database:

If you want to perform memory statistics on objects in the global lifecycle, you must set them in the configuration file and restart:

[mysqld]
performance-schema-instrument='memory/%=COUNTED'

The same underlying data can be queried using the memory_global_by_current_bytes table under the sys library, which shows the current memory usage within the global server, broken down by allocation type.

mysql> SELECT * FROM sys.memory_global_by_current_bytes
 WHERE event_name LIKE 'memory/innodb/buf_buf_pool'\G
*************************** 1. row ***************************
 event_name: memory/innodb/buf_buf_pool
 current_count: 1
 current_alloc: 131.06 MiB
current_avg_alloc: 131.06 MiB
 high_count: 1
 high_alloc: 131.06 MiB
 high_avg_alloc: 131.06 MiB

This sys mode query aggregates the currently allocated memory through the current_alloc () code region:

mysql> SELECT SUBSTRING_INDEX(event_name,'/',2) AS
 code_area, sys.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 |
+---------------------------+---------------+

Summarize