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.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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/


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster