--全局内存设置
SELECT
ROUND(@@key_buffer_size/1024/1024,2) as KEY_BUF_MB,
ROUND(@@innodb_buffer_pool_size/1024/1024,2) as BUF_POOL ,
ROUND(@@innodb_log_buffer_size/1024/1024,2) as LOG_BUF,
ROUND(@@tmp_table_size/1024/1024,2) as TMP_TABLE,
ROUND(@@read_buffer_size/1024/1024,2) as READ_BUF,
ROUND(@@sort_buffer_size/1024/1024,2) as SORT_BUF,
ROUND(@@join_buffer_size/1024/1024,2) as JOIN_BUF,
ROUND(@@read_rnd_buffer_size/1024/1024,2) as READ_RND_BUF,
ROUND(@@binlog_cache_size/1024/1024,2) as BINLOG_CACHE,
ROUND(@@thread_stack/1024/1024,2) as THREAD_STACK,
(SELECT COUNT(host) FROM information_schema.processlist where command<>'Sleep') as active_connect;



--总内存使用
SELECT SUM(CAST( replace(current_alloc, 'MiB', '') as DECIMAL(10, 2)))
FROM sys.memory_global_by_current_bytes
WHERE current_alloc like '%MiB%' ;



--分事件统计内存
SELECT event_name,
SUM(CAST( replace(current_alloc, 'MiB', '') as DECIMAL(10, 2)))
FROM sys.memory_global_by_current_bytes
WHERE current_alloc like '%MiB%'
GROUP BY event_name
ORDER BY SUM(CAST( replace(current_alloc, 'MiB', '') as DECIMAL(10, 2))) DESC ;


SELECT event_name,
sys.format_bytes(CURRENT_NUMBER_OF_BYTES_USED)
FROM performance_schema.memory_summary_global_by_event_name
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC
LIMIT 10;





--账号级别统计
SELECT user,
event_name,
ROUND(current_number_of_bytes_used/1024/1024,2) as MB_CURRENTLY_USED
FROM performance_schema.memory_summary_by_account_by_event_name
WHERE host<> "localhost"
ORDER BY current_number_of_bytes_used DESC
LIMIT 10;



--线程对应sql语句，内存使用统计


SELECT tm.thread_id,ts.name,ts.type,ts.PROCESSLIST_USER,ts.PROCESSLIST_ID,ts.THREAD_OS_ID,
tm.event_name,
sys.format_bytes(CURRENT_NUMBER_OF_BYTES_USED) 
FROM performance_schema.memory_summary_by_thread_by_event_name tm
left join performance_schema.threads ts on ts.thread_id = tm.thread_id
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC
LIMIT 20;


SELECT m.thread_id as thread_id,
m.user,
esc.DIGEST_TEXT,
m.current_allocated,
m.total_allocated
FROM sys.memory_by_thread_by_current_bytes m,
performance_schema.events_statements_current esc
WHERE m.`thread_id`= esc.THREAD_ID;


--内存事件
SELECT EVENT_NAME,ROUND(COUNT_ALLOC/1024/1024,2) AS COUNT_ALLOC_MB,
ROUND(COUNT_FREE/1024/1024,2) AS COUNT_FREE,
ROUND(SUM_NUMBER_OF_BYTES_ALLOC/1024/1024,2) AS SUM_NUMBER_OF_BYTES_ALLOC,
ROUND(COUNT_ALLOC/1024/1024,2) AS COUNT_ALLOC_MB,
ROUND(COUNT_ALLOC/1024/1024,2) AS COUNT_ALLOC_MB,
ROUND(COUNT_ALLOC/1024/1024,2) AS COUNT_ALLOC_MB,
ROUND(COUNT_ALLOC/1024/1024,2) AS COUNT_ALLOC_MB,
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/%'
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC;



select * from sys.x$memory_by_host_by_current_bytes;
select * from sys.x$memory_by_thread_by_current_bytes;
select * from sys.x$memory_by_user_by_current_bytes;
select * from sys.x$memory_global_by_current_bytes;
select * from sys.x$memory_global_total;
select * from performance_schema.memory_summary_by_account_by_event_name;
select * from performance_schema.memory_summary_by_host_by_event_name;
select * from performance_schema.memory_summary_by_thread_by_event_name;
select * from performance_schema.memory_summary_by_user_by_event_name;
select * from performance_schema.memory_summary_global_by_event_name;
复制