解決PostgreSQL 執行超時的情況

Posted on by

使用背景

最近在使用PostgreSQL的時候，在執行一些數據庫事務的時候，先後出現瞭statement timetout 和idle-in-transaction timeout的問題，導致數據庫操作失敗。

經研究查找，PostgreSQL有關於SQL語句執行超時和事務執行超時的相關配置，而默認超時時間是10000毫秒，即10秒鐘的時間，這樣會導致執行時間稍長的任務執行失敗。可以通過修改PostgreSQL服務器配置文件的方式修改默認配置。

參數說明

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

默認是0，表示語句可以一直執行下去。

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

建議設置為0，禁用該參數。

idle_in_transaction_session_timeout

PostgreSQL 9.6版本開始支持自動查殺超過指定時間的 idle in transaction 空閑事務連接，用於清理應用代碼中忘記關閉已開啟的事務，或者系統中存在僵死進程等。

idle_in_transaction_session_timeout 在 postgresql 被用來控制事務執行時長，單位是ms。

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

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

修改方法

查找配置

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

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

修改參數

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

：/statement_timeout

重啟配置

通過以下命令，查找pg_ctl的位置，然後執行 pg_ctl reload重新加載配置。

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

PG_CTL用法

啟動服務器

啟動服務器：

$ pg_ctl start

啟動服務器的一個例子，等到服務器啟動瞭才退出：

$ pg_ctl -w start

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

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

停止服務器

$ pg_ctl stop

使用 -m 選項停止服務器允許用戶控制如何關閉後端。

重啟服務器

這個命令幾乎等於先停止服務器然後再啟動它，隻不過 pg_ctl 保存並重新使用上一次運行服務器的命令行參數。重啟服務器的最簡單的方法是：

$ pg_ctl restart

重啟服務器，等待其停止和重啟：

$ pg_ctl -w restart

使用 5433 端口重啟並且重啟後關閉 fsync ：

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

顯示服務器狀態

下面是來自 pg_ctl 的狀態輸出的例子：

$ 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 模式中被調用的命令行。

補充：PostgreSQL 設置單條SQL的執行超時 – 防雪崩

背景

設置單條SQL的執行超時，防雪崩。

通常來說可以在SQL發起前設置事務級超時參數，SQL執行結束，重置。（如果SQL異常退出，會自動重置事務級參數）

例子

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

函數級超時例子 – statement_timeout不可用

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

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

將它寫到函數中，在函數中設置超時

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 ;

調用SQL改成這樣

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

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

所以需要plpgsql超時，需要通過插件HOOK來實現。

https://www.postgresql.org/message-id/flat/200702201200.53535.xzilla%40users.sourceforge.net#[email protected]

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.

參數級別

1、實例級

修改

postgresql.conf

2、庫級

alter database dbname set parameter=?;

3、用戶級

alter role rolname set parameter=?;

4、會話級

set parameter=?;

5、事務級

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

6、函數級

alter function fun_name() set parameter=?;

其他超時控制

1、空閑事務超時

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

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方，望不吝賜教。

推薦閱讀：