pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedAutovacuum Hung Due to Bufferpin
4+ messages / 2 participants
[nested] [flat]
* Autovacuum Hung Due to Bufferpin
@ 2023-01-11 13:59 Fred Habash <fmhabash@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Fred Habash @ 2023-01-11 13:59 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
I have a very small table of 28 rows that is less than 10kB. One of the AV
workers has been stuck autovac'ing it for over 20 hrs now with no progress
in heap blocks scanned or vac'd. I terminated the AV worker and ran a
manual vac which also ended up stuck waiting for a bufferpin.
pg_locks shows no blockers while this is happening. This view shows a
constant 13 sessions running SELECT statements on this table posting
AccessShareLock. Of course, these is also the AV sessions
with ShareUpdateExclusiveLock
I have also disabled AV for the table, but AV does not appear to honor that
due to 'to prevent wraparound'.
Why is AV blocked by bufferpin given the fact that this table does not get
an DML changes. It is purely read only. What can be done to resolve this?
----------------------------------------
Thank you
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Autovacuum Hung Due to Bufferpin
@ 2023-01-11 15:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: Fred Habash <fmhabash@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2023-01-11 15:32 UTC (permalink / raw)
To: Fred Habash <fmhabash@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
Fred Habash <fmhabash@gmail.com> writes:
> pg_locks shows no blockers while this is happening. This view shows a
> constant 13 sessions running SELECT statements on this table posting
> AccessShareLock. Of course, these is also the AV sessions
> with ShareUpdateExclusiveLock
> ...
> Why is AV blocked by bufferpin given the fact that this table does not get
> an DML changes. It is purely read only. What can be done to resolve this?
Apparently there has been some DML on it in the past, leaving dead rows
that vacuum now needs to clean up --- but it needs a transient buffer
lock for long enough to do that. If you have a constant stream of readers
it will never be able to get that lock. You'll need to find a way to
momentarily block those readers.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Autovacuum Hung Due to Bufferpin
@ 2023-01-11 17:46 Fred Habash <fmhabash@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Fred Habash @ 2023-01-11 17:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org
According to pgstattuple, dead_tuple_count = 0. If this is the case, then
what other explanations do we have? I mean, how can I find out what blocker
session is holding the bufferpin to terminate it?
SELECT * FROM pgstattuple('****.*****'::regclass);
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count |
dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
8192 | 28 | 2224 | 27.15 | 0 |
0 | 0 | 5764 | 70.36
(1 row)
On Wed, Jan 11, 2023 at 10:32 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Fred Habash <fmhabash@gmail.com> writes:
> > pg_locks shows no blockers while this is happening. This view shows a
> > constant 13 sessions running SELECT statements on this table posting
> > AccessShareLock. Of course, these is also the AV sessions
> > with ShareUpdateExclusiveLock
> > ...
> > Why is AV blocked by bufferpin given the fact that this table does not
> get
> > an DML changes. It is purely read only. What can be done to resolve this?
>
> Apparently there has been some DML on it in the past, leaving dead rows
> that vacuum now needs to clean up --- but it needs a transient buffer
> lock for long enough to do that. If you have a constant stream of readers
> it will never be able to get that lock. You'll need to find a way to
> momentarily block those readers.
>
> regards, tom lane
>
--
----------------------------------------
Thank you
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Autovacuum Hung Due to Bufferpin
@ 2023-01-11 21:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Fred Habash <fmhabash@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2023-01-11 21:07 UTC (permalink / raw)
To: Fred Habash <fmhabash@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
Fred Habash <fmhabash@gmail.com> writes:
> According to pgstattuple, dead_tuple_count = 0. If this is the case, then
> what other explanations do we have?
Could be that old tuple(s) now require freezing.
> I mean, how can I find out what blocker
> session is holding the bufferpin to terminate it?
I don't think there's any ready way to discover that from SQL level.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-01-11 21:07 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-01-11 13:59 Autovacuum Hung Due to Bufferpin Fred Habash <fmhabash@gmail.com>
2023-01-11 15:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-01-11 17:46 ` Fred Habash <fmhabash@gmail.com>
2023-01-11 21:07 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox