mysql-内存排查

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

top -b -n1 -p $(pidof mysqld) |grep PID -A 1

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

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

 

2. 查看mysql是否使用swap

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

2.1 查看应用在使用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

 

3. 查看mysql是否使用LINUX 的文件缓存

show variables like "innodb_flush_method";

 4. 查看mysql是否存在匿名内存块

 pmap -x 19544  #mysql进程号，标记为 [anon] 的内存块，这些即为匿名块。

这里匿名块通常正常，暂时不知道具体如何分析，先记在这里。

 

5.查看内存配置

查看全局共享内存和线程内存分配

全局共享

• innodb_buffer_pool_size：InnoDB缓冲池的大小
• innodb_additional_mem_pool_size：InnoDB存放数据字典和其他内部数据结构的内存大小，5.7已被移除
• innodb_log_buffer_size：InnoDB日志缓冲的大小
• key_buffer_size：MyISAM缓存索引块的内存大小
• query_cache_size：查询缓冲的大小，8.0已被移除

线程独占

• thread_stack：每个线程分配的堆栈大小
• sort_buffer_size：排序缓冲的大小。只会在有查询需要做排序操作时才会为该缓存分配内存，并且会一次性分配该参数指定大小的全部内存。
• join_buffer_size：连接缓冲的大小。只会在有表连接时才会为该缓存分配内存，并且会一次性分配该参数指定大小的全部内存。
• read_buffer_size：顺序读缓冲的大小。只会在有查询需要使用时才会为该缓存分配内存，并且会一次性分配该参数指定大小的全部内存。
• read_rnd_buffer_size：随机读缓冲的大小、MRR缓冲的大小。只会在有查询需要使用时才会为该缓存分配内存，并且只会分配需要的内存大小而不是全部指定的大小。
• tmp_table_size/max_heap_table_size：内存临时表的大小
• binlog_cache_size：二进制日志缓冲的大小

如：thread，read，sort，join,tmp 等只是在需要的时候才分配，并且在那些操作做完之后就释放。

默认分配thread_stack（256K,512k)，空闲时这些内存是默认使用，除此之外网络缓存，还有表缓存等。大致评估会在1M~3M这样的情况。

SELECT concat(round(@@key_buffer_size / 1024 / 1024, 2), 'MB') as key_buffer_size_MB, 
concat(round(@@innodb_buffer_pool_size / 1024 / 1024, 2), 'MB') as innodb_buffer_pool_size_MB, 
concat(round(@@innodb_log_buffer_size / 1024 / 1024, 2), 'MB') as innodb_log_buffer_size_MB, 
concat(round(@@tmp_table_size / 1024 / 1024, 2), 'MB') as tmp_table_size_MB, 
concat(round(@@read_buffer_size / 1024 / 1024, 2), 'MB') as read_buffer_size_MB, 
concat(round(@@sort_buffer_size / 1024 / 1024, 2), 'MB') as sort_buffer_size_MB, 
concat(round(@@join_buffer_size / 1024 / 1024, 2), 'MB') as join_buffer_size_MB, 
concat(round(@@read_rnd_buffer_size / 1024 / 1024, 2), 'MB') as read_rnd_buffer_size_MB, 
concat(round(@@binlog_cache_size / 1024 / 1024, 2), 'MB') as binlog_cache_size_MB, 
concat(round(@@thread_stack / 1024 / 1024, 2), 'MB') as thread_stack_MB, 
(SELECT COUNT(host) FROM information_schema.processlist where command<>'Sleep') as active_connections_count\G

查看数据库连接数

show status  like "%Thread%";
SELECT * FROM information_schema.processlist;

SHOW STATUS LIKE "%Thread%"; 和 SELECT * FROM information_schema.processlist; 返回的线程数可能会有所不同，因为它们提供的是不同层次和不同类型的线程信息。

SHOW STATUS LIKE "%Thread%"; 主要显示的是系统级别的线程状态信息，例如连接的线程数、正在运行的线程数等，这些线程数是数据库服务器级别的统计数据。

SELECT * FROM information_schema.processlist; 则提供了当前活动线程的详细信息，包括每个线程的ID、用户、主机、数据库、状态等，这些是针对具体执行操作的线程。

因此，两者返回的线程数可能会有差异，因为系统级别的线程数统计和实际活动线程数之间可能存在一定的时间差或统计误差。要获取准确的线程信息，建议综合考虑这两种方式提供的线程数据。

 

