Shulou(Shulou.com)05/31 Report--

How do I view the memory used in MySQL? In view of this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Performance_schema has the following table to record 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 columns:

* COUNT_ALLOC,COUNT_FREE: total number of calls to memory allocation and freeing memory 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: the statistical size of the currently allocated memory block but not released. This is a convenient column, equal to SUM_NUMBER_OF_BYTES_ALLOC-SUM_NUMBER_OF_BYTES_FREE

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

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

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

* in general, the truncate operation resets the baseline data of the statistics (that is, the data before emptying), but does not modify the memory allocation and other states of the current server. That is, the truncate memory statistics table does not release allocated memory

* reset the COUNT_ALLOC and COUNT_FREE columns and restart the count (equal to memory statistics using the reset value as the benchmark 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

* in addition, memory statistical tables or memory_summary_global_by_event_name tables classified by account, host, user or thread will implicitly execute truncate statements on these memory statistical tables when truncate is executed on the dependent accounts, hosts, and users tables

To put it simply, the memory can be monitored according to the user, host, thread, account, and global dimension. At the same time, the library sys further formats these tables, making 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 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 it down 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 |

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 do memory statistics on objects in the global lifecycle, you must set it in the configuration file and restart it:

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

You can query the same underlying data 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'\ gateway * 1. Row * * event_name: memory/innodb/buf_buf_pool current_count: 1 current_alloc: 131.06 MiBcurrent_avg_alloc : 131.06 MiB high_count: 1 high_alloc: 131.06 MiB high_avg_alloc: 131.06 MiB

This sys schema 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 | +-+-+ about how to view the memory used in MySQL The answer to the question is shared here. I hope the above content can help you to a certain extent, if you still have a lot of doubts to be solved, you can follow the industry information channel to learn more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.