PostgreSQL_如何查找TOP_SQL_(例如IO消耗最高的SQL)

Stella981
• 阅读 892

标签

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();
点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
4个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Easter79 Easter79
3年前
sql注入
反引号是个比较特别的字符,下面记录下怎么利用0x00SQL注入反引号可利用在分隔符及注释作用,不过使用范围只于表名、数据库名、字段名、起别名这些场景,下面具体说下1)表名payload:select\from\users\whereuser\_id1limit0,1;!(https://o
Stella981 Stella981
3年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Stella981 Stella981
3年前
PostgreSQL死锁进程及慢查询处理
1、死锁进程查看:SELECTFROMpg_stat_activityWHEREdatname'数据库名称'andwaitingtrue;pid进程id。2、慢查询SQL:selectdatname,pid,usename,application_name,client_addr,client
Wesley13 Wesley13
3年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Python进阶者 Python进阶者
10个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这