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