pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedVACUUM vs auto-vacuum daemon
6+ messages / 4 participants
[nested] [flat]
* VACUUM vs auto-vacuum daemon
@ 2007-06-12 15:54 Sabin Coanda <sabin.coanda@deuromedia.ro>
0 siblings, 2 replies; 6+ messages in thread
From: Sabin Coanda @ 2007-06-12 15:54 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: VACUUM vs auto-vacuum daemon
@ 2007-06-12 16:08 Alvaro Herrera <alvherre@commandprompt.com>
parent: Sabin Coanda <sabin.coanda@deuromedia.ro>
1 sibling, 0 replies; 6+ messages in thread
From: Alvaro Herrera @ 2007-06-12 16:08 UTC (permalink / raw)
To: Sabin Coanda <sabin.coanda@deuromedia.ro>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: VACUUM vs auto-vacuum daemon
@ 2007-06-12 16:25 Bill Moran <wmoran@collaborativefusion.com>
parent: Sabin Coanda <sabin.coanda@deuromedia.ro>
1 sibling, 1 reply; 6+ messages in thread
From: Bill Moran @ 2007-06-12 16:25 UTC (permalink / raw)
To: Sabin Coanda <sabin.coanda@deuromedia.ro>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: VACUUM vs auto-vacuum daemon
@ 2007-06-12 16:42 Sabin Coanda <sabin.coanda@deuromedia.ro>
parent: Bill Moran <wmoran@collaborativefusion.com>
0 siblings, 1 reply; 6+ messages in thread
From: Sabin Coanda @ 2007-06-12 16:42 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: VACUUM vs auto-vacuum daemon
@ 2007-06-13 14:46 Bill Moran <wmoran@collaborativefusion.com>
parent: Sabin Coanda <sabin.coanda@deuromedia.ro>
0 siblings, 1 reply; 6+ messages in thread
From: Bill Moran @ 2007-06-13 14:46 UTC (permalink / raw)
To: Sabin Coanda <sabin.coanda@deuromedia.ro>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: VACUUM vs auto-vacuum daemon
@ 2007-06-13 15:08 Joshua D. Drake <jd@commandprompt.com>
parent: Bill Moran <wmoran@collaborativefusion.com>
0 siblings, 0 replies; 6+ messages in thread
From: Joshua D. Drake @ 2007-06-13 15:08 UTC (permalink / raw)
To: Bill Moran <wmoran@collaborativefusion.com>; +Cc: Sabin Coanda <sabin.coanda@deuromedia.ro>; pgsql-performance
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-06-13 15:08 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-12 15:54 VACUUM vs auto-vacuum daemon Sabin Coanda <sabin.coanda@deuromedia.ro>
2007-06-12 16:08 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-06-12 16:25 ` Bill Moran <wmoran@collaborativefusion.com>
2007-06-12 16:42 ` Sabin Coanda <sabin.coanda@deuromedia.ro>
2007-06-13 14:46 ` Bill Moran <wmoran@collaborativefusion.com>
2007-06-13 15:08 ` Joshua D. Drake <jd@commandprompt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox