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

使用背景

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

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

參數(shù)說明

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

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

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

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

idle_in_transaction_session_timeout

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

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

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

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

修改方法

查找配置

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

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

修改參數(shù)

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

：/statement_timeout

重啟配置

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

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

PG_CTL用法

啟動服務(wù)器

啟動服務(wù)器：

$ pg_ctl start

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

$ pg_ctl -w start

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

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

停止服務(wù)器

$ pg_ctl stop

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

重啟服務(wù)器

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

$ pg_ctl restart

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

$ pg_ctl -w restart

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

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

顯示服務(wù)器狀態(tà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)用的命令行。

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

背景

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

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

例子

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

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

例如這個QUERY，我們想讓它100毫秒超時。

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

將它寫到函數(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);  

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

所以需要plpgsql超時，需要通過插件HOOK來實現(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ù)級別

1、實例級

修改

postgresql.conf 

2、庫級

alter database dbname set parameter=?; 

3、用戶級

alter role rolname set parameter=?; 

4、會話級

set parameter=?; 

5、事務(wù)級

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

6、函數(shù)級

alter function fun_name() set parameter=?; 

其他超時控制

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

idle_in_transaction_session_timeout = 2h  

2、鎖等待超時

lock_timeout = 1s

3、死鎖檢測超時間隔

deadlock_timeout = 1s

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

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