KingbaseES的CPU优化点分析¶

CPU优化点分析

在数据库遭遇CPU瓶颈时，通过分析耗时SQL语句的执行计划可以有效的降低数据库的响应时间，提高吞吐量。

目前KingbaseES内部提供了2种工具来做耗时语句统计（kwr也集成了类似能力）：

sys_stat_statement

sys_stat_statement是一个插件，通过共享内存来做数据记录，其特点如下：

• 对于同一类语句只做一条记录，有效节省时间和刷盘时间

• 结果可以实时查看

• 无法查看语句中的参数信息、事务信息、执行时间等信息

kbbadger

kbbadger依赖于KingbaseES的日志系统来做记录，其特点如下：

• 同一类语句的信息都可以做记录，会带来IO压力

• 需要将日志通过工具分析后才可以查看

• 还可以额外记录临时文件、checkpoint、绑定信息等

在得到耗时语句后，则需要对耗时语句做SQL性能分析，一般的分析包括：

• 统计信息是否够及时

• 是否有数据、索引膨胀的情况

• 是否缺少索引、分析、物化视图等访问结构

• Join顺序、join算法等是否合理

• 是否有更高效的改写方式

1. 使用sys_stat_statement工具

sys_stat_statement是KingbaseES系统的一个扩展组件，它提供了所有执行语句的统计信息，可以帮助找出哪种类型的查询很慢以及多久调用一次查询。

使用这个模块的执行步骤：

1. 在kingbase.conf里添加预加载项：

shared_preload_libraries = 'liboracle_parser, sys_stat_statements'
sys_stat_statements.track = 'top'

1. 重启数据库服务器

2. 在执行的数据库里创建扩展：

CREATE EXTENSION sys_stat_statements;

注意

从V8R6版本开始，sys_stat_statements插件已经内置化，初始化数据库实例的时候，就会被自动加载。

该功能默认关闭，需要设置：sys_stat_statements.track = 'top' 才能正常使用。

该插件的最新版本是1.10。

sys_stat_statements视图结构：

表 3.3.15 视图结构 ¶

名称	类型	描述
userid	oid	执行该语句的用户的 OID
dbid	oid	在其中执行该语句的数据库的 OID
queryid	bigint	内部哈希码，从语句的解析树计算得来
query	text	语句的文本形式
parses	bigint	该语句被解析的次数
total_parse_time	double precision	在该语句中解析花费的总时间，以毫秒计
min_parse_time	double precision	在该语句中解析花费的最小时间，以毫秒计
max_parse_time	double precision	在该语句中解析花费的最大时间，以毫秒计
mean_parse_time	double precision	在该语句中解析花费的平均时间，以毫秒计
stddev_parse_time	double precision	在该语句中解析花费时间的总体标准偏差，以毫秒计
plans	bigint	该语句执行Plan的次数
total_plan_time	double precision	在该语句中执行Plan花费的总时间，以毫秒计
min_plan_time	double precision	在该语句中执行Plan花费的最小时间，以毫秒计
max_plan_time	double precision	在该语句中执行Plan花费的最大时间，以毫秒计
mean_plan_time	double precision	在该语句中执行Plan花费的平均时间，以毫秒计
stddev_plan_time	double precision	在该语句中执行P lan花费时间的总体标准偏差，以毫秒计
calls	bigint	被执行的次数
total_exec_time	double precision	在该语句中花费的总时间，以毫秒计（该字段在V8R5里面为：total_time）
min_exec_time	double precision	在该语句中花费的最小时间，以毫秒计（该字段在V8R5里面为：min_time）
max_exec_time	double precision	在该语句中花费的最大时间，以毫秒计（该字段在V8R5里面为：max_time）
mean_exec_time	double precision	在该语句中花费的平均时间，以毫秒计（该字段在V8R5里面为：mean_time）
stddev_exec_time	double precision	在该语句中花费时间的总体标准偏差，以毫秒计（该字段在V8R5里面为：stddev_time）
rows	bigint	该语句检索或影响的行总数
shared_blks_hit	bigint	该语句造成的共享块缓冲命中总数
shared_blks_read	bigint	该语句读取的共享块的总数
shared_blks_dirtied	bigint	该语句弄脏的共享块的总数
shared_blks_written	bigint	该语句写入的共享块的总数
local_blks_hit	bigint	该语句造成的本地块缓冲命中总数
local_blks_read	bigint	该语句读取的本地块的总数
local_blks_dirtied	bigint	该语句弄脏的本地块的总数
local_blks_written	bigint	该语句写入的本地块的总数
temp_blks_read	bigint	该语句读取的临时块的总数
temp_blks_written	bigint	该语句写入的临时块的总数
blk_read_time	double precision	该语句花在读取块上的总时间，以毫秒计
blk_write_time	double precision	该语句花在写入块上的总时间，以毫秒计

