From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Kevin Kempter <kevin@kevinkempterllc.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: More Vacuum questions...
Date: Tue, 11 Sep 2007 18:29:08 +0100
Message-ID: <46E6D064.3000909@enterprisedb.com> (raw)
In-Reply-To: <200709111024.58444.kevin@kevinkempterllc.com>
References: <200709111024.58444.kevin@kevinkempterllc.com>
Kevin Kempter wrote:
> It's difficult to try and vacuum this table during the day as it seems to
> begin blocking all the other queries against the database after some time.
Vacuum can generate so much I/O that it overwhelms all other
transactions, but it shouldn't block other queries otherwise. You can
use the vacuum cost delay options to throttle vacuum so that it doesn't
runs slower, but doesn't disrupt other operations so much.
> I plan to rebuild the table again and see if I can get away with vacuuming more
> often - it during the day. Also I'm considering a weekly cron job each Sunday
> (minimal processing happens on the weekends) to rebuild the table.
>
> Just curious if anyone has any thoughts on an automated rebuild scenario? or
> better yet managing the vac of this table more efficiently?
CLUSTER is a handy way to do rebuild tables.
> Maybe it's worth upping maintenance_work_mem sky-high for this table (via a
> session specific SET of maintenance_work_mem) and running a vacuum every 3
> hours or so.
You only need enough maintenance_work_mem to hold pointers to all dead
tuples in the table. Using more than that won't help.
> Also, does Postgres allocate maintenence_work_memory from the
> overall shared_buffers space available (I think not) ?
No.
> Is there some method / guideline I could use to determine the memory needs on
> a table by table basis for the vacuum process ? If so, I suspect I could use
> this as a guide for setting a session specific maintenance_work_mem via cron
> to vacuum these problem tables on a specified schedule.
You need 6 bytes per dead tuple in the table to avoid scanning the
indexes more than once. If you vacuum regularly, you shouldn't need more
than a few hundred MB.
One way is to run VACUUM VERBOSE, which will tell how many passes it
used. If it used more than one, increase maintenance_work_mem.
I would suggest using autovacuum after all. If it seems to be disrupting
other activity too much, increase autovacuum_cost_delay. Or decrease it
if it can't keep up with the updates.
BTW, you didn't mention which version of PostgreSQL you're using.
There's been some performance enhancements to VACUUM in 8.2, as well as
autovacuum changes. You might consider upgrading if you're not on 8.2
already.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
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: heikki@enterprisedb.com, kevin@kevinkempterllc.com
Subject: Re: More Vacuum questions...
In-Reply-To: <46E6D064.3000909@enterprisedb.com>
* 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