centosmysql性能调优实战

CentOS 上 MySQL 性能调优实战手册

一 基线评估与监控

• 明确目标：以业务关键指标为牵引，优先保障P95/P99 延迟、QPS/TPS、错误率与连接可用性。
• 建立监控：在 CentOS 上部署htop、iostat -x 1、vmstat 1、sar等系统工具；在 MySQL 内持续采集SHOW GLOBAL STATUS/LONG STATUS、SHOW ENGINE INNODB STATUS、错误日志与慢查询。
• 基线采集：记录当前硬件与系统状态（CPU 型号与核数、内存、磁盘类型与阵列、文件系统、I/O 调度器、网络带宽）、MySQL 版本与关键变量、峰值 QPS/TPS、慢查询数量、连接数、InnoDB 缓冲池命中率、磁盘 await/rrqm/s、%util 等。
• 压测与验证：使用代表性负载进行压测（如 sysbench、业务脚本回放），在变更前后对比指标，确保优化收益可复现且不影响正确性。
• 变更流程：遵循“备份—小步变更—灰度/回滚预案—复核监控”，每次只调整少量参数并观察足够时间窗。

二 操作系统与存储层优化

• 存储与阵列：优先选择SSD/NVMe；阵列建议RAID10（高并发/高可靠），避免或慎用RAID5（写放大与重建压力大）。主库写多、从库可结合业务在RAID10/RAID5间权衡。
• 文件系统与挂载：大并发/大表业务优先XFS；常规业务可用ext4。挂载建议增加noatime、nodiratime，在具备电池/超级电容保护的 RAID 卡时可考虑nobarrier；如用 deadline 调度器，可按业务将read_expire设为write_expire 的约 1/2（如 read_expire=500ms、write_expire=1000ms）。
• I/O 调度器：SSD/NVMe 推荐none/mq-deadline；机械盘可用deadline。示例：echo deadline > /sys/block/sdX/queue/scheduler。
• 内核与网络：适度降低vm.swappiness（如10），设置vm.dirty_background_ratio=5–10、vm.dirty_ratio≈其 2 倍以平滑刷脏；网络侧可调大net.core.somaxconn、net.ipv4.tcp_max_syn_backlog、开启tcp_tw_reuse/tcp_tw_recycle（注意与内核/云环境兼容性）、缩短tcp_fin_timeout、适度增大rmem/wmem与tcp_rmem/tcp_wmem缓冲，提升高并发连接与短连接场景表现。

三 InnoDB 与 my.cnf 关键参数建议

• 内存与缓冲池：InnoDB 为主时，将innodb_buffer_pool_size设为物理内存的50%–70%（专用库可更高，但避免与 OS 争抢内存）；启用innodb_buffer_pool_instances（如4/8/16，按内存与并发划分）以降低锁争用。
• 日志与持久性：将innodb_log_file_size设为256M（多数场景足够，写入密集可适当增大），innodb_log_files_in_group=2；innodb_flush_log_at_trx_commit=1提供最强持久性（每次提交落盘），对延迟极敏感且可接受秒级数据丢失风险时可权衡2；innodb_flush_method=O_DIRECT减少双缓冲；innodb_io_capacity/innodb_io_capacity_max按介质设置（如 SSD 常见2000–5000，更高端介质可上调），配合innodb_max_dirty_pages_pct（如75–80）与innodb_adaptive_flushing=ON平滑刷脏。
• 表空间与文件：启用innodb_file_per_table=1；如默认ibdata1过小，可按需设置更大的innodb_data_file_path=ibdata1:1G:autoextend（变更需谨慎、按官方流程操作）。
• 并发与连接：将max_connections设为业务峰值所需并预留一定余量；同时关注thread_cache_size、back_log等，避免频繁线程创建与连接排队；skip-name-resolve可缩短连接建立时间（授权需改用 IP）。
• 缓存与临时表：MySQL 8.0 已移除查询缓存（QC），5.7 及更早版本如启用 QC，建议仅用于读多写少且重复度极高的场景，生产常关闭或控制在≤512M；合理设置tmp_table_size与max_heap_table_size，避免磁盘临时表放大。
• 引擎与索引：默认存储引擎使用InnoDB；如仍存MyISAM，将key_buffer_size控制在几十 MB量级即可，避免占用过多内存。

示例my.cnf片段（仅示意，需结合实例与硬件调整）： [mysqld] innodb_buffer_pool_size = 24G innodb_buffer_pool_instances = 8 innodb_log_file_size = 256M innodb_log_files_in_group = 2 innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT innodb_io_capacity = 5000 innodb_io_capacity_max = 20000 innodb_max_dirty_pages_pct = 78 innodb_adaptive_flushing = ON innodb_file_per_table = 1 innodb_data_file_path = ibdata1:1G:autoextend max_connections = 1500 thread_cache_size = 256 back_log = 512 skip-name-resolve default_storage_engine = InnoDB

四 SQL 与索引优化

• 抓慢 SQL：在 my.cnf 中开启slow_query_log、设置long_query_time=1–2（按业务容忍度），必要时开启log_queries_not_using_indexes；使用pt-query-digest或 mysqlsla分析慢日志，聚焦 Top SQL。
• 执行计划：对慢 SQL 使用EXPLAIN检查扫描方式（ALL/ref/range/index_merge）、是否走索引、扫描行数与 Extra 中的Using filesort/Using temporary等，优先通过索引与改写 SQL 消除文件排序与临时表。
• 索引策略：为高频WHERE/JOIN/ORDER BY/GROUP BY列建立合适索引；避免在低基数列上建索引；控制索引数量与宽度，减少写入与缓存压力；定期用pt-duplicate-key-checker清理冗余索引，用pt-index-usage识别低效索引。
• 语句与分页：避免SELECT ，只取必要列；减少大表OFFSET深翻页，改用游标/键集分页*；批量操作分批提交；合理使用JOIN与子查询，必要时拆分与预计算。

五 维护、压测与回滚预案

• 例行维护：定期执行ANALYZE TABLE（更新统计信息）、对高碎片表执行OPTIMIZE TABLE（InnoDB 多为在线 DDL，但仍需评估锁与空间影响）；周期性重建/整理分区表；校验主从一致性（如 pt-table-checksum）。
• 在线变更：对大表结构变更优先使用pt-online-schema-change或gh-ost，降低锁表风险与业务抖动。
• 压测闭环：以sysbench oltp_read_write/point_select或业务回放进行回归压测，观察TPS/QPS、P95/P99、InnoDB 行锁等待、磁盘 I/O、错误率等，确认优化收益后再推广。
• 备份与回滚：任何配置变更前先做全量/增量备份；变更失败或指标劣化时按预案快速回滚至上一个稳定版本与配置，并保留变更记录与回放数据用于复盘。