sys_stat_statements_all视图结构：

表 3.3.16 视图结构 ¶

名称	类型	描述
userid	oid	执行该语句的用户的 OID
dbid	oid	在其中执行该语句的数据库的 OID
queryid	bigint	内部哈希码，从语句的解析树计算得来
parent_queryid	bigint	上一层SQL的哈希码
query	text	语句的文本形式
parses	bigint	该语句被解析的次数
total_parse_time	double precision	在该语句中解析花费的总时间，以毫秒计
min_parse_time	double precision	在该语句中解析花费的最小时间，以毫秒计
max_parse_time	double precision	在该语句中解析花费的最大时间，以毫秒计
mean_parse_time	double precision	在该语句中解析花费的平均时间，以毫秒计
stddev_parse_time	double precision	在该语句中解析花费时间的总体标准偏差，以毫秒计
plans	bigint	该语句执行Plan的次数
total_plan_time	double precision	在该语句中执行Plan花费的总时间，以毫秒计
min_plan_time	double precision	在该语句中执行Plan花费的最小时间，以毫秒计
max_plan_time	double precision	在该语句中执行Plan花费的最大时间，以毫秒计
mean_plan_time	double precision	在该语句中执行Plan花费的平均时间，以毫秒计
stddev_plan_time	double precision	在该语句中执行P lan花费时间的总体标准偏差，以毫秒计
calls	bigint	被执行的次数
total_exec_time	double precision	在该语句中花费的总时间，以毫秒计（该字段在V8R5里面为：total_time）
min_exec_time	double precision	在该语句中花费的最小时间，以毫秒计（该字段在V8R5里面为：min_time）
max_exec_time	double precision	在该语句中花费的最大时间，以毫秒计（该字段在V8R5里面为：max_time）
mean_exec_time	double precision	在该语句中花费的平均时间，以毫秒计（该字段在V8R5里面为：mean_time）
stddev_exec_time	double precision	在该语句中花费时间的总体标准偏差，以毫秒计（该字段在V8R5里面为：stddev_time）
rows	bigint	该语句检索或影响的行总数
shared_blks_hit	bigint	该语句造成的共享块缓冲命中总数
shared_blks_read	bigint	该语句读取的共享块的总数
shared_blks_dirtied	bigint	该语句弄脏的共享块的总数
shared_blks_written	bigint	该语句写入的共享块的总数
local_blks_hit	bigint	该语句造成的本地块缓冲命中总数
local_blks_read	bigint	该语句读取的本地块的总数
local_blks_dirtied	bigint	该语句弄脏的本地块的总数
local_blks_written	bigint	该语句写入的本地块的总数
temp_blks_read	bigint	该语句读取的临时块的总数
temp_blks_written	bigint	该语句写入的临时块的总数
blk_read_time	double precision	该语句花在读取块上的总时间，以毫秒计
blk_write_time	double precision	该语句花在写入块上的总时间，以毫秒计

出于安全原因，非超级用户不允许查看其他用户执行的SQL文本或queryid。但是，如果视图已安装在数据库中，则他们可以查看统计信息。

只要有计划的查询（即SELECT，INSERT，UPDATE和DELETE）根据内部哈希计算具有相同的查询结构，它们就会组合到单个sys_stat_statements条目中。通常，如果两个查询在语义上等效，则两个查询在此意义上相同，只是出现在查询中的文字常量的值除外。但是，将严格根据实用程序命令（即所有其他命令）的文本查询字符串进行比较。

