Thread: 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
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 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
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