解決PostgreSQL 執(zhí)行超時(shí)的情況

使用背景

最近在使用PostgreSQL的時(shí)候，在執(zhí)行一些數(shù)據(jù)庫(kù)事務(wù)的時(shí)候，先后出現(xiàn)了statement timetout 和idle-in-transaction timeout的問(wèn)題，導(dǎo)致數(shù)據(jù)庫(kù)操作失敗。

經(jīng)研究查找，PostgreSQL有關(guān)于SQL語(yǔ)句執(zhí)行超時(shí)和事務(wù)執(zhí)行超時(shí)的相關(guān)配置，而默認(rèn)超時(shí)時(shí)間是10000毫秒，即10秒鐘的時(shí)間，這樣會(huì)導(dǎo)致執(zhí)行時(shí)間稍長(zhǎng)的任務(wù)執(zhí)行失敗。可以通過(guò)修改PostgreSQL服務(wù)器配置文件的方式修改默認(rèn)配置。

參數(shù)說(shuō)明

statement_timeout
statement_timeout 在 postgresql 被用來(lái)控制語(yǔ)句執(zhí)行時(shí)長(zhǎng)，單位是ms。
$ vi postgresql.conf 
#statement_timeout = 0         # in milliseconds, 0 is disabled

默認(rèn)是0，表示語(yǔ)句可以一直執(zhí)行下去。

如果設(shè)置為10000，那就意味著語(yǔ)句最多可以執(zhí)行 10000ms = 10s。

建議設(shè)置為0，禁用該參數(shù)。

idle_in_transaction_session_timeout

PostgreSQL 9.6版本開(kāi)始支持自動(dòng)查殺超過(guò)指定時(shí)間的 idle in transaction 空閑事務(wù)連接，用于清理應(yīng)用代碼中忘記關(guān)閉已開(kāi)啟的事務(wù)，或者系統(tǒng)中存在僵死進(jìn)程等。

idle_in_transaction_session_timeout 在 postgresql 被用來(lái)控制事務(wù)執(zhí)行時(shí)長(zhǎng)，單位是ms。

$ vi postgresql.conf 
#idle_in_transaction_session_timeout = 0         # in milliseconds, 0 is disabled

默認(rèn)是0，表示語(yǔ)句可以一直執(zhí)行下去。超時(shí)會(huì)報(bào) FATAL: terminating connection due to idle-in-transaction timeout。

修改方法

查找配置

通過(guò)命令查找到postgresql配置文件的位置，用vi進(jìn)行編輯。

find / -name "postgresql.conf"
vi /var/lib/pgsql/9.6/data/postgresql.conf

修改參數(shù)

進(jìn)入vi編輯界面，可以通過(guò)vi查找命令定位到相關(guān)參數(shù),修改成合適的時(shí)間，保存退出。

：/statement_timeout

重啟配置

通過(guò)以下命令，查找pg_ctl的位置，然后執(zhí)行 pg_ctl reload重新加載配置。

find / -name "pg_ctl"
/usr/pgsql-9.6/bin/pg_ctl reload

PG_CTL用法

啟動(dòng)服務(wù)器

啟動(dòng)服務(wù)器：

$ pg_ctl start

啟動(dòng)服務(wù)器的一個(gè)例子，等到服務(wù)器啟動(dòng)了才退出：

$ pg_ctl -w start

服務(wù)器使用 5433 端口，而且不帶 fsync 運(yùn)行，使用：

$ pg_ctl -o "-F -p 5433" start

停止服務(wù)器

$ pg_ctl stop

使用 -m 選項(xiàng)停止服務(wù)器允許用戶(hù)控制如何關(guān)閉后端。

重啟服務(wù)器

這個(gè)命令幾乎等于先停止服務(wù)器然后再啟動(dòng)它，只不過(guò) pg_ctl 保存并重新使用上一次運(yùn)行服務(wù)器的命令行參數(shù)。重啟服務(wù)器的最簡(jiǎn)單的方法是：

$ pg_ctl restart

重啟服務(wù)器，等待其停止和重啟：

$ pg_ctl -w restart

使用 5433 端口重啟并且重啟后關(guān)閉 fsync ：

$ pg_ctl -o "-F -p 5433" restart

顯示服務(wù)器狀態(tài)

下面是來(lái)自 pg_ctl 的狀態(tài)輸出的例子：

$ pg_ctl statuspg_ctl: server is running (pid: 13718)
Command line was:
/usr/local/pgsql/bin/postgres '-D' '/usr/local/pgsql/data' '-p' '5433' '-B' '128'

這就是在 restart 模式中被調(diào)用的命令行。

補(bǔ)充：PostgreSQL 設(shè)置單條SQL的執(zhí)行超時(shí) - 防雪崩

背景

設(shè)置單條SQL的執(zhí)行超時(shí)，防雪崩。

通常來(lái)說(shuō)可以在SQL發(fā)起前設(shè)置事務(wù)級(jí)超時(shí)參數(shù)，SQL執(zhí)行結(jié)束，重置。（如果SQL異常退出，會(huì)自動(dòng)重置事務(wù)級(jí)參數(shù)）

例子

begin; 
...... 
set local statement_time='100ms'; 
select count(*) from a;  -- 這條SQL的執(zhí)行時(shí)間超過(guò)100MS則主動(dòng)退出，并回滾整個(gè)事務(wù)  
set local statement_timeout to default; 
...... 
end; 

函數(shù)級(jí)超時(shí)例子 - statement_timeout不可用

例如這個(gè)QUERY，我們想讓它100毫秒超時(shí)。

select count(*) as cnt, id from a where id$1 group by id;  

將它寫(xiě)到函數(shù)中，在函數(shù)中設(shè)置超時(shí)

create or replace function f1(int) returns setof record as $$ 
declare 
begin 
 set local statement_timeout='100ms'; 
 return query select count(*) as cnt, id from a where id$1 group by id;  
end; 
$$ language plpgsql strict ; 

調(diào)用SQL改成這樣

select cnt,id from f1(1) as t(cnt int8, id int);  

但是這么做實(shí)際上是沒(méi)有效果的，原因是statement_timeout的設(shè)計(jì)之初是為交互性SQL設(shè)計(jì)的，在postgres.c中。

所以需要plpgsql超時(shí)，需要通過(guò)插件HOOK來(lái)實(shí)現(xiàn)。

https://www.postgresql.org/message-id/flat/200702201200.53535.xzilla%40users.sourceforge.net#200702201200.53535.xzilla@users.sourceforge.net

statement_timeout is measured across an entire interactive command, not  
individual commands within a function; and the timeout that applies to  
an interactive command is determined at its beginning. So the above  
doesn't do what you think. 

參數(shù)級(jí)別

1、實(shí)例級(jí)

修改

postgresql.conf 

2、庫(kù)級(jí)

alter database dbname set parameter=?; 

3、用戶(hù)級(jí)

alter role rolname set parameter=?; 

4、會(huì)話級(jí)

set parameter=?; 

5、事務(wù)級(jí)

begin; 
set local parameter=?; 
.... 
end; 

6、函數(shù)級(jí)

alter function fun_name() set parameter=?; 

其他超時(shí)控制

1、空閑事務(wù)超時(shí)

idle_in_transaction_session_timeout = 2h  

2、鎖等待超時(shí)

lock_timeout = 1s

3、死鎖檢測(cè)超時(shí)間隔

deadlock_timeout = 1s

https://www.postgresql.org/docs/9.4/static/runtime-config-client.html

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教。