计算全局内存+线程内存最大使用量

select ( @@key_buffer_size + @@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@max_connections * (@@read_buffer_size 
+ @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size + @@thread_stack + @@tmp_table_size ) ) /(1024*1024*1024) as max_memory_gb;

查看NNODB 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 到底够不够用的快速的方法

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

# Innodb_buffer_pool_reads衡量了需要从磁盘读取数据到缓冲池的次数

#Innodb_buffer_pool_read_requests衡量了数据已经在缓冲池中而无需从磁盘读取数据。

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

 

6.查看内存使用

打开所有内存性能监控，会影响性能，需注意。默认是全打开的

#打开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;

系统表内存监控信息

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;      #全局纬度的内存监控表

COUNT_ALLOC：内存分配次数
COUNT_FREE：内存回收次数
SUM_NUMBER_OF_BYTES_ALLOC：内存分配大小
SUM_NUMBER_OF_BYTES_FREE：内存回收大小
CURRENT_COUNT_USED：当前分配的内存，通过COUNT_ALLOC-COUNT_FREE计算得到
CURRENT_NUMBER_OF_BYTES_USED：当前分配的内存大小，通过SUM_NUMBER_OF_BYTES_ALLOC-SUM_NUMBER_OF_BYTES_FREE计算得到
LOW_COUNT_USED：CURRENT_COUNT_USED的最小值
HIGH_COUNT_USED：CURRENT_COUNT_USED的最大值
LOW_NUMBER_OF_BYTES_USED：CURRENT_NUMBER_OF_BYTES_USED的最小值
HIGH_NUMBER_OF_BYTES_USED：CURRENT_NUMBER_OF_BYTES_USED的最大值

 

 总内存使用

从sys.memory_global_by_current_bytes表中筛选出的current_alloc字段中包含'MiB'的数值数据，并将这些数值相加求和。

 实际上排除了innodb/buf_buf_pool（GiB单位），和其他kib的时间后求和。

SELECT SUM(CAST(replace(current_alloc,'MiB','')  as DECIMAL(10, 2))  )  FROM sys.memory_global_by_current_bytes WHERE current_alloc like '%MiB%';

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 20;

 

分主机统计内存

SELECT  host,event_name,  sys.format_bytes(CURRENT_NUMBER_OF_BYTES_USED)  FROM performance_schema.memory_summary_by_host_by_event_name  
ORDER BY  CURRENT_NUMBER_OF_BYTES_USED DESC LIMIT 20;

select *, (current_allocated/1024/1024) as current_allocated_MB from sys.x$memory_by_host_by_current_bytes;

 

分事件统计内存

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 ;

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;

分账号统计内存

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 
ORDER BY  current_number_of_bytes_used DESC LIMIT 10;

select *, (current_allocated/1024/1024) from sys.x$memory_by_user_by_current_bytes;

备注：有必要统计用户级别内存，因为很多环境对接了第三方插件，模拟从库，这些插件容易内存不释放。

 

分线程统计内存

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;

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\G

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

select * from performance_schema.global_status  where variable_name like '%tmp%tables';

select * from sys.memory_global_by_current_bytes where event_name like '%temp%'\G

当前MYSQL使用的TEMP 的删除情况

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

在使用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';

 

7.查看存储过程&函数&触发器&视图内存使用

目前积累的使用经验中，存储过程&函数&触发器&视图 在MySQL场景下是不适合的。性能不好，又容易发现内存不释放的问题，所以建议尽量避免。

 存储过程

mysql5.7

SELECT db,type,count(*) FROM mysql.procWHERE db not in ('mysql','information_schema','performance_schema','sys') GROUP BY db, type;

mysql8.0

SELECT  Routine_schema, Routine_type FROM information_schema.Routines WHERE  Routine_schema 
not in ('mysql','information_schema','performance_schema','sys') GROUP BY Routine_schemaa, Routine_type;

视图

SELECT  TABLE_SCHEMA , COUNT(TABLE_NAME) 
FROM information_schema.VIEWSWHERE TABLE_SCHEMA not in ('mysql','information_schema','performance_schema','sys') GROUP BY TABLE_SCHEMA ;

触发器

SELECT TRIGGER_SCHEMA, count(*) FROM information_schema.triggers 
WHERE  TRIGGER_SCHEMA not in ('mysql','information_schema','performance_schema','sys') GROUP BY TRIGGER_SCHEMA;