建议收藏 | 在openGauss上遇到慢SQL该怎么办？

在数据库的日常使用中，难免会遇到慢SQL，遇到慢SQL本身并不可怕，困难之处在于如何识别慢SQL并对其优化，使它不至于拖慢整个系统的性能，避免危害到日常业务的正常进行。

对不同的数据库来说，由于其系统架构的差异、代码实现的不同，很多慢SQL解决“套路”往往是无法直接复用的。为此，我们梳理了在openGauss上进行慢SQL分析的经验，并总结了下来，希望能给openGauss的用户一些启发。openGauss的数据库自治运维系统DBMind也已经初步具备了慢SQL根因分析的能力，感兴趣的读者也可以尝试一下。

首先，我们可以通过设置GUC参数log_min_duration_statement 来指定openGauss系统监控的慢SQL阈值。同时，我们也应调大instr_unique_sql_count的数值，以免出现“missing SQL statement, GUC instr_unique_sql_count is too small.”的提示。这里以设置慢SQL检测阈值为5秒（默认数值单位是毫秒）为例：

然后执行一个慢SQL，可以在dbe_perf.statement_history视图中查看到结果：

由索引原因引起的慢SQL在绝大多数数据库系统中都是十分常见的，甚至可以列为第一大慢SQL问题来源。简单来说，大致存在以下几种情况：

对于缺乏有效索引的场景，在解决问题时，可以先从SQL语句本身入手，绝大多数此类SQL语句都是SELECT语句，且该类SQL语句涉及到的表数据量较多，且谓词上没有创建索引，导致数据库系统需要通过全盘扫描来获取数据。对于该情况，一般的做法往往比较“暴力”，即直接在WHERE子句、JOIN子句等涉及到的字段上创建索引。一般存在于WHERE子句中的简单比较都是可以使用索引扫描的，因此在该涉及到的字段上创建索引可能是有效的。但是，索引也并非是创建得越多越好（后面我们会提到冗余索引的情况），在创建索引时需要在选择度较高、数据量不是特别少的字段上创建索引，否则该索引收益不大。

单语句索引推荐的核心逻辑可以表示为：

对于推荐出来的候选索引，用户可以自行决策是否创建，也可以通过openGauss的虚拟索引功能来评估索引收益，进行辅助决策。

就索引失效而言，一般存在以下六种情况：

上面我们提到了创建索引的一般情况，对于绝大多数慢SQL场景，创建一个合适的索引就可以使得性能突飞猛进。但是，索引是不是就可以越多越好呢？显然不是。我们日常创建的索引中，使用最多的是B+ Tree索引，因此我们以B+ Tree为例，简单解释一下缘由。

众所周知，B+ Tree是一个多叉树，它的每一个子节点都是父节点的一个子“范围”。记录（或记录的位置）最终存储在B+ Tree的叶子节点中。因此，在进行数据检索时，只需要扫描匹配的子节点中的指定“范围”即可。但是，对于数据的删除，也需要付出相同的时间开销，进行B+ Tree节点的调整；如果被索引的数据修改了，还需要调整B+ Tree中原有的节点结构。由于B+ Tree的插入、删除、检索的算法时间复杂度都是相同的，因此当业务系统中的插入和删除操作更多时，索引维护的代价就会更大，甚至超过索引检索时带来的收益。与此同时，索引页也需要占用额外的磁盘空间，被索引数据量越大，索引页占据的空间就越大。而且，当前openGauss中的B+ Tree的实现仍然是有锁的，更多的索引页面有可能涉及更多的锁维护操作。

在openGauss数据库中，可以通过下述语句简单识别没有被使用过的索引：

可以修改上述SQL语句中的 idx_scan 条件中的阈值，来调整返回结果。

对于workload中全量SQL语句进行索引创建其实是非常困难的，因为需要权衡全量SQL中增删查改语句的占比情况，同时需要估计索引的检索收益和维护代价，这个权衡过程十分复杂，一般的人工操作其实是很难的。因此，在日常数据库使用中，当需要创建索引时，最好进行全局业务的评估，衡量是否会干扰到其他业务，以及创建的总体收益是否为正，以免后期难以维护。

不过，对于openGauss数据库来说，可以使用系统级别的索引推荐功能来解决上述痛点问题，可以通过下述命令查看使用说明：

在系统配置中，最常见的配置项就是对资源的配置。这包括允许使用的最大资源（主要是内存）、以及资源的使用方式等。除了调整资源配置，有些情况下还需要配置数据库优化器Cost Model的代价值。下面我们重点看几个会影响SQL语句成为慢SQL的系统参数：

max_process_memory: 该参数与enable_memory_limit配合使用，用于限制一个openGauss实例可用的最大内存。需要将该参数值与宿主机系统的内存总量进行匹配，将宿主机用于操作系统正常运行所需的内存刨除后，剩下的内存空间就可以尽可能多地划分给openGauss实例使用了。否则，openGauss为了避免造成OOM问题，会通过该参数限制数据库允许使用的最大内存。因此，如果在客户端或者日志中出现类似“memory usage reach the max_dynamic_memory”的报错时，一般是由于该参数值太小导致的。

