注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

使用 pg_stat_statements 记录运行中的 SQL 信息  

2011-08-21 18:19:27|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

  
       pg_stat_statements 属于一个 Extension 模块,用来记录数据库所有的SQL语句的运行信息,
这个模块需要设置参数 shared_preload_libraries 的值为 "pg_stat_statements" , 并且需要
重启 pg 服务。

       pg_stat_statements 模块用于记录 SQL 的运行信息,这个和 Oracle 的视图 v$sql 类似,
v$sql 里详细记录了数据库 SQL的运行状态,包括SQL的执行时间,执行次数,逻辑读,解析等
信息, 下面介绍下 pg_stat_statements 模块的下载和使用。
  

--1 The pg_stat_statements View

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 statement
  
    从上面看出, pg_stat_statements 视图提供了语句所涉及的行数,执行时间 shared_buffer 命中的
数据块等信息。

 

--2  pg_stat_statements 模块的加载
2.1 设置 postgresql.conf 模块并重启 PG 服务
shared_preload_libraries = 'pg_stat_statements'
custom_variable_classes = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

  备注:参数 shared_preload_libraries 修改后需要重启PG服务.


2.2 加载 pg_stat_statements 模块
skytf=# create extension pg_stat_statements;
CREATE EXTENSION
 
  备注:9.1 版本以后,需要使用 "CREATE EXTENSION" 加载外部模块。


--3 使用 pgbench 执行SQL
pgbench -c 10 -T 30 -n -M prepared -d skytf -U skytf -f script_1.sql    

    这里用的是默认的 pgbench 脚本,关于 pgbench 的使用,可以参考之前写的一篇 blog
http://francs3.blog.163.com/blog/static/405767272011720103758178/


--4 查询执行总时间排前五位的 SQL 语句信息
skytf=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
               nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
          FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
                                query                                 | calls |    total_time    | rows |     hit_percent     
----------------------------------------------------------------------+-------+------------------+------+----------------------
 UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2; |  3119 |       147.078177 | 3119 |  99.9780330821782396
 UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2;  |  3119 | 90.9786530000002 | 3119 | 100.0000000000000000
 UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2; |  3119 |          7.25809 | 3119 |  97.8061756156419679
 alter table pgbench_accounts add primary key (aid)                   |     1 |         7.232948 |    0 |  13.3546405966033716
 vacuum analyze pgbench_accounts                                      |     1 |           7.0277 |    0 |  40.3064903846153846
(5 rows)


--5 pg_stat_statements 函数
pg_stat_statements_reset()
   pg_stat_statements_reset discards all statistics gathered so far by pg_stat_statements. By default, this function can only be executed by superusers.
   备注:上面的解释很明白, pg_stat_statements_reset()函数将会丢弃所有的语句统计信息。


--6 pg_stat_statements_reset 函数测试
skytf=# select pg_stat_statements_reset();
 pg_stat_statements_reset
--------------------------
 
(1 row)

skytf=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
skytf-#                nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
skytf-#           FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
               query                | calls | total_time | rows | hit_percent
------------------------------------+-------+------------+------+-------------
 select pg_stat_statements_reset(); |     1 |   0.146981 |    1 |           
(1 row)

      备注:在运行了函数 pg_stat_statements_reset() 之后,视图 pg_stat_statements 的信息
            被清空了。

--7 总结
   
    1  pg_stat_statements 用于分析语句的执行状态,在比较繁忙的生产库上应用对性能分析会有很大帮助;
    2  pg_stat_statements 模块加载会消耗部分内存,可以通过  pg_stat_statements.max * track_activity_query_size
        来计算。这个值是比较小的, 假如 pg_stat_statements.max 值为 10000, 也就消耗了 10 M内存。
    3  加载 pg_stat_statements  模块需要重启 PG服务,这点需要注意。


附一:  pg_stat_statements 配置参数
pg_stat_statements.max (integer)
     pg_stat_statements.max is the maximum number of statements tracked by the module (i.e., the maximum number of rows in the pg_stat_statements view). If more distinct statements than that are observed, information about the least-executed statements is discarded. The default value is 1000. This parameter can only be set at server start.

pg_stat_statements.track (enum)
     pg_stat_statements.track controls which statements are counted by the module. Specify top to track top-level statements (those issued directly by clients), all to also track nested statements (such as statements invoked within functions), or none to disable. The default value is top. Only superusers can change this setting.

pg_stat_statements.track_utility (boolean)
     pg_stat_statements.track_utility controls whether utility commands are tracked by the module. Utility commands are all those other than SELECT, INSERT, UPDATE and DELETE. The default value is on. Only superusers can change this setting.

pg_stat_statements.save (boolean)
     pg_stat_statements.save specifies whether to save statement statistics across server shutdowns. If it is off then statistics are not saved at shutdown nor reloaded at server start. The default value is on. This parameter can only be set in the postgresql.conf file or on the server command line.

  评论这张
 
阅读(27496)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016