Re: VACUUM degrades performance significantly. Database becomes unusable!

"Stephen" <jleelim(at)xxxxxx(dot)com> writes:
> Is it normal for plain VACUUM on large table to degrade performance by over
> 9 times? My database becomes unusable when VACUUM runs. From reading
> newsgroups, I thought VACUUM should only slow down by 10% to 15%.

We have heard reports of very significant slowdowns from people who were
already nearly saturating their disk I/O bandwidth, and then VACUUM
pushed their systems over the knee of the response curve. You haven't
said anything about what was happening in your system besides the VACUUM
and the test query, but I wonder how busy the disk drive was already.

Another possibility is that you've got the disk subsystem misconfigured
somehow, although your hdparm output looks okay to my not-expert eyes.

Possibly you should think about SCSI instead of IDE disk drives.
Consumer-grade IDE drives seem to be not very good about servicing
parallel loads. As an example, running "pgbench" with 10 client threads
on a fairly new Dell PC (P4, some IDE drive or other), I see about a 3x
slowdown in reported transactions-per-second when a background VACUUM
is running. The same test on my trusty old built-like-a-tank HP server
(with fast-for-its-time SCSI drives) shows only a 20% slowdown. I
attribute this to the SCSI drive being better able to handle concurrent
requests.

regards, tom lane