17370845950

InnoDB缓冲池大小应设为物理内存的50%–75%，独占服务器推荐70%，共用环境建议50%并预留≥2GB；需为1MB整数倍，重启生效；命中率低（Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests>0.01）表明不足。

innodb_buffer_pool_size 设为物理内存的 50%–75%

这是 MySQL 最关键的性能参数，决定 InnoDB 缓存数据和索引的内存大小。设得太小会导致频繁磁盘读，设得太大可能引发系统 OOM。

• 若服务器独占运行 MySQL，innodb_buffer_pool_size 可设为物理内存的 70%（例如 16GB 内存 → 11G）
• 若共用机器（如跑 Docker、Redis），建议保守设为 50%，并预留至少 2GB 给系统和其他进程
• 该值必须是 1MB 的整数倍，且重启 MySQL 才生效；在线调整需 MySQL 5.7+ 且启用 innodb_buffer_pool_chunk_size
• 监控是否命中不足：查 SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests' 和 Innodb_buffer_pool_reads，比值 Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests > 0.01 表示缓存严重不足

max_connections 不要盲目调高

默认 151 对多数中小应用足够。盲目增大不仅浪费内存（每个连接约占用 256KB–2MB），还可能压垮线程调度或触发 OS 文件描述符限制。

• 先确认真实并发连接数：执行 SHOW STATUS LIKE 'Threads_connected'，观察峰值
• 应用层应使用连接池（如 HikariCP、mysql-connector-python 的 pool_size），而非每次请求新建连接
• 若确需提高，同步检查 OS 限制：ulimit -n，必要时在 /etc/security/limits.conf 中提升 mysql 用户的 nofile
• 设置后记得验证：启动时若报错 Can't create thread (errno 11) 或 Too many open files，说明系统资源已到瓶颈

log_bin 和 sync_binlog 的取舍

开启二进制日志（log_bin）是主从复制和 PITR（基于时间点恢复）的前提，但写 binlog 会拖慢写入性能，尤其当 sync_binlog = 1（每事务刷盘）时。

• 生产环境必须开启 log_bin；路径建议单独挂载 SSD 分区，避免与数据目录争 I/O
• sync_binlog = 1 保证 crash-safe，但吞吐下降明显；若允许最多丢失 1 秒事务，可设为 100（每 100 个事务刷一次）
• 注意：innodb_flush_log_at_trx_commit = 1 和 sync_binlog = 1 同时启用才能满足 ACID 的持久性要求；若关掉任一，主从延迟或崩溃后数据不一致风险上升
• 关闭 log_bin 仅适用于本地开发或只读测试库，线上禁止

tmp_table_size 和 max_heap_table_size 要匹配

这两个值控制内存临时表上限。若查询含大量 GROUP BY、ORDER BY 或 DISTINCT，临时表超限会自动转成磁盘 MyISAM 表，性能断崖下跌。

• 二者必须设为相同值，否则以较小者为准；建议从 64M 起步（tmp_table_size = 64M, max_heap_table_size = 64M）
• 监控是否频繁落盘：查 SHOW STATUS LIKE 'Created_tmp_disk_tables'，若该值增长快于 Created_tmp_tables，说明内存临时表不够用
• 不要无限制调大——每个连接都可能创建临时表，总内存消耗 = 连接数 × 该值，容易触发 OOM
• 更治本的方式是优化 SQL：加索引覆盖 ORDER BY 字段，避免 SELECT *，用 LIMIT 限制结果集

SET GLOBAL tmp_table_size = 67108864;
SET GLOBAL max_heap_table_size = 67108864;

真正影响调优效果的，往往不是参数本身，而是你是否清楚当前业务的查询模式、QPS 波峰、慢查询占比，以及 OS 层面的 I/O 调度器和文件系统配置。没监控就调参，和蒙眼换轮胎差不多。