shared_buffers: 数据库系统使用的缓存池大小。一般来说，综合来看对数据库影响最大的参数就是它了，因为如果该参数设置得过小，会导致缓存不足，从而产生大量的磁盘I/O. 该参数在openGauss上的默认值很小，只有32MB，对于绝大多数的生产场景是不够的。一般的经验值是设置为系统内存的25%, 甚至在某些场景中还可以再大一点。不过openGauss的buffer没有通过DirectIO实现，仍然使用了系统缓存（cache），所以一般认为超过系统内存的40%也起不到再好的效果了。与此同时，checkpoint_segments 参数也需要随着shared_buffers的调大跟着变大一些。

work_mem: 显式指定内排序和哈希表能使用的内存空间大小，如果该值设得比较小，会向磁盘写入更多的临时文件。因此，我们可以适当地增加该值的大小。但是需要注意的是，业务系统可能存在并行执行的复杂语句，如果这些语句都占用非常多的work_mem大小的资源，则可能会导致内存使用占满（如前文所述，openGauss存在内存管控机制，一般不至于由于OOM导致系统重启）。故而，该值设置得很大的时候要关注系统的并发问题。该参数对ORDER BY, DISTINCT, JOIN (merge join, hash join), HASH Agg, 基于hash的IN子查询都有影响。

random_page_cost: 一般与seq_page_cost配合调整。该参数调整数据库的CBO优化器中随机扫描的代价。该值设置得越大，数据库越认为随机扫描不可取，也就越不倾向于使用索引。该参数的默认值是4，对于机械硬盘来说，是合适的。但是，如果业务系统的磁盘是固态硬盘的话，就应该适当调小一下该参数值，一般的经验是调整为1.

default_statistics_target: 当前openGauss的默认优化器是CBO, 它高度依赖数据的统计信息。因此，对于复杂查询来说，更优质的统计信息往往可以获得更好的执行计划。通过增大该参数的值，可以获得更准确的统计信息，但是也会增加ANALYZE的时间。因此，对于复杂语句较多的场景，可以适当增加该参数值。

如果用户希望针对自己的业务试探出最合适的参数，也可以使用离线模式（tune或train模式）。不过该场景一般是对未上线的业务系统进行初始调参，因为执行该功能可能会影响业务运行，故称之为离线模式。

当系统同时执行某些SQL语句的时候，它们可能会互相影响，进而导致某些SQL语句变为慢SQL, 这就是典型的资源竞争导致的慢SQL. 同时，不仅数据库中的语句们可能会进行资源竞争。在混合部署的环境中，操作系统上的其他任务也可能会影响数据库系统的表现。

比较困难的是，数据库自己的某些任务之间互相影响，例如锁竞争、IO竞争等。

值得一提的是，openGauss并不支持pg_blocking_pids 函数。所以，通过该函数是无法查看到锁等待情况的。

还有一种情况是IO使用受到影响，例如系统正在进行IO操作时，执行某条SQL语句，该SQL语句对磁盘的访问被阻塞了。典型的数据库系统IO操作包括Analyze, Vacuum以及checkpoint 等。openGauss为此做了很多优化，例如增量checkpoint, 使用更大的版本号等（可以避免大量的autovacuum for prevent wrap）。

当然，除了上面列出的情况外，还存在并发量接近或超过系统负荷导致的性能下降和拒绝服务。例如，大量复杂查询语句对CPU资源的竞争、大并发情况下引起数据库的响应时间变慢等。

openGauss-exporter: 用于采集数据库指标，除常规指标外，还能监控慢SQL、系统配置等。

reprocessing-exporter: 可以对Prometheus中已经采集到的指标进行聚合，例如计算QPS、内存使用率等。

注意：openGauss对于采集指标也进行了权限隔离，必须要求openGauss-expoter连接的用户具有sysadmin, monadmin 权限才可以获取某些监控表的指标。

尽管openGauss对于大的行存表处理性能非常优秀，但表本身的数据情况依然是导致慢SQL的重要原因。一般来说，具有以下几种情况：

表的数据量较大导致的慢SQL问题，一般需要从业务上进行入手，直接通过修改数据库来达到优化慢SQL的目的是很难实现的。因此，需要用户分析具体的业务，对业务数据进行冷热分离、分库分表、使用分布式中间件等。如果希望在数据库层进行优化，则可以通过增加宿主机的内存，进而增加max_process_memory、shared_buffers、work_mem等的大小；使用性能更佳的磁盘；适当创建索引；使用表空间调整磁盘布局等。

由SQL语句写法问题导致的慢SQL也相对多见，这类写得比较差的慢SQL也被俗称为“烂SQL”。多数情况都下，由“烂SQL”导致的索引失效的问题较多，对于这种情况，可参考前面的描述对SQL语句进行改写，使其能够使用到索引。

除了修改慢SQL使其能够使用索引，下面还列出了几种比较常见的、可能优化openGauss数据库性能的SQL改写规则：

对于业务系统，SQL语句上线之前的审计工作基本都可以覆盖上述的场景，业内也具备很多对SQL语句进行改写的工具，不过这些工具的一些改写规则并不是绝对意义上的等值改写。而且，很多改写条件对于openGauss来说不见得有效，因为openGauss在数据库内部也存在rewrite逻辑。

DBMind平台会进一步演进SQL语句的智能改写功能，提供给用户在线的交互式智能查询改写能力，预计在未来的版本中与用户见面。

当然，如果用户希望手动检查一下慢SQL的原因，也可以根据附表的检查项来检查慢SQL的产生原因。