【PostgreSQL]PostgreSQL数据库层限流脚本

?

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

with tmp3 as (select count(*) as cnt from pg_stat_activity WHERE pid <> pg_backend_pid() and datname='mypg1' and usename='postgres' and state='idle' and query like '%select now%') select case  when cnt <= 400 then  (with tmp1 as ( select pg_terminate_backend(pid) from  (select pid from pg_stat_activity WHERE 1=2 ) as foo1)  select count(*) from tmp1 )  when cnt > 400 then  (with tmp2 as ( select pg_terminate_backend(pid) from  (select pid from pg_stat_activity WHERE pid <> pg_backend_pid() and datname='mypg1' and usename='postgres'                 and state='active' and query like '%select now%' order by backend_start                 limit 10) as foo2)  select count(*) from tmp2 ) end as kill_if_too_many_process from tmp3 \watch 1

　　

作用：程序执行的语句为类似select now语句，并且该进程是active的，当这样的进程超过400个时，按照进程发起的先后顺序，先杀掉早期的进程。该语句每秒检查一次，每次发现有超过400个时，kill掉最早的10个。

注：语句内容，进程数上限，每次杀的进程数，检查时间，都可以根据自己的情况酌情修改。

原文地址：https://oracleblog.org/useful-script/script-to-limit-process-for-postgresql/