Thread: VACUUM: Nonremovable rows due to wal sender process

VACUUM: Nonremovable rows due to wal sender process

From

Steve Nixon

Date:

04 January 2022, 20:16:00

I have PostgreSQL Version 10.7 on AIX 7.1 set up with streaming replication. Replication appears to be working fine and database contents are staying current.

ps -ef |grep sender
postgres 54854022 30212254 0 10:10:29 - 0:00 postgres: wal sender process postgres 10.253.15.123(47852) streaming 54/BB631A30

ps -ef |grep receiver
postgres 34079622 9897420 0 10:10:29 - 0:00 postgres: wal receiver process streaming 54/BB631A30

The problem I have is related to the wal sender process. The AUTOVACUUM and VACUUM are not cleaning up dead tuples in the tables because it is reporting that they are "nonremovable" due to the backend_xmin that is not changing. This has resulted in queries on some tables taking seconds or minutes to return under 100 tuples that should take 5ms or less.

VACUUM VERBOSE scttlk_tbl;

INFO: "scttlk_tbl": found 0 removable, 149715 nonremovable row versions in 3322
out of 12152 pages
DETAIL: 149699 dead row versions cannot be removed yet, oldest xmin: 340818216
There were 21246 unused item pointers.
Skipped 0 pages due to buffer pins, 8830 frozen pages.

When I check the backend_xmin that is indicated as preventing the dead tuples from being removed, the PID it points to is the wal sender.

I have determined that if I shut down the replication database, the wal sender process will shut down. When I do this and run my VACUUM, it is then able to remove the dead tuples that were nonremovable prior. However, when I restart the replication database, the wal sender becomes active again and tries to pick up where it left off, at the same backend_xmin.

I believe the issue may be related to another product we are using as part of the replication process called "Attunity". But we have shut that down and restarted it to make sure it did not have any long running queries or other hooks that may be affecting the wal sender and preventing the backend_xmin from moving forward. It just does not seem to do so.

My questions are as follows:

1) Is there anything I can do short of shutting down and restarting the primary (production system) that would allow the backend_xmin to move forward?

2) Is it possible to "kill" the WAL sender process? I know it's possible, but what I mean is will it crash Postgres doing that? Or will it simply respawn?

Ultimately, the goal is to get backend_xmin to be caught up to work being done today and not waiting on something from days or weeks ago to release so the autovacuum can take place.

Hope I'm explaining myself right! Please let me know any advice you may have on this, and thanks in advance for any tips on where to look or how to address this.

Regards,

Steve N.

Re:VACUUM: Nonremovable rows due to wal sender process

From

Sergei Kornilov

Date:

04 January 2022, 23:17:52

Hello
This is exactly the reason why you need to track the age of the oldest transaction on the primary itself and on every
replicathat has hot_standby_feedback = on. By default hot_standby_feedback is disabled.
 

> Is there anything I can do short of shutting down and restarting the primary (production system) that would allow the
backend_xminto move forward?
 

You need to investigate this replica. Not a primary database. What transactions are in progress? Is it reasonable? Is
hot_standby_feedbackreally needed here and is it reasonable to pay for its impact across the entire cluster?
 
In my practice, hot_standby_feedback = on is only needed on replicas intended for fast OLTP queries. And where any long
requestsare prohibited. 
 

regards, Sergei

Re: VACUUM: Nonremovable rows due to wal sender process

From

Steve Nixon

Date:

05 January 2022, 00:01:11

Thank you for the quick reply. You are correct that hot_standby_feedback is indeed on. I'm trying to find out why at the moment because we are not using the replication for any queries that would need that turned on. I was just made aware of that after posting my question, and I am looking to get permission to turn it off. I have access to the primary and the streaming replication, but I do not have access to the replication being done by this "Attunity" product. Our parent company is managing that.

The AUTOVACUUM appears to have stopped working sometime around NOV 22. If I look on the replication server I have access to, one of the pg_stat_activity entries are older than today. Based on that, I suspect that the culprit long running transaction may be on the corporate replicated database that I do not have direct access to.

select pid, backend_xmin, backend_start, backend_type from pg_stat_activity;

Thanks again. At least it helped me figure out where I should be looking.

Steve Nixon

On Tue, 4 Jan 2022 at 15:17, Sergei Kornilov <sk@zsrv.org> wrote:

Hello
This is exactly the reason why you need to track the age of the oldest transaction on the primary itself and on every replica that has hot_standby_feedback = on. By default hot_standby_feedback is disabled.

> Is there anything I can do short of shutting down and restarting the primary (production system) that would allow the backend_xmin to move forward?

You need to investigate this replica. Not a primary database. What transactions are in progress? Is it reasonable? Is hot_standby_feedback really needed here and is it reasonable to pay for its impact across the entire cluster?
In my practice, hot_standby_feedback = on is only needed on replicas intended for fast OLTP queries. And where any long requests are prohibited.

regards, Sergei