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

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL: "oldest xmin is far in the past" 处理一例  

2012-01-18 17:47:35|  分类: PG案例分析 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 

         快过年了,今天对数据库进行健康检查,发现海外一数据库中出现大量以下日志,而且已经报了十几天,
  CSV 日志如下:

 

--1 csv log
2012-01-18 15:22:39.098 CST,,,8871,,4f16733e.22a7,1,,2012-01-18 15:22:38 CST,81/42114625,0,WARNING,01000,"oldest xmin is far in the past",,"Close open transactions soon to avoid wraparound problems.",,,,,,
2012-01-18 15:22:39.109 CST,,,8871,,4f16733e.22a7,2,,2012-01-18 15:22:38 CST,81/42114627,0,WARNING,01000,"oldest xmin is far in the past",,"Close open transactions soon to avoid wraparound problems.",,,,,,
2012-01-18 15:22:39.119 CST,,,8871,,4f16733e.22a7,3,,2012-01-18 15:22:38 CST,81/42114629,0,WARNING,01000,"oldest xmin is far in the past",,"Close open transactions soon to avoid wraparound problems.",,,,,,
2012-01-18 15:22:39.119 CST,,,8871,,4f16733e.22a7,4,,2012-01-18 15:22:38 CST,81/42114631,0,WARNING,01000,"oldest xmin is far in the past",,"Close open transactions soon to avoid wraparound problems.",,,,,,

     备注:根据提示信息, 猜测有尚未提交的事务,导致 autovacuum 进程不能 vacuum。


--2 查询数据库对像年龄
postgres=# select age(relfrozenxid) from pg_class where relkind='r' order by 1 desc limit 10;
    age   
-----------
 341105277
 341105277
 341105277
 341105277
 341105277
 341105277
 341105277
 315987817
 315987814
 315987811
(10 rows)

    备注:数据库对像年龄已经达到 3 亿。


--3 查询数据库进程,发现异常进程
postgres=# select procpid, datname,usename,query_start  from pg_stat_activity where current_query !='<IDLE>'
order by query_start;
 procpid |  datname   | usename  |          query_start         
---------+------------+----------+-------------------------------
   27694 | db_oversea | oversea  | 2011-09-12 07:35:56.615883+08
    5019 | db_oversea | oversea  | 2012-01-18 15:40:47.063949+08
    5927 | db_oversea | oversea  | 2012-01-18 15:40:47.290211+08
    1552 | db_oversea | oversea  | 2012-01-18 15:40:47.32765+08
    8227 | db_oversea | oversea  | 2012-01-18 15:40:47.381145+08
    7758 | db_oversea | oversea  | 2012-01-18 15:40:47.391125+08
    7759 | db_oversea | oversea  | 2012-01-18 15:40:47.45002+08
    8094 | db_oversea | oversea  | 2012-01-18 15:40:47.812721+08
    4804 | db_oversea | oversea  | 2012-01-18 15:40:47.821056+08
    6689 | db_oversea | oversea  | 2012-01-18 15:40:47.90369+08
    8506 | db_oversea | oversea  | 2012-01-18 15:40:47.915079+08
    9039 | postgres   | postgres | 2012-01-18 15:43:47.834318+08
(12 rows)

  
postgres=# select procpid,datname,usename,current_query,query_start from pg_stat_activity where procpid=27694;
 procpid |  datname   | usename |                   current_query                    |          query_start         
---------+------------+---------+----------------------------------------------------+-------------------------------
   27694 | skytf | skytf | SELECT                                             | 2011-09-12 07:35:56.615883+08
                                : mrp.*,res.name as displayname                       
                                : FROM                                                
                                : skytf.tbl_app as mrp,tbl_app_resource as res  
                                : where mrp.appid=res.appid and                       
                                :  mrp.status='1' and res.status='1'                                                                 
                                : order by id desc
                               
    备注:上面发现进程 27694 是去年的,至今仍未提交,经和应用人员确认后,可以 kill。                            


--4 kill 进程 27694
postgres=# select pg_terminate_backend(27694);
 pg_terminate_backend
----------------------
 t
(1 row)


--5 接着发现 autovacuum 进程在跑
postgres@skytf-db-> ps -ef | grep auto
root      3523     1  0  2010 ?        00:00:01 automount
postgres  4002  3949  0  2010 ?        00:10:54 postgres: autovacuum launcher process                 
postgres 10001  3949  0 16:16 ?        00:00:00 postgres: autovacuum worker process   skytf      
postgres 10002  3949  0 16:16 ?        00:00:00 postgres: autovacuum worker process   skytf      
postgres 10003  3949  0 16:16 ?        00:00:00 postgres: autovacuum worker process   skytf      
postgres 10004  3949  0 16:16 ?        00:00:00 postgres: autovacuum worker process   skytf      
postgres 10005  3949  0 16:16 ?        00:00:00 postgres: autovacuum worker process   skytf      
postgres 10006  3949  0 16:16 ?        00:00:00 postgres: autovacuum worker process   skytf      
postgres 10007  3949  0 16:16 ?        00:00:00 postgres: autovacuum worker process   skytf
                          
                             
--6 进程 kill 后再次查看年龄
postgres=# select age(relfrozenxid) from pg_class where relkind='r' order by 1 desc limit 10;
   age   
----------
 50003395
 50003393
 50003382
 50003381
 50003381
 50003381
 50003381
 50003374
 50003374
 50003374
(10 rows)                 

 备注:进程 kill 后再次查看年龄,数据库对像年龄已经循环,当前最大值为 50003395,并且日志也不再报错。

 
--7 参考
http://www.postgresql.org/docs/9.0/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

  评论这张
 
阅读(26283)| 评论(3)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

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

页脚

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