MYSQL 内存排查
2021-07-30 本文已影响0人
轻飘飘D
- processlist命令的输出结果显示了有哪些线程在运行
show full processlist;
1.查参数配置
SELECT @@query_cache_size,
@@key_buffer_size,
@@innodb_buffer_pool_size ,
@@innodb_log_buffer_size ,
@@tmp_table_size ,
@@read_buffer_size,
@@sort_buffer_size,
@@join_buffer_size ,
@@read_rnd_buffer_size,
@@binlog_cache_size,
@@thread_stack,
(SELECT COUNT(host) FROM information_schema.processlist where command<>'Sleep')\G;
*************************** 1. row ***************************
@@query_cache_size: 67108864
@@key_buffer_size: 402653184
@@innodb_buffer_pool_size: 6442450944
@@innodb_log_buffer_size: 16777216
@@tmp_table_size: 268435456
@@read_buffer_size: 4194304
@@sort_buffer_size: 6291456
@@join_buffer_size: 8388608
@@read_rnd_buffer_size: 16777216
@@binlog_cache_size: 4194304
@@thread_stack: 262144
(SELECT COUNT(host) FROM information_schema.processlist where command<>'Sleep'):
4
目前积累的使用经验中，存储过程&函数&触发器&视图 在MySQL场景下是不适合的。性能不好，又容易发现内存不释放的问题，所以建议尽量避免.
2.存储过程&函数
#5.7
SELECT db,type,count(*) FROM mysql.proc
WHERE db not in ('mysql','information_schema','performance_schema','sys')
GROUP BY db, type;
#8.0
SELECT Routine_schema, Routine_type FROM information_schema.Routines
WHERE Routine_schema not in ('mysql','information_schema','performance_schema','sys')
GROUP BY Routine_schema, Routine_type;
3.视图
SELECT TABLE_SCHEMA , COUNT(TABLE_NAME) FROM information_schema.VIEWS
WHERE TABLE_SCHEMA not in ('mysql','information_schema','performance_schema','sys')
GROUP BY TABLE_SCHEMA;
4.触发器
SELECT TRIGGER_SCHEMA, count(*) FROM information_schema.triggers
WHERE TRIGGER_SCHEMA not in ('mysql','information_schema','performance_schema','sys')
GROUP BY TRIGGER_SCHEMA;
5.1 总内存使用
SELECT SUM( CAST(replace(current_alloc,'MiB','') as DECIMAL(10, 2)) )
FROM sys.memory_global_by_current_bytes
WHERE current_alloc like '%MiB%';
5.2 分事件统计内存
#1
SELECT event_name,SUM( CAST(replace(current_alloc,'MiB','') as DECIMAL(10, 2)) )
FROM sys.memory_global_by_current_bytes
WHERE current_alloc like '%MiB%' GROUP BY event_name
ORDER BY SUM(CAST(replace(current_alloc,'MiB','') as DECIMAL(10, 2)) ) DESC;
#2
SELECT event_name,sys.format_bytes(CURRENT_NUMBER_OF_BYTES_USED)
FROM performance_schema.memory_summary_global_by_event_name
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC
LIMIT 10;
5.3 账号级别统计
SELECT user,event_name,current_number_of_bytes_used/1024/1024 as MB_CURRENTLY_USED
FROM performance_schema.memory_summary_by_account_by_event_name
WHERE host<>"localhost"
ORDER BY current_number_of_bytes_used DESC LIMIT 10;
5.4 线程对应sql语句，内存使用统计
#1
SELECT thread_id,event_name,sys.format_bytes(CURRENT_NUMBER_OF_BYTES_USED)
FROM performance_schema.memory_summary_by_thread_by_event_name
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC
LIMIT 20;
#2
SELECT m.thread_id tid,m.user,esc.DIGEST_TEXT,m.current_allocated,m.total_allocated
FROM sys.memory_by_thread_by_current_bytes m,performance_schema.events_statements_current esc
WHERE m.thread_id=esc.THREAD_ID
5.5 打开所有内存性能监控，会影响性能，需注意
#打开
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';
#关闭
UPDATE performance_schema.setup_instruments SET ENABLED = 'NO' WHERE NAME LIKE 'memory/%';
#查看使用
SELECT * FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/%'
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC;
5.6 系统表内存监控信息
select * from sys.x$memory_by_host_by_current_bytes;
select * from sys.x$memory_by_thread_by_current_bytes;
select * from sys.x$memory_by_user_by_current_bytes;
select * from sys.x$memory_global_by_current_bytes;
select * from sys.x$memory_global_total;
select * from performance_schema.memory_summary_by_account_by_event_name;
select * from performance_schema.memory_summary_by_host_by_event_name;
select * from performance_schema.memory_summary_by_thread_by_event_name;
select * from performance_schema.memory_summary_by_user_by_event_name;
select * from performance_schema.memory_summary_global_by_event_name;
6.top 命令
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
69265 mysql 20 0 11.5g 7.9g 6172 S 1.3 68.1 69:27.64 mysqld
- free -h命令
[root@mpb91 ~]# free -h
total used free shared buff/cache available
Mem: 11G 8.2G 75M 7.9M 3.3G 3.3G
Swap: 8.0G 28M 8.0G
8.ps命令
[root@mpb91 ~]# ps eo user,pid,vsz,rss $(pgrep -f 'mysqld')
USER PID VSZ RSS
mysql 69265 12017232 8296068
9.pmap 命令
pmap是Linux调试及运维一个很好的工具，查看进程的内存映像信息
用法1：执行一段时间记录数据变化，最少20个记录，下面69265是MySQL pid
[root@mpb91 ~]#
while true; do pmap -d 69265 | tail -1; sleep 2; done
mapped: 12017236K writeable/private: 9644980K shared: 128K
mapped: 12017236K writeable/private: 9644980K shared: 128K
mapped: 12017236K writeable/private: 9644980K shared: 128K
mapped: 12017236K writeable/private: 9644980K shared: 128K
mapped: 12017236K writeable/private: 9644980K shared: 128K
mapped: 12017236K writeable/private: 9644980K shared: 128K
mapped: 12017236K writeable/private: 9644980K shared: 128K
mapped: 12017236K writeable/private: 9644980K shared: 128K
用法2：linux 命令pmap MySQL pid导出内存，下面69265是MySQL pid
[root@mpb91 ~]# pmap -X -p 69265 > /tmp/memmysql.txt
[root@mpb91 ~]# cat /tmp/memmysql.txt
69265: /usr/local/servers/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mysql/data/mysql.pid
Address Perm Offset Device Inode Size Rss Pss Referenced Anonymous Swap Locked Mapping
00400000 r-xp 00000000 fd:00 603980265 23160 5936 5936 5920 0 0 0 /usr/local/servers/mysql/bin/mysqld
01c9e000 rw-p 0169e000 fd:00 603980265 1668 908 908 772 556 0 0 /usr/local/servers/mysql/bin/mysqld
01e3f000 rw-p 00000000 00:00 0 760 612 612 384 612 0 0
03ce9000 rw-p 00000000 00:00 0 132 132 132 76 132 0 0 [heap]
03d0a000 rw-p 00000000 00:00 0 207144 207144 207144 206776 207144 0 0 [heap]
...
7ffd55555000 rw-p 00000000 00:00 0 132 84 84 12 84 0 0 [stack]
7ffd555bd000 r-xp 00000000 00:00 0 8 4 0 4 0 0 0 [vdso]
ffffffffff600000 r-xp 00000000 00:00 0 4 0 0 0 0 0 0 [vsyscall]
======== ======= ======= ========== ========= ==== ======
12017236 8307772 8306994 8146552 8300092 0 0 KB
RSS就是这个process实际占用的物理内存。
Dirty: 脏页的字节数（包括共享和私有的）。
Mapping: 占用内存的文件、或[anon]（分配的内存）、或[stack]（堆栈）。
writeable/private：进程所占用的私有地址空间大小，也就是该进程实际使用的内存大小。
1.首先使用/top/free/ps在系统级确定是否有内存泄露。如有，可以从top输出确定哪一个process。
2.pmap工具是能帮助确定process是否有memory leak。确定memory leak的原则：writeable/private (‘pmap –d’输出）如果在做重复的操作过程中一直保持稳定增长，那么一定有内存泄露
