Inline: On Mon, Mar 15, 2010 at 10:12 AM, Kevin Grittner < kevin.gritt...@wicourts.gov> wrote:
> VJK <vjkm...@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 >
