文章目录每日一句正能量1. 背景与问题2. 环境与数据3. 复现过程4. 方案实施5. 结果对比6. 风险与复盘7. 常见问题 FAQ每日一句正能量“慢下来但不要停下来。”慢是为了校准方向、恢复觉察让每一步都扎实。不停则是守护那份最宝贵的“动量”——一旦完全停止重新启动所需的心理能量是巨大的。1. 背景与问题生产交易系统在业务高峰收到连续告警接口超时、数据库响应升高、TPS下降。CPU利用率约55%内存和网络均正常单一资源指标无法直接解释故障。运维团队采用KDDM数据库诊断方法建立“告警→监控→SQL→执行计划→参数→根因”的分析流程最终定位到索引失效叠加排序落盘导致的综合性能异常。2. 环境与数据环境PostgreSQL 16Linux 964 Core / 256GB RAMNVMe SSD峰值并发2200告警指标指标异常值DB Time4180 sP99延迟182 msIO Wait37%TPS19200SQLSELECTcustomer_id,SUM(amount)FROMordersWHEREcreate_timeCURRENT_DATE-30GROUPBYcustomer_idORDERBYSUM(amount)DESCLIMIT100;优化前执行计划Gather Merge Sort Method: external merge Disk: 960MB Parallel Seq Scan on orders Execution Time: 12.63 s3. 复现过程收集告警时间窗口的监控数据。查看KDDM诊断报告确认等待事件与Top SQL。使用EXPLAIN (ANALYZE, BUFFERS)分析执行计划。对照pg_stat_statements、IO监控和业务日志建立证据链。证据链告警触发时间与DB Time同步上升。Top SQL耗时占比超过45%。排序落盘导致IO Wait增加。缺少复合索引造成全表扫描。索引失效原因分析日期范围过大导致索引选择性低WHERE create_time CURRENT_DATE - 30覆盖了最近 30 天的数据在订单表数据量较大的场景下该范围可能命中表中绝大部分行。优化器评估后认为走索引回表的代价高于全表扫描因此放弃索引选择Parallel Seq Scan。排序字段未包含在索引中SQL 中ORDER BY SUM(amount) DESC的排序键是聚合结果而现有索引并未覆盖customer_id与amount的联合结构无法直接提供有序输出优化器只能对聚合结果做external merge排序数据量超出work_mem后落盘产生 960MB 的磁盘排序直接推高 IO Wait。复合索引idx_orders_time_customer(create_time, customer_id)的覆盖逻辑过滤条件索引首列create_time与WHERE条件匹配优化器可通过索引快速定位目标时间范围内的数据避免全表扫描。排序条件索引第二列customer_id与GROUP BY customer_id对齐索引天然按(create_time, customer_id)有序排列聚合过程可借助索引顺序完成无需额外排序。消除排序落盘优化后执行计划由external merge Disk: 960MB变为quicksort Memory: 30MB排序完全在内存中完成IO Wait 随之大幅下降。索引失效排查步骤检查索引使用率通过pg_stat_user_indexes视图查看索引的扫描次数与命中情况确认idx_orders_time_customer是否被实际使用。SELECTschemaname,relname,indexrelname,idx_scan,idx_tup_read,idx_tup_fetchFROMpg_stat_user_indexesWHERErelnameordersORDERBYidx_scanDESC;若idx_scan长期为 0说明优化器从未选择该索引需要进一步分析原因。对比走索引与全表扫描的代价使用EXPLAIN分别查看两种执行计划的代价估算确认优化器为何放弃索引。-- 强制走索引SETenable_seqscanoff;EXPLAIN(ANALYZE,BUFFERS)SELECTcustomer_id,SUM(amount)FROMordersWHEREcreate_timeCURRENT_DATE-30GROUPBYcustomer_idORDERBYSUM(amount)DESCLIMIT100;-- 恢复默认对比全表扫描RESET enable_seqscan;EXPLAIN(ANALYZE,BUFFERS)SELECTcustomer_id,SUM(amount)FROMordersWHEREcreate_timeCURRENT_DATE-30GROUPBYcustomer_idORDERBYSUM(amount)DESCLIMIT100;对比两次执行计划中的cost、Execution Time与Buffers即可量化索引回表与全表扫描的代价差异。验证复合索引是否被优化器采用创建索引后重新执行EXPLAIN并观察执行计划中的访问路径。EXPLAIN(ANALYZE,BUFFERS)SELECTcustomer_id,SUM(amount)FROMordersWHEREcreate_timeCURRENT_DATE-30GROUPBYcustomer_idORDERBYSUM(amount)DESCLIMIT100;若执行计划中出现Index Scan using idx_orders_time_customer且排序方式为quicksort Memory说明复合索引已被优化器采用排序落盘问题得到解决。下面是 KDDM 诊断流程的完整步骤从告警触发到根因确认形成闭环关键输出告警时间窗口关键输出DB Time、IO Wait、TPS 趋势关键输出等待事件、Top SQL关键输出Top SQL 耗时占比关键输出排序落盘、全表扫描关键输出work_mem、索引优化关键输出索引失效 排序落盘验证通过指标异常告警触发监控数据收集KDDM 报告分析SQL 定位执行计划分析参数调整根因确认方案实施与验证持续监控4. 方案实施新增索引CREATEINDEXidx_orders_time_customerONorders(create_time,customer_id);参数优化work_mem64MB effective_cache_size96GB random_page_cost1.1优化后执行计划GroupAggregate - Index Scan using idx_orders_time_customer Sort Method: quicksort Memory: 30MB Execution Time: 3.41 s持续监控DB TimeTop SQL等待事件P95/P99Buffer Hit5. 结果对比指标优化前优化后Top SQL耗时12.63 s3.41 sDB Time4180 s2360 sIO Wait37%13%P99延迟182 ms69 msTPS1920024300优化后告警消失业务恢复稳定证据链各项指标相互印证。下面是优化前后各项关键指标的直观对比优化前后性能指标对比Top SQL耗时DB TimeIO WaitP99延迟TPS240002200020000180001600014000120001000080006000400020000数值说明Top SQL耗时、DB Time、IO Wait、P99延迟均为越低越好TPS为越高越好。从图中可以直观看到优化后各项指标均显著改善——Top SQL耗时下降约73%DB Time下降约44%IO Wait下降约65%P99延迟下降约62%TPS提升约27%整体性能得到大幅提升。6. 风险与复盘风险仅凭单一告警容易误判。参数调优需结合压测不可直接套用。索引增加会带来写入成本应评估维护开销。复盘建议建立统一诊断流程从告警开始逐步缩小范围。结合监控、日志、SQL、执行计划和等待事件形成完整证据链。每次优化保留前后参数、执行计划和压测结果方便回溯。定期开展巡检持续跟踪DB Time、Top SQL及等待事件变化趋势。本文通过完整案例展示了KDDM诊断流程从告警到根因定位形成闭环结合执行计划、监控数据、参数调整和结果对比为生产环境数据库综合故障诊断提供参考。转载自https://blog.csdn.net/u014727709/article/details/164124633欢迎 点赞✍评论⭐收藏欢迎指正7. 常见问题 FAQQ1为什么 work_mem 设置为 64MB 而不是更大work_mem是每个排序、哈希等操作可使用的内存上限并非全局共享。在峰值并发 2200 的场景下若同时有大量会话执行排序操作过大的work_mem会成倍放大内存占用极易触发 OOM。64MB 是在「排序不落盘」与「整体内存可控」之间取得的平衡——本案例排序数据量约 30MB64MB 已足够容纳同时为并发会话预留了充足的内存余量。实际调优时应结合压测逐步上调并监控内存水位而非盲目加大。Q2如果索引仍然未被使用下一步排查方向是什么若创建复合索引后执行计划仍走全表扫描建议按以下方向排查检查统计信息是否过期执行ANALYZE orders;刷新统计信息优化器依赖统计信息估算代价过期统计会导致错误选择。确认查询写法是否匹配索引例如对索引列使用函数或隐式类型转换如create_time::date会导致索引失效应改写为create_time CURRENT_DATE - 30这类可匹配索引首列的写法。对比强制走索引与全表扫描的真实代价用SET enable_seqscan off;强制走索引后对比Execution Time与Buffers若索引回表代价确实更高说明该查询本身不适合此索引需重新设计索引或改写 SQL。检查索引维护状态通过pg_stat_user_indexes观察idx_scan是否持续为 0并结合pg_stat_all_indexes确认索引是否因大量写入而膨胀必要时REINDEX重建。Q3如何判断排序是否落盘最直接的方式是查看执行计划中的Sort Method字段quicksort Memory: 30MB排序完全在内存中完成未落盘。external merge Disk: 960MB数据量超出work_mem排序已落盘到临时文件会显著推高 IO Wait。此外还可以通过EXPLAIN (ANALYZE, BUFFERS)观察Buffers: temp read/written是否有非零值或监控pg_stat_database中的temp_files与temp_bytes字段两者持续增长即说明存在排序落盘。转载自https://blog.csdn.net/u014727709/article/details/164124633欢迎 点赞✍评论⭐收藏欢迎指正