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

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL 9.2 Beta: pg_stat_activity view changes a lot  

2012-05-22 14:13:51|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

   
    
            根据 PostgreSQL9.2beta1 版的 release note,监控视图 pg_stat_activity 变化蛮大,包括   
1  Rename pg_stat_activity.procpid to pid, to match other system tables (Magnus Hagander)
    重命名字段 pg_stat_activity.procpid 为 pid。

2  Create a separate pg_stat_activity column to report state information, e.g. idle
    (Scott Mead, Magnus Hagander, Greg Smith)
    新增 state 字段,显示语句状态。
 
3 Rename pg_stat_activity.current_query to query because it is not cleared when the query
    completes (Magnus Hagander)
    重命名字段 pg_stat_activity.current_query 为 query。

   备注:从上面看出,pg_stat_activity  变化挺大,重命名了两个字段,并且新增了 state 字段。
 
 
--参考手册,查询  pg_stat_activity 视图

Table 27-2. pg_stat_activity view

Column Type Description
datid oid OID of the database this backend is connected to
datname name Name of the database this backend is connected to
pid integer Process ID of this backend
usesysid oid OID of the user logged into this backend
usename name Name of the user logged into this backend
application_name text Name of the application that is connected to this backend
client_addr inet IP address of the client connected to this backend. If this field is null, it indicates either that the client is connected via a Unix socket on the server machine or that this is an internal process such as autovacuum.
client_hostname text Host name of the connected client, as reported by a reverse DNS lookup of client_addr. This field will only be non-null for IP connections, and only when log_hostname is enabled.
client_port integer TCP port number that the client is using for communication with this backend, or -1 if a Unix socket is used
backend_start timestamp with time zone Time when this process was started, i.e., when the client connected to the server
xact_start timestamp with time zone Time when this process' current transaction was started, or null if no transaction is active. If the current query is the first of its transaction, this column is equal to the query_start column.
query_start timestamp with time zone Time when the currently active query was started, or if state is not active, when the last query was started
state_change timestamp with time zone Time when the state was last changed
waiting boolean True if this backend is currently waiting on a lock
state text Current overall state of this backend. Possible values are:
  • active: The backend is executing a query.

  • idle: The backend is waiting for a new client command.

  • idle in transaction: The backend is in a transaction, but is not currently executing a query.

  • idle in transaction (aborted): This state is similar to idle in transaction, except one of the statements in the transaction caused an error.

  • fastpath function call: The backend is executing a fast-path function.

  • disabled: This state is reported if track_activities is disabled in this backend.

query text Text of this backend's most recent query. If state is active this field shows the currently executing query. In all other states, it shows the last query that was executed.

 

  备注:  根据手册说明,query 字段含义有变化:当 state 状态为 active 的会话时,显示当前运行的语句,
               当 state 为其它状态时, query 显示最近执行的语句;而之前的版本 current_query 只显示当会运行
               的会话,如果是空闲会话,current_query 值为 <IDLE>。
       
               新增的 state 字段有以下值
        
      active: The backend is executing a query. ( 显示当前活跃会话的SQL )

      idle: The backend is waiting for a new client command. ( 当前会话空闲)
        
      idle in transaction: The backend is in a transaction, but is not currently executing a query.   事务没有提交或回滚时.
        

      idle in transaction (aborted): This state is similar to idle in transaction, except one of

                                                          the statements in the transaction caused an error. (  当事务中的语句出错时.)
        
      fastpath function call: The backend is executing a fast-path function.
                                                 这个还不太理解,以后查到相关资料再来补充。
     

      disabled: This state is reported if track_activities is disabled in this backend.  (  当 track_activities 设置为 off 时 ).

       

一 构造 state 值为idle 场景
--1.1 session A

 [postgres@redhat6 tf]$ psql francs francs
psql (9.2beta1)
Type "help" for help.

francs=>

   


--1.2 session B

 postgres=# select pid, datname,usename,query ,state from pg_stat_activity where pid <> pg_backend_pid();
 pid  | datname | usename | query | state
------+---------+---------+-------+-------
 8151 | francs  | francs  |       | idle
(1 row) 

   
       
       
      
二 构造 pg_stat_activitys.state 值为 active  场景 

 postgres=# select pid, datname,usename,query ,state from pg_stat_activity;
 pid  | datname  | usename  |                              query                              | state 
------+----------+----------+-----------------------------------------------------------------+--------
 8134 | postgres | postgres | select pid, datname,usename,query ,state from pg_stat_activity; | active 
   
 
 备注:state 值为 active 时,表示会话正在执行;  直接查询当前会话时,state 值为 active,
           当然可以构造慢查询,然后重新开启窗口验证,这里就不做验证了,如果一个库中 active 状态的语句比较多
           说明语句需要优化了。
      

