Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id D2A9B643635 for ; Mon, 15 Mar 2010 10:54:25 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 20531-03 for ; Mon, 15 Mar 2010 13:54:06 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-yw0-f187.google.com (mail-yw0-f187.google.com [209.85.211.187]) by mail.postgresql.org (Postfix) with ESMTP id D3196643657 for ; Mon, 15 Mar 2010 10:54:14 -0300 (ADT) Received: by ywh17 with SMTP id 17so1733545ywh.20 for ; Mon, 15 Mar 2010 06:54:13 -0700 (PDT) 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:content-type; bh=YgcGNiW0e0MrN1tex4HH2cCWERajO0sQH0r1FIjlNbY=; b=XWpzO4OC4LJO05JoxqBOjv7oLjtJwLp8NwHm81DAxng51Yer5pB4tc4lq0F4RTgEpw GDrPpZna+q+UzUBc2tWF1JyZKN+abrhBd6aK8iYCxlJzTmoaDDauahuOz2HrCoIm2Ht/ k8mavNaLPPc8qzzjF0TvOK4esaqFKW/czMamg= 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 :content-type; b=WZRgMxbk6BsZfNUJIsaGHXbGVV8OdI9nZDbtYMGZOwFzneXab9CRwiyMfTqhO+oN7B JUdOhcCit9RbWN7C6sm8GFM+wfHBxW0tRsUycWQSIJcQ7GOGcAdTLWJxfS8Z+q5jFn0+ fsl6lrf1y7V6hvKxrUiRmjFWyTfZrBOEBnG7o= MIME-Version: 1.0 Received: by 10.150.252.8 with SMTP id z8mr3210011ybh.232.1268661253099; Mon, 15 Mar 2010 06:54:13 -0700 (PDT) In-Reply-To: <600ad6df1003141822u297522dbvf1f7187d0eee803@mail.gmail.com> References: <20100313211742.GC2178@comppasch2> <600ad6df1003141822u297522dbvf1f7187d0eee803@mail.gmail.com> Date: Mon, 15 Mar 2010 09:54:13 -0400 Message-ID: <600ad6df1003150654if287c53k301f5763e95a3540@mail.gmail.com> Subject: Re: Deleting bytea, autovacuum, and 8.2/8.4 differences From: VJK To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=000e0cd70e2454d2370481d73799 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/140 X-Sequence-Number: 37965 --000e0cd70e2454d2370481d73799 Content-Type: text/plain; charset=ISO-8859-1 A quick test: ----- 1. create table x1(x int, y bytea); 2. Load some data say with python: cp /opt/java/src.zip ~/tmp/a.dat (19MB) ## import psycopg2 conn = psycopg2.connect("dbname='test' user='*****' password='****' host='127.0.0.1'"); conn.cursor().execute("INSERT INTO x1 VALUES (1, %s)", (psycopg2.Binary(open("a.dat").read()),)) conn.commit() ## 3. create table x2(x int, y bytea); 4. Copy table x1 100 times to x2 (1.9GB) and monitor/measure IO: insert into x2 select a x, y from generate_series(1,100) a, x1; Results: ----------- On Linux 2.6.32 with an ext3 file system on one 15K rpm disk, we saw with SystemTap that the source 1.9GB (19MB x 100) resulted in 5GB of actual disk IO and took 61 seconds (52 CPU + 9 sleep/wait for IO) Deletion (delete from x2) took 32 seconds with 12 seconds CPU and 20 sec sleep + wait for IO. Actual disk IO was about 4GB. Since Pg does not use the concept of rollback segments, it is unclear why deletion produces so much disk IO (4GB). VJ On Sat, Mar 13, 2010 at 5: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. > > * 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'; > > 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). > > * This deletion SQL command was simply repeatedly executed > by pgAdmin while my app kept adding the 16 MB rows. > > * 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. > > * I am using server + libpq version 8.2.4, currently on > WinXP. Will an upgrade to 8.4 help here? > > 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 > --000e0cd70e2454d2370481d73799 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
A quick test:
-----
1. create table x1= (x int, y bytea);
2. Load some data=A0 say with python:
cp /opt/java/= src.zip ~/tmp/a.dat (19MB)
##
import psycopg2
conn =3D psycopg2.co= nnect("dbname=3D'test' user=3D'*****' password=3D'= ****' host=3D'127.0.0.1'");
conn.cursor().execute("INSERT INTO x1 VALUES (1, %s)", (psycopg2.= Binary(open("a.dat").read()),))
conn.commit()
##
3. crea= te table x2(x int, y bytea);
4. Copy table x1 100 times to x2 (1.9GB) an= d monitor/measure IO:
insert into x2 select a x, y from generate_series(1,100) a, x1;

Resu= lts:
-----------
On Linux 2.6.32 with an ext3 file system on one 15K = rpm disk, we saw with SystemTap that the source 1.9GB=A0 (19MB x 100) resul= ted in 5GB of actual disk IO and took 61 seconds (52 CPU + 9 sleep/wait for= IO)

Deletion (delete from x2) took 32 seconds with 12 seconds CPU and 20 se= c sleep + wait for IO. Actual disk IO was about 4GB.

Since Pg does n= ot use the concept of rollback segments,=A0 it is unclear why deletion prod= uces so much disk IO (4GB).

VJ



On Sat, = Mar 13, 2010 at 5: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.

* 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';

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).

* This deletion SQL command was simply repeatedly executed
by pgAdmin while my app kept adding the 16 MB rows.

* 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.

* I am using server + libpq version 8.2.4, currently on
WinXP. Will an upgrade to 8.4 help here?

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


--000e0cd70e2454d2370481d73799--