问题规避 如果发现AUTOVACUUM清理死行过慢，跟不上业务死行生成的速度，导致死行持续堆积，则需要人工介入处理，具体策略如下： 调整AUTOVACUUM参数：在系统I/O资源充足的情况下，可以通过减小autovacuum_vacuum_cost_delay来加快VACUUM的清理速度。该参数默认值为20ms，即扫描约20-50个页面时会触发一次强制睡眠，每次睡眠时间为20ms。建议将该参数调整为1ms，以极大地提高后台线程的清理效率。 手动执行VACUUM：如果调整参数后仍无法立即回收死行，可以通过视图获取死行较多的数据表，并对这些表进行手动VACUUM。手动VACUUM不使用I/O管控，会以最大效率清理死行，从而快速减少死行数量。

问题定位 针对该问题，可以使用gs_txid_oldestxmin()视图查询oldestxmin，使用txid_current()视图查询当前最新的xid，通过两者的差值判断oldestxmin是否卡住。 select txid_current() as xid, gs_txid_oldestxmin() as oldestxmin, xid - oldestxmin as diff; 示例： gaussdb=# select txid_current() as xid, gs_txid_oldestxmin() as oldestxmin, xid - oldestxmin as diff; xid | oldestxmin | diff --------+------------+------ 220790 | 220789 | 1 (1 row) 一般情况下，二者的差值在10000以内是正常的。如果差值过大，达到几十甚至几百万，则系统存在长事务。可以通过pg_stat_activity获取卡住的事务信息： select 'elp:'|| now() - xact_start diff, query, datname, pid, usename, xact_start, enqueue, state, query_id, query from pg_stat_activity where query not like 'WLM%' order by diff desc; select * from pg_running_xacts; 示例： gaussdb=# select 'elp:'|| now() - xact_start diff, query, datname, pid, usename, xact_start, enqueue, state, query_id, query from pg_stat_activity where query not like 'WLM%' and state='active' order by diff desc; diff | query | datname | pid | usename | xact_start | enqueue | state | query_ id | query --------------+--------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------+----------+-----------------+-----------+-------------------------------+---------+--------+----------- -------+---------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------- elp:00:00:00 | select 'elp:'|| now() - xact_start diff, query, datname, pid, usename, xact_start, enqueue, state, query_id, query from pg_stat_activity where que ry not like 'WLM%' and state='active' order by diff desc; | postgres | 140123736897280 | postgres | 2025-02-09 13:50:21.628494-05 | | active | 3096224744 018863 | select 'elp:'|| now() - xact_start diff, query, datname, pid, usename, xact_start, enqueue, state, query_id, query from pg_stat_activity where query not like 'WLM%' and state='active' order by diff desc; 0 | elp: | | postgres | 140124852582144 | postgres | | | active | 0 | elp: | | postgres | 140124747724544 | postgres | | | active | 0 | elp: | | postgres | 140124232349440 | postgres | | | active | 0 | (6 rows) gaussdb=# select * from pg_running_xacts where pid=140125682005760; handle | gxid | state | node | xmin | vacuum | timeline | prepare_xid | pid | next_xid | dbid --------+--------+-------+--------+------+--------+----------+-------------+-----------------+----------+------- -1 | 220796 | 0 | sgnode | 0 | f | 11 | 0 | 140125682005760 | 0 | 12912 (1 row) 除此之外，逻辑复制槽也可能阻塞oldestxmin的推进，可以通过以下查询查看复制槽信息： select * from pg_get_replication_slots(); 示例： gaussdb=# select * from pg_get_replication_slots(); slot_name | plugin | slot_type | datoid | active | xmin | catalog_xmin | restart_lsn | dummy_standby | confirmed_flush | confirmed_csn | dictionary_csn_min | slo t_dictionary_type -----------+--------+-----------+--------+--------+------+--------------+-------------+---------------+-----------------+---------------+--------------------+---- ------------------ dn_6003 | | physical | 0 | t | | | 0/15E52890 | f | | | | dn_6002 | | physical | 0 | t | | | 0/15E52890 | f | | | | (2 rows)

问题规避 如果发现AUTOVACUUM清理死行过慢，跟不上业务死行生成的速度，导致死行持续堆积，则需要人工介入处理，具体策略如下： 调整AUTOVACUUM参数：在系统I/O资源充足的情况下，可以通过减小autovacuum_vacuum_cost_delay来加快VACUUM的清理速度。该参数默认值为20ms，即扫描约20-50个页面时会触发一次强制睡眠，每次睡眠时间为20ms。建议将该参数调整为1ms，以极大地提高后台线程的清理效率。 手动执行VACUUM：如果调整参数后仍无法立即回收死行，可以通过视图获取死行较多的数据表，并对这些表进行手动VACUUM。手动VACUUM不使用I/O管控，会以最大效率清理死行，从而快速减少死行数量。