【视频】云原生数据仓库 AnalyticDB PG 解析与实践（下）｜学习笔记（三）

开发者学堂课程【数据仓库 ACP 认证课程：【视频】云原生数据仓库 AnalyticDB PG 解析与实践（下）】学习笔记，与课程紧密联系，让用户快速学习知识。

课程地址：https://developer.aliyun.com/learning/course/928/detail/14627

【视频】云原生数据仓库 AnalyticDB PG 解析与实践（下）

十三、SQL 性能调优：通过索引提升查询性能

B-Tree索引优化建议:

l 建议创建索引的场景:

1.点查询的场景;

2. where条件的过滤效果较好的场景;

l 不建议创建索引的场景

1.更新较多的表上不建议建索引,更新较为频繁的表上创建索引;

2.一个表的索引数最好不超过6个;

3.避免创建超过3列的组合索引;

4.避免创建重复的索引或具有相同前导列的索引;

l 索引使用的建议:

1.组合索引是从前向后匹配where条件的,不能命中前导列的where条件,不会使用该索引;

2.批量导入大量数据前可删除索引,导入数据后重建索引;

3.索引创建完成后,最好做一下统计信息收集，路径更加准确;

 

十四、SQL 性能调优：消除 Redistribute Motion

在进行连接或聚集操作时,会根据数据分布情况添加分布式算子,对数据进行重分布( RedistributeMotion )或广播( Broadcast Motion)。分布式算子会占用大量的网络资源。如果能够通过建表和业务逻辑进行分布式算子的规避,则能够提升数据库查询性能。

SELECT * FROM t1, t2 WHERE t1.a=t2.a;

t1表的分布键为t1.a , t2表的分布列是t2.b ,会出现t2表的重分布

t1表的分布键为t1.a , t2表的分布列是t2.a ,无需重分布直接Join

假设有两张表，t1表和t2表，在t1表和t2表分别都有一个列a，在a列上把两个表进行关联，在建表的时候需要指定分布列，这个表在哪一列上，按照什么样的规则进行分布。一般默认的是哈希分布方式，然后t1表的分布键为t1.a , t2表的分布列是t2.b ，查询是在a上，可以看到如下的执行过程

postgres= EXPLAIN SELECT*FROM  t1, t2 WHERE t1. a=t2. a;

QUERY PLAN

Gather Motion 3:1 (slice2; secments: 3) (cost=0.00, .862.00 rows=1 width=32)

-> Hash Join (cost=0.00..862.00 rovs=1 width=32)

Hash Cond: tl.a = t2.a

-> Table Scan on t1 (cost=0.00. .431.00 rovg=1 width=16)

->Hash (cost=431.00.431.00 rows=1 width=16)

