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

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL9.2Beta: Add a deadlock counter to the pg_stat_database system view  

2012-05-29 21:16:11|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |


         pg_stat_database 系统视图记录每个数据库的运行信息,包括 blks_read,blks_hit,tup_fetched,

tup_inserted 等信息,在 9.2 版本增加了 deadlocks 信息,用于统计 数据库的死锁次数。


一 环境准备
--1.1 一张测试表

 francs=> select * from test_1 limit 10;
 id | name
----+------
  2 | a
  3 | a
  4 | a
  5 | a
  6 | a
  7 | a
  8 | a
  9 | a
 10 | a
 11 | a
(10 rows)

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)
    "idx_test_1_name" btree (name)

   
 
--1.2 实验前查询 pg_stat_database 视图   
francs=# select * from pg_stat_database where datname='francs';
-[ RECORD 1 ]--+-----------------------------
datid          | 16386
datname        | francs
numbackends    | 1
xact_commit    | 4871
xact_rollback  | 118
blks_read      | 280156
blks_hit       | 9684659
tup_returned   | 30715579
tup_fetched    | 59701
tup_inserted   | 27185774
tup_updated    | 168
tup_deleted    | 12800
conflicts      | 0
temp_files     | 2
temp_bytes     | 72048640
deadlocks      | 0
blk_read_time  | 0
blk_write_time | 0
stats_reset    | 2012-05-17 14:42:46.51038+08

    备注:  此时的 pg_stat_database.deadlocks 值为 0。
   
   
二 死锁实验
--2.1 session A

 francs=> begin;
BEGIN
francs=> select * from test_1 where id=2;
 id | name
----+------
  2 | a
(1 row)

francs=> update test_1 set name='aaa' where id=2;
UPDATE 1

   


--2.2 session B

 francs=> begin;
BEGIN
francs=> select * from test_1 where id=2;
 id | name
----+------
  2 | a
(1 row)

francs=> select * from test_1 where id=3;
 id | name
----+------
  3 | a
(1 row)

francs=> update test_1 set name='aaa3' where id=3;
UPDATE 1

francs=> update test_1 set name='aaaa' where id=2;
< hang >

   


--2.3 再次回到 session A

 francs=> update test_1 set name='aaa3333' where id=3;
ERROR:  deadlock detected
DETAIL:  Process 25454 waits for ShareLock on transaction 1995; blocked by process 25508.
Process 25508 waits for ShareLock on transaction 1994; blocked by process 25454.
HINT:  See server log for query details.
   

 备注:死锁发生。
 
 
--2.4 再次查询 pg_stat_database 视图
francs=# select * from pg_stat_database where datname='francs';
-[ RECORD 1 ]--+-----------------------------
datid          | 16386
datname        | francs
numbackends    | 4
xact_commit    | 4907
xact_rollback  | 119
blks_read      | 280159
blks_hit       | 9687460
tup_returned   | 30725377
tup_fetched    | 61093
tup_inserted   | 27185774
tup_updated    | 168
tup_deleted    | 12800
conflicts      | 0
temp_files     | 2
temp_bytes     | 72048640
deadlocks      | 1
blk_read_time  | 0
blk_write_time | 0
stats_reset    | 2012-05-17 14:42:46.51038+08

 备注:发现 deadlocks 值为1。
 
 
三 附:pg_stat_database 说明

Table 27-4. pg_stat_database view

Column Type Description
datid oid OID of a database
datname name Name of this database
numbackends integer Number of backends currently connected to this database. This is the only column in this view that returns a value reflecting current state; all other columns return the accumulated values since the last reset.
xact_commit bigint Number of transactions in this database that have been committed
xact_rollback bigint Number of transactions in this database that have been rolled back
blks_read bigint Number of disk blocks read in this database
blks_hit bigint Number of times disk blocks were found already in the buffer cache, so that a read was not necessary (this only includes hits in the PostgreSQL buffer cache, not the operating system's filesystem cache)
tup_returned bigint Number of rows returned by queries in this database
tup_fetched bigint Number of rows fetched by queries in this database
tup_inserted bigint Number of rows inserted by queries in this database
tup_updated bigint Number of rows updated by queries in this database
tup_deleted bigint Number of rows deleted by queries in this database
conflicts bigint Number of queries canceled due to conflicts with recovery in this database. (Conflicts occur only on standby servers; see pg_stat_database_conflicts for details.)
temp_files bigint Number of temporary files created by queries in this database. All temporary files are counted, regardless of why the temporary file was created (e.g., sorting or hashing), and regardless of the log_temp_files setting.
temp_bytes bigint Total amount of data written to temporary files by queries in this database. All temporary files are counted, regardless of why the temporary file was created, and regardless of the log_temp_files setting.
deadlocks bigint Number of deadlocks detected in this database
blk_read_time double precision Time spent reading data file blocks by backends in this database, in milliseconds
blk_write_time double precision Time spent writing data file blocks by backends in this database, in milliseconds
stats_reset timestamp with time zone Time at which these statistics were last reset

The pg_stat_database view will contain one row for each database in the cluster, showing database-wide statistics.


 
四 参考
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
http://www.depesz.com/2012/02/09/waiting-for-9-2-deadlock-counter/
  评论这张
 
阅读(26083)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

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

页脚

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