Indexes will be random write workload, but these won't by synchronous writes and will be buffered by the raid controller's cache. Assuming you're using a hardware raid controller that is, and one that doesn't have major performance problems on your platform. Which brings those questions up --- what is your RAID card and OS?
For reads, if your shared_buffers is large enough, your heavily used indexes won't likely go to disk much at all.
A good raid controller will typically help distribute the workload effectively on a large array.
You probably want a simple 2 disk mirror or 4 disks in raid 10 for your OS + xlog, and the rest for data + indexes -- with hot spares IF your card supports them.
The biggest risk to splitting up data and indexes is that you don't know how much I/O each needs relative to each other, and if this isn't a relatively constant ratio you will have one subset busy while the other subset is idle.
Unless you have extensively profiled your disk activity into index and data subsets and know roughly what the optimal ratio is, its probably going to cause more problems than it fixes.
Furthermore, if this ratio changes at all, its a maintenance nightmare. How much each would need in a perfect world is application dependant, so there can be no general recommendation other than: don't do it.
On Thu, Aug 21, 2008 at 1:34 AM, Christiaan Willemsen
<cwillemsen@technocon.com> wrote:
Thanks Joshua,
So what about putting the indexes on a separate array? Since we do a lot of inserts indexes are going to be worked on a lot of the time.
Regards,
Christiaan