MySQL性能诊断－内存

原创

laosu

修改于 2021-03-14 17:17:01

1.8K0

修改于 2021-03-14 17:17:01

文章被收录于专栏：db小强db小强

问题

MySQL 5.7

内存占用过高，持续增长，不释放，甚至OOM

诊断

top -Hp ;free -m 查看内存使用情况，若紧急则先缩一下bpsize
show processlist查看是否有异常
查看实例分配内存，MySQL内存使用＝全局内存 +（每个线程内存*连接数）

#全局内存select (@@innodb_buffer_pool_size+@@innodb_log_buffer_size+@@key_buffer_size) /1024/1024 AS MEMORY_MB;#单个连接线程内存SELECT ( ( @@read_buffer_size+ @@read_rnd_buffer_size+ @@sort_buffer_size+ @@join_buffer_size+ @@binlog_cache_size+ @@thread_stack+ @@max_allowed_packet+ @@net_buffer_length )) / (1024*1024) AS MEMORY_MB;

查看内存表占用内存

SELECT SUM(max_data_length)/1024/1024 AS MEMORY_MB FROM information_schema.tables WHERE ENGINE='memory';

通过performance_schema统计内存

* 需开启所有的memory类型的instruments：update setup_instruments set enabled='yes' where name like '%memory/%';

#单个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 ;#统计buffer pool占用内存表select * from innodb_buffer_stats_by_table order by pages desc limit 10;

背景知识

1. 关于buffer/cache

曾经buffer是写缓存，cache是读缓存
buffer cache和page cache，缓冲区缓存和页面缓存
page cache主要用来作为文件系统缓存，buffer cache主要是块设备读写缓存
回收缓存：echo 1 > /proc/sys/vm/drop_caches

2. vm.swappiness

避免swap 设置为0

3. memory_by_thread_by_current_bytes 视图报错

#ERROR 1356 (HY000): View 'sys.memory_by_host_by_current_bytes' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them如果主从都报错，先修改从库STOP SLAVE;SET SQL_LOG_BIN=0;DROP DATABASE sys;SET SQL_LOG_BIN=1;Run mysql_upgradeSTART SLAVE;

4.net_buffer_length，可以动态申请，根据需求最大能到max_allowed_packet，所以sql结果集要尽量小，max_allowed_packet不要过大，大并发很可以OOM

5. 关于tmp_table_size，与max_heap_size中取较小值，是每个线程独自占用的，有的计算里面把它归到全局里，估计是考虑不是所有连接都会用到，乘以连接数后数值会很大

show global status like '%tmp%';select sleep(2);show global status like '%tmp%';

参考：

http://mysql.taobao.org/monthly/2018/11/08/

http://www.mysqlcalculator.com/

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html