Обсуждение: Vacuum process waiting on BufferPin
Postgres 9.6.6 on CentOS 7
This afternoon I discovered an autovacuum process that had been running for over 6 days. It was waiting on BufferPin event. I kicked off a manual vacuum+analyze of the table, which automatically killed that autovacuum. This ran for a few minutes before it too was waiting on a BufferPin event. I've never witnessed a vacuum session waiting on BufferPin before.
In pg_locks, I see a handful of sessions that have an AccessShareLock on the table I'm trying to vacuum. My vacuum session has a ShareUpdateExclusiveLock on that relation. All of those sessions look like orphaned reporting sessions sitting "idle in transaction". It's unclear to me why a report job would end up idle in transaction, to my knowledge we don't disable autocommit from the reporting app server.
Anyway, my next step is getting the OK to terminate those idle in transaction sessions to see if that gets my vacuum job moving. Meanwhile I'll ask a dev to sort out why they might be sitting idle in transaction, there's no reason for them to be unless the app server connection died and they are zombies. However I'm curious if there is someplace else I could/should also look to get to the root cause of this.
On 2018-Aug-13, Don Seiler wrote: > This afternoon I discovered an autovacuum process that had been running for > over 6 days. It was waiting on BufferPin event. I kicked off a manual > vacuum+analyze of the table, which automatically killed that autovacuum. > This ran for a few minutes before it too was waiting on a BufferPin event. > I've never witnessed a vacuum session waiting on BufferPin before. Buffer pins are normally released quickly enough. Of course, an idle-in-transaction session has its own problems, but even those would normally not have buffer pins; so vacuum would run to completion without blocking, even if no tuples would be removed. Maybe you had a cursor that was not fully scanned before the session was left idle -- as I recall, those can leave buffers pinned. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Mon, Aug 13, 2018 at 4:15 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
--
Maybe you had a cursor that was not fully scanned before the session was
left idle -- as I recall, those can leave buffers pinned.
I don't quite follow this. What circumstances would lead to this situation?
For what its worth, these sessions are backend reporting jobs, not user interfacing at all.
Don.
Don Seiler
www.seiler.us
www.seiler.us
On Mon, Aug 13, 2018 at 4:04 PM, Don Seiler <don@seiler.us> wrote:
Anyway, my next step is getting the OK to terminate those idle in transaction sessions to see if that gets my vacuum job moving. Meanwhile I'll ask a dev to sort out why they might be sitting idle in transaction, there's no reason for them to be unless the app server connection died and they are zombies. However I'm curious if there is someplace else I could/should also look to get to the root cause of this.
FYI, killing those idle-in-transaction sessions did free up the vacuum job, which then finished almost immediately afterward.
Don Seiler
www.seiler.us
www.seiler.us
On Mon, Aug 13, 2018 at 5:19 PM, Don Seiler <don@seiler.us> wrote:
On Mon, Aug 13, 2018 at 4:15 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
Maybe you had a cursor that was not fully scanned before the session was
left idle -- as I recall, those can leave buffers pinned.I don't quite follow this. What circumstances would lead to this situation?
BEGIN WORK;
DECLARE CURSOR ... ;
FETCH ...; -- for some number of fetches, which does not reach the end of the cursor.
then just sit there idle, without having closed the cursor or fetching anything more.
On Mon, Aug 13, 2018 at 5:55 PM, Vick Khera <vivek@khera.org> wrote:
On Mon, Aug 13, 2018 at 5:19 PM, Don Seiler <don@seiler.us> wrote:I don't quite follow this. What circumstances would lead to this situation?BEGIN WORK;DECLARE CURSOR ... ;FETCH ...; -- for some number of fetches, which does not reach the end of the cursor.then just sit there idle, without having closed the cursor or fetching anything more.
So the fix in that case would be to ensure that they CLOSE the cursors when done with them?
On Tue, Aug 14, 2018 at 9:21 AM, Don Seiler <don@seiler.us> wrote:
On Mon, Aug 13, 2018 at 5:55 PM, Vick Khera <vivek@khera.org> wrote:On Mon, Aug 13, 2018 at 5:19 PM, Don Seiler <don@seiler.us> wrote:I don't quite follow this. What circumstances would lead to this situation?BEGIN WORK;DECLARE CURSOR ... ;FETCH ...; -- for some number of fetches, which does not reach the end of the cursor.then just sit there idle, without having closed the cursor or fetching anything more.
So the fix in that case would be to ensure that they CLOSE the cursors when done with them?
The general fix is to never sit idle in transaction, but this specific case closing the cursor seems like it will also do it.
On Tue, Aug 14, 2018 at 8:23 AM, Vick Khera <vivek@khera.org> wrote:
The general fix is to never sit idle in transaction, but this specific case closing the cursor seems like it will also do it.
Of course. I've let development know that they need to sort out why it's left that way. But if they're also NOT closing cursors, that seems like another bad practice to correct.
Would commit/rollback automatically close cursors opened in that transaction?
On 14/08/18 15:26, Don Seiler wrote: > On Tue, Aug 14, 2018 at 8:23 AM, Vick Khera <vivek@khera.org > <mailto:vivek@khera.org>> wrote: > > > The general fix is to never sit idle in transaction, but this > specific case closing the cursor seems like it will also do it. > > > Of course. I've let development know that they need to sort out why it's > left that way. There is an idle_in_transaction_session_timeout parameter to kill connections that are idle (in transaction) for too long. It was implemented specifically for cases like this. -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
On Tue, Aug 14, 2018 at 8:58 AM, Vik Fearing <vik.fearing@2ndquadrant.com> wrote:
There is an idle_in_transaction_session_timeout parameter to kill
connections that are idle (in transaction) for too long. It was
implemented specifically for cases like this.
Thanks for reminding me of this. I'll probably look to set it but make sure to point out that it is just a safety net to let DB maintenance run and they should make sure their work is committed cleanly if they want to keep it.
Don.
Don Seiler
www.seiler.us
www.seiler.us