PostgreSQL之查找最慢的SQL 的方法

来源：互联网发布：数据流量怎么打开不了编辑：程序博客网时间：2024/05/01 02:21

帮帮忙顶一下这个文章，非常好的一个文章，非常值得了解。

来自：http://blog.chinaunix.net/uid-24774106-id-3780341.html

PostgreSQL部署上之后，经过一段时间的运行，我们比较关心那些SQL运行时间比较长，或者说那些SQL执行的特别慢，拖累的性能，只有找到这些SQL，才能有针对性地对这些SQL进行优化，提升PostgreSQL的性能。
PostgreSQL提供了pg_stat_statements来存储SQL的运行次数，总运行时间，shared_buffer命中次数，shared_buffer read次数等统计信息。
NameTypeReferencesDescriptionuseridoidpg_authid.oidOID of user who executed the statementdbidoidpg_database.oidOID of database in which the statement was executedquerytext Text of the statement (up to track_activity_query_size bytes)callsbigint Number of times executedtotal_timedouble precision Total time spent in the statement, in secondsrowsbigint Total number of rows retrieved or affected by the statementshared_blks_hitbigint Total number of shared blocks hits by the statementshared_blks_readbigint Total number of shared blocks reads by the statementshared_blks_writtenbigint Total number of shared blocks writes by the statementlocal_blks_hitbigint Total number of local blocks hits by the statementlocal_blks_readbigint Total number of local blocks reads by the statementlocal_blks_writtenbigint Total number of local blocks writes by the statementtemp_blks_readbigint Total number of temp blocks reads by the statementtemp_blks_writtenbigint Total number of temp blocks writes by the statemen 上图表来自PostgreSQL官方文档，注意的一点是，我的PostgreSQL是9.1.9,此时total_time的单位是秒，我观9.2的PostgreSQL的文档，total_time单位已经是毫秒。所以我的参考文献More On PostgreSQL perform 里面应该用的是9.2,因为外国这位大神默认单位是毫秒。
可以看出，pg_stat_statements统计了SQL的很多信息，方便我们分析SQL的性能。但是这个属于PostgreSQL的扩展，需要修改postgresql.conf，才能使用：
操作步骤如下
1 修改配置文件，并且重启PostgreSQL方能生效

#------------------------------------------------------------------------------
# PG_STAT_STATEMENTS OPTIONS
#------------------------------------------------------------------------------
shared_preload_libraries = 'pg_stat_statements'
custom_variable_classes = 'pg_stat_statements'
pg_stat_statements.max = 1000
pg_stat_statements.track = all

2 创建pg_stat_statements扩展

CREATE EXTENSION pg_stat_statements;

从此之后，PostgreSQL就能记录SQL的统计信息。
上面的表格虽然丰富，其实我们基本比较关心执行最慢的SQL，如何查看执行最慢的10条SQL？

SELECT  query, calls, total_time, (total_time/calls) as average ,rows, 
        100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent 
FROM    pg_stat_statements 
ORDER   BY average DESC LIMIT 10;

我在我本地的DB，查找最慢的2条SQL，输出如下：

在我另一台机器上，用pgadmin查看：

统计结果一直都在，重启也不会清零，那么统计结果如何清零重新统计呢？执行下面SQL即可：

select pg_stat_statements_reset() ;

找到最耗时的SQL，我们就能针对这些耗时的SQL，查看是否有优化的余地。

参考文献：
1 More on Postgres Performance
2 PostgreSQL manual

0 0