Re: FW: Queries becoming slow under heavy load

On Wed, Jan 26, 2011 at 9:04 AM, Anne Rosset <arosset(at)collab(dot)net> wrote:

<HUGE LIST OF SETTINGS DELETED>

PLEASE post just the settings you changed. I'm not searching through
a list that big for the interesting bits.

> Today we did more analysis and observed  postgress processes that
> continually reported status 'D' in top.

Full stop. The most likely problem here is that the query is now
hitting the disks and waiting. If you have 1 disk and two users, the
access speed will drop by factors, usually much higher than 2.

To put it very simply, you need as many mirror pairs in your RAID-10
or as many disks in your RAID5 or RAID 6 as you have users reading the
disk drives. If you're writing you need more and more disks too.
Mediating this issue we find things like SSD cache in ZFS or battery
backed RAID controllers. They allow the reads and writes to be
streamlined quite a bit to the spinning disks, making it appear the
RAID array underneath it was much faster, had better access, and all
the sectors were near each other. To an extent.

If you have the answer to the previous poster's question "can you tell
us what sort of IO you have (sata, scsi, raid, # of disks, etc)." you
should provide it. If you've got a pair of 5k RPM SATA drives in a
RAID-1 you might need more hardware.

So, instead of just settings, show us a few carefully selected lines
of output from vmstat or iostat while this is happening. Don't tell
us what you see, show us.