数据库性能调优实战：从瓶颈诊断到落地优化

云边有个稻草人
0 阅读16分钟

在日常数据库运维工作中，相信大家都遇到过这样的问题：业务高峰期数据库卡顿、接口响应超时，排查来排查去，却找不到问题根源。其实KingbaseES作为咱们常用的企业级数据库，本身自带了不少性能诊断和优化工具，只要用对方法，就能快速解决大部分性能瓶颈。结合我平时运维的实战经验，今天就从性能诊断、核心优化方向、实际案例这几块，跟大家好好聊聊KingbaseES的调优技巧，都是实打实能用上的干货，新手也能轻松上手。

一、性能诊断：找准瓶颈是前提，别瞎优化

很多人调优喜欢凭感觉来，看到数据库慢就盲目调参数、加索引，最后不仅没解决问题，反而越调越乱。其实调优的第一步，也是最关键的一步，就是精准定位问题。KingbaseES给我们提供了三个核心工具——SYS_KWR、SYS_KSH、SYS_KDDM，掌握这三个工具，就能轻松摸清数据库的“脾气”，找到问题所在。

1. 自动负载信息库 SYS_KWR：全局性能画像，摸清整体情况

SYS_KWR这个工具，我平时用得最多，它就像一个“全职监控员”，会周期性地采集数据库的快照，把操作系统状态、数据库运行时间、各类等待事件、消耗资源最多的TOP SQL这些关键信息，都记录得明明白白。不管是长期的性能趋势，还是阶段性的资源消耗，看它生成的报告就能一目了然，是做全局性能分析的首选工具。

快速上手 KWR，新手也能轻松操作

-- 1. 先配置kingbase.conf参数，这一步很关键，没配置好后续用不了
shared_preload_libraries = 'liboracle_parser, sys_kwr, sys_stat_statements'
track_sql = on
track_instance = on
track_io_timing = on
sys_stat_statements.track = 'top'

-- 2. 配置完一定要重启数据库，然后创建插件，不创建插件无法使用相关函数
CREATE EXTENSION sys_kwr;

-- 3. 手动创建快照，也可以开启自动快照（后续会说），这里先教手动操作
SELECT * FROM perf.create_snapshot(); -- 生成起始快照，记好这个快照ID
-- 接下来执行业务操作，比如模拟高峰期的查询、更新操作
SELECT * FROM perf.create_snapshot(); -- 生成结束快照，同样记好ID

-- 4. 生成HTML格式报告，这个格式最直观，里面有各种图表，推荐大家用这个
SELECT * FROM perf.kwr_report(1, 2, 'html', 'kingbase'); -- 1和2就是刚才记的快照ID

关键报告解读，重点看这3点就够了

  • Top 10 前台等待事件：这个是重点中的重点，只要占比超过5%的等待事件，就一定要重点关注。比如看到transactionid等待，大概率是事务锁冲突；看到DataFileRead等待，基本就是IO瓶颈了，后续优化就有方向了。
  • TOP SQL：按数据库消耗时间排序的SQL语句，那些占比15%以上的查询，就是我们优先要优化的对象。毕竟解决一个占比高的慢SQL，比优化十个小SQL效果明显多了。
  • 实例效率百分比：重点看Buffer Hit（缓存命中率），正常情况下应该接近100%，如果低于90%，不用想，肯定是shared_buffers参数设小了，调大这个参数就能缓解。

2. 活跃会话历史 SYS_KSH：实时会话分析，抓瞬时卡顿

SYS_KWR适合看长期趋势，但如果遇到数据库突然卡顿、瞬时响应变慢的情况，它就有点跟不上了。这时候就需要SYS_KSH出场，它会以每秒一次的频率，采样记录所有会话的状态、等待事件、正在执行的SQL语句，能精准捕捉到瞬时的性能瓶颈，比如突然出现的锁阻塞、临时的SQL风暴，用它都能快速定位。

启用 KSH 并生成报告，步骤很简单

-- 先配置kingbase.conf参数，开启KSH采集
sys_kwr.collect_ksh = on
sys_kwr.ringbuf_size = 100000 -- 内存缓冲区大小，不用设太大，够用就行

-- 生成最近15分钟的TEXT格式报告，方便快速查看，不用等HTML加载
SELECT * FROM perf.ksh_report(current_timestamp - interval '15 minutes', 15, 0, 'text', 'kingbase');

