PostgreSQL 如何查找TOP SQL (例如IO消耗最高的SQL)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介:

标签

PostgreSQL , pg_stat_statements , TOP SQL


背景

数据库是较大型的应用,对于繁忙的数据库,需要消耗大量的内存、CPU、IO、网络资源。

SQL优化是数据库优化的手段之一,优化什么SQL效果最佳呢?首先要了解最耗费资源的SQL,即TOP SQL。

从哪里可以了解数据库的资源都被哪些SQL消耗掉了呢?

资源分为多个维度,CPU,内存,IO等。如何能了解各个维度层面的TOP SQL呢?

pg_stat_statements插件可以用于统计数据库的资源开销,分析TOP SQL。

一、安装pg_stat_statements

pg_stat_statements是PostgreSQL的核心插件之一。可以在编译PostgreSQL时安装,也可以单独安装。

编译时安装

make world  
make install-world  

单独安装

cd src/contrib/pg_stat_statements/  
make; make install  

二、加载pg_stat_statements模块

vi $PGDATA/postgresql.conf  
  
shared_preload_libraries='pg_stat_statements'  

如果要跟踪IO消耗的时间,还需要打开如下参数

track_io_timing = on  

设置单条SQL的最长长度,超过被截断显示(可选)

track_activity_query_size = 2048  

三、配置pg_stat_statements采样参数

vi $PGDATA/postgresql.conf  
  
pg_stat_statements.max = 10000           # 在pg_stat_statements中最多保留多少条统计信息,通过LRU算法,覆盖老的记录。  
pg_stat_statements.track = all           # all - (所有SQL包括函数内嵌套的SQL), top - 直接执行的SQL(函数内的sql不被跟踪), none - (不跟踪)  
pg_stat_statements.track_utility = off   # 是否跟踪非DML语句 (例如DDL,DCL), on表示跟踪, off表示不跟踪  
pg_stat_statements.save = on             # 重启后是否保留统计信息  

重启数据库

pg_ctl restart -m fast  

四、创建pg_stat_statements extension

在需要查询TOP SQL的数据库中,创建extension

create extension pg_stat_statements;  

五、分析TOP SQL

pg_stat_statements输出内容介绍

查询pg_stat_statements视图,可以得到统计信息

SQL语句中的一些过滤条件在pg_stat_statements中会被替换成变量,减少重复显示的问题。

pg_stat_statements视图包含了一些重要的信息,例如:

1. SQL的调用次数,总的耗时,最快执行时间,最慢执行时间,平均执行时间,执行时间的方差(看出抖动),总共扫描或返回或处理了多少行;

2. shared buffer的使用情况,命中,未命中,产生脏块,驱逐脏块。

3. local buffer的使用情况,命中,未命中,产生脏块,驱逐脏块。

4. temp buffer的使用情况,读了多少脏块,驱逐脏块。

5. 数据块的读写时间。

Name Type References Description
userid oid pg_authid.oid OID of user who executed the statement
dbid oid pg_database.oid OID of database in which the statement was executed
queryid bigint - Internal hash code, computed from the statement's parse tree
query text - Text of a representative statement
calls bigint - Number of times executed
total_time double precision - Total time spent in the statement, in milliseconds
min_time double precision - Minimum time spent in the statement, in milliseconds
max_time double precision - Maximum time spent in the statement, in milliseconds
mean_time double precision - Mean time spent in the statement, in milliseconds
stddev_time double precision - Population standard deviation of time spent in the statement, in milliseconds
rows bigint - Total number of rows retrieved or affected by the statement
shared_blks_hit bigint - Total number of shared block cache hits by the statement
shared_blks_read bigint - Total number of shared blocks read by the statement
shared_blks_dirtied bigint - Total number of shared blocks dirtied by the statement
shared_blks_written bigint - Total number of shared blocks written by the statement
local_blks_hit bigint - Total number of local block cache hits by the statement
local_blks_read bigint - Total number of local blocks read by the statement
local_blks_dirtied bigint - Total number of local blocks dirtied by the statement
local_blks_written bigint - Total number of local blocks written by the statement
temp_blks_read bigint - Total number of temp blocks read by the statement
temp_blks_written bigint - Total number of temp blocks written by the statement
blk_read_time double precision - Total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)
blk_write_time double precision - Total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)

