PostgreSQL之查找最慢的SQL 的方法

绝地灬酷狼 2022-09-26 15:52 37阅读 0赞

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

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

<table style="word-wrap:break-word; margin:2ex 0px 2ex 2ex; border-spacing:0px; background-color:rgb(224,236,239); border:2px solid rgb(167,198,223); color:rgb(0,0,0); font-family:verdana,sans-serif; font-size:15.2px"> 
 <tbody style="word-wrap:break-word"> 
  <tr style="word-wrap:break-word"> 
   <th style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223)"> Name</th> 
   <th style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223)"> Type</th> 
   <th style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223)"> References</th> 
   <th style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223)"> Description</th> 
  </tr> 
 </tbody> 
 <tbody style="word-wrap:break-word"> 
  <tr style="word-wrap:break-word"> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> userid</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> oid</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> <a href="http://www.postgresql.org/docs/9.1/static/catalog-pg-authid.html" style="word-wrap:break-word; text-decoration:none; color:rgb(0,78,102)" rel="nofollow">pg_authid</a>.oid</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> OID of user who executed the statement</td> 
  </tr> 
  <tr style="word-wrap:break-word"> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> dbid</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> oid</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> <a href="http://www.postgresql.org/docs/9.1/static/catalog-pg-database.html" style="word-wrap:break-word; text-decoration:none; color:rgb(0,78,102)" rel="nofollow">pg_database</a>.oid</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> OID of database in which the statement was executed</td> 
  </tr> 
  <tr style="word-wrap:break-word"> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> query</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> text</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> &nbsp;</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> Text of the statement (up to&nbsp;<a href="http://www.postgresql.org/docs/9.1/static/runtime-config-statistics.html#GUC-TRACK-ACTIVITY-QUERY-SIZE" style="word-wrap:break-word; text-decoration:none; color:rgb(0,78,102)" rel="nofollow">track_activity_query_size</a>&nbsp;bytes)</td> 
  </tr> 
  <tr style="word-wrap:break-word"> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> calls</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> bigint</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> &nbsp;</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> Number of times executed</td> 
  </tr> 
  <tr style="word-wrap:break-word"> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> total_time</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> double precision</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> &nbsp;</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> Total time spent in the statement, in seconds</td> 
  </tr> 
  <tr style="word-wrap:break-word"> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> rows</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> bigint</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> &nbsp;</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> Total number of rows retrieved or affected by the statement</td> 
  </tr> 
  <tr style="word-wrap:break-word"> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(239,239,239)"> shared_blks_hit</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(239,239,239)"> bigint</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(239,239,239)"> &nbsp;</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(239,239,239)"> Total number of shared blocks hits by the statement</td> 
  </tr> 
  <tr style="word-wrap:break-word"> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> shared_blks_read</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> bigint</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> &nbsp;</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> Total number of shared blocks reads by the statement</td> 
  </tr> 
  <tr style="word-wrap:break-word"> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> shared_blks_written</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> bigint</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> &nbsp;</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> Total number of shared blocks writes by the statement</td> 
  </tr> 
  <tr style="word-wrap:break-word"> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> local_blks_hit</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> bigint</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> &nbsp;</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> Total number of local blocks hits by the statement</td> 
  </tr> 
  <tr style="word-wrap:break-word"> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> local_blks_read</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> bigint</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> &nbsp;</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> Total number of local blocks reads by the statement</td> 
  </tr> 
  <tr style="word-wrap:break-word"> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> local_blks_written</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> bigint</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> &nbsp;</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> Total number of local blocks writes by the statement</td> 
  </tr> 
  <tr style="word-wrap:break-word"> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> temp_blks_read</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> bigint</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> &nbsp;</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> Total number of temp blocks reads by the statement</td> 
  </tr> 
  <tr style="word-wrap:break-word"> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> temp_blks_written</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> bigint</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> &nbsp;</td> 
   <td style="word-wrap:break-word; margin:0px; padding:0.5ex; border:1px solid rgb(167,198,223); background-color:rgb(255,255,255)"> Total number of temp blocks writes by the statemen</td> 
  </tr> 
 </tbody> 
</table>

上图表来自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方能生效

1.  \#\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-  
    
2.  \# PG\_STAT\_STATEMENTS OPTIONS  
    
3.  \#\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-  
    
4.  shared\_preload\_libraries = 'pg\_stat\_statements'  
    
5.  custom\_variable\_classes = 'pg\_stat\_statements'  
    
6.  pg\_stat\_statements.max = 1000  
    
7.  pg\_stat\_statements.track = all

2 创建pg\_stat\_statements扩展

1.  CREATE EXTENSION pg\_stat\_statements;

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

1.  SELECT  query, calls, total\_time, (total\_time/calls) as average ,rows, 
2.          100.0 \* shared\_blks\_hit /nullif(shared\_blks\_hit \+ shared\_blks\_read, 0) AS hit\_percent 
3.  FROM    pg\_stat\_statements 
4.  ORDER   BY average DESC LIMIT 10;

我在我本地的DB，查找最慢的2条SQL，输出如下：  
    ![24774106_1372468965KEDZ.png][]  
   在我另一台机器上，用pgadmin查看：  
    ![24774106_1372469196qArW.png][]  
    统计结果一直都在，重启也不会清零，那么统计结果如何清零重新统计呢？执行下面SQL即可：

1.  select pg\_stat\_statements\_reset() ;

找到最耗时的SQL，我们就能针对这些耗时的SQL，查看是否有优化的余地。  
  
参考文献：  
1  [More on Postgres Performance  
][More On PostgreSQL perform] 2   PostgreSQL manual

[PostgreSQL]: http://blog.csdn.net/shanzhizi
[More On PostgreSQL perform]: http://www.craigkerstiens.com/2013/01/10/more-on-postgres-performance/
[24774106_1372468965KEDZ.png]: http://blog.chinaunix.net/attachment/201306/29/24774106_1372468965KEDZ.png
[24774106_1372469196qArW.png]: http://blog.chinaunix.net/attachment/201306/29/24774106_1372469196qArW.png