From: | Steve Nixon <inverasln(at)gmail(dot)com> |
---|---|
To: | Sergei Kornilov <sk(at)zsrv(dot)org> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: VACUUM: Nonremovable rows due to wal sender process |
Date: | 2022-01-04 21:01:11 |
Message-ID: | CACSoXP+gTkUZXQUgcAcWc62BFB3N1BqP_sy_-r6R2T+w5GrC9g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(at)zsrv(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Avi Weinberg | 2022-01-06 07:43:46 | Same query 10000x More Time |
Previous Message | Sergei Kornilov | 2022-01-04 20:17:52 | Re:VACUUM: Nonremovable rows due to wal sender process |