MySQL内存使用的查看方式详解(2)-木庄网络博客

查看innodb相关的内存监控是否开启，默认不开启

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 |

可以通过条件缩小范围：

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 |

对所有可能的对象进行内存监控。因此，还需要做下面的设置：

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

但是这种在线打开内存统计的方法仅对之后新增的内存对象有效，重启数据库后又会还原设置：

如想要对全局生命周期中的对象进行内存统计，必须在配置文件中进行设置，然后重启：

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

可以使用sys库下的memory_global_by_current_bytes表来查询相同的底层数据，该模式表显示了全局服务器内当前内存使用情况，按分配类型进行细分。

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

此sys模式查询通过current_alloc()代码区域聚合当前分配的内存：

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 |
+---------------------------+---------------+

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对的支持。

更多相关Mysql内容来自木庄网络博客

标签：Mysql

返回前面的内容