3. 自动诊断报告 SYS_KDDM：智能优化建议，新手福音

如果是刚接触KingbaseES调优的新手，不知道怎么分析报告、怎么优化，SYS_KDDM绝对是救星。它能自动分析KWR的快照数据，直接给出针对性的优化建议，比如哪里需要加索引、哪个参数需要调整、哪条SQL需要改写，不用我们自己慢慢分析，省了很多功夫。

-- 生成KDDM报告，基于之前创建的两个快照（ID1和ID2）进行分析
SELECT * FROM perf.kddm_report(1, 2);

-- 如果想针对某条具体的慢SQL做详细分析，就用这个函数，QueryID从TOP SQL报告里找
SELECT * FROM perf.kddm_sql_report(1, 2, 5728683370928235360); -- 替换成自己的QueryID

二、核心优化方向：对症下药提性能，不做无用功

找到性能瓶颈后，就该针对性优化了。根据我多年的运维经验，大部分数据库性能问题，都集中在CPU、IO、内存、锁资源这四个方面，只要把这四个方向优化到位，就能解决80%的性能问题，下面就跟大家详细说说每个方向的优化技巧。

1. CPU 资源优化：减少计算开销，让CPU“轻装上阵”

CPU瓶颈很好判断，只要看服务器CPU利用率持续接近100%，基本就是CPU不够用了。核心思路就是减少不必要的计算开销，让CPU只处理关键任务，具体可以从这两点入手。

（1）优化慢SQL，最直接的减负方式

  • 收集统计信息：很多时候，优化器生成低效执行计划，并不是SQL写得差，而是统计信息不准确，优化器“判断失误”。所以遇到慢SQL，先试试更新统计信息，往往能有意外收获。
  • 创建高效索引：全表扫描是CPU和IO的“杀手”，尤其是数据量大的表，全表扫描会占用大量资源。针对查询频繁的字段，创建合适的索引，能直接避免全表扫描，提升查询速度。
  • 改写低效SQL：很多时候，一些看似正常的SQL，其实隐藏着很大的性能隐患。比如用UNION替代UNION ALL，虽然结果一样，但UNION会多一步去重操作，开销会大很多；还有条件下推，能减少不必要的计算，这些小细节都能有效优化CPU开销。

（2）绑定 CPU 核心，减少上下文切换

数据库进程如果在多个CPU核心之间来回切换，会消耗大量CPU资源，影响性能。通过bindcpulist参数，把数据库进程绑定到固定的CPU核心上，能有效减少上下文切换的开销，让CPU资源得到更高效的利用。

# kingbase.conf配置，根据自己服务器的CPU核心数调整
# 比如我这边服务器有16核，绑定0-3核和10-13核，避开其他业务的核心
bindcpulist = '0-3,10-13' -- 绑定到0-3核和10-13核

2. IO 资源优化：降低磁盘读写压力，解决“卡慢”根源

IO瓶颈也是数据库性能差的常见原因，表现为磁盘利用率居高不下，或者DataFileRead/Write等待事件占比很高。核心思路就是减少磁盘的物理读写，让数据尽量在内存中处理，具体可以从这三点优化。

（1）内存参数调优，重中之重

  • shared_buffers：这是数据库的共享缓存，简单说就是把磁盘上的数据缓存到内存中，下次查询直接从内存读取，不用再读磁盘，能极大提升查询速度。建议设置为物理内存的20%-80%，具体根据服务器的业务情况调整。
  • work_mem：这个参数是用于排序、哈希操作的工作内存，如果设置太小，排序操作就会写入临时文件，频繁读写磁盘，消耗大量IO资源。设置的时候要注意，并发量高的话，要预留足够的内存，避免内存不足。
  • wal_buffers：WAL日志缓冲区，写操作频繁的场景（比如大量INSERT、UPDATE），这个参数一定要调大，避免频繁将日志写入磁盘，提升写操作效率。默认是64KB，写密集场景建议调至16-64MB。

（2）IO 调度策略优化，适配数据库场景

如果服务器用的是机械硬盘，默认的IO调度策略可能不太适合数据库场景，会导致IO等待延迟过高。推荐大家使用deadline调度策略，它能保证每个IO请求都有明确的响应时限，减少IO等待，提升磁盘读写效率。

