来源 | OSCHINA 社区


(资料图片)

作者 | Gauss松鼠会

原文链接:https://my.oschina.net/gaussdb/blog/5553522

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

上期我们讲了索引原因、系统配置和资源竞争导致的慢 SQL,今天我们继续分析和总结。

目录

表本身包含大量数据

SQL 语句写得很差

总结

表本身包含大量数据

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

1. 表的数据量很大,且很少被缓存,导致语句需要扫描的元组很多;

2. 表的数据量很大,在修改、删除数据时需要修改较多的元组;

3. 向表中插入的数据量很大;

4. 业务上需要检索出的数据量很多;

5. 频繁的数据修改,导致表中存在很多死元组(dead tuple),影响扫描性能;

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

SQL 语句写得很差

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

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

改写规则

改写条件

改写说明

原始查询语句示例

改写后语句示例

将"select distinct *" 改写为"select *"

所查询表格含唯一列或主键

通过确定 tuple 无重复,去掉 distinct,从而省去去重步骤,提升效率

select distinct * from bmsql_customer limit 10;

select * from bmsql_customer limit 10;

将 having 子句中条件放到 where 子句中

-

将谓词表达式提前,可有效缩减 group 时的数据集

select cfg_name from bmsql_config group by cfg_name having cfg_name="1"

select cfg_name from bmsql_config where cfg_name = "1" group by cfg_name

简化 where 子句中谓词表达式

-

某些复杂谓词无法有效触发 openGauss 内的 rewrite 逻辑,无法使用索引扫描

select o_w_id, o_d_id, o_id, o_c_id from bmsql_oorder where o_w_id + 1> 3

select o_w_id, o_d_id, o_id, o_c_id from bmsql_oorder where o_w_id > 2

将 order by 或 group by 中的无用列去掉

group by 或 order by 涉及的列包含在 where 子句中的等值表达式中

去掉无用字段,SQL 更为简洁

select cfg_name from bmsql_config where cfg_name="2" group by cfg_name order by cfg_name, cfg_value

select cfg_name from bmsql_config where cfg_name = "2" order by cfg_value

去掉 where 子句中永为真的表达式

-

去掉无用字段,SQL 更为简洁

select * from bmsql_config where 1=1 and 2=2 limit 10

select * from bmsql_config limit 10

将 union 转换为 union all

-

避免了去重带来的执行代价

select * from bmsql_config union select * from bmsql_config

select * from bmsql_config union all select * from bmsql_config

将 delete 语句转换为 truncate 语句

无 where 子句

将 DML 语句转换为 DDL 语句,一次性回收表空间,执行速度更快

delete from bmsql_config

truncate table bmsql_config

将 where 子句中 "or" 连接的等式转换为 "in" 结构

-

"in" 结构可加快过滤速度

select * from bmsql_stock where s_w_id=10 or s_w_id=1 or s_w_id=100 or s_i_id=1 or s_i_id=10

select * from bmsql_stock where s_w_id in (1,10,100) or s_i_id in(1,10)

将 self join 查询拆分为效率更高两个子查询

1) self join 查询。

2) where 子句包含相同列差值的范围查询。

例如 1

通过等值谓词加快查询速度

select a.c_id from bmsql_customer a, bmsql_customer b where a.c_id - b.c_id <= 20 and a.c_id > b.c_id

select * from (select a.c_id from bmsql_customer as a, bmsql_customer as b where trunc((a.c_id) / 20) = trunc(b.c_id / 20) and a.c_id > b.c_id union all select a.c_id from bmsql_customer as a, bmsql_customer as b where trunc((a.c_id) / 20) = trunc(b.c_id / 20 + 1) and a.c_id - b.c_id <= 20)

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

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

总结

我们在上面已经列出了能够导致慢 SQL 的原因,基本覆盖了在 openGauss 上造成慢 SQL 的大多数原因。不过,one-by-one 手动地进行慢 SQL 检查对于用户来说工作量确实太大。故而,openGauss 的 DBMind 功能本身已经集成了对慢 SQL 进行智能根因识别的能力,用户可以通过运行下述命令在后台启动慢 SQL 根因分析功能(需要首先部署 Prometheus 以及 expoter,以便能够采集到监控指标):

gs_dbmind service start-c confpath --only-run slow_query_diagnosis

注:显式指定 --only-run 参数可以仅启动被选择的 DBMind 服务项

被诊断后的慢 SQL 会存储在元数据库(存放诊断结果的数据库)中,用户可以通过下述命令查看:

