PostgreSQL 执行计划,成本公式解说,代价因子校准,自动跟踪SQL执行计划(三)|学习笔记

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB PostgreSQL 版,企业版 4核16GB
推荐场景:
HTAP混合负载
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
简介: 快速学习PostgreSQL 执行计划,成本公式解说,代价因子校准,自动跟踪SQL执行计划(三)

开发者学堂课程【PostgreSQL快速入门: PostgreSQL 执行计划,成本公式解说,代价因子校准,自动跟踪SQL执行计划(三)】学习笔记与课程紧密联系,让用户快速学习知识

课程地址https://developer.aliyun.com/learning/course/16/detail/91


PostgreSQL 执行计划,成本公式解说,代价因子校准,自动跟踪SQL执行计划(三)

 

内容介绍:

七、explain代价因子的校准

八、auto_explain 插件的使用

七、explain 代价因子校准

1、不同的硬件环境 CPU 性能,IO 性能各不相同,所以默认的代价因子可能不适合实际的硬件环境,如图代价需要实际的案例校准。

image.png

举个例子,第一步要校准的是连续扫描页面的成本(seq_page_cost)以及 cpu_tuple_cost,通过全表扫描校准 cpu_tuple_cost,连续扫描成本通过第三方的软件计算出来,或者从硬盘给的参数中推出来。这里用了 system temp 脚本计算每一个连续扫描的块带来的开销。

2、创建测试表

digoal=# create table tbl_cost align (id int, info text, crt_time timesteap)

CREATE TABLE

然后往里面插入一些记录,记录最好是随机的。这样的话可以使得我们后面要做的离散 IO请求测试更准确一些

digoal=# insert into tbl_cost_align select (random ()*2000000000)::int,md5(random0::text), clock_tinestanp ()from

generate_series(1,100000):

INSERT O 100000

digoal=# insert into (tbl_cost_align select Grandom ()*2000000000)::int,md5random()::text), clock_tinestamp ()from

generate_series(1,10000000);

INSERT O 10000000

总共插入了一千零十万行。插入以后要分析表,获得统计信息。

digoal=# analyze tbl_cost_align:

ANALYZE

3、可以查看到占用的数据块个数

digoal=# select relpages from pc_alass where relname

=’tbl_cost_align’

relpages

94393

(1 row)

得到 relpages 和总共的行数,行数是一千零十万行。

执行 checkpoint 后关闭数据库的了得到一个纯粹的物理磁盘的连续 io请求的 cost 常量,不能有 shared buffer 的干扰.

digoal# checkpoint;

CHECKPOINT

pc93@db-172-16-3-150-> pgctl stop -m fast

waiting for server to shut down.... done

server stopped

同时还不能有 OSCache的干扰,所以要清理操作系统 cache。

[root@db-172-16-3-150ssd1]# sync;echo3>/proc/sys/vm/drop_caches

前面我们说了,有些指标可以通过硬件厂商得到或者自行测试得到。那么这里我们就要自己测试得到。测试方法比较多,本文将通过systemtap 来得到每次 IO请求的时间。为了降低 systentap 带来的额外开销,

请参考:

http://blog.163.com/digoal@126/blog/static/1638770402013102610150692/ http://blog.163.com/digoal@126/blog/static/168770402013102621826954/

指定亲和1启动数据库:

pg93@db-172-16-3-150->taskset-c1 /home/pg93/pgsq19.3.1/bin/postgres >/dev/null 2>@1

4、开启psql

pg93@db-172-16-3-150-> psq1

psql (9.3.1)

Type “help“ for help

digoal=# select pg_backend_pid():

pg_backend_pid

-----------------------

5727

1 rows)

指定亲和7,开启stap,收集postgres进程相关的io信息

[root@db-172-16-3-150 ~]#  taskset -c 7 stap -e ‘

global a

probe process("/hone/pg93/pgsq19.3.1/bin/postgres”).

mark("query_start")

delete a

println("query_start“,user_string($argl),“pid:",pid())

}

probe vfs. read return {

t=gettimeofday_ns() - @entry(gettimeofday_ns())

# if (execnme() ==“postarez" && devname != “N/A")

a[pid()] <<< t

}

