digoal
2024-04-12
PostgreSQL , PolarDB , DuckDB , transaction_timeout
长事务, 长SQL(有获得快照标识), 长2PC 这几个都是危险份子, 因为会引起最早快照xid对应dead tuple无法被回收. age无法下降.
未消费或消费慢的逻辑复制槽, 也会引起catalog的dead tuple无法被回收. age 无法下降.
会有什么问题呢? 一旦表的dead tuple的比例超过触发垃圾回收的阈值, auto vacuum就会扫描这个表, 然后又不断发现不能被回收. 这个动作会导致CPU上升(扫描+判断 dead tuple是否可以被回收), IO read上升(扫描需要回收垃圾的表), 表和索引膨胀, shared buffer内存消耗增加, 性能下降等问题.
会造成以上问题对还有2个参数:
- 从库开启 hot_standby_feedback, 并且在从库有长事务, 长SQL(有获得快照标识).
- vacuum_defer_cleanup_age (PG 16开始删掉了这个参数), 延迟回收年龄小于这个参数的dead tuple.
PostgreSQL 17 支持事务超时, 可以大幅度避免业务逻辑或程序问题导致的long transaction, 引发的 CPU上升(扫描+判断 dead tuple是否可以被回收), IO read上升(扫描需要回收垃圾的表), 表和索引膨胀, shared buffer内存消耗增加, 性能下降等问题.
#statement_timeout = 0 # in milliseconds, 0 is disabled
+#transaction_timeout = 0 # in milliseconds, 0 is disabled
#lock_timeout = 0 # in milliseconds, 0 is disabled
#idle_in_transaction_session_timeout = 0 # in milliseconds, 0 is disabled
#idle_session_timeout = 0 # in milliseconds, 0 is disabled
Introduce transaction_timeout
author Alexander Korotkov <akorotkov@postgresql.org>
Thu, 15 Feb 2024 21:34:11 +0000 (23:34 +0200)
committer Alexander Korotkov <akorotkov@postgresql.org>
Thu, 15 Feb 2024 21:56:12 +0000 (23:56 +0200)
commit 51efe38cb92f4b15b68811bcce9ab878fbc71ea5
tree d51bcdea74cd327444b5a00632a951b5b82baa48 tree
parent 5c9f2f9398b46a283dcdf4366aaeb06eb04aa76a commit | diff
Introduce transaction_timeout
This commit adds timeout that is expected to be used as a prevention
of long-running queries. Any session within the transaction will be
terminated after spanning longer than this timeout.
However, this timeout is not applied to prepared transactions.
Only transactions with user connections are affected.
Discussion: https://postgr.es/m/CAAhFRxiQsRs2Eq5kCo9nXE3HTugsAAJdSQSmxncivebAxdmBjQ%40mail.gmail.com
Author: Andrey Borodin <amborodin@acm.org>
Author: Japin Li <japinli@hotmail.com>
Author: Junwang Zhao <zhjwpku@gmail.com>
Reviewed-by: Nikolay Samokhvalov <samokhvalov@gmail.com>
Reviewed-by: Andres Freund <andres@anarazel.de>
Reviewed-by: Fujii Masao <masao.fujii@oss.nttdata.com>
Reviewed-by: bt23nguyent <bt23nguyent@oss.nttdata.com>
Reviewed-by: Yuhang Qiu <iamqyh@gmail.com>
