Detailed explanation of how to view MySQL memory usage

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

Recommend

Some suggestions on Vue code readability

Table of contents 1. Make good use of components ...

MySQL 8.0.11 installation and configuration method graphic tutorial

The installation and configuration methods of MyS...

HTML Tutorial: Definition List

<br />Original text: http://andymao.com/andy...

Detailed explanation of the mechanism and implementation of accept lock in Nginx

Preface nginx uses a multi-process model. When a ...

jQuery+swiper component realizes the timeline sliding year tab switching effect

Result: Implementation code: Need to be used with...

How to create LVM for XFS file system in Ubuntu

Preface lvm (Logical Volume Manager) logical volu...

Troubleshooting the security group ports added by Alibaba Cloud and the inability to access them after adding them

Purpose: 1. In order to map the server's exte...

Detailed explanation of the application of the four states of hyperconnection

Although you think it may be a browser problem, i...

Modify the default scroll bar style in the front-end project (summary)

I have written many projects that require changin...

$nextTick explanation that you can understand at a glance

Table of contents 1. Functional description 2. Pa...

Graphical explanation of the underlying principle of JavaScript scope chain

Table of contents Preface Scope 1. What is scope?...

Detailed explanation of how to use WeChat mini program map

This article example shares the specific implemen...

Analysis and solution of the cause of web page style loss caused by browser automatic form filling

From the backend to the front end, what a tragedy....

How to monitor mysql using zabbix

Zabbix deployment documentation After zabbix is ​...

Navicat for MySQL scheduled database backup and data recovery details

Database modification or deletion operations may ...