probe process("/home/pg93/pgsql9.3.1/bin/postges").mark("

query_done") {

if  (@count(a[pid()]))

printdln(“**", pid(), @count(a[pid()]), @avg(a[pid()])) println("query_done“,user_string($argl),“pid:",pid())

if  (@count(a[pid()]))  {

println (@hist_log(a[pid()]))

#println(@hist_linear(alpid()],1024,4096,100))

}

delete a

}' -x 5727

这个 postgre 里面内置了一个探针,叫 query_start,探针一旦触发,会把a的全局变量数据删掉,然后打印一条记录,打印的是当前的 pid,还有查询的 SQL 语句。

每遇到一次 vfs.read 就会把它的时间记录下来最后当查询结束的时候,统计总共开销的时间是多少,平均每一次 IO 的时间是多少,然后再输出它的柱状图。如果不是从真正的物理设备读,不要这条记录 # if (execnme() ==“postarez" && devname != “N/A") ,但是实际的操作过程中有域图的功能,实际场景当中可以不写这条语句。

5、接下来在 psql 中执行 explain analyze,在 explain 的结果中可以得到一个值,实际的执行时间(3260.695-0.839)。并且可以得到原始的cost(195393.00),这个原始的cost有助于验证公式是否正确

digoal=# explain (analyze,verbose, costs,buffers, liming) select * from tbl_cost_align:

QUERY PLAN

Seq Scan on postgres.tbl_cost_align (cost=0.00..195393.00 rows=10100000 width=45) (actual tine=0.839..3260.695

rows=10100000 loops

=1)

Output: id,info,crt_tine

Buffers: shared read=94393--注意这个read指的是未命中shared buffer,如果是命中的话会有hit=?

Total runtime: 4325.885 ms

(4 rows)

实际执行explain (analyze,verbose, costs,buffers, liming) select * from tbl_cost_align: 这条 SQL 语句的时候,stap 脚本已经开始监听了,有 query_start 的输出,之后对每一个 vfs.read 系统调用做一个运算,把时间加到 aipad 数组里。

执行完 explain 之后,在 stap 输出中得到了我们想要的平均 IO 响应时间信息(14329)

image.png

5727是postgre 进程的 pid,94417是调用多少次,@avg(a[pid()]) 是每一次 vfs.read 系统调用的时间开销,用到的秒是纳秒,每次io调用14329纳秒。下面是输出 IO 的柱状图,每一次 IO 请求统计出来的柱状图。

image.png

65536到131072纳秒之间有五千多次调用,实际上,真正的物理读只有五千多次。

实际上调用了九万多次,其中有八万多次在 oscache 里面调用,因为有预读,读的时候有 read had。物理设备每一次预读是 getra,block --getra /dev/sdb1,

sdb1 每次会预读256个数据块,每次会多读一些。最终得到的结果是读了将近六千次,已经预读了很多个数据块。

6现在1000万条数据已经插完了,

image.png

analyze tbl_cost_align 看它占了多少数据块,总共是94393,pg_ct1 stop -m fast,把 oscache 清掉,pg_ct1 start 把数据库起来,起来之后要查看当前连接的 pid,要指定 cpu 亲和7,因为这里是一个八核的CPU。启用数据库,尽量不要用零号 ID 启动,重新启动。这样做是因为零号 CPU 会带来额外的开销,具体的原因和超系统有关。重新用一号 CPU 启动,一号数据库启动完成。

7、然后启动脚本,给它指定一个 pid,这个 pid 指的是进程的 pid,

脚本已经启动完成。stap 已经起来,模块已经加载好了。

现在要给一个查询语句,一定要用 analyze,因为要校准它,把它校准成 cost 跟实际的执行时间一样,这个时候就能看到输出。

image.png

30404**94419**8230 和原来的差别有点大,os cache 已经清掉了,说明数据是对的真正的 io 响应时间在65539纳秒以上,因为有预读的功能,大部分 IO 在 os cache 里面响应。已经得到了平均 IO 响应时间,我们得到的是8230,跟预想不一样。

8、下面做一个公式,

image.png

shared hit 没有,read 是94393个数据块,这里真正在发生一些物理的读,下面从公式里面得到 cpu_tuple_cost,真正的时间是3554.348,这个值等于页面乘以真正的时间开销这个时间是得到的8230,0.00823是毫秒,94393*0.00823 是每个数据会消耗的毫秒数,再加上 cpu_tuple_cost,cpu 是一千零十万行,再乘以一个x,还要减去启动0.839,要从这个公式里得到x。select ((3554.348-0.839)-94393*0.00823)/10100000,算出来x等于0.0002749162980198。

image.png

现在的两个值已经算好了,把它修改一下,seq_page_cost 修改成0.00823,cpu_tuple_cost 改成0.0002749162980198,现在两个因子已经校准完成

image.png

可以重启数据库,pg_ctl restart -m fast现在在 os cache 有如下内容,

image.png

所以要先把 os cache 清掉。然后再 explain analyze,得到的实际时间低一些,因为没有 cache stap,cache stap 会额外带来一些开销,现在校准的时间跟2369已经很接近了,刨去 skype 做探针 hander 带来的额外开销,探测94419次,这带来的额外开销有1.2秒左右,把这个时间去掉。现在出来的时间是连续扫描的时间,校准完这个之后,另外还要校准随机扫描的因子。

9、随机扫描页面的成本以及 CPU 的 index tuple cost,就是索引扫描带来的额外开销是多少,cpu_operate_cost 的校准。

这里有两个未知数,random_page_cosr 从stap跟踪获得cpu_index_tuple_cost 和 cpu_operate_cost 两个未知数需要两个等式求得,一个等式不能求出两个未知数,要利用 cpu_index_tuple_cost 和 cpu_operate_cost 的比例得到第二个等式,这个比例来自 postgre 参数,它们的比例是2:1。现在要计算这个成本,先把所有的成本都归一,这样方便后面计算

digoal=# set random_page_cost=1;

SET

digoal=# set cpu_tuple_cost=1

SET

digoal=# set cpu_index_tuple_cost=1

SET

digoal=# set cpu_operator_cost=1;

SET

这次要走索引扫描,

digoal=# set enable_scqscan=off; set enable_bitmapscan=off; explain(analyze,verbose,costs,buffers,timing) select * from tbl_cost_align where id>1998999963;

QUERY PLAN

Index Scan using idx_tbl_cost_align_id on postgres.tbl_cost_align(cost=174.00..20181.67 rows=5031 width=45)actual time=0.029.17.73 rows=5037 loops=1)

