Shuo
Shuo I'm a DBA(Database Administrator), we can share and discuss MySQL, MongoDB, Redis and other databases here, also including learning Python, Shell, Golang together.

MySQL Get Memory Status From Performance_Schema

MySQL Get Memory Status From Performance_Schema

确认是否打开

1
mysql>  show global variables like "performance_schema";

若为ON,则为打开。

分类查询

统计事件消耗内存

event_name排最前,就代表这个事件模块占内存最多。 例如:JOIN_CACHE就是join的操作,mem0mem就是客户端连接,没开启统计的话,数据可能并不会很多.

1
mysql> select event_name, CURRENT_NUMBER_OF_BYTES_USED,SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_global_by_event_name order by CURRENT_NUMBER_OF_BYTES_USED desc limit 10;

统计线程消耗内存

thread_id就是show processlist的thread_id,如果SUM_NUMBER_OF_BYTES_ALLOC为零就代表没开启统计

1
mysql> select thread_id, event_name, CURRENT_NUMBER_OF_BYTES_USED,SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_by_thread_by_event_name order by CURRENT_NUMBER_OF_BYTES_USED desc limit 10;

统计账户消耗内存

如果SUM_NUMBER_OF_BYTES_ALLOC为零就代表没开启统计

1
mysql> select USER, HOST, EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED,SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_by_account_by_event_name order by CURRENT_NUMBER_OF_BYTES_USED desc limit 10;

统计主机消耗内存

如果SUM_NUMBER_OF_BYTES_ALLOC为零就代表没开启统计

1
mysql > select HOST, EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED,SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_by_host_by_event_name order by CURRENT_NUMBER_OF_BYTES_USED desc limit 10;

统计用户消耗内存

如果SUM_NUMBER_OF_BYTES_ALLOC为零就代表没开启统计

1
mysql> select USER, EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED,SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_by_user_by_event_name order by CURRENT_NUMBER_OF_BYTES_USED desc limit 10;

附:MySQL内存使用拆分

comments powered by Disqus