flash工作室网站模板,建站宝盒自助建站系统,东方论坛,高明网站设计报价在 MySQL 使用过程中#xff0c;偶尔会出现OOM、内存异常突增等异常现象。为了排查内存异常#xff0c;我们需要分析内存占用情况#xff0c;找出内存占用的具体事件。我们可以从以下几张performance下的表分析内存#xff0c;每张表对应不同的维度#xff0c;有用户维度、… 在 MySQL 使用过程中偶尔会出现OOM、内存异常突增等异常现象。为了排查内存异常我们需要分析内存占用情况找出内存占用的具体事件。我们可以从以下几张performance下的表分析内存每张表对应不同的维度有用户维度、主机维度、进程维度。为了打开 performance_schema 功能我们需要在 MySQL 配置中设置 performance_schema ON。
#每张表对应不同的维度有用户维度、主机维度、进程维度。我们需要在 MySQL 配置中设置 performance_schema ON。rootmysql 21:46: [performance_schema] show tables like %mem%;
-----------------------------------------
| Tables_in_performance_schema (%mem%) |
-----------------------------------------
| 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 |
| replication_group_member_stats |
| replication_group_members |
-----------------------------------------
7 rows in set (0.00 sec)#查看 MySQL 总消耗内存rootmysql 21:48: [performance_schema] select * from sys.memory_global_total;
-----------------
| total_allocated |
-----------------
| 1.32 GiB |
-----------------
1 row in set (0.00 sec)#查看总体内存占用情况rootmysql 21:48: [performance_schema] select event_name,CURRENT_NUMBER_OF_BYTES_USED/1024/1024 from performance_schema.memory_summary_global_by_event_name order by CURRENT_NUMBER_OF_BYTES_USED desc LIMIT 20;
----------------------------------------------------------------------------------------------------------------------
| event_name | CURRENT_NUMBER_OF_BYTES_USED/1024/1024 |
----------------------------------------------------------------------------------------------------------------------
| memory/innodb/buf_buf_pool | 1048.50000000 |
| memory/innodb/log0log | 64.00839996 |
| memory/mysys/KEY_CACHE | 32.00141907 |
| memory/innodb/hash0hash | 31.70213318 |
| memory/innodb/os0event | 25.56757355 |
| memory/performance_schema/events_statements_history_long | 13.65661621 |
| memory/performance_schema/events_statements_summary_by_digest.tokens | 9.76562500 |
| memory/performance_schema/events_statements_history_long.sqltext | 9.76562500 |
| memory/performance_schema/events_statements_history_long.tokens | 9.76562500 |
| memory/performance_schema/events_statements_summary_by_thread_by_event_name | 8.66992188 |
| memory/performance_schema/memory_summary_by_thread_by_event_name | 5.62500000 |
| memory/performance_schema/events_statements_summary_by_digest | 4.88281250 |
| memory/performance_schema/events_statements_summary_by_account_by_event_name | 4.33496094 |
| memory/performance_schema/events_statements_summary_by_user_by_event_name | 4.33496094 |
| memory/performance_schema/events_statements_summary_by_host_by_event_name | 4.33496094 |
| memory/innodb/ut0pool | 4.00016785 |
| memory/performance_schema/table_shares | 4.00000000 |
| memory/performance_schema/events_statements_history | 3.49609375 |
| memory/performance_schema/events_statements_current | 3.49609375 |
| memory/performance_schema/events_waits_summary_by_thread_by_event_name | 3.39062500 |
----------------------------------------------------------------------------------------------------------------------
20 rows in set (0.00 sec)#查看线程内存占用情况rootmysql 21:51: [performance_schema] select thread_id,event_name,CURRENT_NUMBER_OF_BYTES_USED/1024/1024 from performance_schema.memory_summary_by_thread_by_event_name order by CURRENT_NUMBER_OF_BYTES_USED desc limit 10;
-------------------------------------------------------------------------------
| thread_id | event_name | CURRENT_NUMBER_OF_BYTES_USED/1024/1024 |
-------------------------------------------------------------------------------
| 1 | memory/innodb/buf_buf_pool | 1048.50000000 |
| 1 | memory/innodb/log0log | 64.00839996 |
| 1 | memory/mysys/KEY_CACHE | 32.00141907 |
| 1 | memory/innodb/hash0hash | 31.70213318 |
| 1 | memory/innodb/os0event | 25.56420135 |
| 1 | memory/innodb/ut0pool | 4.00016785 |
| 1 | memory/innodb/os0file | 2.60424805 |
| 1 | memory/innodb/buf0dblwr | 2.01696014 |
| 1 | memory/innodb/mem0mem | 0.34970760 |
| 1 | memory/innodb/lock0lock | 0.23637390 |
-------------------------------------------------------------------------------
10 rows in set (0.00 sec)