Output: id, info, crt_time

Index Cond:(tbl_cost_align.id>1998999963)

Buffers: shared hit=5054

Total runtime: 18.477 ms

(5 rows)

执行计划表明这是个索引扫描,至于扫了多少个数据块是未知的,索引的tuples也是未知的,已知的是cost和rows

20181.67=blocks*random_page_cost +cpu_tuple_cost*5031 + cpu_index_tuple_cost*5031 + cpu_operator_cost*? 公式 ,一个是随机的 page cost,blocks 不知道,随机扫描多少是不知道的,最终要在五千多行上做处理,cpu_tuple_cost*5031 是行数,cpu_operator_cost 在索引上面有多少 tuple 是不知道的所以 cpu_operator_cost 乘以多少行是不知道的,这是一个过滤条件过滤条件是从一万行里过滤出5031行还是六千行里过滤是不知道的,所以打了一个问号。这可以通过调整 cpu_operator_cost 来得到,

image.png

如果把它变成2,cost 就变成了25386,25386-20181得到5205,相当于是从5205行里过滤出5031行,相当于做了一个短式变换,这样就求出了从多少个索引的 tuple 上做条件扫描,扫描出5031行。

10现在还要得到random_page_cost 的 blocks,这个 blocks 也是未知的。

image.png

现在把 random_page_cost 该成2,改成2之后在再做一个减法,就得到了 blocks,成本变小。现在等式更新成

0181.67=4914.66*random_page_cost+cpu_tuple_cost*5031 +cpu_index_tuple_cost*5031+ cpu_operator_cost*5205,接下来要做的是通过 stap 统计出 random_page_cost 的成本。通过以下方式写数据库,把 cache 清掉。

pg93@db-172-16-3-150->taskset-c 1/home/pg93/ pgsq9.3.1/

bin/postgres>/dev/null2>&1

[root@db-172-16-3-150~]# sync;echo3>/proc/sys

/vm/drop_caches

digoal=# select pg_backend_pid);

pg_backend_pid

10009

(1 row)

stap也是这样

image.png

复制 taskset-c/home/pg93/pgsql9.3.1/bin/postgres>/dev/

null2>&1,把数据库清掉,重启。然后查看 pid,

30653。执digoal=# set enable_seqscan=off; set enable_

bitmapscan=off;explain (analyz e,verbose,costs, buffers,

timing)select*from tbl_cost_align where id>1998999963

这条 sql 语句,这个 SQL 语句的实际执行时间是1673毫秒,

image.png

随机扫描的成本也已经出来了,是326822

image.png

比连续扫描大了40倍左右。因为是随机扫描,下图预读功能起到的功能很小,

只有10个块左右预读出来是有效的,大部分都不是预读出来的。替换等

image.png

select 1673.138-0.661=4914.66*0.326822+

