MYSQL 8 内存使用分析到底我的内存都跑哪了

人生可悲的事情是,你不知道问题如何解决,并且困惑中, 而更可悲的是,你根本就不知道自己不知道, 当然从另一个角度,那也是一种"幸福".

今天公司的一个DBA 小盆友问我,测试机的MYSQL的内存满了,但是看上去MYSQL 也没有用多少内存,到底这些内存用到哪里了.

最近经历都给POSTGRESQL 了,MYSQL 有点疏忽,两手都要抓. 还的转起来. 所以总结了 18 式 对MYSQL 的内存消耗,上上下下,左左右右的来一个 POP.

下面的是这台要被POP的MYSQL 8.011 内存很少,my,cnf 处于"原始部落",没有配置的状态.

___________________________________________________________________________

1  首先第一个问题我们先问问,到底mysqld 的进程到底分配了多少内存在当下.

 ps -eo size,pid,user,command --sort -size | grep mysqld | awk '{ hr=$1/1024 ; printf("%13.2f Mb ",hr) } { for ( x=4 ; x<=NF ; x++ ) { printf("%s ",$x) } print "" }' |cut -d "" -f2 | cut -d "-" -f1

1636.28 Mb /usr/local/mysql/bin/mysqld 

这里PR是这个应用程序的优先级, VIRT 是虚拟内存的大小, RES 是常驻内存也是当前进程使用的内存,(不包含swap), SHR 是共享内存的大小.

这里显示MYSQLD目前使用的内存是 504MB 共享内存 15MB,进程处于 S SLEEP 的状态.

2  系统中已经开始使用了SWAP 到底是不是MYSQL使用了SWAP 的调查清楚,目前看没有使用SWAP

cat /proc/$(pidof mysqld)/status | grep Swap

当然如果想知道,到底那些应用在使用SWAP 

for i in $(ls -d /proc/[0-9]*) 
do  
   out=$(grep Swap $i/status 2>/dev/null)
   if [ "x$(echo $out | awk '{print $2}')" != "x0" ] && [ "x$(echo $out | awk '{print $2}')" != "x" ]
   then    
	  echo "$(ps -p $(echo $i | cut -d'/' -f3) \
         | tail -n 1 | awk '{print $4'}): $(echo $out | awk '{print $2 $3}')" 
   fi
done

当然如果要更深入,到底当前的SWAP 在没有在被使用,

vmstat -at  1  10     可以看一段时间 vmstat 中的 si  so 有没有变化,如果没有说明可能使用SWAP 这段翻篇了, 当然如果你问他不使用SWAP 了,为什么不回收这个问题,我建议你,还是先百度 SWAP 后在来看.

 

3  MYSQL 8.011 到底会不会使用LINUX 的文件缓存

这里的回答是否定的,如MYISAM数据库引擎是会使用 FS CACHE的,而对于MYSQL INNODB 数据库引擎来说,我们在配置文件中配置了 innodb_flush_method 的方式一般是 O_DIRECT 则这样的方式会绕过FS-C

所以这里不考虑MYSQL 对FS-C 的使用的内存.

4 通过MYSQL 的sys 库中的表进行内存的统计

Select SUBSTRING_INDEX(event_name, '/', 2) as g, SUM(t1.current_count), format_bytes(SUM(t1.current_alloc)) as current_alloc, SUM(t1.high_alloc) from x$memory_global_by_current_bytes t1 group by g order by SUM(t1.current_alloc) desc;

 select * from memory_by_thread_by_current_bytes;

通过上面的两个sys库中的语句可以获得从服务器层面和从连接到服务器的SESSION 层面的内存的使用情况.

5  获得INNODB BUFFER POOL 的使用的情况

SELECT CONCAT(FORMAT(A.num * 100.0 / B.num,2),"%") BufferPoolFullPct FROM
	(SELECT variable_value num FROM performance_schema.global_status
	WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
	(SELECT variable_value num FROM performance_schema.global_status
	WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;

通过上面的语句来分析当前的innodb_buffer_pool_size 已经使用了多少

INNODB BUFFER POOL 主要是为数据页面在内存中进行读取和写入的处理而设置的,相关的innodb_buffer_pool_size 设置的不够将导致MYSQL 处理数据变慢,甚至无法正常工作.

监控innodb buffer pool 的使用情况对于系统的性能提升和稳定是有很大帮助的.

6  判断INNODB buffer pool 到底够不够用的快速的方法

show global status like 'innodb_buffer_pool_read%s';select sleep(60); show global status like 'innodb_buffer_pool_read%s';

通过上面的方法可以看一分钟有没有数据的获取不是从innodb_buffer_pool中获得的,如果太高则需要综合上面的信息添加INNODB_BUFFER_POOL_SIZE的内存了.

8  重复索引对于MYSQL的innodb buffer pool size 的伤害

重复索引如果少还好,除了ORACLE不允许有重复的索引存在于系统中, 其他的数据库均不会阻挡重复的索引但命名不同的情况,但同样的索引,或类似的索引对于INNODB BUFFER POOL SIZE 都是一种伤害. 

9  temp table 会使用内存,可以看看到底程序有没有应用数据库内存

select * from performance_schema.global_status 

    -> where variable_name like '%tmp%tables';

select * from memory_global_by_current_bytes 

    -> where event_name like '%temp%'\G

lsof -p $(pidof mysqld) | grep -i del

通过上面的命令可以看到当前MYSQL使用的TEMP 的删除情况

10 除了INNODB BUFFER POOL 是大头,每个SESSION的连接也是需要关注的. 通过传统的方法可以计算出大致目前服务器应该使用的内存的用量,(大致的)

read_buffer_size

read_rnd_buffer_size

sort_buffer_size, join_buffer_size

thread_stack

max_allowed_packet

net_buffer_length

* 

connections  

11   在使用INNODB CLUSTER 复制的方式中,是需要考虑GCS 通讯中使用的内存,下面的语句可以统计使用 INNODB CLUSTER  (MGR), 使用的内存

SELECT EVENT_NAME,CURRENT_NUMBER_OF_BYTES_USED FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/group_rpl/GCS_XCom::xcom_cache';

这里插一句,mysql 的 MGR 是一致在完善的,8.016完善了相关MGR的CACHE的管理.

12  有没有工具可以快速的收集信息,进行内存和其他信息的查询

pt-mysql-summary

13  最古老的MYSQL 信息获取的方式 ,具体怎么解释这些信息,可以找相关的文字.

show innodb engine status\G

最后曾经有人问了一个问题,我的机器的物理内存只有8G ,但我innodb buffer pool size 设置成10G 也能启动,为什么????

读完上面的英文的文字估计就对这个问题就有答案了，实际上MYSQL 的内存使用还有不少可以说的