如何在MySQL 5.7中通过配置Buffer Pool优化InnoDB的读写性能

应据可用内存、chunk与instances对齐、数据热度三约束设值：查free -h的available列，确认innodb_buffer_pool_chunk_size×instances最小单位（如128M×8=1G），小内存VPS（≤2G）直接设256M并关performance_schema。

怎么设 innodb_buffer_pool_size 才不翻车

直接写死一个百分比（比如“设为内存的75%”）大概率出问题。它必须同时满足三个硬约束：系统可用内存、innodb_buffer_pool_chunk_size × innodb_buffer_pool_instances 的整数倍、以及实际数据热度。

• 先查真实可用内存：free -h 看 available 列，不是 total —— 混部机器上 Redis、Java 进程、甚至 systemd 日志都会抢内存
• 再查当前 chunk 和 instance：SELECT @@innodb_buffer_pool_chunk_size, @@innodb_buffer_pool_instances; 默认是 128M 和 8，最小调整单位就是 1024M（1G）
• 别用 MB/GB 单位写配置：配置文件里只能写 4G 或 4096M，写成 4GB 或 4096MB 会导致 MySQL 启动失败
• 小内存 VPS（≤2G）别套比例：直接设 innodb_buffer_pool_size = 256M 更稳，顺手关掉 performance_schema

innodb_buffer_pool_size 动态调大为什么报错 ERROR 1238

MySQL 5.7.5+ 支持在线调大，但不支持“任意改”。报这个错，基本等于你没看版本或没对齐 chunk。

• 5.7.4 及更早版本：该变量是只读的，SET GLOBAL 必报错，唯一办法是改 /etc/my.cnf + 重启
• 5.7.5+ 仍需满足对齐：假设当前 chunk_size=128M、instances=8，那你只能设成 1024M、2048M、3072M……设 1500M 会被 MySQL 自动向下取整到 1024M，你以为调了，其实没生效
• 调完必须立刻验证：SELECT @@innodb_buffer_pool_size; 看返回值是否如你所设，别信命令没报错就完了
• 动态调大只改运行时，MySQL 重启后恢复旧值——必须同步写进配置文件，否则下次启动还是老样子

命中率怎么看才准？别信 innodb_buffer_pool_hit_rate

这个字段在 MySQL 5.7+ 已被标记为“deprecated”，它用的是过时算法，数值虚高，参考价值极低。

• 真指标是这两个：Innodb_buffer_pool_read_requests（逻辑读）和 Innodb_buffer_pool_reads（物理读），公式： (1 − Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) × 100%
• 查法：SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_%read%';，算出来低于 95% 就该扩容或查慢查询是否走了索引
• 命中率高但响应慢？看 Innodb_buffer_pool_wait_free：只要它持续 > 0，说明脏页刷盘跟不上，buffer pool 不是不够，是写压力太大，得调 innodb_io_capacity 或拆写负载
• 顺便盯一眼 Innodb_buffer_pool_pages_free：长期 > 30%，说明设大了；长期 = 0 且命中率又低，说明缓存淘汰太猛，可能热点数据分散或没主键导致无效缓存

Buffer Pool 调大了，为什么 ps aux 看 mysqld 内存远超配置值

这是正常现象，innodb_buffer_pool_size 只管 InnoDB 缓冲池主块，不管其他开销。

• 每个活跃连接额外吃 1–2MB：100 个连接 ≈ +200MB，max_connections 设太高会悄悄吃掉大量内存
• sort_buffer_size、join_buffer_size、read_buffer_size 这些 per-connection 参数，按连接数线性放大
• performance_schema 默认开，小内存机器上能占 100MB+，资源紧张时建议关：performance_schema = OFF
• 控制块本身也占内存：约缓存页大小的 5%，16KB 页对应约 800 字节控制块，总量不可忽略

所以看到 RSS 比配置值高 20%–30%，别慌；但如果 swap 开始活动（vmstat 1 里 si/so 非零），那才是真内存不足，得立刻降 buffer pool 或清理其他进程。