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.
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@xxxxxxxxxxxxx> 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
Joshua D. Drake wrote:
Christiaan Willemsen wrote:
So, what you are basically saying, is that a single mirror is in general more than enough to facilitate the transaction log.
http://www.commandprompt.com/blogs/joshua_drake/2008/04/is_that_performance_i_smell_ext2_vs_ext3_on_50_spindles_testing_for_postgresql/
http://wiki.postgresql.org/wiki/HP_ProLiant_DL380_G5_Tuning_Guide
And to answer your question, yes. Transaction logs are written sequentially. You do not need a journaled file system and raid 1 is plenty for most if not all work loads.
Sincerely,
Joshua D. Drake
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
