人大金仓数据库KingbaseES-SQL调优

最新推荐文章于 2024-09-12 15:33:38 发布

沉舟侧畔千帆过_

最新推荐文章于 2024-09-12 15:33:38 发布

阅读量807

点赞数

文章标签：数据库 sql 金仓数据库 kingbase 人大金仓 DBA

本文链接：https://blog.csdn.net/arthemis_14/article/details/134462008

版权

关键字：

SQL调优、诊断、优化、吞吐量、负载、人大金仓、KingbaseES

SQL调优概述

(1) SQL调优是什么

SQL调优简单来说就是诊断和优化未能满足性能指标的SQL语句的行为。

(2) SQL 调优目的

① 减少用户响应时间，这意味着减少用户发出请求到得到响应的这段时间。

② 提高吞吐量，即让一条SQL只使用最少的资源。

SQL调优步骤

$C:\Users\sunna\Desktop\图片2.png$

(1) 识别高负载语句

通过查看过去的执行历史记录，找到消耗了较多系统资源的 SQL 语句，KingbaseES提供的工具为：sys_stat_statements和kbbadger。其中：

① sys_stat_statements

sys_stat_statements是KingbaseES的一个扩展插件，该插件提供了一种获取服务器执行所有SQL语句统计信息的方法，可以用于统计数据库的资源开销，如分析TOP SQL。插件sys_stat_statements通过修改 kingbase.conf 文件中 shared_preload_libraries 参数后重启数据库加载。

该组件的主要配置参数如下：

GUC参数	参数类型	含义	默认值	配置方式
sys_stat_statements.max	整数	模块跟踪的最大语句数（sys_stat_statements视图中的最大行数）	5000	在服务器启动时设置
sys_stat_statements.track	枚举	控制模块计算哪些语句	top	仅超级用户可以更改此设置
sys_stat_statements.track_utility	boolean	控制模块是否跟踪实用程序命令【注】除 SELECT，INSERT，UPDATE 和 DELETE之外的所有命令，都是实用程序命令	on	仅超级用户可以更改此设置
sys_stat_statements.save	boolean	指定是否在服务器关闭时保存语句统计信息【注】当设为off时，统计信息不会在关闭时保存，也不会在服务器启动时重新加载。	on	在kingbase.conf中配置或在服务器命令行中设置

这些参数在kingbase.conf中设置的典型用法是：

＃kingbase.conf

shared_preload_libraries ='sys_stat_statements'

sys_stat_statements.max = 10000

sys_stat_statements.track = 'top'

② kbbadger

kbbadger 是一个命令行工具，可以分析大型的日志文件。当日志文件足够长时，kbbadger可以自动检测日志文件的格式（syslog，stderr，csvlog或jsonlog）。

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

kbbadger生成的报告中关于SQL查询的信息	报告提供的每小时的统计图表内容
总体统计	SQL查询统计信息
占用时间最多的查询	临时文件统计
最常见的查询	检查点统计
最常见的错误	自动vacuum和自动分析统计
查询时间直方图	已取消查询
会话时间直方图	错误事件（死机、致命、错误和警告）
参与顶级查询的用户	/
涉及顶级查询的应用程序	/
生成最多取消的查询	/
大多数查询已取消	/
最耗时的准备/绑定查询	/

命令行参数为：kbbadger [ options ...] logfile ...

其中，options如下：

-e (end datetime)：通过日志解析数据的结束时间

-b (begin datetime)：通过日志解析数据的开始时间

-p (prefix string)：kingbase.conf文件中使用的log_line_prefix的值

-J (Jobs number)：指定并行解析的日志文件的数量，默认为1，即单线解析

-j (jobs number)：指定同时运行的工作数量，默认为1，即单线工作

`` `` (exclude-query regex)：将与'regex'匹配的查询排除在报告之外

-f (format logtype)：指定日志的文件格式

使用kbbadger必须在kingbase.conf中进行如下配置：

# 启用SQL查询日志记录才能进行解析

log_min_duration_statement = 0

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

lc_messages='en_US.UTF-8'

# 不启用log_statement，即设置为none，若将log_statement设置为'all'，则不会通过log_min_duration_statement指令记录任何内容

log_statement='none'

# 也可以在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

下面给出一个示例（通过所有2023-08-04的日志生成报告）：

其他情况下实例

$ 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

(2) 收集性能相关的数据

优化器统计信息是 SQL 调优的关键，如果统计信息不存在或不够及时准确，那么优化器将无法生成最优的执行计划。与 SQL 性能相关的其他数据包括：该语句访问的表和视图的结构，以及该语句可用的任何索引的定义。

(3) 确定性能问题产生的原因

① 低效的 SQL 语句设计

如果一天SQL本身写的很差，导致它要做很多必要的工作，那么优化器也没办法提升这条语句的性能。

比如

② 缺少 SQL 访问结构

缺少索引、物化视图、分区之类的 SQL 访问结构是导致 SQL 性能欠佳的典型原因。最佳的访问结构可以将 SQL 性能提高几个数量级。

③ 次优的执行计划

优化器的主要作用是给出最优（或较优）的执行计划。但在某些时候，因为优化器自身的局限，它会选择访问路径不理想的计划。

例如，当谓词的列的选择性低的时候，执行计划可能选择使用全表扫描而不是索引，这个情况下，如果过滤的数值占多数的话，全表可能是没有问题的，但假如过滤的数值只有一条，那么全表的性能就要差很多，并且还会影响后续选择什么连接方式。

可以将不怎么样的执行计划和好的执行计划进行对比，然后再联系最近发生的变动（比如数据量），来判断性能下降的原因。

④ 陈旧的优化统计数据

当统计信息维护操作（自动或手动）无法跟上 DML 导致的表数据更改时，收集到的统计信息可能会过时。由于表上的陈旧统计信息无法准确反映表数据，因此优化器可以基于错误信息做出决策并生成次优执行计划。

⑤ 硬件问题

受限于当前硬件环境（CPU、内存、IO、网络等），所有的优化手段都不足以达到预期性能，则需要做硬件扩展。

⑥ 架构问题

单机硬件扩展无法解决的情况下，则需要考虑集群方案。

(4) 定义问题的范围

解决方案的范围必须与问题的范围相匹配。在数据库级别考虑一个问题和在语句级别考虑一个问题的解决方案是不一样的。例如：

① shared pool太小，游标老化太快，导致大量的硬解析。解决方法就是在数据库级别修改初始化参数增大shared pool，这种解决方案是对所有会话生效的。

② 在单个 SQL 语句未使用有用的索引的情况下，在数据库级别修改整个数据库的优化器参数（enable_seqscan = off）可能会损害整体性能（导致其他会话的性能问题）。如果单个 SQL 语句有问题，则可以选择在于语句级别考虑适当的解决方案来解决此问题。

(5) 实施优化手段

SQL 性能问题产生的原因较为多样，所以优化手段也会因情况而异。例如，可以通过将 UNION 改写成 UNIONALL 来提高性能，也可以通过创建索引来提高性能。常用的优化手段包括：使用索引、使用 HINT、调整优化参数、使用 JIT、使用并行以及改写 SQL 语句。

(6) 评估优化效果

在实施优化手段后，通过实际执行来评估优化效果是否达到了预期。