Thread: VACUUM vs auto-vacuum daemon
Hi there, Using explicitly VACUUM command give me the opportunity to fine tune my VACUUM scheduling parameters, after I analyze the log generated by VACUUM VERBOSE. On the other hand I'd like to use the auto-vacuum mechanism because of its facilities. Unfortunately, after I made some initial estimations for autovacuum_naptime, and I set the specific data into pg_autovacuum table, I have not a feedback from the auto-vacuum mechanism to check that it works well or not. It would be nice to have some kind of log similar with the one generated by VACUUM VERBOSE. Is the auto-vacuum mechanism able to provide such a useful log ? TIA, Sabin
Sabin Coanda wrote:
> Hi there,
>
> Using explicitly VACUUM command give me the opportunity to fine tune my
> VACUUM scheduling parameters, after I analyze the log generated by VACUUM
> VERBOSE.
>
> On the other hand I'd like to use the auto-vacuum mechanism because of its
> facilities. Unfortunately, after I made some initial estimations for
> autovacuum_naptime, and I set the specific data into pg_autovacuum table, I
> have not a feedback from the auto-vacuum mechanism to check that it works
> well or not. It would be nice to have some kind of log similar with the one
> generated by VACUUM VERBOSE. Is the auto-vacuum mechanism able to provide
> such a useful log ?
No, sorry, autovacuum is not currently very good regarding reporting its
activities. It's a lot better in 8.3 but even there it doesn't report
the full VACUUM VERBOSE log. It looks like this:
LOG: automatic vacuum of table "alvherre.public.foo": index scans: 0
pages: 45 removed, 0 remain
tuples: 10000 removed, 0 remain
system usage: CPU 0.00s/0.00u sec elapsed 0.01 sec
LOG: automatic analyze of table "alvherre.public.foo" system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
In response to "Sabin Coanda" <sabin.coanda@deuromedia.ro>: > Hi there, > > Using explicitly VACUUM command give me the opportunity to fine tune my > VACUUM scheduling parameters, after I analyze the log generated by VACUUM > VERBOSE. > > On the other hand I'd like to use the auto-vacuum mechanism because of its > facilities. Unfortunately, after I made some initial estimations for > autovacuum_naptime, and I set the specific data into pg_autovacuum table, I > have not a feedback from the auto-vacuum mechanism to check that it works > well or not. It would be nice to have some kind of log similar with the one > generated by VACUUM VERBOSE. Is the auto-vacuum mechanism able to provide > such a useful log ? Ditto what Alvaro said. However, you can get some measure of tracking my running VACUUM VERBOSE on a regular basis to see how well autovacuum is keeping up. There's no problem with running manual vacuum and autovacuum together, and you'll be able to gather _some_ information about how well autovacuum is keeping up. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ wmoran@collaborativefusion.com Phone: 412-422-3463x4023
Hi Bill, ... > > However, you can get some measure of tracking my running VACUUM VERBOSE > on a regular basis to see how well autovacuum is keeping up. There's > no problem with running manual vacuum and autovacuum together, and you'll > be able to gather _some_ information about how well autovacuum is > keeping up. Well, I think it is useful just if I am able to synchronize the autovacuum to run always after I run vacuum verbose. But I don't know how to do that. Do you ? Sabin
In response to "Sabin Coanda" <sabin.coanda@deuromedia.ro>: > Hi Bill, > > ... > > > > However, you can get some measure of tracking my running VACUUM VERBOSE > > on a regular basis to see how well autovacuum is keeping up. There's > > no problem with running manual vacuum and autovacuum together, and you'll > > be able to gather _some_ information about how well autovacuum is > > keeping up. > > Well, I think it is useful just if I am able to synchronize the autovacuum > to run always after I run vacuum verbose. But I don't know how to do that. > Do you ? No, I don't. Why would you want to do that? Personally, I'd be more interested in whether autovacuum, running whenever it wants without me knowing, is keeping the table bloat under control. If this were a concern for me (which it was during initial testing of our DB) I would run vacuum verbose once a day to watch sizes and what not. After a while, I'd switch to once a week, then probably settle on once a month to ensure nothing ever gets out of hand. Put it in a cron job and have the output mailed. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ wmoran@collaborativefusion.com Phone: 412-422-3463x4023
Bill Moran wrote:
> In response to "Sabin Coanda" <sabin.coanda@deuromedia.ro>:
>
>> Hi Bill,
>>
>> ...
>>> However, you can get some measure of tracking my running VACUUM VERBOSE
>>> on a regular basis to see how well autovacuum is keeping up. There's
>>> no problem with running manual vacuum and autovacuum together, and you'll
>>> be able to gather _some_ information about how well autovacuum is
>>> keeping up.
>> Well, I think it is useful just if I am able to synchronize the autovacuum
>> to run always after I run vacuum verbose. But I don't know how to do that.
>> Do you ?
>
> No, I don't. Why would you want to do that?
>
> Personally, I'd be more interested in whether autovacuum, running whenever
> it wants without me knowing, is keeping the table bloat under control.
analyze verbose.
>
> If this were a concern for me (which it was during initial testing of
> our DB) I would run vacuum verbose once a day to watch sizes and what
> not. After a while, I'd switch to once a week, then probably settle on
> once a month to ensure nothing ever gets out of hand. Put it in a cron
> job and have the output mailed.
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/