数据库调优口诀帮助做深入的香港站群服务器优化工作

1.

准备与基线测量

a. 在开始前备份：mysqldump --single-transaction 或 pg_basebackup，确认备份可用。
b. 收集基线指标：使用 vmstat 1 5, iostat -x 1 5, sar, top/htop；在MySQL上执行 SHOW GLOBAL STATUS; 在Postgres执行 pg_stat_activity 与 pg_stat_database。记录响应时间、TPS、慢查询数与磁盘IO。

2.

部署监控与告警

a. 部署 Prometheus + Grafana 或 Zabbix，监控关键指标：CPU、内存、磁盘队列、连接数、InnoDB缓冲池命中率、慢查询。
b. 开启慢查询日志：MySQL 设置 slow_query_log=ON, long_query_time=1，使用 pt-query-digest 分析。

3.

索引与查询优化实操

a. 找出慢查询：pt-query-digest /var/log/mysql/slow.log，按影响排序。
b. 对每条慢SQL做 EXPLAIN 分析，定位全表扫描/文件排序/临时表；若缺索引则 CREATE INDEX，避免在 WHERE 上对列做函数操作。
c. 对复杂聚合考虑预计算或物化视图，分页使用延迟高效方式（seek pagination）。

4.

架构与分库分表策略

a. 小流量先垂直切分（读写分离）：主从复制 + Proxy（如 HAProxy 或 ProxySQL）。
b. 高并发用分库分表：根据业务维度取模或时间分区，落地脚本迁移历史表，保持路由逻辑与索引一致。

5.

MySQL/InnoDB 参数实战调整

a. innodb_buffer_pool_size = 60%~70% 内存（独占数据库实例）。
b. innodb_log_file_size = 256M~1G（根据写入量调整），innodb_flush_method = O_DIRECT，innodb_io_capacity 根据SSD能力设置如2000。
c. thread_cache_size = 100, max_connections 根据峰值并发设但结合连接池，禁用 query_cache_size=0（MySQL新版已弃用）。

6.

PostgreSQL 调优要点

a. shared_buffers ≈ 内存的25%，effective_cache_size ≈ 系统可用内存的50%-75%。
b. work_mem 根据并发复杂查询设置，maintenance_work_mem 提高索引重建速度。开启 pg_stat_statements 用于分析。

7.

系统层面与网络优化（针对香港机房）

a. sysctl 调整：net.core.somaxconn=10240, net.ipv4.tcp_fin_timeout=30, vm.swappiness=1。使用 echo 设置并写入 /etc/sysctl.conf。
b. 磁盘与IO调度：SSD 上使用 noop 或 deadline，mount 时使用 noatime；若使用RAID，关注队列深度与多队列网卡（multiqueue）设置。

8.

缓存与中间件实践

a. 热点数据上 Redis 或 Memcached，设置合理过期与持久化策略（RDB/AOF）。
b. 使用应用层缓存（本地 L1 + 分布式 L2），读写分离配合 ProxySQL 做自动故障切换。

9.

备份、恢复与演练

a. 制定 RPO/RTO，定时全量+增量备份，测试恢复流程：定期做恢复演练，记录耗时与问题。
b. 对主从切换做演练：模拟主库宕机，验证自动/手动切换流程。

10.

滚动发布与变更控制

a. 变更配置先在预生产或canary节点验证，使用 ansible/terraform 管理配置，保证可回滚。
b. 调整参数先在低峰期逐步放量：小窗口修改 -> 观察 -> 全量应用。

11.

性能测试与容量规划

a. 使用 sysbench 或 pgbench 进行压力测试，根据95p响应时间核算机器数量。
b. 按业务增长做线性或折半扩容，优先横向扩展以降低单点压力。

12.

香港站群专属注意事项

a. 选择香港本地机房或CDN节点减少延迟，与内地访问考虑合规与网络链路质量。
b. 节假日与时区高峰（如双十一类似营销）提前做容量预留与压测。

13.

问：如何快速定位香港站群中最耗资源的数据库实例？

14.

答：先在监控面板筛选CPU、IOPS、磁盘队列、连接数异常的实例；登录实例执行 SHOW GLOBAL STATUS 与 vmstat/iostat；开启慢查询日志并用 pt-query-digest 列出 Top SQL，结合 EXPLAIN 判断是否为索引/查询问题。

15.

问：在香港机房做深度优化时，哪些系统参数是优先级最高的？

16.

答：优先调整内存与IO相关参数：MySQL 的 innodb_buffer_pool_size、innodb_log_file_size；系统层面的 vm.swappiness=1、磁盘调度器（noop/deadline）、net.core.somaxconn；再根据实际IO能力调 innodb_io_capacity。

17.

问：如何在不影响线上服务的情况下做索引优化与大表改造？

18.

答：使用在线DDL工具或数据库提供的在线改表（如 MySQL 的 pt-online-schema-change 或 gh-ost），分批迁移数据到新分区表，先在从库回放并验证，最后切换写流量并再同步回主库，整个过程监控慢查询与锁等待。

来源：数据库调优口诀帮助做深入的香港站群服务器优化工作