淘宝内核月报的这边文章值得一看,推荐一下:
http://mysql.taobao.org/monthly/2018/11/08/
排查思路,排查方法都涉及。
Mysql Server Memory Usage= Sum of Global Buffers + (number of Connection * Per thread memory variables)
a)单个mysql连接线程的内存消耗统计,这里只是统计分配值(具体驻留内存占用值统计不到)
select b.thd_id, b.user, current_count_used,current_allocated, current_avg_alloc, current_max_alloc,total_allocated,current_statement from memory_by_thread_by_current_bytes a,session b where a.thread_id = b.thd_id limit 1;
b)统计top 10的buffer pool占用内存的表
select * from innodb_buffer_stats_by_table order by pages desc limit 10;