gs_dbmind component slow_query_diagnosis show -c confpath --query SQL --start-timetimestamps0--end-timetimestamps1

也可以通过与 Grafana 联合来展示慢 SQL 的分析结果,DBMind 也提供了简单的 Grafana 配置模板,可供用户参考:

https://github.com/opengauss-mirror/openGauss-server/blob/master/src/gausskernel/dbmind/tools/misc/grafana-template-slow-query-analysis.json

由于openGauss 官方网站的发行包中的 DBMind 可能滞后于代码托管平台(gitee 或 github)上的最新代码,直接编译 openGauss 又需要花费很多的时间。故而,如果用户只是想单纯提取最新的 DBMind 功能,可以通过下面的 Linux 命令来实现:

gitclone -b master --depth 1 https://gitee.com/opengauss/openGauss-server.gitcdopenGauss-server/src/gausskernel/dbmind/mvtools dbmindtarzcf dbmind.tar.gz gs_dbmind dbmind

生成的 dbmind.tar.gz 压缩包在合适的部署位置解压即可。

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

附表:慢 SQL 检查列表

检查项

检查方式(系统表或系统视图)

检查方法

语句执行时存在锁竞争

dbe_perf.statement_history(start_time,finish_time, query)、pg_locks(pid, mode, locktype, grant)、pg_stat_activity(xact_start, query_start, query, pid)

查询在语句执行期间是否被阻塞。

表中死元组占比超过设定阈值

dbe_perf.statement_history(query, dbname, schemaname)

pg_stat_users_tables(relname, schemaname,n_live_tup, n_dead_tup)

n_dead_tup /n_live_tup,占比超过阈值则认为表过度膨胀 (默认阈值:0.2)。

语句扫描行数较多

dbe_perf.statement_history(n_tuples_fetched, n_tuples_returned, n_live_tup, n_dead_tup)

n_tuples_fetched+n_tuples_returned,超过阈值则认为过大(默认阈值:10000)。

语句缓存命中率较低

dbe_perf.statement_history(n_blocks_fetched, n_blocks_hit)

n_block_hit /n_block_fetched,小于阈值则认为较低(默认阈值:0.95)

慢 SQL (delete、insert、update) 相关表存在冗余索引

dbe_perf.statement_history(dbname, schemaname, query), pg_stat_user_indexes(schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch)

pg_indexes(schemaname, tablename, indexname, indexdef)

SQL 相关表满足:① 不是唯一索引;② (idx_scan, idx_tup_read,idx_tup_fetch)=(0,0,0);③ 索引不在数据库的("pg_catalog", "information_schema","snapshot", "dbe_pldeveloper")schema 下。如果满足则认为次索引为冗余索引,否则为有效索引。

更新数据量较多

dbe_perf.statement_history(query, n_tuples_updated)

pg_stat_user_tables(n_live_tup, n_dead_tup)

n_tuples_updated 超过阈值则认为更新数据量较多(默认阈值:1000)。

插入数据量较多

dbe_perf.statement_history(query, n_tuples_inserted)

pg_stat_user_tables(n_live_tup, n_dead_tup)

n_tuples_inserted 超过阈值则认为插入数据量较多(默认阈值:1000)。

删除数据量较多

dbe_perf.statement_history(query, n_tuples_deleted)

pg_stat_user_tables(n_live_tup, n_dead_tup)

n_tuples_deleted 超过阈值则认为删除数据量较多(默认阈值:1000)。

相关表索引个数较多

pg_stat_user_indexes(relname,schemaname, indexrelname)

如果表中索引数大于阈值并且索引与字段数比率超过设定阈值,则认为索引数较多(索引个数阈值:3,比率默认阈值:0.6)。

执行语句发生落盘(外排序)行为

dbe_perf.statement(sort_count, sort_spilled_count, sort_mem_used, hash_count, hash_spilled_count, hash_ued_mem, n_calls)

分析指标判断是否有 hash 或者 order 导致的落盘行为,主要逻辑为:

1 如果 sort_count 或者 hash_count 不为 0,sort_mem_used 或者 hash_mem_used 为 0,则此 SQL 一定发生了落盘行为;

2 如果 sort_spilled_count 或者 hash_spilled_count 不为 0,则执行可能发生落盘行为;

语句执行期间相关表正在执行 AUTOVACUUM 或 AUTOANALYZE 操作

dbe_perf.statement_history(start_time, finish_time, query)

pg_stat_user_tables(last_autovacuum, last_autoanalyze)

