PG数据库如何发现和采集TOP SQL

TOP SQL是在某个时间段内对系统影响较大的SQL语句，在不同时间窗口中，我们可能发现不同的TOP SQL。不过对于系统影响较大的TOP SQL，其出现有一定的周期性。因此我们采用某些周期性的采集方法，一定能够发现这些SQL语句的存在。

PostgreSQL数据库中采集TOP SQL可以通过两种主要的方法，第一种是通过在日志中记录TOP SQL。通过设置log_min_duration_statement参数，可以把执行时间超出该阈值的SQL语句记录到日志中，从而帮助我们捕获TOP SQL。

postgres=#SELECT name,setting FROM pg_settings

WHERE name = 'log_min_duration_statement';

            name            | setting

----------------------------+---------

 log_min_duration_statement | -1

(1 row)

postgres=# SET log_min_duration_statement TO 100;

SET

postgres=# SELECT pg_reload_conf();

 pg_reload_conf

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

 t

(1 row)

上面的例子中，我们将log_min_duration_statement设置为100ms，也就是说执行时间超过100ms的sql语句都将被记录到日志中。

可以看到，通过设置该参数，可以把超出阈值的SQL语句以及其执行计划都写到日志文件中，我们通过阅读日志文件，可以发现一些执行开销较大的SQL语句。这种TOP SQL的发现方法效率很高，但是也存在一定的局限性，首先，该方法进能够记录一些执行时间较长的SQL语句，如果某些SQL执行时间低于100毫秒，但是执行频率十分高，对系统影响较大，那么这种方法就无法发现了。其次是每条超过阈值的SQL语句都写入日志文件，日志文件的膨胀会比较快，如果系统存在性能问题，大量的SQL执行时间超过100毫秒的时候，很可能在短时间内就会把日志所在的文件系统写满，从而导致数据库故障。因此这种方法一般是在做SQL性能分析或者查找TOP SQL的专项工作中启用，不建议在实际生产环境中开启。

另外一种TOP SQL采集的方式目前已经成为实际生产环境中SQL优化的主要方式，那就是pg_stat_statement插件。pg_stat_statements插件提供了记录服务器所执行的所有 SQL 语句的执行统计信息，可以用于统计数据库的SQL语句产生的资源开销，通过分析pg_stat_statements表的数据，我们可以更为精准的查找TOP SQL。如果要启用该插件，需要在postgresql.conf的shared_preload_libraries中增加pg_stat_statements来预先加载，一些比较新的PG版本中，该模块是缺省装在的。当pg_stat_statements被载入并启用时，它会跟踪该服务器 的所有数据库的统计信息。该模块提供了一个视图 pg_stat_statements以及函数pg_stat_statements_reset 用于访问和操纵这些统计信息。在某个数据库中如果你没有发现pg_stat_statemnts，可以用CREATE EXTENSION pg_stat_statements 为特定数据库启用它们。

启用pg_stat_statements后，为了更好的采集TOP SQL语句，还需要调整一些相关的参数。具体的参数定义如下表：

参数	含义	建议设置
track_io_timing	启用对系统 I/O 调用的计时。这个参数默认为关闭，开启会带来较大的系统开销	生产环境建议关闭，测试或者分析时短暂开启
track_activity_query_size	为每个活动会话指定存储当前执行命令的文本所保留的内存量，它们被用于pg_stat_activity.query域。	默认1024一般够用，如果系统中存在较长的SQL,可以加大，会增加SQL采集的内存开销
pg_stat_statements.max	最多保留多少条TOP SQL	一般可采用默认值，如果系统中的TOP SQL数量十分庞大，可以加大
pg_stat_statements.track	all - 所有SQL包括函数内嵌套的SQL； top - 直接执行的SQL(函数内的sql不被跟踪)；  none - 不跟踪。	建议使用ALL
pg_stat_statements.track_utility	是否跟踪DDL/DCL语句	建议设置为on
pg_stat_statements.save	数据库实例重启时是否保留历史数据	一般情况建议设置为OFF,如果需要持续跟踪，建议设置为ON，PG数据库会将内存中的数据固化到物理表中

设置好相关参数后，插件就会自动持续采集TOP SQL，并在pg_stat_statements中保存相关的数据了。pg_stat_statements数据字典的定义如下：

字段	含义
userid	用户ID
dbid	数据库ID
queryid	SQLID
query	SQL语句
calls	执行次数
total_time	总时间
min_time	最短时间
max_time	最大时间
mean_time	平均时间
stddev_time	方差时间
rows	返回行数
shared_blks_hit	共享池命中数
shared_blks_read	共享池读取数
shared_blks_dirtied	脏块数量
shared_blks_written	共享池写出块数
local_blks_hit	会话本地缓冲命中
local_blks_read	会话本地缓冲读
local_blks_dirtied	会话本地缓冲脏块
local_blks_written	会话本地缓冲写出
temp_blks_read	临时块读取
temp_blks_written	临时块写
blk_read_time	块读取时间
blk_write_time	块写出时间

pg_stat_statements表的字段如上，通过这些字段可以根据你的需求去查找TOP SQL。TOP SQL采集之后需要首先进行分析，并不是所有的SQL都是需要去优化的，有些SQL的优化并不能真正提升数据库的整体性能。比如一些非周期性的临时SQL语句，这些SQL偶尔会执行，可能你花了比较大的代价去优化后发现，这条SQL再也没有被执行过；还有一些SQL是一些定时的批处理任务执行的，虽然具有极高的开销，执行时间也很长，不过这些SQL往往都在半夜的批处理中执行，没有业务人员在线等待该任务执行完成，哪怕这些SQL不够优化，也不会影响应用体验，也不会对白天的OLTP业务高峰产生任何性能影响，这类SQL也不需要进行优化；还有一类SQL十分复杂，也可能出现在白天的OLTP业务高峰期，如果这类SQL不太好优化，不过可以把此类任务安排到每天中午OLTP交易较少时或者晚上批处理窗口执行，那么较为低代价的优化方法是调整这些SQL的执行时间，而不是花太大的代价去优化它。

