pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAuto-Vacuum in 8.1 was ineffective for me. 8.2 may work better?
5+ messages / 3 participants
[nested] [flat]
* Auto-Vacuum in 8.1 was ineffective for me. 8.2 may work better?
@ 2007-02-21 18:37 Mark Stosberg <mark@summersault.com>
0 siblings, 1 reply; 5+ messages in thread
From: Mark Stosberg @ 2007-02-21 18:37 UTC (permalink / raw)
To: pgsql-performance
When I upgraded a busy database system to PostgreSQL 8.1, I was excited
about AutoVacuum, and promptly enabled it, and turned off the daily
vacuum process.
(
I set the following, as well as the option to enable auto vacuuming
stats_start_collector = true
stats_row_level = true
)
I could see in the logs that related activity was happening, but within
a few days, the performance became horrible, and enabling the regular
vacuum fixed it.
Eventually autovacuum was completely disabled.
What could have happened? Is 8.2 more likely to "just work" in the
regard? Is the the table-specific tuning that I would have needed to do?
I realize getting autovacuuming to work could be one way to exclude the
large table I wrote about in a recent post.
Mark
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Auto-Vacuum in 8.1 was ineffective for me. 8.2 may work better?
@ 2007-02-21 18:55 Alvaro Herrera <alvherre@commandprompt.com>
parent: Mark Stosberg <mark@summersault.com>
0 siblings, 1 reply; 5+ messages in thread
From: Alvaro Herrera @ 2007-02-21 18:55 UTC (permalink / raw)
To: Mark Stosberg <mark@summersault.com>; +Cc: pgsql-performance
Mark Stosberg wrote:
>
> When I upgraded a busy database system to PostgreSQL 8.1, I was excited
> about AutoVacuum, and promptly enabled it, and turned off the daily
> vacuum process.
>
> (
> I set the following, as well as the option to enable auto vacuuming
> stats_start_collector = true
> stats_row_level = true
> )
>
> I could see in the logs that related activity was happening, but within
> a few days, the performance became horrible, and enabling the regular
> vacuum fixed it.
>
> Eventually autovacuum was completely disabled.
This has been tracked down to a bug in 8.1's Windows port. See
http://people.planetpostgresql.org/mha/index.php?/archives/134-8.1-on-win32-pgstat-and-autovacuum.ht...
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Auto-Vacuum in 8.1 was ineffective for me. 8.2 may work better?
@ 2007-02-21 20:28 Mark Stosberg <mark@summersault.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 2 replies; 5+ messages in thread
From: Mark Stosberg @ 2007-02-21 20:28 UTC (permalink / raw)
To: pgsql-performance
Alvaro Herrera wrote:
> Mark Stosberg wrote:
>> When I upgraded a busy database system to PostgreSQL 8.1, I was excited
>> about AutoVacuum, and promptly enabled it, and turned off the daily
>> vacuum process.
>>
>> (
>> I set the following, as well as the option to enable auto vacuuming
>> stats_start_collector = true
>> stats_row_level = true
>> )
>>
>> I could see in the logs that related activity was happening, but within
>> a few days, the performance became horrible, and enabling the regular
>> vacuum fixed it.
>>
>> Eventually autovacuum was completely disabled.
>
> This has been tracked down to a bug in 8.1's Windows port. See
> http://people.planetpostgresql.org/mha/index.php?/archives/134-8.1-on-win32-pgstat-and-autovacuum.ht...
Thanks for the response Alvaro. This would have been on FreeBSD.
Let me ask the question a different way: Is simply setting the two
values plus enabling autovacuuming generally enough, or is further
tweaking common place?
Perhaps I'll give it another tree when we upgrade to 8.2.
Mark
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Auto-Vacuum in 8.1 was ineffective for me. 8.2 may work better?
@ 2007-02-21 20:36 Matthew T. O'Connor <matthew@zeut.net>
parent: Mark Stosberg <mark@summersault.com>
1 sibling, 0 replies; 5+ messages in thread
From: Matthew T. O'Connor @ 2007-02-21 20:36 UTC (permalink / raw)
To: Mark Stosberg <mark@summersault.com>; +Cc: pgsql-performance
Mark Stosberg wrote:
> Let me ask the question a different way: Is simply setting the two
> values plus enabling autovacuuming generally enough, or is further
> tweaking common place?
No, most people in addition to setting those two GUC settings also lower
the threshold values (there is a fair amount of discussion on this in
the lists) the defaults are not aggressive enough, so you tables
probably aren't getting vacuumed often enough to keep up with the load.
Some work loads also require that you do cron based vacuuming of
specific highly active tables.
> Perhaps I'll give it another tree when we upgrade to 8.2.
Autovacuum is still somewhat new, and there were some significant
improvements in 8.2 so yes you should give it another try.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Auto-Vacuum in 8.1 was ineffective for me. 8.2 may work better?
@ 2007-02-21 20:51 Alvaro Herrera <alvherre@commandprompt.com>
parent: Mark Stosberg <mark@summersault.com>
1 sibling, 0 replies; 5+ messages in thread
From: Alvaro Herrera @ 2007-02-21 20:51 UTC (permalink / raw)
To: Mark Stosberg <mark@summersault.com>; +Cc: pgsql-performance
Mark Stosberg wrote:
> Alvaro Herrera wrote:
> > Mark Stosberg wrote:
> >> When I upgraded a busy database system to PostgreSQL 8.1, I was excited
> >> about AutoVacuum, and promptly enabled it, and turned off the daily
> >> vacuum process.
> >>
> >> (
> >> I set the following, as well as the option to enable auto vacuuming
> >> stats_start_collector = true
> >> stats_row_level = true
> >> )
> >>
> >> I could see in the logs that related activity was happening, but within
> >> a few days, the performance became horrible, and enabling the regular
> >> vacuum fixed it.
> >>
> >> Eventually autovacuum was completely disabled.
> > This has been tracked down to a bug in 8.1's Windows port. See
> > http://people.planetpostgresql.org/mha/index.php?/archives/134-8.1-on-win32-pgstat-and-autovacuum.ht...
>
> Thanks for the response Alvaro. This would have been on FreeBSD.
Oh, maybe I misread your OP :-) With "completely disabled" I thought
you meant it was "frozen", i.e., it ran, but did nothing.
> Let me ask the question a different way: Is simply setting the two
> values plus enabling autovacuuming generally enough, or is further
> tweaking common place?
I assume your FSM configuration is already good enough?
What you should do is find out what tables are not getting vacuumed
enough (e.g. by using contrib/pgstattuple repeteadly and seeing where is
dead space increasing) and tweak the autovacuum settings to have them
vacuumed more often. This is done by inserting appropriate tuples in
pg_autovacuum.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-02-21 20:51 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-21 18:37 Auto-Vacuum in 8.1 was ineffective for me. 8.2 may work better? Mark Stosberg <mark@summersault.com>
2007-02-21 18:55 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-02-21 20:28 ` Mark Stosberg <mark@summersault.com>
2007-02-21 20:36 ` Matthew T. O'Connor <matthew@zeut.net>
2007-02-21 20:51 ` Alvaro Herrera <alvherre@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