人生可悲的事情是,你不知道问题如何解决,并且困惑中, 而更可悲的是,你根本就不知道自己不知道, 当然从另一个角度,那也是一种”幸福”.
今天公司的一个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 的内存使用还有不少可以说的