# 临时生效，重启服务器后会失效，适合测试
echo deadline > /sys/block/sda/queue/scheduler

# 永久生效，需要修改/etc/grub.conf文件，重启后生效
kernel ... elevator=deadline

（3）文件系统挂载优化，减少不必要的磁盘操作

文件系统挂载的时候，添加一些参数，能减少磁盘的不必要操作，提升IO性能。比如noatime参数，能关闭文件访问时间的更新，避免每次读取文件都要写磁盘；nobarrier参数，能关闭写屏障，提升写操作的效率，大家可以根据自己的文件系统类型调整。

# /etc/fstab文件配置，供大家参考
/dev/sdb1 /kingbase_data xfs rw,noatime,nodiratime,nobarrier 0 0

3. 锁资源优化：减少并发冲突，避免事务阻塞

高并发场景下，锁等待是很常见的问题，一旦出现锁等待，就会导致事务阻塞，接口响应变慢，严重影响业务。核心思路就是减少锁竞争，缩短锁持有时间，具体可以从这两点入手。

（1）开启 XLogInsert 无锁化优化，解决wal_insert等待

如果在KWR报告中，发现wal_insert等待事件占比很高，说明写操作的锁竞争很严重，这时候开启XLogInsert无锁化优化，就能有效消除这种等待，提升写操作的并发能力。

# kingbase.conf配置，开启无锁化优化
enable_xlog_insert_lock_free = on

这里提醒大家一句：开启这个参数后，commit_delay和commit_siblings这两个参数就失效了，大家根据自己的业务场景选择是否开启，写密集场景建议开启。

（2）优化事务逻辑，缩短锁持有时间

  • 缩短事务时长：很多人在写业务代码的时候，喜欢把一些无关操作（比如IO等待、用户交互）放到事务里，导致事务执行时间很长，锁持有时间也随之变长，容易引发锁冲突。建议大家尽量缩短事务时长，只在事务中执行必要的数据库操作。
  • 降低锁粒度：尽量使用行锁，避免使用表锁，表锁会导致所有操作都阻塞，严重影响并发能力。这里要注意，只有查询条件有索引的时候，才会触发行锁，没有索引的话，会自动触发表锁，大家一定要注意这一点。

4. 网络资源优化：减少数据传输开销，适配分布式部署

如果数据库是分布式部署，或者应用服务器和数据库服务器不在同一台机器，网络瓶颈就可能成为性能短板。核心思路就是减少数据传输量，提升数据传输效率，具体可以从这两点优化。

（1）限制结果集大小，避免大量数据传输

很多业务接口会查询大量数据，然后在应用端进行分页处理，这样会导致大量不必要的数据传输，占用网络带宽，影响接口响应速度。建议大家在数据库层面就做好分页，通过LIMIT限制返回行数，只传输当前需要的数据。

-- 分页查询，每次只返回100条数据，避免大量数据传输
-- 这里提醒一下，分页的时候一定要加ORDER BY，保证数据顺序一致
SELECT * FROM orders WHERE user_id=123 ORDER BY create_time DESC LIMIT 100 OFFSET 0;

（2）开启批量返回模式，提升传输效率

在自动提交模式下，开启enable_autocommit_fetch参数，数据库会按需返回数据，而不是一次性返回所有数据，能有效减少网络传输的开销，提升接口响应速度，适合查询数据量较大的场景。

# kingbase.conf配置，开启批量返回模式
enable_autocommit_fetch = on

三、实战案例：从卡顿到秒响应，真实场景复盘

场景描述

前段时间，我负责的一个电商系统，订单查询接口突然变得很慢，响应时间超过3秒，到了促销活动期间，甚至出现5秒以上的超时，用户投诉不断，领导也催着解决。我当时第一时间就用KWR工具生成了报告，仔细分析后，发现了三个核心问题，跟大家分享一下，大家遇到类似情况可以参考。

  1. Buffer Hit只有85%，远低于理想值99%，这说明共享缓存不足，大量数据需要从磁盘读取，导致IO操作频繁，这是接口卡顿的主要原因之一；
  2. TOP SQL是SELECT * FROM orders WHERE user_id=? AND status=?，执行计划显示是全表扫描，这条SQL占数据库总时间的28%，是最大的性能消耗点；
  3. 存在transactionid等待事件，占比7%，结合业务日志排查后发现，开发人员把订单查询和订单状态更新放到了同一个事务里，查询操作虽然不修改数据，但会持有共享锁，导致更新操作阻塞，进而影响整个接口的响应速度。