三  构造 state 值为 idle in transaction  场景     
--3.1 session A

 [postgres@redhat6 tf]$ psql francs francs
psql (9.2beta1)
Type "help" for help.

francs=> begin;
BEGIN
francs=> select count(*) from test_1;
 count
-------
 10000
(1 row)

   

--3.2 sesion B

 postgres=# select pid, datname,usename,query ,state from pg_stat_activity where pid <> pg_backend_pid();
 pid  | datname | usename |            query             |        state       
------+---------+---------+------------------------------+---------------------
 8165 | francs  | francs  | select count(*) from test_1; | idle in transaction
(1 row)
   
  备注:当事务没有结束时,state 字段显示为 idle in transaction, 同时 query 显示的是最近执行的语句。
            注意这并不是当前执行的语句,而是历史最近执行的语句。


四 构造 state 值为 idle in transaction (aborted) 场景   
--4.1 session A

 [postgres@redhat6 tf]$ psql francs francs
psql (9.2beta1)
Type "help" for help.

francs=> begin;
BEGIN
francs=> select count(*) from test_22;
ERROR:  relation "test_22" does not exist
LINE 1: select count(*) from test_22;

                                ^
--4.2 session B

 postgres=# select pid, datname,usename,query ,state from pg_stat_activity where pid <> pg_backend_pid();
 pid  | datname | usename |             query             |             state            
------+---------+---------+-------------------------------+-------------------------------
 8181 | francs  | francs  | select count(*) from test_22; | idle in transaction (aborted)
(1 row)
   


五 构造 pg_stat_activitys.state 值为 disabled  场景 
--5.1 修改 $PGDATA/postgresql.conf
  
     修改参数配置文件 $PGDATA/postgresql.conf ,设置 track_activities = off,默认为 on。
    
--5.2 修改后 pg_ctl reload -D $PGDATA 重新加载配置文件
[postgres@redhat6 pg_root]$ pg_ctl reload -D $PGDATA
server signaled

--5.3 session A

 [postgres@redhat6 pg_root]$ psql francs francs
psql (9.2beta1)
Type "help" for help.

francs=>

   


--5.4 session B

 postgres=# show track_activities;
 track_activities
------------------
 off
(1 row)

postgres=# select pid, datname,usename,query ,state from pg_stat_activity where pid <> pg_backend_pid();
 pid  | datname | usename | query |  state  
------+---------+---------+-------+----------
 8201 | francs  | francs  |       | disabled
(1 row)

   

  备注:当参数 track_activities 设置为 off 时,pg_stat_activity.state 值始终为 disabled, 表示不记录
             session 信息。

六 构造有 session 被阻塞的情况
--session A  删除一条记录

 [postgres@redhat6 pg_root]$ psql francs francs
psql (9.2beta1)
Type "help" for help.

francs=> \d test_1
           Table "francs.test_1"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               |
 name   | character varying(32) |
Indexes:
    "idx_test_1_id" UNIQUE, btree (id)

francs=> begin;
BEGIN
francs=> delete from test_1 where id=1;
DELETE 1

          

  备注: session 准备删除表的一条记录,注意此时事务未提交。

--session B  创建索引

 [postgres@redhat6 pg_root]$ psql francs francs
psql (9.2beta1)
Type "help" for help.

francs=> create index idx_test_1_name on test_1 using btree (name);

   

 备注: session B 准备在表 test_1 上创建一个索引,注意此时会话被 BLOCK,光标下不去。

 

--session C 监控会话

 postgres=# select pid, datname,usename,query ,state ,waiting from pg_stat_activity where pid <> pg_backend_pid();
 pid  | datname | usename |                           query                            |        state        | waiting
------+---------+---------+------------------------------------------------------------+---------------------+---------
 8641 | francs  | francs  | delete from test_1 where id=1;                             | idle in transaction
| f
 8713 | francs  | francs  | create index idx_test_1_name on test_1 using btree (name); | active             
| t
(2 rows)

   

      备注:session B 的进程号为 8713 ,虽然它的 state 状态为 active ,但是它处理于等侍状态, waiting 为 t;
                 也就是说 waiting 字段和 state 字段是独立的,如果 state 字段为 active , 那么会话的 wating 字段
                 可能为 true 也可能为 false。

七 附
track_activities (boolean)


        Enables the collection of information on the currently executing command of each session, along
with the time when that command began execution. This parameter is on by default. Note that even when
enabled, this information is not visible to all users, only to superusers and the user owning the session
being reported on, so it should not represent a security risk. Only superusers can change this setting.


八 参考
http://www.postgresql.org/docs/9.2/static/release-9-2.html
http://www.postgresql.org/docs/9.2/static/monitoring-stats.html#MONITORING-STATS-VIEWS-TABLE
       

  评论这张
 
阅读(29129)| 评论(2)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

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

页脚

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