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
Date: Sun, 14 Mar 2010 17:31:05 +0100
Message-ID: <20100314163105.GB2153@comppasch2> (raw)
In-Reply-To: <ca24673e1003131904p1e17e607o17eb67f08df1db30@mail.gmail.com>
References: <20100313211742.GC2178@comppasch2>
<ca24673e1003131904p1e17e607o17eb67f08df1db30@mail.gmail.com>
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
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: fkater@googlemail.com, dcrooke@gmail.com
Subject: Re: Deleting bytea, autovacuum, and 8.2/8.4 differences
In-Reply-To: <20100314163105.GB2153@comppasch2>
* 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