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.
