PostgreSQL之查找最慢的SQL 的方法

3/7/2017来源:SQL技巧人气:2846

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

来自: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次数等统计信息。

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
query text   Text of the statement (up to track_activity_query_size bytes)
calls bigint   Number of times executed
total_time double PRecision   Total time spent in the statement, in seconds
rows bigint   Total number of rows retrieved or affected by the statement
shared_blks_hit bigint   Total number of shared blocks hits by the statement
shared_blks_read bigint   Total number of shared blocks reads by the statement
shared_blks_written bigint   Total number of shared blocks writes by the statement
local_blks_hit bigint   Total number of local blocks hits by the statement
local_blks_read bigint   Total number of local blocks reads by the statement
local_blks_written bigint   Total number of local blocks writes by the statement
temp_blks_read bigint   Total number of temp blocks reads by the statement
temp_blks_written bigint   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