Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id D22666330EE for ; Sat, 13 Mar 2010 23:05:38 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 51184-02 for ; Sun, 14 Mar 2010 03:05:27 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f216.google.com (mail-bw0-f216.google.com [209.85.218.216]) by mail.postgresql.org (Postfix) with ESMTP id 1CFB463472E for ; Sat, 13 Mar 2010 23:04:43 -0400 (AST) Received: by bwz8 with SMTP id 8so2059145bwz.3 for ; Sat, 13 Mar 2010 19:04:41 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=hVSS6gp0GiniYZiN0AuqF1/3UsuRCTQ2aDmX1DvAy7Q=; b=YUJElqq6Yqy71UHOSZaSaOVWgCN8tANVE9OztbvdsOu5tH+NjY6bpV4s0qR3Vc9PcQ zcCbmX/D4FUP7SeXyBsz9FcXShfv6AqcSC06OZr19dONTTvYXQqiYmLSHWA5roRVMs8U 1JaBCUlSoKDxN5s1nRMRjnLrz9meaga4BK+H8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=JSSHVbH5e0qyxZRPjiNINDO3/1lvGOVioHtRMdx7IozpDMvAbYhDsh5DTOCDoZhv0I C5ZO4x44xgYml8/kktJvHxKx8qdde0yww/nPttp2ZJciweWfThDRSrB/g5bJuv7Nv1xL B/12eKRvztaIcsiMJWHve3TbDqFOrQ5BFulVg= MIME-Version: 1.0 Received: by 10.204.141.69 with SMTP id l5mr1446042bku.64.1268535881654; Sat, 13 Mar 2010 19:04:41 -0800 (PST) In-Reply-To: <20100313211742.GC2178@comppasch2> References: <20100313211742.GC2178@comppasch2> Date: Sat, 13 Mar 2010 21:04:41 -0600 Message-ID: Subject: Re: Deleting bytea, autovacuum, and 8.2/8.4 differences From: Dave Crooke To: "fkater@googlemail.com" Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=00151747927a9c63340481ba0604 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/132 X-Sequence-Number: 37957 --00151747927a9c63340481ba0604 Content-Type: text/plain; charset=ISO-8859-1 Hi there I'm not an expert on PG's "toast" system, but a couple of thoughts inline below. Cheers Dave On Sat, Mar 13, 2010 at 3:17 PM, fkater@googlemail.com < fkater@googlemail.com> wrote: > Hi all, > > my posting on 2010-01-14 about the performance when writing > bytea to disk caused a longer discussion. While the fact > still holds that the overall postgresql write performance is > roughly 25% of the serial I/O disk performance this was > compensated for my special use case here by doing some other > non-postgresql related things in parallel. > > Now I cannot optimize my processes any further, however, now > I am facing another quite unexpected performance issue: > Deleting rows from my simple table (with the bytea column) > having 16 MB data each, takes roughly as long as writing > them! > > Little more detail: > > * 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? > > * All the usual optimizations are done to reach better > write through (pg_xlog on another disk, much tweaks to the > server conf etc), however, this does not matter here, since > not the absolute performance is of interest here but the > fact that deleting roughly takes 100% of the writing time. > > * I need to write 15 rows of 16 MB each to disk in a maximum > time of 15 s, which is performed here in roughly 10 seconds, > however, now I am facing the problem that keeping my > database tidy (deleting rows) takes another 5-15 s (10s on > average), so my process exceeds the maximum time of 15s for > about 5s. > > * Right now I am deleting like this: > > DELETE FROM table WHERE (CURRENT_TIMESTAMP - > my_timestamp_column) > interval '2 minutes'; > You *need* an index on my_timestamp_column > > 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. If you really do want to drop the entire table contents before refilling it, do a *DROP TABLE* and recreate it. > * 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? > > * 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. Postgres vacuuming does not free disk space (in the sense of returning it to the OS), it removes old versions of rows that have been UPDATEd or DELETEd and makes that space in the table file available for new writes. > * I am using server + libpq version 8.2.4, currently on > WinXP. Will an upgrade to 8.4 help 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. > Do you have any other ideas to help me out? > Oh, please... > > Thank You > Felix > > > > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > --00151747927a9c63340481ba0604 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi there

I'm not an expert on PG's "toast" system,= but a couple of thoughts inline below.

Cheers
Dave

On Sat, Mar 13, 2010 at 3:17 PM, fkater@googlemail.com <fkater@googlemail.com> wrot= e:
Hi all,

my posting on 2010-01-14 about the performance when writing
bytea to disk caused a longer discussion. While the fact
still holds that the overall postgresql write performance is
roughly 25% of the serial I/O disk performance this was
compensated for my special use case here by doing some other
non-postgresql related things in parallel.

Now I cannot optimize my processes any further, however, now
I am facing another quite unexpected performance issue:
Deleting rows from my simple table (with the bytea column)
having 16 MB data each, takes roughly as long as writing
them!

Little more detail:

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

* All the usual optimizations are done to reach better
write through (pg_xlog on another disk, much tweaks to the
server conf etc), however, this does not matter here, since
not the absolute performance is of interest here but the
fact that deleting roughly takes 100% of the writing time.

* I need to write 15 rows of 16 MB each to disk in a maximum
time of 15 s, which is performed here in roughly 10 seconds,
however, now I am facing the problem that keeping my
database tidy (deleting rows) takes another 5-15 s (10s on
average), so my process exceeds the maximum time of 15s for
about 5s.

* Right now I am deleting like this:

DELETE FROM table WHERE (CURRENT_TIMESTAMP -
my_timestamp_column) > interval '2 minutes';

You need an index on my_timestamp_column
=A0

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. =A010.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=A0 interval '6 hours'= in the SQL acting as the cutoff.

If you really do want to drop the entire table contents before refillin= g it, do a DROP TABLE and recreate it.


* 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 som= e kind of buffer?
=A0

* 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 aggressiv= ely, it may be interfering with I/O to the tables.

Postgres vacuumi= ng does not free disk space (in the sense of returning it to the OS), it re= moves old versions of rows that have been UPDATEd or DELETEd and makes that= space in the table file available for new writes.


* I am using server + libpq version 8.2.4, currently on
WinXP. Will an upgrade to 8.4 help 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 o= f PG and a much bigger buffer cache.


Do you have any other ideas to help me out?
Oh, please...

Thank You
=A0Felix





--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

--00151747927a9c63340481ba0604--