DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma;
	h=date:from:to:cc:subject:message-id:references:mime-version
	:content-type:content-disposition:in-reply-to:user-agent;
	b=jddvIlRza3weQtzjEZCIWS/jK8VFNPnXxbC9GVXJb62BFo3xLJhOGDqDJoSGl434Ta
	O0ttyurDbMDdKquchaccsmm98JqbM2PRdMQ7bMAteRLuV9CpSsLMvCUmfpyvUL1j+IXZ
	jASCjTCHpxXB6pW7zN5DDUT+uDtskTlrPdgc0=
Date: Sun, 14 Mar 2010 17:31:05 +0100
From: "fkater@googlemail.com" <fkater@googlemail.com>
To: Dave Crooke <dcrooke@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Deleting bytea, autovacuum, and 8.2/8.4 differences
Message-ID: <20100314163105.GB2153@comppasch2>
References: <20100313211742.GC2178@comppasch2>
	<ca24673e1003131904p1e17e607o17eb67f08df1db30@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: <ca24673e1003131904p1e17e607o17eb67f08df1db30@mail.gmail.com>
User-Agent: Mutt/1.5.20 (2009-06-14)

Hi Dave,

thank you for your answers! Here some comments:

Dave Crooke:

> > * The table just has 5 unused int columns, a timestamp,
> > OIDs, and the bytea column, *no indices*; the bytea storage
> > type is 'extended', the 16 MB are compressed to approx. the
> > half.
> >
> 
> Why no indices?

Simply because the test case had just < 50 rows (deleting
all rows older than 2 minues). Later on I would use indices.


> > while it is planned to have the interval set to 6 hours in
> > the final version (thus creating a FIFO buffer for the
> > latest 6 hours of inserted data; so the FIFO will keep
> > approx.  10.000 rows spanning 160-200 GB data).
> >
> 
> That's not the way to keep a 6 hour rolling buffer ... what you need to do
> is run the delete frequently, with  *interval '6 hours'* in the SQL acting
> as the cutoff.

In fact the delete was run frequently to cut everything
older than 6 hours *immediately*.


> If you really do want to drop the entire table contents before refilling it,
> do a *DROP TABLE* and recreate it.

No, I do not want to drop the whole table.


> > * This deletion SQL command was simply repeatedly executed
> > by pgAdmin while my app kept adding the 16 MB rows.
> >
> 
> Are you sure you are timing the delete, and not pgAdmin re-populating some
> kind of buffer?

Quite sure, yes. Because I launched just the delete command
in pgAdmin while the rest was executed by my application
outside pgAdmin, of course.


> > * Autovacuum is on; I believe I need to keep it on,
> > otherwise I do not free the disk space, right? If I switch
> > it off, the deletion time reduces from the average 10s down
> > to 4s.
> >
> 
> You may be running autovaccum too aggressively, it may be interfering with
> I/O to the tables.

Hm, so would should I change then? I wonder if it helps to
run autovacuum less aggressive if there will not be a
situation were the whole process is stopped for a while. But
I'd like to understand what to change here.


> 8.4 has a lot of performance improvements. It's definitely worth a shot. I'd
> also consider switching to another OS where you can use a 64-bit version of
> PG and a much bigger buffer cache.

O.k., I'll give it a try.


Thank You.
 Felix