Greg Stark <gsstark@mit.edu> writes:
> Bryan Vest <bvest@bright.net> writes:
>> Currently the database is at a size of 87.6Gig. A Vacuum Analyze runs every
>> night and has been taking 4 or 5 hours to complete. Everything seems to run
>> fine for a while, then at some point the load goes through the roof and the
>> iowait % also goes way up. It will recover after a little bit and then do the
>> same thing all over again.
> While others have pointed out problems with the config I don't think any of
> them explains this irregular behaviour.
As Greg says, it might be checkpoints or a background query. If it
actually is the vacuum itself causing the variation in load, the theory
that comes to mind is that the performance tanks when the vacuum run
switches from find-dead-tuples to clean-indexes mode; clean-indexes is
usually a lot more I/O intensive.
ISTM it actually doesn't matter much which of these explanations is
correct, because all three imply the same thing: not enough disk I/O
bandwidth. The disk is near saturation already and any increase in
demand drives response time over the knee of the curve.
If you are using a RAID configuration it might just be that you need
to adjust the configuration (IIRC, there are some RAID setups that
are not very write-friendly). Otherwise you may have little alternative
but to buy faster disks.
regards, tom lane