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

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL: Autoabort user's statement that takes over the specified time  

2012-05-24 21:24:59|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 
       有的时候生产库上的一个长时间 SQL 很容易消耗整个服务器的资源,那么数据库层面是否有方法
应对这种问题呢?今天听德哥说 PostgreSQL 可以设置用户级别的 SQL 超时参数,如果某个用户的
发出的 SQL 超过设定时间,则会被 cancel ,后来查了下手册,并做了下测试,果然可以实现。

 

--首先看一个参数,手册上的解释
statement_timeout (integer)
          Abort any statement that takes over the specified number of milliseconds, starting from the
time the command arrives at the server from the client. If log_min_error_statement is set to ERROR
or lower, the statement that timed out will also be logged. A value of zero (the default) turns this off.

Setting statement_timeout in postgresql.conf is not recommended because it affects all sessions.

   备注:这个参数是全局的,如果设置,则会对整个 PostgreSQL Cluster 生效,如果设置这个参数,那么
             超过设定时间的 SQL 都会被 cancel ,显然是不可取的,手册上也说并不推荐,通常将这个参数
             设为0,即关闭这个参数。 幸运的是 PostgreSQL 可以在 Role 级别设置配置参数,即可以在 Role
            级别设置语句的超时参数


一 用户级别 statement_timeout 设置演示
--1.1 设置前信息

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

francs=> show statement_timeout;
 statement_timeout
-------------------
 0
(1 row)

   


--1.2 修改 francs 的 statement_timeout 参数 并验证

 postgres=# alter role francs set statement_timeout=5000;
ALTER ROLE
   

  备注:这里设置了用户 francs 的 statement_timeout 值为 5 秒,如果加上   IN DATABASE 选项,则只对
           指定用户登陆指定数据库有效,例如" alter role francs in database francs set statement_timeout=5000 ;"
           这个命令仅当 francs 用户连接 francs 库时生效。


--1.3 以 francs 用户登陆 postgres 库验证

 postgres=# \c postgres francs;
You are now connected to database "postgres" as user "francs".

postgres=> show statement_timeout;
 statement_timeout
-------------------
 5s
(1 row)

   

--1.4 以 francs 用户登陆 francs  库验证

 postgres=> \c francs francs;
You are now connected to database "francs" as user "francs".

francs=> show statement_timeout;
 statement_timeout
-------------------
 5s
(1 row)

   

 备注:可见用户级的 statement_timeout 已成功设置。

 
--1.5  sql 测试

 francs=> \c francs francs;
You are now connected to database "francs" as user "francs".
francs=> select pg_sleep(4);
 pg_sleep
----------
 
(1 row)
Time: 4005.496 ms

francs=> select pg_sleep(5);
ERROR:  canceling statement due to statement timeout
Time: 5002.358 ms

   
 
 备注:可见当语句执行时间达到设定值 5 秒时,则被 cancel 了;注意这里 cancel 的只是 SQL 语句本身,
            session 并不会被 kill 。


--1.6 放在事务中测试
--1.6.1 session A

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

francs=>
francs=> begin;
BEGIN

   


--1.6.2查看系统上 francs 进程

 [postgres@redhat6 pg_root]$ ps -ef | grep francs
postgres 18536 18072  0 21:06 pts/1    00:00:00 psql francs francs
postgres 18537 12033  0 21:06 ?        00:00:00 postgres: francs francs [local] idle in transaction            
postgres 18543 18136  0 21:06 pts/3    00:00:00 grep francs
   
--1.6.3 session A 继续往下执行

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

francs=>
francs=> begin;
BEGIN
francs=> select pg_sleep(5);
ERROR:  canceling statement due to statement timeout

   

--1.6.4 再次查看系统上 francs 进程

 [postgres@redhat6 pg_root]$ ps -ef | grep francs
postgres 18536 18072  0 21:06 pts/1    00:00:00 psql francs francs
postgres 18537 12033  0 21:06 ?        00:00:00 postgres: francs francs [local] idle in transaction (aborted)  
postgres 18555 18136  0 21:07 pts/3    00:00:00 grep francs
   

  备注:说明语句被 cancel ,事务被 abort。
 
 
--1.6 总结
        这种方法在生产上用得很少,如果要用时得谨慎。
    
--1.7 参考
http://www.postgresql.org/docs/9.2/static/sql-alterrole.html
    

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

历史上的今天

在LOFTER的更多文章

评论

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

页脚

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