当为了将查询与其他查询匹配而忽略了常量的值时，该常量将替换为？。在sys_stat_statements显示中，查询文本的其余部分是第一个查询的文本，该查询具有与sys_stat_statements条目关联的特定queryid哈希值。

在某些情况下，文本明显不同的查询可能会合并到一个sys_stat_statements条目中。通常，这仅会在语义上等效的查询中发生，但是散列冲突会导致不相关的查询合并到一个条目中的可能性很小。（但是，这对于属于不同用户或数据库的查询不会发生。）

UTILITY语句中的CALL语句与有计划的查询（即SELECT，INSERT，UPDATE和DELETE）具有类似的获取queryid和常量替换逻辑。需要说明：PLSQL/PLPGSQL由于结果集缓存，执行sys_stat_statements_reset() 后，部分 CALL proc(xxx); 语句中的常量无法替换。

该组件的主要配置参数：

• sys_stat_statements.max（整数）

  sys_stat_statements.max是模块跟踪的最大语句数（即sys_stat_statements视图中的最大行数）。如果观察到的语句不同，则将丢弃关于执行最少的语句的信息。默认值为5000。只能在服务器启动时设置此参数。

• sys_stat_statements.track（枚举）

  sys_stat_statements.track控制模块计算哪些语句。指定top以跟踪顶级语句（由客户端直接发出的语句），全部也可以跟踪嵌套语句（例如在函数内调用的语句），或者不指定以禁用语句统计信息收集。默认值为 'none'。仅超级用户可以更改此设置。

  注意：该参数在V8R5里默认值为 'top'，如果需要使用sys_stat_statements功能，需要将其配置为 'top'。

• sys_stat_statements.track_utility（boolean）

  sys_stat_statements.track_utility控制模块是否跟踪实用程序命令。实用程序命令是除SELECT，INSERT，UPDATE和DELETE之外的所有命令。默认值为on。仅超级用户可以更改此设置。

• sys_stat_statements.save（boolean）

  sys_stat_statements.save指定是否在服务器关闭时保存语句统计信息。如果关闭，则统计信息不会在关闭时保存，也不会在服务器启动时重新加载。默认值为on。只能在kingbase.conf文件或服务器命令行中设置此参数。

该模块需要与sys_stat_statements.max成比例的附加共享内存。请注意，即使sys_stat_statements.track设置为none，只要加载模块，就会消耗此内存。

这些参数必须在kingbase.conf中设置。典型用法可能是：

shared_preload_libraries ='liboracle_parser, sys_stat_statements'
sys_stat_statements.max = 10000
sys_stat_statements.track = 'top'

查询和管理函数：

1、sys_stat_statements(showtext boolean)

功能：查询 TOP SQL 列表

参数：showtext boolean：是否显示SQL字符串

返回值：TOP SQL列表

2、sys_stat_statements_all(showtext boolean)

功能：查询全部 SQL 列表

参数：showtext boolean：是否显示SQL字符串

返回值：全部 SQL列表

3、sys_stat_statements_limit_len(showtext boolean, limit_query_len int)

功能：查询 TOP SQL 列表，可以限制返回的SQL字符串最大长度。该函数为 1.9 版本新增。

参数：showtext boolean：是否显示SQL字符串

limit_query_len：返回SQL字符串最大长度，超过该长度的字符串被截断

返回值：TOP SQL列表

4、sys_stat_statements_reset()

功能：重置（清空）TOP SQL列表

2. 使用kbbadger工具

Kbbadger可以分析大型的日志文件。当日志文件足够长时，kbbadger可以自动检测日志文件的格式（syslog，stderr，csvlog或jsonlog）。

由kbbadger生成的所有图表都是可缩放的并且可单独下载为PNG文件。另外，在生成的报告中SQL查询将突出显示。

Kbbadger生成的报告中包含的关于SQL查询的信息有：

• 总体统计

• 占用时间最多的查询

