Re: disk I/O problems and Solutions

On Fri, Oct 9, 2009 at 9:45 AM, Alan McKay <alan.mckay@gmail.com> wrote:
> We've just discovered thanks to a new Munin plugin
> http://blogs.amd.co.at/robe/2008/12/graphing-linux-disk-io-statistics-with-munin.html
> that our production DB is completely maxing out in I/O for about a 3
> hour stretch from 6am til 9am
> This is "device utilization" as per the last graph at the above link.

As Flavio mentioned, we really need to know if it's seek limited or
bandwidth limited, but I suspect it's seek limited.  Actual data from
vmstat or sar would be helpful.

Also knowing what kind of raid controller is being used and whether or
not it has a BBU or not would be useful.

And finally, you didn't mention what version of CentOS or PostgreSQL.

> One thing I am thinking of in the immediate term is taking the RAID5 +
> hot spare and converting it to RAID10 with the same amount of storage.
>  Will that perform much better?

Depends on how the array is IO limited.  But in general, RAID10 >
RAID5 in terms of performance.

> So I have 2 thoughts with this 12 disk array.   1 is to fill it up
> with 12 x cheap SATA2 drives and hope that even though the spin-rate
> is a lot slower, that the fact that it has more drives will make it
> perform better.  But somehow I am doubtful about that.   The other
> thought is to bite the bullet and fill it up with 300G SAS drives.

Not a bad idea.  Keep in mind that your 15k drives can seek about
twice as fast as 7200 rpm drives, so you'll probably need close to
twice as many to match performance with the same configuration.

If you're random IO limited, though, RAID5 will only write about as
fast as a single disk (but sometimes a LOT slower!) - a 12-disk RAID10
will write about 6 times faster than a single disk.  So overall, the
12 disk 7.2k RAID10 array should be significantly faster than the 3
disk 15k RAID5 array.

> any thoughts here?  recommendations on what to do with a tight budget?
>  It could be the answer is that I just have to go back to the bean
> counters and tell them we have no choice but to start spending some
> real money.  But on what?  And how do I prove that this is the only
> choice?

It's hard to say without knowing all the information.  One free
possibility would be to move the log data onto the RAID1 from the
RAID5, thus splitting up your database load over all of your disks.
You can do this by moving the pg_xlog folder to the RAID1 array and
symlink it back to your data folder.  Should be able to try this with
just a few seconds of downtime.

-Dave