全部产品
存储与CDN 数据库 安全 应用服务 数加·人工智能 数加·大数据基础服务 互联网中间件 视频服务 开发者工具 解决方案 物联网
云数据库 RDS 版

查找最耗费资源的 SQL(Top SQL)

更新时间:2017-06-07 13:26:11

数据库是较大型的应用,对于繁忙的数据库,需要消耗大量的内存、CPU、IO、网络资源。SQL 优化是数据库优化的手段之一,而为了达到 SQL 优化的最佳效果,您首先需要了解最消耗资源的 SQL(Top SQL),例如 IO 消耗最高的 SQL。

数据库资源分为多个维度、CPU、内存、IO 等,为能够从各个维度层面查找最消耗数据库资源的 SQL,您可以使用 pg_stat_statements 插件统计数据库的资源开销和分析 Top SQL。

本文将通过示例介绍如何创建 pg_stat_statements 插件、如何分析 Top SQL 以及如何重置统计信息。

创建 pg_stat_statements 插件

执行如下命令,在需要查询 TOP SQL 的数据库中,创建 pg_stat_statements 插件。

  1. create extension pg_stat_statements;

分析 TOP SQL

pg_stat_statements 输出内容介绍

通过查询 pg_stat_statements 视图,您可以得到数据库资源开销的统计信息。SQL 语句中的一些过滤条件在 pg_stat_statements 中会被替换成变量,可以减少重复显示的问题。

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

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

  • shared buffer 的使用情况:命中、未命中、产生脏块、驱逐脏块。

  • local buffer 的使用情况:命中、未命中、产生脏块、驱逐脏块。

  • temp buffer 的使用情况:读了多少脏块、驱逐脏块。

  • 数据块的读写时间。

下表列出了 pg_stat_statements 输出内容中各参数的含义。

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。

  1. 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。

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

最耗时 SQL

执行如下命令,查询单次调用最耗时 SQL TOP 5。

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

执行如下命令,查询总最耗时 SQL TOP 5。

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

响应时间抖动最严重 SQL

执行如下命令,查询响应时间抖动最严重 SQL。

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

最耗共享内存 SQL

执行如下命令,查询最耗共享内存 SQL。

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

最耗临时空间 SQL

执行如下命令,查询最耗临时空间 SQL。

  1. 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)》

您也可以通过执行如下命令,来定期清理历史统计信息。

  1. select pg_stat_statements_reset();

参考文档

PostgreSQL 9.6.2 Documentation — F.29. pg_stat_statements

本文导读目录