Re: Deleting bytea, autovacuum, and 8.2/8.4 differences

Inline:

On Mon, Mar 15, 2010 at 10:12 AM, Kevin Grittner <
Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:

> VJK <vjkmail(at)gmail(dot)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
>