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

关于 statement_timeout

首先看一个参数，手册上的解释

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 设置前信息

1
2
3
4
5
6
7
8
9

[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 参数 并验证

1
2

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 库验证

1
2
3
4
5
6
7
8

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 库验证

1
2
3
4
5
6
7
8

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 测试

1
2
3
4
5
6
7
8
9
10
11
12
13

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

1
2
3
4
5
6

[postgres@redhat6 tf]$ psql francs francs  
psql (9.2beta1)  
Type "help" for help.
francs=>  
francs=> begin;  
BEGIN

1.6.2查看系统上 francs 进程

1
2
3

[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 transactionpostgres 18543 18136 0 21:06 pts/3 00:00:00 grep francs

1.6.3 session A 继续往下执行

1
2
3
4
5
6
7
8

[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 进程

1
2
3

[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。

总结

这种方法在生产上用得很少，如果要用时得谨慎。

参考

• http://www.postgresql.org/docs/9.2/static/sql-alterrole.html