From: VJK <vjkmail@gmail.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: pgsql-performance@postgresql.org
Subject: Re: Deleting bytea, autovacuum, and 8.2/8.4 differences
Date: Mon, 15 Mar 2010 10:46:05 -0400
Message-ID: <600ad6df1003150746uc73750cqe50fa898cc7c7c4e@mail.gmail.com> (raw)
In-Reply-To: <4B9DFA06020000250002FD52@gw.wicourts.gov>
References: <20100313211742.GC2178@comppasch2>
<600ad6df1003141822u297522dbvf1f7187d0eee803@mail.gmail.com>
<600ad6df1003150654if287c53k301f5763e95a3540@mail.gmail.com>
<4B9DFA06020000250002FD52@gw.wicourts.gov>
Inline:
On Mon, Mar 15, 2010 at 10:12 AM, Kevin Grittner <
Kevin.Grittner@wicourts.gov> wrote:
> VJK <vjkmail@gmail.com> wrote:
>
> > the source 1.9GB (19MB x 100) resulted in 5GB of actual disk 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).
>
> One delete would mark the xmax of the tuple, so that transactions
> without that transaction ID in their visible set would ignore it.
> The next table scan would set hint bits, which would store
> information within the tuple to indicate that the deleting
> transaction successfully committed, then the vacuum would later wake
> up and rewrite the page with the deleted tuples removed.
>
I did not observe any vacuuming activity during the deletion process.
However, even with vacuuming, 4GB of disk IO is rather excessive for
deleting 1.9GB of data.
>
> If you have enough battery backed cache space on a hardware RAID
> controller card, and that cache is configured in write-back mode,
> many of these writes might be combined -- the original delete, the
> hint bit write, and the vacuum might all combine into one physical
> write to disk.
They are combined alright, I see between 170-200 MB/s IO spikes on the iotop
screen which means writes to the cache -- the disk itself is capable of
110(ic)-160(oc) MB/s only, with sequential 1MB block size writes.
What does your disk system look like, exactly?
>
As I wrote before, it's actually a single 15K rpm mirrored pair that you
can look at as a single disk for performance purposes. It is connected
through a PERC6i controller to a Dell 2950.
The disk subsystem is not really important here. What is really
interesting, why so much IO is generated during the deletion process ?
>
> -Kevin
>
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, Kevin.Grittner@wicourts.gov
Subject: Re: Deleting bytea, autovacuum, and 8.2/8.4 differences
In-Reply-To: <600ad6df1003150746uc73750cqe50fa898cc7c7c4e@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