From: Robert Treat <xzilla@users.sourceforge.net>
To: Greg Stark <gsstark@mit.edu>
Cc: Max Baker <max@warped.org>
Cc: Joe Conway <mail@joeconway.com>
Cc: Manfred Koizar <mkoi-pg@aon.at>
Cc: PostgreSQL Performance Mailing List <pgsql-performance@postgresql.org>
Subject: Re: speeding up COUNT and DISTINCT queries
Date: 13 Mar 2003 15:05:30 -0500
Message-ID: <1047585930.23128.913.camel@camel> (raw)
In-Reply-To: <874r67ff28.fsf@stark.dyndns.tv>
References: <20030312223811.GM30411@warped.org>
<a7hv6v4c6vt4vi2eg8s3dmgg3dskggob84@4ax.com>
<20030313015540.GP30411@warped.org>
<3E6FE59E.1050602@joeconway.com>
<20030313020546.GQ30411@warped.org>
<874r67ff28.fsf@stark.dyndns.tv>
On Thu, 2003-03-13 at 10:42, Greg Stark wrote:
> Max Baker <max@warped.org> writes:
> > On Wed, Mar 12, 2003 at 05:57:50PM -0800, Joe Conway wrote:
> > That would explain why once a night isn't enough. Thanks.
> > The contents of this table get refreshed every 4 hours. I'll add a
> > vacuum after every refresh and comapre the results in a couple days.
>
> If it gets completely refreshed, ie, every tuple is updated or deleted and
> re-inserted in a big batch job then VACUUM might never be enough without
> boosting some config values a lot. You might need to do a VACUUM FULL after
> the refresh. VACUUM FULL locks the table though which might be unfortunate.
>
hmm... approx 35,000 records, getting updated every 4 hours. so..
35000 / (4*60) =~ 145 tuples per minute.
Lets assume we want to keep any overhead at 10% or less, so we need to
lazy vacuum every 3500 updates. so...
3500 tuples / 145 tpm =~ 25 minutes.
So, set up a cron job to lazy vacuum every 20 minutes and see how that
works for you.
Robert Treat
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: xzilla@users.sourceforge.net, gsstark@mit.edu, max@warped.org, mail@joeconway.com, mkoi-pg@aon.at
Subject: Re: speeding up COUNT and DISTINCT queries
In-Reply-To: <1047585930.23128.913.camel@camel>
References: <20030312223811.GM30411@warped.org> <a7hv6v4c6vt4vi2eg8s3dmgg3dskggob84@4ax.com> <20030313015540.GP30411@warped.org> <3E6FE59E.1050602@joeconway.com> <20030313020546.GQ30411@warped.org> <874r67ff28.fsf@stark.dyndns.tv> <1047585930.23128.913.camel@camel>
* 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