[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-admin
Subject:    Re: [ADMIN] Autovacuum not keeping up. (PG 9.2.9)
From:       jayknowsunix () gmail ! com
Date:       2014-07-31 13:56:53
Message-ID: 5A546F03-0769-4D4A-B29F-C06CF235F42A () gmail ! com
[Download RAW message or body]



Sent from my iPad

> On Jul 31, 2014, at 9:40 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> 
> jesper@krogh.cc wrote:
> > Hi.
> > 
> > I have a large  database with a message queue table, that has high
> > activity. The database supports 1-300 client connection concurrently,
> > having transactions open in up to 30 minutes each.
> > 
> > Recently I am seeing autuvacuum being issued, but it takes
> > ages to get through the message queue table, with strace showing waiting
> > for semop's for 10's to 100's of seconds.
> 
> Do you have data on how relfrozenxid advances for that table?
> 
> Vacuuming needs to grab a "cleanup lock" on each page it's going to
> vacuum, which is a special kind of lock that requires that no other
> process is even looking at the page at that moment (we call this "to
> have the page pinned"), which is even weaker than having a shared lock
> on the page.  If traffic to some pages is high, it might be difficult
> for vacuum to acquire this.
> 
> Normally, vacuum doesn't break much sweat about this: if it cannot
> acquire the cleanup lock, it ignores the page, keeps calm and carries
> on.  But if it's a for-wraparound vacuuming, it will need to wait until
> it is able to acquire cleanup lock.
> 
> I think one idea might be to try to manually vacuum the table once in a
> while with a reduced value of min_freeze_table_age.  This will cause a
> full table scan (i.e. cleanup lock for all pages is waited for), which
> decreases the "frozen xid age", which moves the need to do this again
> further in the future; so the autovacuum-invoked vacuums will be able to
> skip the pages on which it cannot get cleanup lock.
> 
> Another idea is to increase min_freeze_table_age for the queue table
> through ALTER TABLE, the idea being that you can delay forced vacuuming
> of hot pages for long enough that they can wait until they have cooled
> off.  Default value is 150 million transactions, which you can raise
> tenfold and even higher.
> 
> See
> http://www.postgresql.org/docs/9.2/static/runtime-config-client.html#GUC-VACUUM-FREEZE-TABLE-AGE
>  
> 
> The other idea is that heap truncation is what's causing the problem,
> but AFAICS that uses conditional lock acquisition so you shouldn't be
> seeing stalls in semop().
> 
> -- 
> Álvaro Herrera                http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
> 
> 
> -- 
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

You should probably consider, if you haven't done already, to partition this table. \
Without knowing what's adding new transactions, it sounds like you could break up the \
table with a timestamp rule. That would permit autovacuum to do parts of the table \
                much quicker, and only once.
-/
Jay

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin


[prev in list] [next in list] [prev in thread] [next in thread]