执行 SQL 期间,正在发生 vacuum 或者 analyze 行为。

数据库 TPS 较大

dbe_perf.statement_history(start_time, finish_time)

pg_stat_database(datname, xact_commit, xact_rolback)

相对于正常业务时的 TPS,当前 TPS 增长较大,则认为数据库 TPS 较大;TPS 短期内增长异常则认为是业务风暴。

IOWait 指标大于设定阈值

系统 IOWait 指标异常升高

IOWait 大于用户设定阈值(默认阈值:10%)

IOPS 指标大于设定阈值

系统 IOPS 指标异常

IOPS 指标大于用户设定阈值(默认阈值:1000)。

load average 指标大于设定阈值

系统 load average 指标异常

load average 与服务器逻辑核数比率大于用户设定阈值(默认阈值:0.6)。

CPU USAGE 指标大于设定阈值

系统 CPU USAGE 指标异常

CPU USAGE 指标大于用户设定阈值(默认阈值:0.6)。

IOUTILS 指标大于设定阈值

系统 IOUTILS 指标异常

IOUTILS (磁盘利用率) 大于用户设定阈值(默认阈值:0.5)。

IOCAPACITY 指标大于设定阈值

系统 IO CAPACITY 指标异常

IOCAPACITY (IO 吞吐量) 大于用户设定阈值 (默认阈值:50MB/s)。

IODELAY 指标大于设定阈值

系统 IO DELAY 指标异常

IO DELAY(IO 延迟)大于用户设定阈值(默认阈值:50ms)。

网卡丢包率

系统网卡丢包率异常

NETWORK DROP RATE 大于用户设定阈值(默认 0 阈值:0.01)。

网卡错误率

系统网卡错误率异常

NETWORK ERROR RATE 大于用户设定阈值(默认阈值:0.01)。

线程池占用量异常

dbe_perf.global_threadpool_status

数据库线程池使用率大于阈值(默认阈值:0.95)

连接池占用量异常

pg_settings.max_connections,pg_stat_activity

数据库连接池占用率大于阈值(默认阈值:0.8)

双写延迟较大

dbe_perf.wait_events

双写延迟大于阈值(默认阈值:100us)

表长时间未更新

pg_stat_user_tables

表未更新时长超过阈值(默认阈值:60s)

checkpoint 效率低(本规则仅作为粗略判断)

pg_stat_bgwriter

数据库 buffers_backend 与(buffers_clean+buffers_checkpoint)占比小于阈值(默认阈值:1000)

主备复制效率较低

pg_stat_replication

主备 write_diff、replay_diff、sent_diff 超过阈值(默认阈值:500000)

执行计划存在异常 seqscan 算子

执行计划

seqscan 算子代价与总代价比率超过阈值(默认阈值:0.3),此特征也会判断是否缺少相关索引。

执行计划存在异常 nestloop 算子

执行计划

nestloop 算子代价与总代价比率超过阈值(默认阈值:0.3)并且进行 nestloop 的结果集行数超过阈值(默认阈值:10000)。

执行计划存在异常 hashjoin 算子

执行计划

hashjoin 算子代价与总代价比率超过阈值(默认阈值:0.3)并且进行 hashjoin 的结果集小于阈值(默认阈值:10000)。

执行计划存在异常 groupagg 算子

执行计划

groupagg 算子代价与总代价比率超过阈值(默认阈值:0.3)并且执行 groupagg 的行数超过阈值(默认阈值:10000)。

SQL 写法不优

SQL 文本、pg_stat_user_tables

SQL 写法不优导致执行性能较差

SQL 执行被定时任务影响

pg_job,

dbe_perf.statement_history

定时任务执行影响了 SQL 执行性能,考虑调整定时任务时间,避免产生影响。

执行计划生成时间较长

dbe_perf.statement_history

SQL 执行计划生成时间较长。

参考资料:

[1].https://www.2ndquadrant.com/en/blog/managing-freezing/

[2]. http://mysql.taobao.org/monthly/2016/06/03/

[3].https://www.2ndquadrant.com/en/blog/basics-of-tuning-checkpoints/

[4]. https://lwn.net/Articles/591723/

[5].https://dev.mysql.com/doc/refman/8.0/en/glossary.html

[6].https://github.com/opengauss-mirror/openGauss-server/tree/master/src/gausskernel/dbmind

END

PG已碾过MySQL,它真正的对手是Oracle

这里有最新开源资讯、软件更新、技术干货等内容

点这里 ↓↓↓ 记得 关注✔ 标星⭐ 哦~

推荐内容