Re: vacuuming slow

Joe Maldonado <jmaldonado(at)webehosting(dot)biz> writes:
> iostat shows that without vacuum (on SCSI Raid1), our application
> is not using much I/O on an avg (although there is bursty I/O)

> Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s
> avgrq-sz avgqu-sz
> /dev/sda 0.00 1.60 0.00 1.20 0.00 22.40 0.00 11.20
> 18.67 1.97

> await svctm %util
> 164.17 42.50 5.10

> and with vacuum

> Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s
> avgrq-sz avgqu-sz
> /dev/sda 0.20 96.20 0.30 90.70 4.00 1468.80 2.00 734.40
> 16.18 3050.21

> await svctm %util
> 3244.55 10.99 100.00

OK, so sure enough vacuum is saturating your disk.

> 1. Does Checkpointing write the pages that have been vacuumed or does
> vacuum not affect Checkpointing I/O ?

The checkpointer would be sharing in the burden, yes. Vacuum isn't all
that much different from normal updates in how the changes get written
out --- it just tends to make a lot more updates than typical queries
do.

I notice that the write volume vastly exceeds the read volume. If
that's consistently true, it suggests that you might get some win
by doing vacuums more often --- that is, you are leaving too much
work to be done by any one vacuum scan.

> 2. Since vacuum in 7. 4 capable of disrupting disk latency (although
> disk bandwidth is prob only 40% used) so much, given that it is in D
> state most of the time, is rewriting the application to use temp
> tables, truncate etc so as to avoid vacuum a good idea ?

Short of buying faster disks or moving to 8.0, I think that's your next
option.

Note that 8.0's vacuum-cost-delay feature doesn't reduce the I/O volume
at all, it just spreads out the impact. So the vacuum will take longer
than it does now, but hopefully it won't kill the performance of
concurrent queries. Combining that with launching vacuums more
frequently is probably the best hope at solving this without better
hardware or massive application changes.

regards, tom lane