在分析某些SQL对系统的影响的时候，我们需要通过其执行时长、执行次数、CPU开销、IO开销、引发的热块冲突的数量等维度去做统计。比较精准的优化方法是首先定位系统中的主要问题，然后才找出解决这些问题的相关TOP SQL，进行更为精准的优化。

postgres=# SELECT sum(total_time) AS total_time,

postgres-# sum(blk_read_time + blk_write_time) AS io_time,

postgres-# sum(total_time - blk_read_time - blk_write_time) AS cpu_time,

postgres-# sum(calls) AS ncalls,

postgres-# sum(rows) AS total_rows

postgres-# FROM pg_stat_statements;

   total_time    | io_time |    cpu_time     |  ncalls  | total_rows

-----------------+---------+-----------------+----------+------------

 13624282.158227 |       0 | 13624282.158227 | 21609904 |  229038966

(1 row)

上面的SQL语句可以从pg_stat_statements中发现SQL语句的总体汇总情况，包括总的执行时间，CPU时间，IO等待时间，执行数量，返回行数的数据。从这些汇总情况中，我们可以发现当前系统的主要开销情况。如果io_time比较高，那么说明SQL语句的物理读写比较多，需要注重优化物理IO较高的SQL。上面的汇总数据是数据库启动以来或者pg_stat_statements_reset执行之后的累计值，你可以通过pg_stat_statements_reset先清除相关的数据，然后隔一段时间再来执行该SQL,也可以把这些数据定期采集到某张表中，过一段时间后进行对减后获得某个时间段内的增量值，从而更准确的了解某个时间段内数据库的TOP SQL的汇总情况。

SELECT sum(total_time) AS total_time, sum(blk_read_time + blk_write_time) AS io_time, sum(total_time - blk_read_time - blk_write_time) AS cpu_time, sum(calls) AS ncalls, sum(rows) AS total_rows FROM pg_stat_statements WHERE dbid IN (SELECT oid FROM pg_database WHERE datname=current_database());

如果你只关心当前的数据库，那么你可以使用上面的语句，或者直接在where条件中输入某个数据库的名字，从而更为精准的分析你的应用系统的TOP SQL总体情况。

如果我们要查找CPU开销最大的TOP 5 SQL，可以使用下面的语句：

WITH tpsql AS (

SELECT sum(total_time) AS total_time, sum(blk_read_time + blk_write_time) AS io_time,

sum(total_time - blk_read_time - blk_write_time) AS cpu_time,

sum(calls) AS ncalls, sum(rows) AS total_rows

FROM pg_stat_statements

)

SELECT query,(pss.total_time-pss.blk_read_time-pss.blk_write_time)/tpsql.cpu_time*100 cpu_pct

FROM pg_stat_statements pss, tpsql

WHERE (pss.total_time-pss.blk_read_time-pss.blk_write_time)/tpsql.cpu_time >= 0.05

ORDER BY pss.total_time-pss.blk_read_time-pss.blk_write_time DESC LIMIT 5;

通过修改这条查询语句，你可以定义你的自定义筛选条件，从而获得TOP SQL。其他的一些常用分析语句如下表：

TOP SQL查找功能	SQL语句
单次调用最耗IO SQL TOP	select query from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 10;
IO开销总量最大的 SQL TOP	select query from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 10;
单次平均调用最耗时 SQL TOP	select query from pg_stat_statements order by mean_time desc limit 10;
总最耗时最大的 SQL TOP	select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit 10;
最大执行时长与平均执行时长差异最大的TOP SQL	select userid::regrole, dbid, query from pg_stat_statements order by (max_time-mean_time) desc limit 10;
执行时间抖动最严重的TOP SQL	select query from pg_stat_statements order by stddev_time desc limit 10;
最耗共享内存TOP SQL	select query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 10;
最耗临时空间TOP SQL	select query from pg_stat_statements order by temp_blks_written desc limit 10;
执行次数最多的TOP SQL	select query from pg_stat_statements order by calls desc limit 10;
共享池命中率最低的TOP SQL	select query from pg_stat_statements order by (1-shared_blks_hit/(shared_blks_hit+shared_blks_read )) desc limit 10;

准确的找到TOP SQL是SQL优化的最为关键的一步，如果这一步找到的SQL不准确，会导致后续工作的效率不高。因此在查找TOP SQL的时候，一定要针对自己系统中存在的关键问题。如果你没有针对系统中的关键问题去查找TOP SQL,那么很可能你完成SQL优化之后，会发现优化效果不佳。虽然你优化过的SQL都表现出了很好的效果，但是数据库的整体性能并没有明显的提升，业务人员也可能对优化效果没有证明的认可。

因此在做TOP SQL采集的时候，还有一种方法是根据业务人员反馈存在问题的业务模块，找出该业务模块对应的SQL语句，进行针对性的优化。这种优化方式我们一般称为白盒优化。如果你希望优化效果能够直接让业务人员有所感知，那么应该更多的采用白盒优化的方式。在采集TOP SQL的时候，你也应该更为关注那些执行时间超过一定阈值（比如2秒钟）的SQL语句。如果你完成了对这些SQL的优化，那么业务人员会有更好的优化效果体验。因为我们把一条执行时间100毫秒的SQL优化为10毫秒，虽然SQL执行效率提高了10倍，但是业务人员的感知几乎为0。