Hello,
(Apologies for any possible duplication of this email.)
(Also, apologies if this is an obvious question. I have gone through the archives without seeing something that directly ties to this.)
We are running Postgresql on a 64b RHEL5.2 64b server. "Uname -a":
--------------Linux xxxxxxx 2.6.18-92.el5 #1 SMP Tue Apr 29 13:16:15 EDT 2008 x86_64 x86_64 x86_64 GNU/Linux
We have autovacuum enabled with the following settings:
autovacuum_naptime = 30s
autovacuum_vacuum_threshold = 200
autovacuum_vacuum_scale_factor = 0.5
autovacuum_vacuum_cost_delay = 10
In addition to autovacuuming, each day, early, in the morning, we run a full vacuum, like this: "vacuumdb --all --full --analyze". We do not have any special variable set for vacuum in postgresql.conf.
The problem is that once or twice a week, the "vacuum full analyze" seems to cancel out the autovacuum that has already started at the same time. E.g.,
-------------2011-05-07 03:51:04.959 EDT--[unknown]-[unknown] [3348]LOG: connection received: host=##.##.##.## port=60470
-------------2011-05-07 03:51:04.959 EDT-##.##.##.##-xxxx-xxxx [3348]LOG: connection authorized: user=xxxx database=XXXX
-------------2011-05-07 03:51:04.961 EDT-##.##.##.##-xxxx-xxxx [3348]LOG: statement: VACUUM FULL ANALYZE;
-------------...
-------------2011-05-07 03:51:10.733 EDT--- [19879]ERROR: canceling autovacuum task
-------------2011-05-07 03:51:10.733 EDT--- [19879]CONTEXT: automatic vacuum of table "xxxx.xxx.xxxx"
-------------...
-------------2011-05-07 03:52:48.918 EDT-##.##.##.##-xxxx-xxxx [3348]LOG: duration: 103957.270 ms
-------------2011-05-07 03:52:48.920 EDT-##.##.##.##-xxxx-xxxx [3348]LOG: disconnection: session time: 0:01:43.961 user=xxxx database=xxxx host=##.##.##.## port=60470
We would like to eliminate this error. A bigger problem is that sometimes it seems like autovacuum wins out over "vacuum full analyze". This tends to result in a hung job on our client, with other ensuing complications.
* Our basic question is what method we might be able to use to prevent either of these jobs from canceling. What we would like is, instead of autovacuum canceling, it rather always defers to "vacuum full analyze" job, waiting for it to complete.
I am guessing that we can do the above by setting the "autovacuum_vacuum_cost_limit" to a fairly high value (rather than it not being set at all, as it is right now, and thus inheriting the "200" default value from vacuum_cost_limit). Does that sound right? (If, what might be a good value to set?) Or perhaps there is a more foolproof way of doing this that does not rely upon guesswork?
Any suggestions at all would be most welcome!
Daniel C.
