Re: Indexes on ramdisk

What kind of order of improvement do you need to see?

What period are these number for?  Were they collected over 1 hour, 1 day, 1 month?

How much Cache do you have on the controller?

You can certainly get more speed by adding more disk and possibly by adding more controller RAM/a second controller.  10 disks isn't really that many for a totally kick-ass DB server.  You can acheive more block writes with RAID 10s than with RAID 1s.  Wether it's cost effective is dependant on lots of factors like your chassis and drive enclosures etc. vs SSD.  SSD will be faster, but last I heard was expensive, and I checked a few websites but couldn't get much price info.  Normaly when you can't get price info, thats a bad sign ;).  If you are doing large chunks of writes to a small number of tables, then you might be better off with a single large RAID 10 for your tablespace than with seperate RAID 1s.  If you are writing 5 to 1 more table data than index data, you are hurting yourself by seperating on to multiple RAID 1s instead of a single RAID 10 which could write at 2-3x for the 5, and 2-3x for the 1 and only suffer a single seek penalty but get data onto disk twice to three times as fast (depending how many RAID 1s you join).  Try unseperating RAID 1s, and combine to a RAID 10. for indexes and tablespaces.  The controller will re-sequence your writes/reads to help with effeciency, and dbwriter is there to make things go easier.

You can at least get some idea by doing an iostat and see how many IOs and how much throughput is happening. That will rappidly help determine if you are bound by IOs or by MB/sec.

Worst case I'm wrong, but IMHO it's worth a try.

Alex Turner
NetEconomist