Re: Autovacuum stuck for hours, blocking queries

[Tim Bellis <Tim.Bellis@xxxxxxxxxxxxxx>]

 

 

From: Jeff Janes [mailto:jeff.janes@xxxxxxxxx]
Sent: 17 February 2017 02:59
To: Tim Bellis <Tim.Bellis@xxxxxxxxxxxxxx>
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: [GENERAL] Autovacuum stuck for hours, blocking queries

 

On Wed, Feb 15, 2017 at 9:30 AM, Tim Bellis <Tim.Bellis@xxxxxxxxxxxxxx> wrote:

I have a postgres 9.3.4 database table which (intermittently but reliably) gets into a state where queries get blocked indefinitely (at least for many hours) behind an automatic vacuum. I was under the impression that vacuum should never take any blocking locks for any significant period of time, and so would like help resolving the issue.

The process blocking the query is:
postgres 21985 11304 98 Feb13 ?        1-14:20:52 postgres: autovacuum worker process   <db_name>
which is running the query
autovacuum: VACUUM public.<table_name>

 

Are you sure it doesn't really say: 

 

autovacuum: VACUUM public.<table_name> (to prevent wraparound)

[Tim Bellis] It doesn’t. I was using the query from https://wiki.postgresql.org/wiki/Lock_Monitoring and looking at the ‘current_statement_in_blocking_process’ column. Is there a different query I should be using?

 

If it doesn't include the "to prevent wraparound", then it should sacrifice itself as soon as it realizes it is blocking something else.  If it is not doing that, something is wrong.

 

If it does say "(to prevent wraparound)", then see all the other comments on this thread.

 

Notes:
  - This database table is used for about 6 million row writes per day, all of which are then deleted at the end of the day.

 

How many transactions do those 6 million writes comprise?

[Tim Bellis] I’m not sure - I think it’s 6 million transactions, but there might be some batching going on that I’m unaware of. What would this affect? (I can dig in if necessary)

(I might have been slightly wrong in characterising the exact behaviour; the table might be cleared every hour rather than every day, but there are still 6 million writes per day)

 

  - Our application kicks off a manual vacuum against this table each night which doesn't hit this problem, as far as we're aware.

 

If it were having problems, would you be aware of it?  Do you see in the log files the completion of the vacuum?  Or look in pg_stat_user_tables to see when last_vacuum was.  If it runs every night and succeeds, it is hard to see why wraparound would ever kick in.  Unless you are hitting 150,000,000 transactions in a day.

[Tim Bellis] I shall investigate this.

 

Cheers,

 

Jeff