最耗IO SQL

单次调用最耗IO SQL TOP 5

select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 5;  

总最耗IO SQL TOP 5

select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 5;  

最耗时 SQL

单次调用最耗时 SQL TOP 5

select userid::regrole, dbid, query from pg_stat_statements order by mean_time desc limit 5;  

总最耗时 SQL TOP 5

select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit 5;  

响应时间抖动最严重 SQL

select userid::regrole, dbid, query from pg_stat_statements order by stddev_time desc limit 5;  

最耗共享内存 SQL

select userid::regrole, dbid, query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 5;  

最耗临时空间 SQL

select userid::regrole, dbid, query from pg_stat_statements order by temp_blks_written desc limit 5;  

六、重置统计信息

pg_stat_statements是累积的统计,如果要查看某个时间段的统计,需要打快照,建议参考

《PostgreSQL AWR报告(for 阿里云ApsaraDB PgSQL)》

用户也可以定期清理历史的统计信息,通过调用如下SQL

select pg_stat_statements_reset();  

参考

https://www.postgresql.org/docs/9.6/static/pgstatstatements.html

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
1月前
|
SQL 人工智能 关系型数据库
PostgreSQL 常用SQL(持续更新...)
PostgreSQL 常用SQL(持续更新...)
|
23天前
|
SQL 关系型数据库 数据库
实时计算 Flink版操作报错之使用SQL 将 PostgreSQL 的 date 类型字段转换为 TIMESTAMP 类型时遇到报错,该如何处理
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
1天前
|
SQL 关系型数据库 PostgreSQL
【sql】PostgreSQL物化视图表使用案例
【sql】PostgreSQL物化视图表使用案例
6 0
|
9天前
|
SQL Oracle 关系型数据库
SQL SELECT TOP, LIMIT, ROWNUM 子句
SQL SELECT TOP, LIMIT, ROWNUM 子句
16 4
|
10天前
|
SQL 关系型数据库 数据库
nacos 2.2.3版本 查看配置文件的历史版本的接口 是针对MySQL数据库的sql 改成postgresql后 sql语句报错 该怎么解决
在Nacos 2.2.3中切换到PostgreSQL后,执行配置文件历史版本分页查询出错,因`LIMIT 0, 10`语法不被PostgreSQL支持,需改为`LIMIT 10 OFFSET 0`。仅当存在历史版本时报错。解决方案是调整查询SQL以兼容PostgreSQL语法。
|
12天前
|
SQL 关系型数据库 MySQL
SQL SELECT TOP, LIMIT, ROWNUM 子句
SQL SELECT TOP, LIMIT, ROWNUM 子句
22 2
SQL SELECT TOP, LIMIT, ROWNUM 子句
|
18天前
|
SQL Oracle 关系型数据库
SQL SELECT TOP, LIMIT, ROWNUM 子句
SQL SELECT TOP, LIMIT, ROWNUM 子句
25 3
|
23天前
|
SQL 关系型数据库 Java
实时计算 Flink版操作报错之在阿里云DataHub平台上执行SQL查询GitHub新增star仓库Top 3时不显示结果,是什么原因
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
1月前
|
SQL 关系型数据库 数据库
SQL 42501: Postgresql查询中的权限不足错误
SQL 42501: Postgresql查询中的权限不足错误
|
1月前
|
SQL Oracle 关系型数据库
常用数据库的分页语句(mySQL、oracle、PostgreSQL、SQL Server)
常用数据库的分页语句(mySQL、oracle、PostgreSQL、SQL Server)

相关产品

  • 云原生数据库 PolarDB