Thread

1. VACUUM: Nonremovable rows due to wal sender process

   Steve Nixon <inverasln@gmail.com> — 2022-01-04T17:16:00Z

   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.
   
   
   *SELECT pid, datname, usename, state, backend_xid, backend_xminFROM
   pg_stat_activity WHERE backend_xmin = 340818216;*
   
      pid    |   datname    | usename  | state  | backend_xid | backend_xmin
   ----------+--------------+----------+--------+-------------+--------------
   54854022 | | postgres | active | | 340818216
   
   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.
   

2. Re:VACUUM: Nonremovable rows due to wal sender process

   Sergei Kornilov <sk@zsrv.org> — 2022-01-04T20:17:52Z

   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
   
   
   
   

3. Re: VACUUM: Nonremovable rows due to wal sender process

   Steve Nixon <inverasln@gmail.com> — 2022-01-04T21:01:11Z

   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;
   
   -[ RECORD 1 ]-+------------------------------
   pid | 63111452
   backend_xmin | 661716178
   backend_start | 2022-01-04 15:52:42.269666-05
   backend_type  | client backend
   -[ RECORD 2 ]-+------------------------------
   pid | 46400004
   backend_xmin  |
   backend_start | 2022-01-04 11:10:28.939006-05
   backend_type  | startup
   -[ RECORD 3 ]-+------------------------------
   pid | 46270090
   backend_xmin  |
   backend_start | 2022-01-04 11:10:28.979557-05
   backend_type  | background writer
   -[ RECORD 4 ]-+------------------------------
   pid           | 918684
   backend_xmin  |
   backend_start | 2022-01-04 11:10:28.978996-05
   backend_type  | checkpointer
   -[ RECORD 5 ]-+------------------------------
   pid | 34079622
   backend_xmin  |
   backend_start | 2022-01-04 11:10:29.172959-05
   backend_type  | walreceiver
   
   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
   >