Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id C32EF632D96 for ; Mon, 15 Mar 2010 12:28:30 -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 36287-07 for ; Mon, 15 Mar 2010 15:28:16 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-px0-f136.google.com (mail-px0-f136.google.com [209.85.216.136]) by mail.postgresql.org (Postfix) with ESMTP id 891E86355D7 for ; Mon, 15 Mar 2010 12:28:16 -0300 (ADT) Received: by pxi42 with SMTP id 42so246216pxi.19 for ; Mon, 15 Mar 2010 08:28:15 -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=fizV1h0NRqlKgazNNKR0h8QR5aQfvlMhKR08OxCmaj4=; b=j115rgytgU6aqN2Po/r6OYVyuU3LABs6z9yqGtl86rpFq8Vg2RyUK/sQkPQFuF8mlb Ln9uVXtOwP5W0fllTeG9HQanJTNuaudbA3oC2pY6K2hUFW2LOSDuWe2Jh//ctMmrv60d udUVtUxhI64RS2Qjq0wOfAuBpH5WqRKd+/KVM= 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=vhrD6IPrc/CxKk7eUutKQu+1IMoe4dfxlxb4VjODwJyYMBXUbhpQnIqrRA9VSOCyex lcEBp6AKENUAyhOsHi1HnNUGXMFwZALmvhvmAa1u/O3c7h/Mjuq5a7P6ITH2/5bCFqJj 3otb18TZBXiuqMYUe7zBNJszKNljWoKWqdamA= MIME-Version: 1.0 Received: by 10.142.208.21 with SMTP id f21mr5515248wfg.311.1268666895285; Mon, 15 Mar 2010 08:28:15 -0700 (PDT) In-Reply-To: <4B9E4762.7060703@2ndquadrant.com> References: <20100313211742.GC2178@comppasch2> <600ad6df1003141822u297522dbvf1f7187d0eee803@mail.gmail.com> <600ad6df1003150654if287c53k301f5763e95a3540@mail.gmail.com> <4B9E4762.7060703@2ndquadrant.com> Date: Mon, 15 Mar 2010 11:28:15 -0400 Message-ID: <600ad6df1003150828u5540ac3cq5bcf599dc0975469@mail.gmail.com> Subject: Re: Deleting bytea, autovacuum, and 8.2/8.4 differences From: VJK To: Greg Smith , pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=000e0cd32d2ca1bfb60481d88756 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/147 X-Sequence-Number: 37972 --000e0cd32d2ca1bfb60481d88756 Content-Type: text/plain; charset=ISO-8859-1 Inline: On Mon, Mar 15, 2010 at 10:42 AM, Greg Smith wrote: > VJK wrote: > >> Since Pg does not use the concept of rollback segments, it is unclear why >> deletion produces so much disk IO (4GB). >> > > With PostgreSQL's write-ahead log, MVCC and related commit log, and > transactional DDL features, there's actually even more overhead that can be > involved than a simple rollback segment design when you delete things: > There does not appear to be much WAL activity. Here's the insertion of 100 rows as seen by iotop: 4.39 G 0.00 % 9.78 % postgres: writer process 5.34 G 0.00 % 5.93 % postgres: postgr~0.5.93(1212) idle 27.84 M 0.00 % 1.77 % postgres: wal writer process 144.00 K 0.00 % 0.00 % postgres: stats collector process 0.00 B 0.00 % 0.00 % postgres: autova~ launcher process 0.00 B 0.00 % 0.00 % postgres: postgr~0.5.93(4632) idle .. and the deletion: 288.18 M 0.00 % 37.80 % postgres: writer process 3.41 G 0.00 % 19.76 % postgres: postgr~0.5.93(1212) DELETE 27.27 M 0.00 % 3.18 % postgres: wal writer process 72.00 K 0.00 % 0.03 % postgres: stats collector process 0.00 B 0.00 % 0.00 % postgres: autova~ launcher process 0.00 B 0.00 % 0.00 % postgres: postgr~0.5.93(4632) idle So, the original 1.9 GB of useful data generate about 10GB of IO, 5 of which end up being written to the disk The deletion generates about 3.8 GB of IO all of which results in disk IO. WAL activity is about 27MB in both cases. > > http://www.postgresql.org/docs/current/static/wal.html > http://www.postgresql.org/docs/current/static/mvcc-intro.html > http://wiki.postgresql.org/wiki/Hint_Bits > > http://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis > > I read all of the above, but it does not really clarify why deletion generates so much IO. > One fun thing to try here is to increase shared_buffers and > checkpoint_segments, then see if the total number of writes go down. The > defaults for both are really low, which makes buffer page writes that might > otherwise get combined as local memory changes instead get pushed constantly > to disk. > > -- > Greg Smith 2ndQuadrant US Baltimore, MD > PostgreSQL Training, Services and Support > greg@2ndQuadrant.com www.2ndQuadrant.us > > --000e0cd32d2ca1bfb60481d88756 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Inline:

