Autovacuum Hung Due to Bufferpin
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
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
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 notget
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
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