优化步骤，一步步解决问题

  1. 调大缓存参数，提升缓存命中率 我先看了一下服务器的配置，物理内存是128GB，之前shared_buffers只配置了16GB，缓存空间明显不足，导致大量数据换入换出，IO压力很大。于是我调整了核心内存参数，具体如下：
  2. 创建针对性索引，消除全表扫描 针对那条TOP SQL的查询条件（user_id和status），我创建了一个联合索引，这样查询的时候就能直接命中数据，避免全表扫描，具体操作如下：
  3. 拆分事务逻辑，减少锁竞争 针对锁等待的问题，我跟开发人员沟通后，把订单查询和订单状态更新的逻辑拆分开了。查询操作不需要事务包裹，因为是只读操作，不会修改数据，也不会持有锁；更新操作单独开启短事务，快速执行、快速提交，缩短锁持有时间，具体优化如下：
  4. 补充优化：开启执行计划缓存，进一步提升效率 这个订单查询接口调用频率很高，每秒能达到几百次，如果每次都解析SQL、生成执行计划，会消耗大量CPU资源。于是我开启了PBE执行计划缓存，让数据库复用执行计划，避免重复解析。

四、总结与调优最佳实践，都是实战经验总结

结合我KingbaseES运维经验，其实数据库调优并不是什么高深的技术，核心就是遵循“诊断-优化-验证-迭代”的闭环流程。先用水KWR、KSH、KDDM这三个工具，全面采集性能数据，精准找到瓶颈所在；然后针对CPU、IO、锁、网络这四个核心方向，对症下药做优化；优化完成后，再用快照对比、业务压测验证效果，如果没达到预期，就重复迭代，直到满足业务需求。

调优核心原则与最佳实践，新手必看

  1. 优先级原则：调优的时候，一定要分清主次，优先优化TOP SQL和高占比等待事件（占比5%以上）。我平时调优都是先解决这些“大头”，往往能以最小的成本，获得最大的性能提升，性价比最高，不用在一些小问题上浪费时间。
  2. 内存优先原则：内存优化是调优的基础，也是最容易出效果的一步。合理配置shared_buffers和work_mem，能解决大部分IO相关的问题。这里给大家一个参考：OLTP场景（比如电商、支付），shared_buffers设为物理内存的20%-40%；OLAP场景（比如报表、数据分析），可以提升到50%-80%，具体根据业务情况调整。
  3. 索引平衡原则：很多新手觉得索引越多越好，其实不然。过多的索引会显著增加INSERT、UPDATE、DELETE等DML操作的开销，反而会影响性能。建议大家只给高频查询的过滤条件、连接条件创建索引，并且定期通过sys_stat_user_indexes视图，清理那些长期未使用的索引，避免资源浪费。
  4. 事务极简原则：高并发场景下，事务一定要遵循“短、快、少”的原则——执行时间要短，提交速度要快，涉及的资源要少。避免在事务中执行无关操作，比如IO等待、用户交互，尽量缩短锁持有时间，减少锁竞争。
  5. 监控常态化原则：性能调优不是一次性的工作，而是一个长期的过程。建议大家开启KWR自动快照，比如每小时生成一次，保留15天的快照数据，定期生成报告分析性能趋势，提前发现潜在的性能问题，避免瓶颈爆发，影响业务正常运行。
  6. 参数适配原则：数据库参数没有绝对的最优值，不能照搬别人的配置，一定要结合自己的硬件配置、业务场景动态调整。比如写密集场景，要调大wal_buffers和checkpoint_timeout；读密集场景，重点优化shared_buffers和索引设计，适合自己的才是最好的。
  7. 避免过度优化原则：调优的目的是满足业务需求，而不是追求极致的性能。当接口响应时间、并发量都达到业务要求后，就不用再继续优化了。过度优化会增加系统复杂度，提高维护成本，甚至可能引入新的稳定性风险，得不偿失。

​

avatar
文章
阅读
粉丝