《阿里云认证的解析与实战-数据仓库ACP认证》——云原生数据仓库AnalyticDB PostgreSQL版解析与实践(下)——三、SQL性能调优(下)

本文涉及的产品
阿里云百炼推荐规格 ADB PostgreSQL,4核16GB 100GB 1个月
云解析 DNS,旗舰版 1个月
全局流量管理 GTM,标准版 1个月
简介: 《阿里云认证的解析与实战-数据仓库ACP认证》——云原生数据仓库AnalyticDB PostgreSQL版解析与实践(下)——三、SQL性能调优(下)

更多精彩内容,欢迎观看:《阿里云认证的解析与实战-数据仓库ACP认证》——云原生数据仓库AnalyticDB PostgreSQL版解析与实践(下)——三、SQL性能调优(下):


6. 可视化执行计划

 

如图是执行计划可视化展示。

 

 示例语句

explain (format json,analyze true) select count(*) from test,testr where test.num1=testr.num2;


image.png

 

Postgres EXPLAIN Visualizer

http://tatiyants.com/pev/#/plans/new

 

7. 如何发现问题

 

自上而下,梳理痛点:自上而下梳理计划,确定时间开销大的算子。

查看代价,对比行数:查看比较代价估算的异常,对比估算行数和实际执行行数差异大的情况。

耗时算子,尽量避免:AP场景很少需要NestLoop、Sort+GroupByAgg。

具体算子,是否合理:是否有不必要的Motion算子,Join内外表顺序是否合适,Scan是否可以使用索引。

内存信息,调整参数:查看下盘情况,分析后适当调整statement_mem参数。

 

8. 通过索引提升查询性能

 

ADB PG支持如下索引类型及语句示例/适用场景:

 

B-tree:create index i1 on t1 using btree(c1),适用大多数场景,尤其对于点查询和更新等操作。

Bitmap:create index i2 on t2 using bitmap(c2),唯一值低于10w且低于总行数1/10,常与其他列有联合过滤条件。

GIN/GiST:全文检索,数组,JSON。

 

1) B-Tree索引优化建议,建议创建索引的场景

 

点查询的场景。

where条件的过滤效果较好的场景。

 

2) 不建议创建索引的场景

 

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

一个表的索引数最好不超过6个。

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

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

 

3) 索引使用的建议

 

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

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

索引创建完成后,最好做一下统计信息收集。

 

9. 消除Redistribute Motion

 

在进行连接或聚集操作时,会根据数据分布情况添加分布式算子,对数据进行重分布Redistribute Motion或广播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。

image.png

 

10. 避免下盘

 

查询执行过程中,当集群内存不足时,数据库可能会选择将临时结果暂存到磁盘。由于磁盘操作相对内存访问缓慢,避免查询执行过程中的算子下盘,有助于提高查询效率。

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

 

11. 锁的检测及处理

 

1) 死锁

 

死锁的检测和处理为数据库内部机制,无需手工干预,出现死锁会影响数据库吞吐量。

 

死锁检测方式:

 Local Deadlock Detector:用于检测单个计算节点内发生的死锁。

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

 

示例

 

Session1

BEGIN;UPDATE t SET j=33 WHERE pk=3;UPDATE t SET j=33 WHERE pk=7;END;

  

Session2

BEGIN;UPDATE t SET j = 33 WHERE pk=7;UPDATE t SET j = 33 WHERE pk=3;END;

 

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

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

 

2) 常规锁

 

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

 

执行语句

select * from gp_toolkit.gp_locks_on_relation where lorrelname ='<table>';

  

12. 空间回收

 

为什么会空间膨胀

 

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

 

膨胀率判断方法

 

通过gp_toolkit.gp_bloat_diag视图,bdirelpages表示表实际占用Page数,bdiexppages表示表实际需要Page数,bdirelpages/bdiexppages > 4时,即可考虑进行空间回收。

 

回收操作可以通过vacuum和vacuum full

 

ü vacuum:回收时不锁表,但只标记删除空间可被再利用,不释放物理空间。

ü vacuum full:回收时锁表,表无法读写,回收物理空间,建议在维护窗口进行。

 

维护定期回收空间任务

https://help.aliyun.com/document_detail/59176.html

 

13. 避免数据倾斜

 

image.png 

 

1) 表现形式

 

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

 

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

节点参与计算数据量不均匀,存在木桶效应。

 

2) 数据倾斜排查

 

用户控制台排查:

 

控制台基础信息项,会展示实例最大存储水位与实例存储总水位,存储数据倾斜时,两个数值将会差异过大。

 控制台监控与报警项,计算节点监控处会展示所有计算节点的空间使用量,存储数据倾斜时,节点磁盘空间使用量会差异过大。

 

通过SQL排查:

 

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

 查询结果根据数据倾斜程度排序,当tb_balance_rate大于1.1时,认为该表存在数据倾斜。

 

排查同一张表在各个Segment节点下的存储数据量,执行如下语句:

select gp_segment_id, pg_size_pretty(pg_total_relation sizeltable name))from gp_dist_random('gp_id');

  

排查同一张表在各个Segment节点下的行数,执行如下语句:

select gp_segment_id,count(1) from table name group by gp segment id;

  

3) 避免数据倾斜

 

建表过程指定分布键或分布规律:

