From: VJK <vjkmail@gmail.com>
To: Greg Smith <greg@2ndquadrant.com>
To: pgsql-performance@postgresql.org
Subject: Re: Deleting bytea, autovacuum, and 8.2/8.4 differences
Date: Mon, 15 Mar 2010 11:28:15 -0400
Message-ID: <600ad6df1003150828u5540ac3cq5bcf599dc0975469@mail.gmail.com> (raw)
In-Reply-To: <4B9E4762.7060703@2ndquadrant.com>
References: <20100313211742.GC2178@comppasch2>
<600ad6df1003141822u297522dbvf1f7187d0eee803@mail.gmail.com>
<600ad6df1003150654if287c53k301f5763e95a3540@mail.gmail.com>
<4B9E4762.7060703@2ndquadrant.com>
Inline:
On Mon, Mar 15, 2010 at 10:42 AM, Greg Smith <greg@2ndquadrant.com> 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
>
>
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: vjkmail@gmail.com, greg@2ndquadrant.com
Subject: Re: Deleting bytea, autovacuum, and 8.2/8.4 differences
In-Reply-To: <600ad6df1003150828u5540ac3cq5bcf599dc0975469@mail.gmail.com>
* 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