->Redistribute Motion 3:3 (slicel; segnents: 3) (cost=0.00. .431.00 rows=1

Hash Key: t2.a.

->Table Scan on t2 (cost=0.00.. 431.00 rows=1 width=16)

Sottinga: optinizer-on

Optinizer status: P9QO version 1.609

(10 rows)

分布式算子会占用大量的网络资源，需要尽量规避，能把查询规避成没有分布式算子之后会提升性能，如果把这个查询改成再分布列上进行join，那么他就不会走Redistribute motion这样一个算子，通过修改表t2把分布列也改成a这一列，可以得到如下的执行计划。

postgres= EXPLAIN SELECT*FROM  t1, t2 WHERE t1. a=t2. a;

QUERY PLAN

Gather Motion 3:1 (slice1; secments: 3) (cost=0.00, .862.00 rows=1 width=32)

-> Hash Join (cost=0.00..862.00 rovs=1 width=32)

Hash Cond: tl.a = t2.a

-> Table Scan on t1 (cost=0.00. .431.00 rovg=1 width=16)

->Hash (cost=431.00.431.00 rows=1 width=16)

->Redistribute Motion 3:3 (slicel; segnents: 3) (cost=0.00. .431.00 rows=1

->Table Scan on t2 (cost=0.00.. 431.00 rows=1 width=16)

Sottinga: optinizer-on

Optinizer status: P9QO version 1.609

(8 rows)

大幅度提高SQL的性能，也会减少网络资源的占用。

 

十五、SQL 性能调优：避免下盘

查询执行过程中,当集群内存不足时,数据库可能会选择将临时结果暂存到磁盘。

由于磁盘操作相对内存访问缓慢,避免查询执行过程中的算子下盘有助于提高查询效率。

算子下盘常见原因优化建议:调整statement_ mem (默认2GB )

敏感词：spelling

出现spilling就是代表有下盘的动作，内存满足不了所要申请的资源，遇到这种情况应该调整参数，默认的内存是2GB，如果在过程中发现内存不够，可以扩大statemen_mem的值，以避免下盘，完成查询操作，这是比较简单的方法。再复杂一些，就有可能需要改写是SQL语句，这就看SQL语句具体的方式。

Work mem used: 524K bytes avg，1563K bytes ax (seg0). Workf1le: (3 splling)

Work_ mem wanted: 1563K bytes avg, 1563K bytes max (seg0) to 1essen wrkfile 1/0 atfecting 1 workers.

 

十六、SQL 性能调优：锁的检测及处理

死锁:死锁的检测和处理为数据库内部机制,无需手工干预。

如果出现死锁的话会影响数据库吞吐量性能，ADBPG具有死锁检测的能力，如果发现死锁超过几秒以后，系统就会进行干预，就会把参与死锁的session处理掉。

●Local Deadlock Detector;用于检测单个计算节点内发生的死锁.

●Global Deadlock Detector;用于检测跨计算节点发生的分布式死锁

发现死锁的例子：

Session1:

BEGIN;

UPDATE tSETj= 33 WHERE pk = 3;

UPDATE tSETj = 33 WHERE pk = 7;

END;

Session2:

BEGIN;

UPDATEtSETj = 33 WHERE pk = 7;

UPDATEtSETj= 33 WHERE pk= 3;

END;

当pk=3, pk=7落在单个计算节点上时, Local Deadlock Detector能检测到死锁.

当pk=3, pk=7落在不同计算节点上, Global Deadlock Detector能检测到这种分布式死锁.

常规锁:查看所有当前被加锁的对象,以及相应加锁的SQL

有可能是客户端的连接把对象加上了锁，但是并没有释放，影响了整个系统，这有可能是一种误操作，但是这个时候就需要通过在后台进行处理来保证其他业务能够顺畅运行

select * from gp_ toolkit.gp_ locks, _on_ relation where lorrelname='<table>';

 

十七、SQL 性能调优：空间回收

为什么会空间膨胀:

Lvc算法好处是允许数据并发的修改和读取，一个session去修改数据，另一个session读取数据，二者是不冲突的。修改过程中也允许其他来读取数据，通过生成多个版本来完成。

●表中的数据被删除或更新后( UPDATE/DELTE ) , 物理存储层面并不会直接删除数据,而是标记这些数据不可见,所以会在数据页中留下很多"空洞”, 在读取数据时,这些“空洞”会随数据页一起加载,拖慢数据扫描速度,需要定期回收删除的空间

膨胀率判断方法:

●通过gp_ toolkit.gp bloat diag视图，bdirelpages表示表实际占用Page数（数据页），bdiexppages表示表实际需要Page数, bdirelpages/bdiexppages > 4时,即可考虑进行空间回收。通过加表名可以控制vacuum的进度。

十八、SQL 性能调优;避免数据倾斜

  Segment0画的比较大，就表示这里面的数据量比较多，Segment 0承载的数据比其他承载的数据要多，表示了节点数据不均衡。

数据存储倾斜表现形式为数据在多个Segment节点上分布不均匀,存在如下影响:

●磁盘存储水位不均匀,个别Segment节点磁盘使用过多,提前用满磁盘存储空间

●节点参与计算数据量不均匀,存在木桶效应（短板）。每个SQL的cpu内存磁盘能力是相等的，如果一旦有一个节点里面数据太多了，在扫描的时候CPU，和内存都会相对缺乏，就会变成短板，会拖慢整个系统的性能。

 

十九、SQL 性能调优：数据倾斜-用户控制台排查

1.控制台基础信息项,会展示实例最大存储水位与实例存储总水位。

存储数据倾斜时,两个数值将会差异过大。

2. 控制台监控与报警项,计算节点监控处会展示所有计算节点的空间使用量。

存储数据倾斜时,节点磁盘空间使用量会差异过大。

实例最大存储水位与实例存储水位，如果这两个值差距比较大，就可能会存在数据倾斜，另外在打开数据节点监控，可以看到对于每一个计算节点，两个节点相差5%以内就为正常，如果两个差别比较大，如果超过10%以上，可能是比较明显的数据倾斜。

 

二十、SQL 性能调优：数据倾斜-SQL 排查

通过控制台信息确定存在存储倾斜后,使用SQL排查倾斜的表

查询结束以后就会列出表名、大小、倾斜值登，来进行查询结果根据数据倾斜程度排序,当tb_ balance_ rate大于1.1时,可以认为该表存在数据倾斜。

排查同一张表在各个Segment节点下的存储数据量

select gp_ segment_ id,pg_ size_ pretty(pg. total _relation_ size('table_ name')) from gp_ dist_ random('gp_ id');

gp_ segment_ id| pg_ size_ pretty

2  | 228MB

3  |228MB

0  |7288MB(0上的节点明显高于其他节点，说明产生了数据倾斜)

1  |227MB

(4 rows)

排查同一张表在各个Segment节点下的行数

select gp_ segment_ id,count(1) from table_ name group by gp_ segment_ id;

Gp_swgment_id|count

3  | 1503360

1  | 1498151

0  |48009216

2  |  1499416

(4 rows)