In response to Herouth Maoz <herouth@unicell.co.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
containa small number of items at any given point in time (typically around 10,000-30,000). The items are inserted when
wesend out a message, and are selected, then deleted when a reply to the message arrives. This may be done at a rather
highrate - 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_autovacuumso 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
vacuumanalyze, it means that until vacuum is over, it doesn't do another analyze, and this may take about five minutes,
inwhich 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
vacuumanalyze falls on a high load time, although the chances are smaller, the risk is higher. We can't afford a
slowdownin 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/