CREATE TABLE table_name (......) [DISTRIBUTED BY(column name,[...]) |DISTRIBUTED RANDOMLY |DISTRIBUTED REPLICATED];

  

修改分布键或分布规律:

ALTER TABLE [IF EXISTS] [ONLY] name SET WITH (REORGANIZE=true/false)| DISTRIBUTED BY (column_name,[...])|DISTRIBUTED RANDOMLY|DISTRIBUTED REPLICATED;

  

注意

修改分布键或分布规律,大多数情况都将会进行数据迁移,对于数据量过大的表,该操作会相对较久并且会锁表,无法查询

REORGANIZE=false仅在修改前后一致、或修改为随机分布时才会不进行数据重分布。

 

分布策略选择规则:

 

 小表(总行数低于1万)优先选择复制表分布策略(DISTRIBUTED REPLACATED)。

 大表优先选择参与Join/GroupBy计算的字段作为分布键Hash分布。

 若没有数据分布均匀的字段作为分布键使用,采用随机分布策略(DISTRIBUTED RANDOMLY)。

 

相关实践学习
阿里云百炼xAnalyticDB PostgreSQL构建AIGC应用
通过该实验体验在阿里云百炼中构建企业专属知识库构建及应用全流程。同时体验使用ADB-PG向量检索引擎提供专属安全存储,保障企业数据隐私安全。
AnalyticDB PostgreSQL 企业智能数据中台:一站式管理数据服务资产
企业在数据仓库之上可构建丰富的数据服务用以支持数据应用及业务场景;ADB PG推出全新企业智能数据平台,用以帮助用户一站式的管理企业数据服务资产,包括创建, 管理,探索, 监控等; 助力企业在现有平台之上快速构建起数据服务资产体系
相关文章
|
3天前
|
SQL Oracle 关系型数据库
SQL(MySQL)
SQL语言是指结构化查询语言,是一门ANSI的标准计算机语言,用来访问和操作数据库。 数据库包括SQL server,MySQL和Oracle。(语法大致相同) 创建数据库指令:CRATE DATABASE websecurity; 查看数据库:show datebase; 切换数据库:USE websecurity; 删除数据库:DROP DATABASE websecurity;
|
1月前
|
监控 数据挖掘 OLAP
深入解析:AnalyticDB中的高级查询优化与性能调优
【10月更文挑战第22天】 AnalyticDB(ADB)是阿里云推出的一款实时OLAP数据库服务,它能够处理大规模的数据分析任务,提供亚秒级的查询响应时间。对于已经熟悉AnalyticDB基本操作的用户来说,如何通过查询优化和性能调优来提高数据处理效率,是进一步提升系统性能的关键。本文将从个人的角度出发,结合实际经验,深入探讨AnalyticDB中的高级查询优化与性能调优技巧。
82 4
|
2月前
|
SQL 监控 数据库
SQL语句是否都需要解析及其相关技巧和方法
在数据库管理中,SQL(结构化查询语言)语句的使用无处不在,它们负责数据的查询、插入、更新和删除等操作
|
2月前
|
SQL 运维 关系型数据库
MySQL 运维 SQL 备忘
MySQL 运维 SQL 备忘录
48 1
|
22天前
|
SQL 监控 安全
员工上网行为监控软件:SQL 在数据查询监控中的应用解析
在数字化办公环境中,员工上网行为监控软件对企业网络安全和管理至关重要。通过 SQL 查询和分析数据库中的数据,企业可以精准了解员工的上网行为,包括基础查询、复杂条件查询、数据统计与分析等,从而提高网络管理和安全防护的效率。
28 0
|
2月前
|
存储 缓存 监控
深入解析:Elasticsearch集群性能调优策略与最佳实践
【10月更文挑战第8天】Elasticsearch 是一个分布式的、基于 RESTful 风格的搜索和数据分析引擎,它能够快速地存储、搜索和分析大量数据。随着企业对实时数据处理需求的增长,Elasticsearch 被广泛应用于日志分析、全文搜索、安全信息和事件管理(SIEM)等领域。然而,为了确保 Elasticsearch 集群能够高效运行并满足业务需求,需要进行一系列的性能调优工作。
114 3
|
1月前
|
SQL 监控 大数据
优化AnalyticDB性能:查询优化与资源管理
【10月更文挑战第25天】在大数据时代,实时分析和处理海量数据的能力成为了企业竞争力的重要组成部分。阿里云的AnalyticDB(ADB)是一款完全托管的实时数据仓库服务,支持PB级数据的秒级查询响应。作为一名已经有一定AnalyticDB使用经验的开发者,我发现通过合理的查询优化和资源管理可以显著提升ADB的性能。本文将从个人角度出发,分享我在实践中积累的经验,帮助读者更好地利用ADB的强大功能。
39 0
|
1月前
|
SQL 关系型数据库 MySQL
MySql5.6版本开启慢SQL功能-本次采用永久生效方式
MySql5.6版本开启慢SQL功能-本次采用永久生效方式
38 0
|
1月前
|
SQL 关系型数据库 MySQL
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
31 0
|
2月前
|
SQL 数据可视化 BI
SQL语句及查询结果解析:技巧与方法
在数据库管理和数据分析中,SQL语句扮演着至关重要的角色

推荐镜像

更多