金仓数据库KingbaseES-性能优化

1.概述

什么是性能优化？性能调优就是在不影响正确性的前提下，使得应用程序运行速度更快，完成特定功能所需的时间更短。本部分将包含以下主题：

• 性能优化方法
• SQL语句常用优化手段 
• IO 资源优化
• 数据库等待资源、锁资源优化

2.性能优化方法

性能调优的实施应该优先考虑瓶颈点的优化，可以带来最大的性能收益。 性能调优主要从下面几个方面着手：

1. 数据库优化

数据库方面的优化主要包括 SQL 优化和数据库内核参数优化两部分。

• SQL 优化主要编写高性能查询 sql，以及对已有的查询语句进行修改，使得查询语句得出来的执行计划最优，同时还需要调整有关的数据库参数来调整执行计划。

• 内核参数优化主要是根据不同应用调节不同内核参数进行优化，比如经常有大量数据频繁访问可以调大 shared_buffers，比如大量相同的（不包括常量）sql 语句执行而且 cpu 比较高，可以开启执行计划缓存。

2. 常用 OS 优化

主要是 cpu，内存，磁盘，网络的性能调优，比如磁盘的调度策略的优化，预读的优化，启动磁盘写缓存的策 略，directio，异步 IO 等优化。

3. 应用层优化

在上述措施无法奏效的情况下，考虑修改应用逻辑来进行优化，如更高效的 SQL 语句、减少某些应用逻辑、查 询分页等。

4. 硬件优化

考虑提升硬件规格：比如提升 cpu 频率、增加 CPU 核心数、比如提升磁盘能力 采用 ssd 盘阵、更换更高带宽的网络等。

5. 架构设计优化

考虑架构层面的优化，如使用缓存数据库、读写分离、分库分表等措施。 下文中的优化手段将从性能优化点出发来做介绍，硬件和架构层面的优化不再展开。

3.SQL语句常用优化手段

当数据库SQL语句执行很慢时，通常我们可以考虑优化数据库中的慢语句。可以按照如下方式找出并进行调整：

针对慢语句做执行计划分析，根据问题原因选用合理的手段。

常用语句优化手段如下：

• 收集统计信息

统计信息不准很有可能误导执行计划使用不合理的执行计划。

• 创建索引

高效的索引可以大幅度减少响应时间。

• 控制执行计划

对于复杂语句，由于优化器的局限性，可能会因为估算不准等原因导致生成执行计划不是最优，可以考虑通过HINT或者修改参数的方式人为控制合理的执行计划。

• 使用并行

CPU 资源充裕的情况下，复杂的 SQL 语句可以使用并行来加快执行速度。

• 调整语句

使用更高效的方式来改写 SQL 语句，如 UNION 改成 UINION ALL，将条件尽量下推到基表等。

• 使用执行计划缓存

使用 PBE 可以缓存执行计划，对于大量的短语句来说，执行之前的词法语法语义解析、生成执行计划阶段在总时间的占比中会比较高，使用执行计划缓存可以有效降低数据库响应时间。

4.CPU优化参数

功能介绍：启动 KingbaseES 服务进程时，根据此参数给出的 CPU 核号列表信息，利用操作系统的 CPU 亲和性 接口函数，将当前服务进程绑定在一个固定的 CPU 核号上，以此提高性能。绑核顺序是按参数列表顺序进行绑定。

参数级别：PGC_POSTMASTER。

语法规则：正确格式为 start_cpunum-end_cpunum | cpunum [, ...]。即：开始的 cpu 核号-结束的 cpu 核号， 或单独给出 CPU 核号。各项之间用逗号隔开。其他非数字，非’-’，非’,’ 的字符都视为非法字符。如 bindcpulist = ‘0-95’，表示将数据库进程绑定到 0 到 95 号 CPU 核心；bindcpulist = ‘0-3,10,20-30,50’，表示将数据库进程绑 定到 0 到 3 号核、10 号核、20 到 30 号核、50 号核新上。

5.IO资源优化 

IO 资源的优化手段较多，一般包括：

• 优化数据库内存参数

• 优化数据库 I/O 相关参数

• 调整 IO 调度策略

• 多 IO 设备分担压力

• 优化文件系统挂载

• 配置预读和 IO 请求队列

5.1 优化数据库内存参数

KingbaseES 的内存主要包括共享内存和私有内存两大类，不合理的内存使用可能会带来 IO 问题：如 shared_buffers 过小导致数据换进换出，work_mem 过小导致排序使用临时文件。合理的使用内存参数可以较好的 发挥硬件的能力。

在做内存调整时也需要考虑 KingbaseES 使用的内存不要过大导致 swap。

