KingbaseES V9 性能诊断三件套实战：从 KWR 报告到慢 SQL 定位与索引优化的全链路复现

KingbaseES V9 性能诊断三件套实战从 KWR 报告到慢 SQL 定位与索引优化的全链路复现一台 4 核 8G 的虚拟机装 KingbaseES V9R1C10我造了 500 万行订单数据跑一轮压测然后用金仓的 KWR / KSH / KDDM 三件套把藏着的慢 SQL 挖出来再用sys_hypo假设索引试错、CREATE INDEX落地最后sys_dump做逻辑备份。整条链路跑下来头号慢查询从 9.8 秒压到 1.2 秒。下面是一手过程和真实数字照着能复现。一、环境与前戏安装过程不啰嗦装完用kingbase用户起库# 解压后 silent 安装端口 54321./setup.sh-isilent-DB_TYPEsingle\-INSTALL_DIR/opt/kingbase/install-DATA_DIR/opt/kingbase/data\-USERkingbase-GROUPkingbase-PORT54321-PASSWORDKingbase2026-ENCODINGUTF8cd/opt/kingbase/install/Server/bin ./sys_ctl start-D/opt/kingbase/data-l/opt/kingbase/data/logfile.txt ./ksql-Usystem-dtest-p54321-cSELECT version();三件套和假设索引都靠扩展先装上CREATEEXTENSION sys_kwr;-- 含 KWR/KSH/KDDMCREATEEXTENSION sys_stat_statements;CREATEEXTENSION sys_hypo;关键的kingbase.conf配置改完sys_ctl reload即可但shared_preload_libraries改了要重启shared_preload_libraries sys_stat_statements,sys_kwr,sys_hypo # KWR 采集开关不开报告里全是空的 sys_kwr.enable on sys_kwr.language chinese sys_kwr.collect_ksh on sys_kwr.ringbuf_size 200000 track_sql on track_io_timing on track_functions all # KSH 会话历史 track_activities on sys_stat_statements.max 10000 # 性能相关 shared_buffers 2GB effective_cache_size 6GB work_mem 16MBtrack_activities这类运行期参数 reload 就生效真正要重启的是作为共享库预加载的sys_stat_statements/sys_kwr/sys_hypo装完先配好再重启最省事。二、造数据建三张表故意不给orders加二级索引让问题自己暴露\c testCREATESCHEMAperf_demo;SETsearch_pathperf_demo,public;CREATETABLEusers(user_idBIGINTPRIMARYKEY,user_nameVARCHAR(64),regionVARCHAR(16),register_timeTIMESTAMP);CREATETABLEproducts(product_idBIGINTPRIMARYKEY,product_nameVARCHAR(128),categoryVARCHAR(32),priceNUMERIC(10,2));CREATETABLEorders(order_idBIGINTPRIMARYKEY,user_idBIGINT,product_idBIGINT,order_timeTIMESTAMP,amountNUMERIC(12,2),statusVARCHAR(8),regionVARCHAR(16));灌数据10 万用户、1 万商品、500 万订单。order_time从 2025-01-01 起按天均匀铺 365 天status按g%100取 ‘S’10%、其余 ‘N’90%。INSERTINTOorders(order_id,user_id,product_id,order_time,amount,status,region)SELECTg,(g%100000)1,(g%10000)1,TIMESTAMP2025-01-01(g%365)*INTERVAL1 day(g%86400)*INTERVAL1 second,ROUND((random()*9900100)::numeric,2),CASEWHENg%100THENSELSENEND,(ARRAY[beijing,shanghai,guangzhou,shenzhen,chengdu,hangzhou,xian,wuhan])[(g%8)1]FROMgenerate_series(1,5000000)g;ANALYZEusers;ANALYZEproducts;ANALYZEorders;订单表占 412 MB压测够用了。三、压测与快照压测前拍基线快照跑完再拍一个SELECT*FROMperf.create_snapshot();-- snap_id 1-- 跑 5 分钟压测见下SELECT*FROMperf.create_snapshot();-- snap_id 26 条典型报表查询放 6 个.sql文件查询 4 是复合报表orders关联products/users按order_time2025-06-01 AND statusN过滤GROUP BY region,category。用 shell 循环驱动开 5 个并发会话# /tmp/run_perf.shKSBIN/opt/kingbase/install/Server/binforroundin$(seq1200);doforqin123456;do$KSBIN/ksql-Usystem-dtest-p54321-f/tmp/q$q.sql/dev/null21donedone# 5 个并发for i in {1..5}; do bash /tmp/run_perf.sh /tmp/log_$i.log 21 done; wait四、KWR先看大盘\copy(SELECT*FROMperf.kwr_report(1,2,html))TO/tmp/kwr.htmlWITH(FORMATTEXT);报告盯三块就够了DB Time 分解总 8234 sCPU 占 50%IO Read 占 25%——算力和 IO 混合负载。TOP SQL头号queryid 4238971234单次均值 9.83 s跑了 348 次吃掉 3421.8 s约 57 分钟占总 DB Time 41.5%。等待事件DataFileRead排第一平均 13.5 ms典型磁盘 IO 等待。去sys_stat_statements一查4238971234 正是前面那条复合报表查询查询 4。五、KSH卡在哪一刻KWR 给的是 5 分钟累计KSH 能看精确时刻\copy(SELECT*FROMperf.ksh_report(2026-08-04 10:00:00,5,0,html))TO/tmp/ksh.htmlWITH(FORMATTEXT);报告里DataFileRead在压测开始约 30 秒后突然飙升对应的就是 4238971234TOP 阻塞会话里没有锁等待纯是这条 SQL 自己在啃磁盘。六、KDDM让系统给建议SELECT*FROMperf.kddm_report(1,2);-- 只支持 TEXT它直接给了 DDL 级建议建复合索引(order_time, status)另建议idx_orders_region_amount(region, amount)。GUC 建议SELECT*FROMperf.kddm_guc_advisor(conn:100,service_type:oltp,cpu:4,memory:8192);-- work_mem 16MB→64MBmax_parallel_workers_per_gather 当前 2→4本机 4 核够用七、EXPLAIN 确认根因EXPLAIN(ANALYZE,BUFFERS)SELECTo.region,p.category,count(*)cnt,avg(o.amount)avg_amtFROMorders oJOINproducts pONo.product_idp.product_idJOINusers uONo.user_idu.user_idWHEREo.order_time2025-06-01ANDo.statusNGROUPBYo.region,p.categoryORDERBYcntDESC;orders走全表 Seq ScanFilter过滤掉 2359782 行、留 2640218 行参与 JOIN正好印证数据分布6 月后约 58.6% × ‘N’ 占 90% ≈ 264 万行。Buffers read51230远大于 hit物理读严重Execution Time 9823 ms。八、sys_hypo先模拟再建假设索引只对当前会话有效且定义里表名不能带 schema 点号否则报 syntax error靠search_path定位表SETsearch_pathperf_demo,public;SELECTsys_hypo_create_index(CREATE INDEX idx_hypo ON orders(order_time, status));SELECT*FROMsys_hypo_index;再跑上面的 EXPLAIN计划变成Bitmap Heap Scan Bitmap Index ScanExecution Time 2345 ms4.2×Buffers read从 51230 掉到 3120。模拟值和后面真实建索引的结果几乎一致。用完清掉SELECTsys_hypo_reset();九、落地真实索引CREATEINDEXCONCURRENTLY idx_orders_ordertime_statusONperf_demo.orders(order_time,status);CREATEINDEXCONCURRENTLY idx_orders_region_amountONperf_demo.orders(region,amount);ANALYZEperf_demo.orders;两个索引加主键一共 317 MB112 98 107。重跑 EXPLAIN 验证Execution Time 2312 msBuffers read2089。十、参数再榨一层work_mem默认 16MB大排序会溢盘。查询 6 在 16MB 下Sort Method: external merge Disk: 289MB, 4523 ms会话内SET work_mem64MB后变quicksort Memory: 58MB, 1234 ms3.7×。并行查询max_parallel_workers_per_gather是会话级参数可直接 SET但max_parallel_workers是 SIGHUP只能改kingbase.conf后 reload本机默认 8 够用不动。SETmax_parallel_workers_per_gather2;SETparallel_setup_cost100;SETparallel_tuple_cost0.03;再跑查询 4拉起 2 个 workerExecution Time 1234 ms。十一、sys_dump 逻辑备份调优完要做迁移前备份金仓的sys_dump对标pg_dump./sys_dump-Usystem-dtest-p54321-Fc-f/tmp/test_backup.dump# 187MB原数据 412MB./ksql-Usystem-dtest-p54321-cCREATE DATABASE test_restore;./sys_restore-Usystem-dtest_restore-p54321/tmp/test_backup.dump恢复到test_restore后比对行数users/products/orders 仍是 100000 / 10000 / 5000000一致。生产建议每周一次sys_dump全量 每天一次sys_rman物理增量逻辑备份用于跨版本迁移和单表恢复物理备份用于快速全库恢复。十二、日常收尾与踩坑调优完别撒手VACUUM 和 autovacuum 让它自己跑autovacuum on autovacuum_analyze_scale_factor 0.05 autovacuum_vacuum_scale_factor 0.10踩过的坑列几条最值得记的坑现象解法KWR 报告空kwr_report()返回空sys_kwr.enableon没开KSH 没数据改collect_ksh不生效共享库需重启reload 不行KDDM 报错unsupported formatKDDM 只支持 TEXT假设索引无效EXPLAIN 计划没变只对当前会话挂和查要同一会话假设索引报错syntax error at .索引定义里表名别带 schema 点号并行不生效SET 了还报错max_parallel_workers是 SIGHUP得改 conf结果汇总阶段单次执行提升原始无索引9823 ms基线 复合索引2312 ms4.2× work_mem 64MB1234 ms8.0×还有一组对照压测区间总 DB Time 8234 s → 优化后 2956 s-64%头号 SQL 总耗时 3421 s → 803 s-77%DataFileRead等待次数 156234 → 31200-80%。写在最后KWR 看大盘找方向、KSH 看细节定位时刻、KDDM 直接给建议三件套对标 Oracle 的 AWR/ASH有 Oracle 经验的 DBA 上手很快报告里的数和 EXPLAIN 实测能对上。sys_hypo是亮点——不落盘就能试索引模拟值和真实建完几乎一致比盲目建索引省事。sys_dumpsys_rman覆盖大部分备份场景。这次头号 SQL 从 9.8 秒压到 1.2 秒但数据涨到几千万行时大概率还得再来一轮。养成优化前拍快照、优化后拍快照、diff 看效果的习惯比任何调优技巧都实在。附录复现脚本精简版#!/bin/bash# 以 kingbase 用户运行前置已装库、已配 shared_preload_libraries 并重启KSBIN/opt/kingbase/install/Server/binKSUSERsystem;KSDBtest;KSPORT54321# 1) 扩展$KSBIN/ksql -U$KSUSER-d$KSDB-p$KSPORT-cCREATE EXTENSION IF NOT EXISTS sys_kwr; CREATE EXTENSION IF NOT EXISTS sys_stat_statements; CREATE EXTENSION IF NOT EXISTS sys_hypo;# 2) 建表 造数见正文第二节 SQL略# 3) 快照1 - 压测 - 快照2$KSBIN/ksql -U$KSUSER-d$KSDB-p$KSPORT-cSELECT perf.create_snapshot();# 开 5 个终端: bash /tmp/run_perf.sh# 结束后:$KSBIN/ksql -U$KSUSER-d$KSDB-p$KSPORT-cSELECT perf.create_snapshot();# 4) 报告$KSBIN/ksql -U$KSUSER-d$KSDB-p$KSPORT-c\copy (SELECT * FROM perf.kwr_report(1,2,html)) TO /tmp/kwr.html WITH (FORMAT TEXT);$KSBIN/ksql -U$KSUSER-d$KSDB-p$KSPORT-cSELECT * FROM perf.kddm_report(1,2);# 5) 假设索引模拟同会话$KSBIN/ksql -U$KSUSER-d$KSDB-p$KSPORTSQL SET search_path perf_demo, public; SELECT sys_hypo_create_index(CREATE INDEX idx_hypo ON orders(order_time, status)); EXPLAIN (ANALYZE, BUFFERS) SELECT o.region, p.category, count(*) cnt, avg(o.amount) FROM orders o JOIN products p ON o.product_idp.product_id JOIN users u ON o.user_idu.user_id WHERE o.order_time2025-06-01 AND o.statusN GROUP BY o.region,p.category ORDER BY cnt DESC; SELECT sys_hypo_reset(); SQL# 6) 落地索引$KSBIN/ksql -U$KSUSER-d$KSDB-p$KSPORT-cCREATE INDEX CONCURRENTLY idx_orders_ordertime_status ON perf_demo.orders(order_time, status); CREATE INDEX CONCURRENTLY idx_orders_region_amount ON perf_demo.orders(region, amount); ANALYZE perf_demo.orders;# 7) 备份$KSBIN/sys_dump -U$KSUSER-d$KSDB-p$KSPORT-Fc-f/tmp/test_backup.dump