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

PostgreSQL 中文网

 
 
 

日志

 
 

Unable to cancel query by using "pg_cancel_backend"  

2011-07-25 22:37:45|  分类: PG案例分析 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

   

        今天在海外 PostgreSQL 库上杀进程, 杀了很久都没成功;数据库为8.3版本, 只能使用
pg_cancel_backend 来杀, 以下为详细过程:


--1 查看当前进程
postgres=# select procpid, datname,current_query ,waiting from pg_stat_activity where current_query !='<IDLE>';
 procpid | datname  |                                            current_query                                             | waiting
---------+----------+------------------------------------------------------------------------------------------------------+---------
   24110 | postgres | select procpid, datname,current_query ,waiting from pg_stat_activity where current_query !='<IDLE>'; | f
    8048 | skytf      | select * From tbl_conn_rec limit 1;                                                                  | f
   10787 | skytf      | select ip from tbl_conn_rec limit 1;                                                                 | f
    1131 | skytf      | select max(createtime) from tbl_conn_rec;                                                            | f
(4 rows)

     备注: 表 tbl_conn_rec 比较大,有20G左右,现在需要将进程,1131, 8048,10787, 都杀掉。

--2 尝试杀会话
postgres=# select pg_cancel_backend(1131);
 pg_cancel_backend
-------------------
 t
(1 row)

postgres=# select pg_cancel_backend(1131);
 pg_cancel_backend
-------------------
 t
(1 row)

   备注:由于8.3 版本没有 pg_terminate_backend, 所以只能使用 pg_cancel_backend 杀查询会话。


--3 再次查看进程,进程未消失
postgres=# select procpid, datname,current_query ,waiting from pg_stat_activity where current_query !='<IDLE>';
 procpid | datname  |                                            current_query                                             | waiting
---------+----------+------------------------------------------------------------------------------------------------------+---------
   24110 | postgres | select procpid, datname,current_query ,waiting from pg_stat_activity where current_query !='<IDLE>'; | f
    8048 | skytf      | select * From tbl_conn_rec limit 1;                                                                  | f
   10787 | skytf      | select ip from tbl_conn_rec limit 1;                                                                 | f
    1131 | skytf      | select max(createtime) from tbl_conn_rec;                                                            | f
(4 rows)
 
     备注:进程还在,说明杀进程失败,虽然 pg_cancel_backend 返回为真,但实际上进程没有杀成功。
      
      
--4 网上查了资料,说是 BUG
The following bug has been logged online:

Bug reference:      5459
Logged by:          Mason Hale
Email address:      [hidden email]
PostgreSQL version: 8.3.8
Operating system:   Redhat EL 5.1-64 bit
Description:        Unable to cancel query while in send()
Details:

ISSUE: unable to cancel queries using pg_cancel_backend(), that are in
send() function call, waiting on client receipt of data.

EXPECTED RESULT: expect to be able to cancel most/all queries using
pg_cancel_backend() as superuser, perhaps with some wait time, but not an
hour or more.

= SYMPTOM =

A SELECT query was running over 18 hours on our PostgreSQL 8.3.8 server.
Verified that it was not waiting on any locks via pg_stat_activity.
Attempted to cancel the query using pg_cancel_backend(), which returned 't'.
However more than an hour later the process was still active, using about 6%
of CPU and 5% of RAM.

Terminated the client process that was running the query (from another
server) did not cause the query process on the pgsql server to stop. In this
case the client was connecting via a ssh tunnel through an intermediate
'gateway' server.

Connection path was:

   CLIENT -->  SSH GATEWAY --> DB SERVER

  备注:上面描述的和今天的问题一样,只是操作系统版本为 " Red Hat Enterprise Linux Server release 4.2",
            今天运气不错,第一次遇到了 PostgreSQL 的 bug。大概过了几小时后,前面那三个查询会话自己运行完了。

--5 后期计划
    后期准备将这个库升为9.0。       

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

历史上的今天

在LOFTER的更多文章

评论

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

页脚

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