Re: auto vacuum

In response to Herouth Maoz <herouth(at)unicell(dot)co(dot)il>:

> Hi all.
>
> We had a crisis this week that was resolved by tuning pg_autovacuum for a particular table. The table is supposed to contain a small number of items at any given point in time (typically around 10,000-30,000). The items are inserted when we send out a message, and are selected, then deleted when a reply to the message arrives. This may be done at a rather high rate - sometimes a thousand a minute or around that.
>
> We found out that the table's response depends on the rate of ANALYZE being performed. We have tuned the values in pg_autovacuum so that we have around one analyze per minute.
>
> What is bothering me is that sometimes the auto vacuum daemon decides to perform a vacuum analyze rather than just analyze. If it just does a vacuum independent of the analyze, we don't see much impact on performance. But if it does vacuum analyze, it means that until vacuum is over, it doesn't do another analyze, and this may take about five minutes, in which our performance under load conditions might deteriorate.
>
> Is there any way to cause pg_autovacuum not to choose vacuum analyze?
>
> I thought of changing the vacuum frequency to be rare - but then it might take even longer to vacuum, and if a long vacuum analyze falls on a high load time, although the chances are smaller, the risk is higher. We can't afford a slowdown in that table.

Have you considered putting either vacuum or analyze on a manual schedule
with cron so you can control it better? You could schedule a manual
analyze every minute and let autovacuum handle vacuuming, but it might
be better to explicitly schedule both of them so you have complete
control over when they run.

I did a couple of quick tests, and it doesn't seem as if a vacuum running
on one connection blocks an analyze running on another connection.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/