tencent cloud

TencentDB for PostgreSQL

文档TencentDB for PostgreSQLKernel Version IntroductionProprietary Kernel FeaturesAutomatic Kill for High-Memory Sessions or Long-Running Transactions

Automatic Kill for High-Memory Sessions or Long-Running Transactions

Download
聚焦模式
字号
最后更新时间： 2026-08-12 11:22:11
本文档由 AI 翻译
This document introduces the kernel feature of TencentDB for PostgreSQL: automatic Kill of high-memory sessions/long-running transactions.

Supported Versions

Kernel Major Version
Earliest Supported Version
PostgreSQL 14
v14.19_r1.38 or later
PostgreSQL 15
v15.14_r1.23 or later
PostgreSQL 16
v16.10_r1.18 or later
PostgreSQL 17
v17.6_r1.12 or later
PostgreSQL 18
v18.1_r1.2 or later
Note:
You can view the kernel version of the current instance on the instance details page in the console or by executing SHOW tencentdb_version;.

Feature Overview

TencentDB for PostgreSQL supports two automatic session termination capabilities, which help you automatically reclaim abnormal sessions in the following scenarios without manual intervention:
Automatic Kill of Long-Running Transactions: When a transaction's age exceeds the set threshold, autovacuum automatically terminates sessions holding overly old transactions upon startup to avoid the risk of XID exhaustion.
Automatic Kill of High-Memory Sessions: When a session's memory usage exceeds the set threshold, a background process periodically scans for and automatically terminates that session to prevent instance OOM caused by a single session's memory surge.

Parameter

Log in to the TencentDB for PostgreSQL console, go to the instance management page, search for the parameter tencentdb_auto_kill_xact_max_age in Parameter Settings, modify it to the target value, and save the changes. The parameter modification takes effect through configuration hot reload, and no instance restart is required.
Parameter description:
Parameter Name
Type
Default Value
Value Range
Description
tencentdb_auto_kill_xact_max_age
integer
2000000000
[100000, 2000000000]
The age threshold for automatically killing XIDs. A value of 2000000000 disables this feature. The actual trigger threshold is the maximum of this value and autovacuum_freeze_max_age.

Configuration Suggestions

It is recommended to set tencentdb_auto_kill_xact_max_age and autovacuum_freeze_max_age to the same value (for example, 200000000), enabling the automatic Kill and VACUUM freeze to work in coordination at the same threshold.
If you want the Kill to be triggered earlier than autovacuum_freeze_max_age, you must also lower autovacuum_freeze_max_age (the actual trigger threshold will not be lower than this value).

Verifying the Effect

After setting the parameters, you can verify whether the feature is effective by using the following methods:
1. Lower the threshold to a value convenient for testing (for example, 100000), and then execute a long-running transaction (such as an INSERT containing pg_sleep) in a session.
2. Generate a large number of short transactions using pgbench to cause the XID counter to grow rapidly and exceed the threshold.
3. After the autovacuum worker starts, long-running transaction sessions are automatically terminated. You can confirm that the session has disappeared by checking pg_stat_activity.
4. Check the database logs, and you can see the following record:
autovacuum kill old transaction, sending SIGTERM to PID <PID>, xmin is <XMIN>, xid is <XID>

Automatic Kill for High-Memory Sessions

Preparing the Environment

The automatic Kill of high-memory sessions depends on the tencentdb_system_stat extension. You need to complete the following two configuration steps:
Step 1: Load the shared library
Log in to the TencentDB for PostgreSQL console, go to the instance management page, search for the parameter shared_preload_libraries in Parameter Settings, add tencentdb_system_stat to its value (separate multiple values with commas), save the changes, and then restart the instance for the changes to take effect.
Step 2: Verify that the extension is available.
After the instance restarts, run the following command to verify that the extension is loaded:
postgres=> SHOW shared_preload_libraries;
        shared_preload_libraries
--------------------------------------
 pg_stat_statements,tencentdb_system_stat
(1 row)

Parameter

Log in to the TencentDB for PostgreSQL console, go to the instance management page, and configure the following two parameters in Parameter Settings:
Parameter description:
Parameter Name
Type
Default Value
Value Range
Description
tencentdb_system_stat.max_process_memory_limit
integer (MB)
10000000
[100, 10000000]
The maximum memory usage allowed for a single session. When the value is 10000000 (approximately 10TB), it indicates that this feature is disabled.
tencentdb_system_stat.check_process_memory_interval
integer (ms)
5000
[100, 2147483]
The interval for memory checking, which is the number of milliseconds between each scan of memory usage for all sessions.

Configuration Suggestions

Set max_process_memory_limit based on the baseline memory consumption of your business SQL. For example, you can set it to 1–2 GB (1024–2048) for OLTP scenarios, and relax it appropriately for OLAP scenarios.
Keep the default value of 5000 ms (5 seconds) for the check interval. This frequency strikes a balance between response speed and system overhead, and transient memory spikes typically subside naturally within 5 seconds.
After the feature is enabled, the background worker periodically reads the RSS (Resident Set Size) metrics of sessions through the /proc file system. Sessions that exceed the threshold are terminated by sending a SIGTERM signal (the effect is equivalent to pg_terminate_backend).

Protection Mechanism

The following types of sessions are not automatically killed:
SUPERUSER sessions: Sessions running as the postgres user are not automatically killed.
Background processes: Internal database background processes such as autovacuum worker, WAL writer, and checkpointer are not automatically killed.

Verifying the Effect

After the configuration is complete, you can verify that the feature is effective by using the following methods:
1. Execute a query or operation that allocates a large amount of memory, causing the memory usage of a single session to exceed the max_process_memory_limit threshold.
2. The session will be automatically terminated during the next check cycle (within 5 seconds by default).
3. Check the database logs, and you can see the following record:
Kill process <PID>, memory usage <bytes> bytes exceed max_allowed_bytes <bytes>
kill process <PID> success

FAQs

Q: Why Are Long Transactions Not Killed After tencentdb_auto_kill_xact_max_age is set?

A: Check the following conditions:
Check whether autovacuum is on. The automatic kill of long-running transactions relies on the scheduling of the autovacuum worker. If autovacuum is disabled, the kill will not be triggered.
The actual trigger threshold is set to max(tencentdb_auto_kill_xact_max_age, autovacuum_freeze_max_age). If the value you set is less than autovacuum_freeze_max_age, the value that actually takes effect is still the latter one. It is recommended to keep the two parameters consistent.

Q: Why Does the High-Memory Auto Kill Parameter Not Take Effect Immediately After Modification?

A: The parameters tencentdb_system_stat.check_process_memory_interval and tencentdb_system_stat.max_process_memory_limit are both SIGHUP-level parameters. They take effect after you execute pg_reload_conf() or save the changes in the console, without requiring an instance restart. Ensure that shared_preload_libraries already contains tencentdb_system_stat (modifying this parameter requires a restart).

Q: Can These Parameters Be SET Directly via SQL?

A: The parameters tencentdb_auto_kill_xact_max_age and the tencentdb_system_stat.* series do not support modification via the session-level SET command. Please perform the operation on the parameter settings page in the Tencent Cloud console.

Q: Can Killed Sessions Reconnect Automatically?

A: Automatic Kill only terminates the backend process of the corresponding session and does not involve the reconnection logic on the application side. It is recommended that the application side configure a connection pool and an automatic reconnection mechanism to automatically recover after the session is terminated.
上一篇: WAL Log Source Analysis (tencentdb_wal_stat)下一篇: Audit Service Description

帮助和支持

本页内容是否解决了您的问题？

填写满意度调查问卷，共创更好文档体验。

文档反馈