On Mon, Mar 15, 2010 at 10:42 AM,= Greg Smith <g= reg@2ndquadrant.com> wrote:
VJK wrote:
Since Pg does not use the concept of rollback segments, =A0it is unclear wh= y deletion produces so much disk IO (4GB).

With PostgreSQL's write-ahead log, MVCC and related commit log, and tra= nsactional DDL features, there's actually even more overhead that can b= e involved than a simple rollback segment design when you delete things:

There does not appear to be much WAL activity.=A0 Her= e's the insertion of 100 rows as seen by iotop:
=A0=A0 4.39 G=A0 0.0= 0 %=A0 9.78 % postgres: writer process=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0
= =A0=A0 5.34 G=A0 0.00 %=A0 5.93 % postgres: postgr~0.5.93(1212) idle
=A0 27.84 M=A0 0.00 %=A0 1.77 % postgres: wal writer process=A0=A0=A0=A0=A0= =A0
=A0144.00 K=A0 0.00 %=A0 0.00 % postgres: stats collector process= =A0
=A0=A0 0.00 B=A0 0.00 %=A0 0.00 % postgres: autova~ launcher proces= s
=A0=A0 0.00 B=A0 0.00 %=A0 0.00 % postgres: postgr~0.5.93(4632) idle =
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0


.. and the deletion:
288.18 M= =A0 0.00 % 37.80 % postgres: writer process=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 <= br>=A0 3.41 G=A0 0.00 % 19.76 % postgres: postgr~0.5.93(1212) DELETE
=A027.27 M=A0 0.00 %=A0 3.18 % postgres: wal writer process=A0=A0=A0=A0=A0= =A0
=A072.00 K=A0 0.00 %=A0 0.03 % postgres: stats collector process=A0=
=A0 0.00 B=A0 0.00 %=A0 0.00 % postgres: autova~ launcher process
= =A0 0.00 B=A0 0.00 %=A0 0.00 % postgres: postgr~0.5.93(4632) idle

So, the original 1.9 GB of useful data generate about 10GB of IO, 5 of = which end up being written to the disk=A0 The deletion generates about 3.8 = GB of IO all of which results in disk IO.=A0 WAL activity is about 27MB in = both cases.
=A0

http://www.postgresql.org/docs/current/static/wal.html
http://www.postgresql.org/docs/current/static/mvcc-intro.h= tml
htt= p://wiki.postgresql.org/wiki/Hint_Bits
http://wiki.postgresql.org/wiki/= Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis


I read all of the above, but it does not really c= larify why deletion generates so much IO.
=A0
One fun thing to try here is to increase shared_buffers and checkpoint_segm= ents, then see if the total number of writes go down. =A0The defaults for b= oth are really low, which makes buffer page writes that might otherwise get= combined as local memory changes instead get pushed constantly to disk.
--
Greg Smith =A02ndQuadrant US =A0Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com =A0 www.2ndQuadrant.us


--000e0cd32d2ca1bfb60481d88756--