pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Max Baker <max@warped.org>
To: Robert Treat <xzilla@users.sourceforge.net>
Cc: Greg Stark <gsstark@mit.edu>
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: Thu, 13 Mar 2003 12:22:54 -0800
Message-ID: <20030313202254.GD30411@warped.org> (raw)
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>

On Thu, Mar 13, 2003 at 03:05:30PM -0500, Robert Treat wrote:
> 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.

I'm not starting with fresh data every time, I'm usually checking for
an existing record, then setting a timestamp and a boolean flag. 

I've run some profiling and it's about 8000-10,000 UPDATEs every 4
hours.  These are accompanied by about 800-1000 INSERTs.   
 
> 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.

I'm now having VACUUM ANALYZE run after each of these updates.  The data
comes in in spurts -- a 90 minute batch job that runs every 4 hours. 


thanks folks,
-m



view thread (14+ messages)  latest in thread

Message-ID: <20030313202254.GD30411@warped.org>
Permalink:  ../20030313202254.GD30411@warped.org/
Also on:    postgresql.org/message-id/20030313202254.GD30411@warped.org

 · 

reply

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: max@warped.org, xzilla@users.sourceforge.net, gsstark@mit.edu, mail@joeconway.com, mkoi-pg@aon.at
  Subject: Re: speeding up COUNT and DISTINCT queries
  In-Reply-To: <20030313202254.GD30411@warped.org>
  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> <20030313202254.GD30411@warped.org>

* 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