问题
MySQL 5.7
内存占用过高,持续增长,不释放,甚至OOM
诊断
#全局内存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';
* 需开启所有的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
2. vm.swappiness
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
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。