Skip site navigation (1) Skip section navigation (2)

Re: More Autovacuum questions

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Chris Hoover <revoohc(at)gmail(dot)com>
Cc: "pgsql-admin(at)postgresql(dot)org Admin" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: More Autovacuum questions
Date: 2008-07-15 13:52:16
Message-ID: 20080715135216.GB7334@alvh.no-ip.org (view raw or flat)
Thread:
Lists: pgsql-admin
Chris Hoover escribió:
> Another question.
> 
> When autovacuum sleeps, does it release the lock it has on the table?

What do you mean sleep?  There are two sleeps: the one caused by
cost-based vacuum delay (lasts for some milliseconds, happens every
handful of pages vacuumed; no locks released here) and the big sleep
that happens after one worker finishes and the next one starts (all
locks are released).

When one autovac worker is processing a table, no other worker touches
it.  So if you have a table that's 64 GB, it won't be analyzed until
the currently running vacuum finishes.

> What we would like to have happen is for vacuum to work for a while, sleep,
> and while it is sleeping run an analyze on the table.  We need this due to
> how quickly our data is changing.  Currently we are running analyze every 5
> minutes against the table of question and get a very serious performance hit
> when vacuum runs due to the index stats getting out of date.  As soon as we
> rerun analyze after the vacuum happiness returns.

This is worst case for Postgres vacuuming, I think.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

In response to

pgsql-admin by date

Next:From: Carol WalterDate: 2008-07-15 14:14:25
Subject: Changing encoding
Previous:From: Chris HooverDate: 2008-07-15 13:19:48
Subject: More Autovacuum questions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group