在数据库的日常使用中,难免会遇到慢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<a.id-b.id<10,其中a,b为同一个表的两个alias。 |
通过等值谓词加快查询速度 |
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-time timestamps0 --end-time timestamps1
也可以通过与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命令来实现:
git clone -b master --depth 1 https://gitee.com/opengauss/openGauss-server.git cd openGauss-server/src/gausskernel/dbmind/ mv tools dbmind tar zcf 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
?如果您觉得博主的文章还不错或者有帮助的话,请关注一下博主,如果三连收藏支持就更好啦!谢谢各位大佬给予的支持!