Detailed explanation of how to view MySQL memory usage

Preface

This article mainly introduces the relevant content about MySQL memory usage, and shares it for your reference and learning. Let's take a look at the detailed introduction.

Version used: MySQL 5.7

Official Documentation

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 statistical columns:

* COUNT_ALLOC, COUNT_FREE: The total number of calls to memory allocation and memory release functions

* SUM_NUMBER_OF_BYTES_ALLOC, SUM_NUMBER_OF_BYTES_FREE: total size in bytes of allocated and freed memory blocks

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

* CURRENT_NUMBER_OF_BYTES_USED: The statistical size of the memory blocks currently allocated but not released. This is a convenience column that is equal to SUM_NUMBER_OF_BYTES_ALLOC - SUM_NUMBER_OF_BYTES_FREE

* LOW_COUNT_USED, HIGH_COUNT_USED: Corresponding to the low and high watermarks of the CURRENT_COUNT_USED column

* LOW_NUMBER_OF_BYTES_USED, HIGH_NUMBER_OF_BYTES_USED: correspond to the low and high watermarks of the CURRENT_NUMBER_OF_BYTES_USED column

The TRUNCATE TABLE statement is allowed for memory statistics tables. The following behavior occurs when using the truncate statement:

* Usually, the truncate operation resets the baseline data of the statistical information (that is, clears the previous data), but does not modify the memory allocation status of the current server. In other words, truncate memory statistics will not release allocated memory

* Reset the COUNT_ALLOC and COUNT_FREE columns and restart the counting (this is equivalent to using the reset values as the baseline data for memory statistics)

* SUM_NUMBER_OF_BYTES_ALLOC and SUM_NUMBER_OF_BYTES_FREE columns are reset similarly to COUNT_ALLOC and COUNT_FREE columns

* LOW_COUNT_USED and HIGH_COUNT_USED will be reset to the CURRENT_COUNT_USED column value

* LOW_NUMBER_OF_BYTES_USED and HIGH_NUMBER_OF_BYTES_USED will be reset to the CURRENT_NUMBER_OF_BYTES_USED column value

* In addition, if you execute truncate on the dependent accounts, hosts, users tables, or memory_summary_global_by_event_name tables that are classified by account, host, user, or thread, the truncate statement will be implicitly executed on these memory statistics tables.

To put it simply, memory can be monitored based on user, host, thread, account, and global dimensions. At the same time, the library sys further formats these tables, allowing users to easily 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 overhead of performance_schema. Depending on your MySQL installation, code areas might include performance_schema, sql, client, innodb, myisam, csv, memory, blackhole, archive, partition, and others.

Check whether innodb-related memory monitoring is enabled. It is not enabled 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 by 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 |

Perform memory monitoring on all possible objects. Therefore, the following settings are required:

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 life cycle, you must set it in the configuration file and then 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 schema query aggregates currently allocated memory by 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

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.

You may also be interested in:

Detailed explanation of memory management of MySQL InnoDB storage engine
Detailed analysis of MySQL 8.0 memory consumption
Detailed explanation of the usage of MySQL memory tables and temporary tables
Solutions to MySQL OOM (memory overflow)
Perfect solution to MySQL common insufficient memory startup failure
Test and solution for MySQL's large memory usage and high CPU usage
Solution to high memory usage when starting MySQL 5.6
Share the process of troubleshooting abnormal memory increase in MySQL production database

<<: How to reset your Linux password if lost

>>: Vue component library ElementUI implements table loading tree data tutorial