目录
索引原因导致的慢SQL
缺乏有效索引
索引失效
索引冗余
系统配置原因导致的慢SQL
资源竞争导致的慢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秒(默认数值单位是毫秒)为例:
gs_guc reload -D $PGDATA -c 'log_min_duration_statement = 5000' -c 'instr_unique_sql_count = 2000'
然后执行一个慢SQL,可以在dbe_perf.statement_history视图中查看到结果:
select pg_sleep(6); -- 构造的慢SQL select * from dbe_perf.statement_history order by start_time desc;
有了上述方法,我们就可以轻易在openGauss数据库中监控到慢SQL了,接下来可以通过下文的方法来分析慢SQL的产生原因。
索引原因导致的慢SQL
由索引原因引起的慢SQL在绝大多数数据库系统中都是十分常见的,甚至可以列为第一大慢SQL问题来源。简单来说,大致存在以下几种情况:
1. 缺乏有效索引
2. 执行计划没有选择索引扫描,即索引失效
3.冗余索引
缺乏有效索引
对于缺乏有效索引的场景,在解决问题时,可以先从SQL语句本身入手,绝大多数此类SQL语句都是SELECT语句,且该类SQL语句涉及到的表数据量较多,且谓词上没有创建索引,导致数据库系统需要通过全盘扫描来获取数据。对于该情况,一般的做法往往比较“暴力”,即直接在WHERE子句、JOIN子句等涉及到的字段上创建索引。一般存在于WHERE子句中的简单比较都是可以使用索引扫描的,因此在该涉及到的字段上创建索引可能是有效的。但是,索引也并非是创建得越多越好(后面我们会提到冗余索引的情况),在创建索引时需要在选择度较高、数据量不是特别少的字段上创建索引,否则该索引收益不大。
对于单语句的索引推荐,openGauss数据库已经内置了该功能,用户可以通过调用系统函数gs_index_advise() 进行推荐,例如:
select * from gs_index_advise('select * from t1 where a > 1');
单语句索引推荐的核心逻辑可以表示为:
1. 提取JOIN类算子中的连接条件,保存为连接关系;
2. 提取Filter类算子中的过滤条件,保存为过滤关系;
3. 分析过滤关系中涉及字段的选择度和数据量,将评估适合创建索引的字段加入到候选索引列表中;
4. 分析连接关系,根据表的结果集大小确定驱动表,根据连接关系,将被驱动表中涉及的字段加入到候选索引列表中;
5. 提取Aggregate类算子涉及的字段,将该字段加入到候选索引列表中;
6. 提取Sort算子涉及的字段,将该字段加入到候选索引列表中;
7. 评估候选索引列表中的全部字段,过滤重复索引,合并相关索引;
8. 输出最终索引推荐的结果。
对于推荐出来的候选索引,用户可以自行决策是否创建,也可以通过openGauss的虚拟索引功能来评估索引收益,进行辅助决策。
对于单语句的索引推荐,业内也有不少开源的工具。不过,该类工具多数基于MySQL数据库实现(如美团开源的SQL Advisor)。同时,在索引推荐的层次上,该类工具使用的是对SQL语句进行语法解析后的结果,即根据SQL语句的抽象语法树(Abstract Syntax Tree, AST)进行索引推荐。然而,openGauss的索引推荐功能还可以建立在查询解析之后的查询树(Query Tree)的基础上进行索引推荐,也就是说,openGauss的索引推荐是建立在算子粒度上的。这样,某些被优化器改写的SQL语句(如exists, in 子查询),也可以被轻易地捕获并进行索引推荐,而前文提到的基于AST进行索引推荐的工具是很难实现的。
索引失效
就索引失效而言,一般存在以下六种情况:
1. 联合索引(又叫复合索引、多列索引)的最左匹配原则失效:同MySQL类似,openGauss的联合索引也满足最左匹配原则,如果查询不满足最左匹配原则,数据库优化器会倾向于放弃选择该索引扫描;
2. 使用了SELECT *: 除了老生常谈的可能扫描到不需要的字段之外,使用该写法还有可能导致openGauss的IndexOnlyScan 失效(在MySQL中称为CoveringIndex),也可能导致索引扫描后进行不必要的回表;
3. 谓词中的索引列参与了运算:这个问题一般不会出现在openGauss数据库中,这是因为openGauss的rewrite过程可以将该写法进行改写。但是openGauss的rewrite过程是基于规则进行的,某些情况下会存在改写匹配不上的情况,例如把WHERE子句的中谓词变得复杂一点就可能出现改写失效,进而导致索引失效,例如select a from t1 where b – 0 > 1 and c < 100; 语句中的减0与否会产生两种截然不同的执行计划;
4. 索引列涉及函数计算:对于openGauss来说,函数计算结果往往是“不可预测”的,故该索引有可能是失效的;不过openGauss支持函数索引(Functional Index),对于必须在字段上执行函数的情况可以选择使用该索引,只不过该索引的维护代价会比较大;同时,如果定义的函数可以被rewrite过程改写,该索引仍然可能是有效的,这点可能与某些数据库的行为不同;
5. 谓词中使用like: 对于字符串类型(如varchar, text)的字段,在使用like进行模糊查询时,在openGauss中默认是不走索引的,这点与MySQL在默认情况下不太一致;openGauss对字符串类型的字段,一般在进行等值查询时会选择使用索引,如果对于该字段更多地进行模糊查询(如like或正则),则需要在创建索引时显式地添加text_pattern_ops参数,如 create index on movies (title text_pattern_ops); 同时,同MySQL等数据库一样,该B+ Tree索引也只仅支持前缀匹配查询,如果希望利用B+ Tree进行后缀匹配,可以使用字符串翻转小技巧;对于全文检索,可以使用openGauss支持的tsquery特性,并通过创建GIN或GiST索引加速查询;
6. SQL语义上不应走索引:这种情况的类型有很多,比较典型的是谓词中对同一张表的两列进行比较、不等值比较(如!=, not in, not exists, is not null)、全量排序、类型转换(如字段的类型是varchar, 在谓词中与bigint进行比较时发生了隐式转换)等。
索引冗余
上面我们提到了创建索引的一般情况,对于绝大多数慢SQL场景,创建一个合适的索引就可以使得性能突飞猛进。但是,索引是不是就可以越多越好呢?显然不是。我们日常创建的索引中,使用最多的是B+ Tree索引,因此我们以B+ Tree为例,简单解释一下缘由。
众所周知,B+ Tree是一个多叉树,它的每一个子节点都是父节点的一个子“范围”。记录(或记录的位置)最终存储在B+ Tree的叶子节点中。因此,在进行数据检索时,只需要扫描匹配的子节点中的指定“范围”即可。但是,对于数据的删除,也需要付出相同的时间开销,进行B+ Tree节点的调整;如果被索引的数据修改了,还需要调整B+ Tree中原有的节点结构。由于B+ Tree的插入、删除、检索的算法时间复杂度都是相同的,因此当业务系统中的插入和删除操作更多时,索引维护的代价就会更大,甚至超过索引检索时带来的收益。与此同时,索引页也需要占用额外的磁盘空间,被索引数据量越大,索引页占据的空间就越大。而且,当前openGauss中的B+ Tree的实现仍然是有锁的,更多的索引页面有可能涉及更多的锁维护操作。
在openGauss数据库中,可以通过下述语句简单识别没有被使用过的索引:
SELECT s.schemaname, s.relname AS tablename, s.indexrelname AS indexname, pg_relation_size(s.indexrelid) AS index_size FROM pg_catalog.pg_stat_user_indexes s JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid WHERE s.idx_scan = 0 -- has never been scanned AND 0 <>ALL (i.indkey) AND NOT i.indisunique AND NOT EXISTS (SELECT 1 FROM pg_catalog.pg_constraint c WHERE c.conindid = s.indexrelid) ORDER BY pg_relation_size(s.indexrelid) DESC;
可以修改上述SQL语句中的 idx_scan 条件中的阈值,来调整返回结果。
对于workload中全量SQL语句进行索引创建其实是非常困难的,因为需要权衡全量SQL中增删查改语句的占比情况,同时需要估计索引的检索收益和维护代价,这个权衡过程十分复杂,一般的人工操作其实是很难的。因此,在日常数据库使用中,当需要创建索引时,最好进行全局业务的评估,衡量是否会干扰到其他业务,以及创建的总体收益是否为正,以免后期难以维护。
不过,对于openGauss数据库来说,可以使用系统级别的索引推荐功能来解决上述痛点问题,可以通过下述命令查看使用说明:
gs_dbmind component index_advisor --help
系统配置原因导致的慢SQL
在系统配置中,最常见的配置项就是对资源的配置。这包括允许使用的最大资源(主要是内存)、以及资源的使用方式等。除了调整资源配置,有些情况下还需要配置数据库优化器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子查询都有影响。
enable_nestloop: 开启该参数可以让优化器使用Nest Loop Join(NLJ), 但是关闭该参数也不会完全压制优化器选择NLJ. 对于某些复杂查询(如在TPC-H benchmark中的语句)来说,不应该选择NLJ, 但是优化器往往会出现规划错误。那么,在此场景下,可以通过禁用该参数来鼓励优化器选择使用其他JOIN方法。
random_page_cost: 一般与seq_page_cost配合调整。该参数调整数据库的CBO优化器中随机扫描的代价。该值设置得越大,数据库越认为随机扫描不可取,也就越不倾向于使用索引。该参数的默认值是4,对于机械硬盘来说,是合适的。但是,如果业务系统的磁盘是固态硬盘的话,就应该适当调小一下该参数值,一般的经验是调整为1.
default_statistics_target: 当前openGauss的默认优化器是CBO, 它高度依赖数据的统计信息。因此,对于复杂查询来说,更优质的统计信息往往可以获得更好的执行计划。通过增大该参数的值,可以获得更准确的统计信息,但是也会增加ANALYZE的时间。因此,对于复杂语句较多的场景,可以适当增加该参数值。
除了上述列出来的可能会影响SQL语句执行表现的系统参数外,还有很多参数可能会产生影响。不过,影响概率会小很多。如果用户希望检查一下数据库的参数配置是否合理,可以通过DBMind的参数推荐功能测试一下(该功能依赖当前正在运行的业务量,故不同时刻执行的效果可能会不同,建议在业务高峰时使用),相关使用帮助是:
gs_dbmind component xtuner recommend –help
如果用户希望针对自己的业务试探出最合适的参数,也可以使用离线模式(tune或train模式)。不过该场景一般是对未上线的业务系统进行初始调参,因为执行该功能可能会影响业务运行,故称之为离线模式。
资源竞争导致的慢SQL
当系统同时执行某些SQL语句的时候,它们可能会互相影响,进而导致某些SQL语句变为慢SQL, 这就是典型的资源竞争导致的慢SQL. 同时,不仅数据库中的语句们可能会进行资源竞争。在混合部署的环境中,操作系统上的其他任务也可能会影响数据库系统的表现。
对于一般的等待事件(wait event)来说,openGauss具备等待事件的记录视图,用户可以通过下列方法从宏观上查看Top级别的等待事件:
select * from dbe_perf.wait_events order by total_wait_time desc;
一般来说,对于数据库外部原因导致的资源竞争包括CPU、内存、IO的竞争,最典型的情况是IO风暴(Freeze IO)、CPU的计算资源的占用等。对于这种情况,一般不要将数据库与其他业务系统混合部署即可避免。
比较困难的是,数据库自己的某些任务之间互相影响,例如锁竞争、IO竞争等。
数据库中的不同SQL语句对锁资源进行占用,阻塞了其他语句的正常执行,导致SQL语句变慢了,甚至还会触发死锁检测。比较简单的排查当前锁占用情况的SQL语句是:
SELECT c.relkind, d.datname, c.relname, l.mode, s.query, extract(epoch FROM pg_catalog.now() - s.xact_start) AS holding_time FROM pg_locks AS l INNER JOIN pg_database AS d ON l.database = d.oid INNER JOIN pg_class AS c ON l.relation = c.oid INNER JOIN pg_stat_activity AS s ON l.pid = s.pid WHERE s.pid != pg_catalog.pg_backend_pid();
值得一提的是,openGauss并不支持pg_blocking_pids 函数。所以,通过该函数是无法查看到锁等待情况的。
下图展示了通过DBMind提供的openGauss-exporter监控到的数据库持锁情况:
还有一种情况是IO使用受到影响,例如系统正在进行IO操作时,执行某条SQL语句,该SQL语句对磁盘的访问被阻塞了。典型的数据库系统IO操作包括Analyze, Vacuum以及checkpoint 等。openGauss为此做了很多优化,例如增量checkpoint, 使用更大的版本号等(可以避免大量的autovacuum for prevent wrap)。
当然,除了上面列出的情况外,还存在并发量接近或超过系统负荷导致的性能下降和拒绝服务。例如,大量复杂查询语句对CPU资源的竞争、大并发情况下引起数据库的响应时间变慢等。
就资源竞争引起的慢SQL来说,基本都可以通过系统指标来发现。例如监控慢SQL发生时刻的CPU、内存、IO、锁、网络等的使用情况,根据该慢SQL发生的背景信息即可推断出该慢SQL是否由资源竞争导致的,以及是何资源短缺导致的。对于openGauss来说,DBMind提供了非常强大的数据库指标采集功能,即DBMind与Prometheus平台适配的exporter. 用户可以直接通过下述命令查看exporter的启动参数:
openGauss-exporter: 用于采集数据库指标,除常规指标外,还能监控慢SQL、系统配置等。
gs_dbmind component opengauss_exporter --help
reprocessing-exporter: 可以对Prometheus中已经采集到的指标进行聚合,例如计算QPS、内存使用率等。
gs_dbmind component reprocessing_exporter --help
注意:openGauss对于采集指标也进行了权限隔离,必须要求openGauss-expoter连接的用户具有sysadmin, monadmin 权限才可以获取某些监控表的指标。
下期介绍
下期,我们会从 表本身包含大量数据、SQL语句写得很差方面进行一下分析,并进行总结。
?如果您觉得博主的文章还不错或者用得到的话,请关注一下博主,如果三连收藏支持就更好啦!谢谢各位大佬给予的支持!