In response to Herouth Maoz <hero...@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 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/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
