从 配置、SQL、连接、存储引擎、程序、操作系统 六个方向排查
#排查思路：
1.通过top命令查看mysql服务当前的内存占用情况
2.查询mysql缓冲区内存大小
3.查询各线程占用的内存分布情况
4.查询mysql的连接数(当前连接、最大连接)、每个连接数的大小（M）
连接数内存大小 = 连接数 * 每个连接数大小
5.通过上面结果数据，分析并计算内存，对比top得到内存大小，检查差距是否很大。
如果各项指标得到结果均正常，但是和实际占用内存相差较大的情况，则需要考虑内存碎片没有即使回收的情况，比如：假性内存泄漏，内存碎片未即使回收。
***************内存碎片未即使回收 排查如下**********************
# 查看MySQL空闲但没有归还给操作系统的堆内存（glibc malloc 持有的 free memory）
#RSS：实际占用物理内存（已归还给 OS 后的真实占用
#VSZ：虚拟内存（进程申请的总内存，通常很大）
#差值越大 → 说明空闲未归还的堆内存越多
ps -p $pid -o pid,rss,vsz,pmem,comm --sort=-rss
ps -p 123456 -o pid,rss,vsz,pmem,comm --sort=-rss
# ① RSS
ps -p 12345 -o pid,rss,vsz,pmem,comm
# ② VmRSS
cat /proc/12345/status | egrep "VmRSS|VmData|VmSize"
# ③ 匿名内存
pmap -x 12345 | tail -20
# ④
SELECT SUM(current_alloc) FROM sys.memory_global_by_current_bytes;
# 检查是否使用 glibc（无输出则可能是 glibc）
lsof -p <mysqld_pid> | grep malloc
#强制 MySQL (mysqld 进程) 将空闲的堆内存归还给操作系统，从而降低进程的物理内存占用（RSS）：
#pid 12345 按需替换 ，建议在业务低谷期执行
gdb --batch --pid 12345 --ex 'call malloc_trim(0)'
gdb --batch --pid 12345 --ex 'call malloc_trim(0)' 2>&1 | cat
建议：
glibc 在数据库场景下的“已知特性”而非严重 bug。切换到 jemalloc 后，内存占用通常会稳定很多，且 RSS 能更好归还给 OS
SQL查询过程：
先查缓冲池 ，若在则直接读取，反之则查询磁盘并将磁盘数据放到缓冲池以供下次查询使用
1. 缓冲池优化·增加缓存提高查询速度
#查询缓存大小
show variables like 'innodb_buffer_pool_size'
#设置缓存(1G=1073741824字节)
set global innodb_buffer_pool_size = 1073741824
#注意上面设置方式重启后会失效
**#永久设置方式：修改my.ini文件中的 innodb_buffer_pool_size 值大小（记得重启服务）
**
大小设置建议：物理内存的80%
2. 慢SQL
-- 1. 查看占用内存最高的执行语句
SELECT query, exec_count, total_latency, avg_examined_rows
FROM sys.statement_analysis
ORDER BY total_latency DESC LIMIT 10;
# 查询正在执行的、消耗资源较多的 SQL：
SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;
-- 2. 查看哪些线程当前正在占用大量内存
SELECT thread_id, processlist_id, current_used_mem_mb
FROM sys.memory_thread_by_current_bytes
WHERE current_used_mem_mb > 50; -- 查找占用超过50MB的线程
--- 定位正在运行的“慢”过程 （如果内存正在飙升，立即查看当前正在运行的 SQL）
-- 查看当前正在执行的线程，观察 Time 和 Info
SHOW FULL PROCESSLIST;
--关注点： Time 很长且 Command 为 Query 的语句；以及 State 显示为 Copying to tmp table 或 Sending data 的语句。
#慢SQL开启
-- 开启慢查询日志功能
SET GLOBAL slow_query_log = 'ON';
-- 设置慢查询的判定阈值（单位：秒，建议先设为 1 或 2，视业务情况而定）
SET GLOBAL long_query_time = 1;
-- 设置日志输出路径（通常在数据目录下）
-- SET GLOBAL slow_query_log_file = '/var/lib/mysql/mysql-slow.log';
#验证
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
永久开启，修改 MySQL 的配置文件（my.cnf 或 my.ini）
[mysqld]
# 开启慢查询日志
slow_query_log = 1
# 设置慢查询时间阈值（超过此时间会被记录）
long_query_time = 1
# 日志文件路径（确保 MySQL 用户对该路径有写权限）
slow_query_log_file = /var/lib/mysql/mysql-slow.log
# 记录未使用索引的查询（可选，建议开启）
log_queries_not_using_indexes = 1
3. 排查手段
# 查看操作系统内存占用
1.使用 top 或 htop 查看 RES (常驻内存) 和 SHR (共享内存)
2.观察 free -m，注意 buff/cache 是否过高。MySQL 依赖系统缓存，这部分高通常是正常的
# 查看 MySQL 进程内存（RSS 是物理内存）
top -p $(pidof mysqld) # 或 htop
# 更详细的内存映射
pmap -x $(pidof mysqld) | tail -n 5
# 或用
cat /proc/$(pidof mysqld)/status | grep -E 'Vm'
#查看 MySQL 内部内存分配
-- 查看各内存分类的占用情况
SELECT * FROM sys.memory_global_by_current_bytes;
-- 查看哪些线程占用了较多内存
SELECT * FROM sys.memory_thread_by_current_bytes;
#排查配置问题
-- 查看所有相关内存参数
SHOW VARIABLES WHERE Variable_name IN (
'innodb_buffer_pool_size',
'innodb_log_buffer_size',
'sort_buffer_size',
'join_buffer_size',
'read_buffer_size',
'max_connections'
);
核算逻辑：
- 全局内存 = innodb_buffer_pool_size + innodb_log_buffer_size + …
- 单连接内存 = sort_buffer_size + join_buffer_size + read_buffer_size + …
- 总预估内存 = 全局内存 + (max_connections × 单连接内存)
注意： 不能超过了服务器物理内存的 80%
@[TOC]################# 优先排查 ######################
- 实时“抓现行”
#这条命令能列出当前 MySQL 实例中所有正在运行的线程
SHOW FULL PROCESSLIST;
核心列：
- Time：该线程执行了多久（单位：秒）。如果数值很大且状态不是 Sleep，说明该 SQL 执行时间过长
- State：线程当前正在做什么（重点关注： Sending data（可能正在从磁盘读取大批量数据）、Copying to tmp table（正在创建临时表，极度消耗内存）、Sorting result（正在排序，消耗 CPU 和内存））
- info：当前执行的完整 SQL 语句。如果语句很长，FULL 关键字能保证你看到完整内容，而不是被截断。
典型场景：
如果你发现某一行的 Time 很高，且 State 为 Sending data，这通常就是那个正在“吃掉”内存的 SQL。
- 查历史帐
你可以运行以下语句查看前 10 条最耗资源的 SQL：
SELECT
query,
exec_count, -- 执行次数
total_latency, -- 总耗时
avg_examined_rows, -- 平均扫描行数（重点关注）
rows_sent_avg, -- 平均返回行数
tmp_tables, -- 使用临时表的次数（内存杀手！）
disk_tmp_tables -- 使用磁盘临时表的次数
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;
@[TOC]4. max_connections（最大连接数）
-- 当前连接数
SHOW STATUS LIKE 'Threads_connected';
-- 当前正在执行的连接
SHOW STATUS LIKE 'Threads_running';
-- 历史最大连接数
SHOW STATUS LIKE 'Max_used_connections';
-- 最大连接数配置
SHOW VARIABLES LIKE 'max_connections';
-- 是否发生连接数耗尽
SHOW STATUS LIKE 'Connection_errors_max_connections';
-- 查看所有连接
SHOW FULL PROCESSLIST;
-- 按状态统计连接
SELECT COMMAND, COUNT(*)
FROM information_schema.PROCESSLIST
GROUP BY COMMAND;
-- 按用户统计连接
SELECT USER, COUNT(*)
FROM information_schema.PROCESSLIST
GROUP BY USER;
#1.查看当前连接数,Threads_connected:表示当前有多少个客户端连接到 MySQL
SHOW STATUS LIKE 'Threads_connected';
#2.再看真正执行 SQL 的连接：
SHOW STATUS LIKE 'Threads_running';
# Threads_running 4
#说明：
#58 个连接
#真正工作的只有 4 个
#其它 54 个都是 Sleep，这是正常现象
#3.查看历史最大连接数
SHOW STATUS LIKE 'Max_used_connections';
# Max_used_connections 100 表示自 MySQL 启动以来，最多只用过 126 个连接。
#4.再看
SHOW VARIABLES LIKE 'max_connections';
#max_connections = 1000
1000
↓
历史最高
126
配置过大。
#5.看连接是否打满
SHOW STATUS LIKE 'Connection_errors_max_connections';
#6.查看连接详情
SHOW FULL PROCESSLIST;
转载自 CSDN-专业IT技术社区
原文链接：https://blog.csdn.net/qq_33776323/article/details/162497719