0.00027491629801980198*4777+ *4777+ *5205,从两个等式里面就能得到 cpu_index_tuple_cost 和 cpu_operator_cost 。

11把这两个放进去之后在执行 analyze,

结合例子1得到的两个常量,所有的5个常量值就调整好了

digoal=# set cpu_tuple_cost=0.00018884145574257426

SET

digoal=#set cpu_index_tuple cost=0.00433497085216479990 SET

digoal=#set cpu_operator_cost=0.00216748542608239995;

SET

digoal=# set seq_page_cost=0.014329

SET

digoal=# set random_page_cost=0.368362

SET

image.png

vi postgre.conf,进入到下图所示地方,

image.png

修改成如图,

image.png

接下来重启数据库,然后把 os cache 清掉,执行 analyze,where id>1998999963,现在真实执行时间更跟 cost 很相近,cost 是1561,真正的执行时间是1141,相差的是随机扫描的时候 stap 带来的一些额外开销,可以忽略不计。

 

八、auto_explain 插件的使用

1、auto_explain 插件可以帮助我们发现一些执行计划不正常的SQL语句。

先把插件安装进来,cd /opt/soft_bak/postgresql1-9.3.2,编译的时候如果用 genetic world,就会编译好;如果没有,可以到 contribute 目录下 auto_explain 里面去 genetic。create extension auto_explain,auto_explain 直接load 就可以用,一个是线程用法,一个是非线程用法。

2、线程的用法是指直接 load ,

image.png

设置 SQL 语句大于指定的时间的时候,就会把它执行计划记下来设定时间是10毫秒,只要超过10毫秒执行时间,都会把 SQL 语句记下来。select  count (*) from tbl_cost_align,执行时间肯定是超过十毫秒的,这个时候就会把日志记到配置的日志目录中。默认配置目录是在 pg log 目录里,看一下当前日志是否已经记下了 explain?

image.png

刚才执行 select count(*) 的时候已经把执行计划记下来了,显然超过十毫秒。如果把它改成十秒钟,select count(*)就不会被记下来,因为执行时间没有超出,这个时候就没有记下来。

image.png

3、用这个还可以配在参数文件中,配置两个参数。

image.png

auto_explain 时间是100毫秒,超出100毫秒就会把这个 SQL 语句记下来。

它的好处是当一条 SQL 语句不正常的时候,把他的执行计划记下来,检查是否是因为没加索引导致执行时间变长。执行计划这个章节到此结束,可以练习使用 auto_explain 跟踪慢 sql 执行计划,explain 代价因此的校准。

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
2月前
|
SQL 关系型数据库 数据库
实时计算 Flink版操作报错之使用SQL 将 PostgreSQL 的 date 类型字段转换为 TIMESTAMP 类型时遇到报错,该如何处理
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
16小时前
|
SQL 存储 关系型数据库
关系型数据库SQL Server学习
【7月更文挑战第4天】
8 2
|
15小时前
|
SQL 自然语言处理 关系型数据库
PolarDB自然语言到SQL语言转义
PolarDB自然语言到SQL语言转义
|
9天前
|
SQL 关系型数据库 分布式数据库
PolarDB产品使用问题之如何迁移SQL Server
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
18天前
|
SQL 存储 关系型数据库
关系型数据库中的SQL Server
【6月更文挑战第11天】
49 3
|
29天前
|
SQL 关系型数据库 数据库
nacos 2.2.3版本 查看配置文件的历史版本的接口 是针对MySQL数据库的sql 改成postgresql后 sql语句报错 该怎么解决
在Nacos 2.2.3中切换到PostgreSQL后,执行配置文件历史版本分页查询出错,因`LIMIT 0, 10`语法不被PostgreSQL支持,需改为`LIMIT 10 OFFSET 0`。仅当存在历史版本时报错。解决方案是调整查询SQL以兼容PostgreSQL语法。
|
8天前
|
SQL 缓存 关系型数据库
PolarDB产品使用问题之已经修改了expire_logs_days参数并确认已生效,但在SQL查询中仍然显示为0,该怎么办
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
8天前
|
SQL 关系型数据库 MySQL
PolarDB产品使用问题之如何将指定的备份SQL文件导入到集群中
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
8天前
|
SQL 关系型数据库 分布式数据库
PolarDB产品使用问题之如何查看SQL语句使用的是行索引还是列索引
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
8天前
|
SQL 关系型数据库 分布式数据库
PolarDB产品使用问题之有一部分sql导致锁表了,如何看是哪一条sql导致的
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。