KingbaseES 使用内存总大小为： shared_buffers(数据) + wal_buffers（日志）+ maintenance_work_mem（创建索引排序时使用）+ n*work_mem(n 为并发做排序的连接数) + 服务进程上下文使用的内存 (无法精确估计大小) + m*thread_stack_size (thread_stack_size 为进程栈的大小, KingbaseES 默认为 1MB; m 为当前连接数)； 在进行内存调整时首先要清楚是哪个部分的内存出现了问题，有针对性的进行参数调整。

5.2 共享内存参数

1. shared_buffers

原理：数据库服务器使用的共享内存缓冲区的数量，主要用于缓存数据，根据需求一般不能设置超过 80% 的内 存，但至少是 20%。

应用范围：数据库本身, 查询的数据量比较大，比较频繁使用到。

2. wal_buffers

原理：日志缓冲区大小, 共享内存里用于 WAL 数据（日志）的磁盘页面缓冲区的数目。

应用范围：如果单位时间事务的数据修改数据量较大，也就是事务的写比较多的情况，如果 IO 是瓶颈，可以调整这个值到很大，有很多的缓存后，就不会频繁的写磁盘，降低 IO。缺省值为 8 ，8 个页面是 64k。当然也可以很 小，这个设置只需要大到能保存下一次事务生成的 WAL 数据即可, 因为这些数据在每次事务提交时都会写入磁盘。

5.3 优化数据库 I/O 相关参数

• Fsync

原理：用来设置日志缓冲区刷盘时, 需要确认已经将其写入了磁盘。如果关闭该功能，那么由操作系统调度磁盘写的操作, 能更好利用缓存机制, 提高 IO 性能。该性能的提高伴随了数据丢失的风险, 当操作系统或主机崩溃时, 不保 证刷出的日志是否真正写入了磁盘。

应用范围：应依据操作系统和主机的稳定性来配置，一般在追求极限性能是修改。

• full_page_writes
• commit_delay

原理：事务提交和日志刷盘的时间间隔,

应用范围：如果并发的非只读事务数目较多, 可以适当增加该值, 使日志缓冲区一次刷盘可以刷出较多的事务, 减 少 IO 次数, 提高性能。需要和 commit_sibling 配合使用。

• commit_siblings

原理：触发 commit_delay 的并发事务数, 只有系统的并发活跃事务数达到了该值, 才会等待 commit_delay 的时 间将日志刷盘, 如果系统中并发活跃事务达不到该值,commit_delay 将不起作用, 防止在系统并发压力较小的情况下, 事务提交后空等其他事务造成时间的浪费。

应用范围：应根据系统写的负载配置，并发越大就配置越大，和 commit_delay 一起使用。

• checkpoint_timeout

原理：两个相邻检查点之间的时间间隔，用于刷数据到磁盘。

应用范围：根据系统写的负载设置, 一般不要太频繁。可以和后台写线程配置相关参数配合使用，一般 pk 测试过 程中会设置的很长（如果内存够的话），可以不用频繁刷。

• bgwriter_delay

原理：后台写线程的自动执行时间, 后台写线程的作用是将 shared_buffer 里的脏页面写回到磁盘, 减少 checkpoint 的压力。默认是 200ms。

应用范围：如果系统数据修改的压力一直很大, 建议将该时间间隔设置小一些, 以免积累的大量的脏页面到 checkpoint, 使 checkpoint 时间过长 (checkpoint 期间系统响应速度较慢)。tpcc 一般设置 10ms 可以减少压力；

• bgwriter_lru_maxpages

原理：后台写线程一次写出的脏页面数，默认 100 个页面。

应用范围：依据系统写的负载修改，比如 tpcc，因为时间间隔短，一次写入的少页面少，比如设置 75。

• bgwriter_lru_multiplier

原理：后台写线程根据最近服务线程需要的 buffer 数量乘上这个比率估算出下次服务线程需要的 buffer 数量, 再 使用后台写进程写回脏页面, 使缓冲区能使用的干净页面达到这个估计值。默认是 2.0 应用范围：应根据系统写负载来配置。

6.数据库等待资源、锁资源优化 

• 修改数据库内核参数

• 修改应用逻辑

开启 XLogInsertLockFree 优化 当发现 wal_insert 等待事件占用时间占比较高时，可以开启 XLogInsert 无锁化优化。在开启 XLogInsert 无锁化优化的前提下，可以消除 wal_insert 等待事件，实现性能的提升。

在kingbase.conf 中将参数 enable_xlog_insert_lock_free 设置为 on，然后重启数据库，就会开启 XLogInsertLockFree 优化。 开启此参数后，将会有以下参数失效： commit_delay，事物提交和对应的 WAL 刷写到磁盘之间的时间间隔。 commit_siblings，在执行 commit_delay 前，最小的并发事物数。

参考资料

《kingbaseES数据库性能优化指南》