• 最常见的查询

• 最常见的错误

• 查询时间直方图

• 会话时间直方图

• 参与顶级查询的用户

• 涉及顶级查询的应用程序

• 生成最多取消的查询

• 大多数查询已取消

• 最耗时的准备/绑定查询

报告也会提供每小时的统计图表，其内容包括：

• SQL查询统计信息

• 临时文件统计

• 检查点统计

• 自动vacuum和自动分析统计

• 已取消查询

• 错误事件（死机、致命、错误和警告）

• 错误的类分布

在kbbadger的使用过程中，可以使用命令行选项来启用并行处理模式从而加快日志解析速度，使用-j命令行选项来指定解析使用的CPU核心数，使用-J命令行选项来并行解析的文件数，两个选项可以同时使用。另外，可以使用-A命令行选项来调整直方图的粒度。默认情况下，它们将报告每小时发生的每个主要查询/错误的平均值。

Kbbadger使用方法

1. 配置kingbase.conf

在开始之前，必须在kingbase.conf中启用并设置一些配置指令。

您必须首先启用SQL查询日志记录才能进行解析：

log_min_duration_statement = 0

在这里，每条语句都会被记录，在繁忙的服务器上，您可能需要增加此值以仅记录持续时间较长的查询。请注意，不要启用log_statement，因为kbBadger不会解析它的日志格式并且如果您将log_statement设置为'all'，则不会通过log_min_duration_statement指令记录任何内容。如果要保证系统性能，也可以使用log_duration来仅提供有关持续时间和查询数量的报告。有关更多信息，请参见下一章。

kbBadger支持任何设置到kingbase.conf文件的log_line_prefix中的自定义格式，只要它指定时间转义序列（％t，％m或％n）和与进程相关的转义序列（％p或％c）。

例如：

--使用“ stderr”日志格式，log_line_prefix必须至少为：
log_line_prefix = '%t [%p]: '

--日志行前缀可以添加用户，数据库名称，应用程序名称和客户端IP地址，如下所示：
log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h '

--或用于syslog日志文件格式：
log_line_prefix = 'user=%u,db=%d,app=%a,client=%h '

--stderr输出的日志行前缀也可以是：
log_line_prefix = '%t [%p]: db=%d,user=%u,app=%a,client=%h '

--或用于syslog输出：
log_line_prefix = 'db=%d,user=%u,app=%a,client=%h '

--您也可以在kingbase.conf中启用其他参数，以从日志文件中获取更多信息：
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 0
log_error_verbosity = default

注意

数据库日志中的内容必须为英文，否则kbbadger可能无法解析日志中的内容：

lc_messages='en_US.UTF-8'

1. 使用kbBadger生成统计信息

kbbadger /var/log/kingbase.log
kbbadger /var/log/kingbase.log.2.gz /var/log/kingbase.log.1.gz /var/log/kingbase.log
kbbadger /var/log/kingbase/kingbase-2012-05-*
kbbadger --exclude-query="^(COPY|COMMIT)" /var/log/kingbase.log
kbbadger -b "2012-06-25 10:56:11" -e "2012-06-25 10:59:11" /var/log/kingbase.log
cat /var/log/kingbase.log | kbbadger -
kbbadger --prefix '%t [%p]: user=%u,db=%d,client=%h ' /sys_log/kingbase-2012-08-21*
kbbadger --prefix '%m %u@%d %p %r %a : ' /sys_log/kingbase.log
# Log line prefix with syslog log output
kbbadger --prefix 'user=%u,db=%d,client=%h,appname=%a' /sys_log/kingbase-2012-08-21*
# Use my 8 CPUs to parse my 10GB file faster, much faster
kbbadger -j 8 /sys_log/kingbase-10.1-main.log

详细请参见 –help

1. 举例说明

$ ./kbbadger ./ sys_log/kingbase-2019-10-26_1544* -f stderr -J 12 -j 32
[========================>] Parsed 283210387 bytes of 283210387 (100.00%),
queries: 327810, events: 51
LOG: Ok, generating html report...

生成的统计信息效果图如下：