From: Tom Lane <tgl@sss.pgh.pa.us>
To: markus@m-bass.com
Cc: pgsql-performance@postgresql.org
Subject: Re: When to do a vacuum for highly active table
Date: Tue, 30 Aug 2005 17:29:17 -0400
Message-ID: <24875.1125437357@sss.pgh.pa.us> (raw)
In-Reply-To: <20050830211356.48138.qmail@web31606.mail.mud.yahoo.com>
References: <20050830211356.48138.qmail@web31606.mail.mud.yahoo.com>
Markus Benne <thing@m-bass.com> writes:
> We have a highly active table that has virtually all
> entries updated every 5 minutes. Typical size of the
> table is 50,000 entries, and entries have grown fat.
> We are currently vaccuming hourly, and towards the end
> of the hour we are seeing degradation, when compared
> to the top of the hour.
On something like this, you really need to be vacuuming more often
not less so; I'd think about how to do it every five or ten minutes
rather than backing off. With only 50K rows it should really not take
more than a couple of seconds to do the vacuum. When you wait till
there are 600K dead rows, it's going to take awhile, plus you are
suffering across-the-board performance degradation from all the dead
rows.
If you are using PG 8.0, there are some "vacuum cost" knobs you can
fiddle with to slow down vacuum so it doesn't impose as much I/O load.
Ideally you could get it to where you could run vacuum as often as
you need to without noticing much impact on foreground processing.
If you're not using 8.0 ... maybe it's time to update.
Another thing you might want to do is look at "vacuum verbose" output,
which will give you some idea of the time spent in each step. It might
be there are specific aspects that could be improved.
> We are thinking of splitting the table in two: the
> part the updates often, and the part the updates
> infrequently as we suspect that record size impacts
> vacuum.
You just said that virtually all rows update constantly --- where's
the "infrequent" part?
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, markus@m-bass.com
Subject: Re: When to do a vacuum for highly active table
In-Reply-To: <24875.1125437357@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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