Re: VACUUM: Nonremovable rows due to wal sender process

[Steve Nixon]

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 | 4644
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  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
>

Same query 10000x More Time

[Avi Weinberg]

Hi

I have postgres_fdw table called tbl_link.  The source table is 2.5 GB in size 
with 122 lines (some lines has 70MB bytea column, but not the ones I select in 
the example)
I noticed that when I put the specific ids in the list "where id in 
(140,144,148)" it works fast (few ms), but when I put the same list as select 
"where id in (select 140 as id union select 144  union select 148)" it takes 50 
seconds.  This select union is just for the example, I obviously have a 
different select (which by itself takes few ms but cause the whole insert query 
to take 1x more time)

Why is that?  How can I still use regular select and still get reasonable 
response time?

Thanks


FAST:
select lnk.*
into local_1
from tbl_link lnk
where id in (140,144,148)

"Foreign Scan on tbl_link lnk  (cost=100.00..111.61 rows=3 width=700) (actual 
time=4.161..4.167 rows=3 loops=1)"
"Planning Time: 0.213 ms"
"Execution Time: 16.251 ms"



SLOW:
select lnk.*
into local_1
from tbl_link lnk
where id in (select 140 as id union select 144  union select 148)


"Hash Join  (cost=100.18..113.88 rows=3 width=700) (actual 
time=45398.721..46812.100 rows=3 loops=1)"
"  Hash Cond: (lnk.id = (140))"
"  ->  Foreign Scan on tbl_link lnk  (cost=100.00..113.39 rows=113 width=700) 
(actual time=45398.680..46812.026 rows=112 loops=1)"
"  ->  Hash  (cost=0.14..0.14 rows=3 width=4) (actual time=0.023..0.026 rows=3 
loops=1)"
"Buckets: 1024  Batches: 1  Memory Usage: 9kB"
"->  HashAggregate  (cost=0.08..0.11 rows=3 width=4) (actual 
time=0.017..0.021 rows=3 loops=1)"
"  Group Key: (140)"
"  Batches: 1  Memory Usage: 24kB"
"  ->  Append  (cost=0.00..0.07 rows=3 width=4) (actual 
time=0.005..0.009 rows=3 loops=1)"
"->  Result  (cost=0.00..0.01 rows=1 width=4) (actual 
time=0.003..0.004 rows=1 loops=1)"
"->  Result  (cost=0.00..0.01 rows=1 width=4) (actual 
time=0.001..0.001 rows=1 loops=1)"
"->  Result  (cost=0.00..0.01 rows=1 width=4) (actual 
time=0.000..0.001 rows=1 loops=1)"
"Planning Time: 0.541 ms"
"Execution Time: 46827.945 ms"


IMPORTANT - This email and any attachments is intended for the above named 
addressee(s), and may contain information which is confidential or privileged. 
If you are not the intended recipient, please inform the sender immediately and 
delete this email: you should not copy or use this e-mail for any purpose nor 
disclose its contents to any person.