Re: Is there a benefit in having indexing on separate spindles

:In article <54k48a$9h2@cssun.mathcs.emory.edu>, Peter Tashkoff :<TASHKOP@kiwi.co.nz> writes :> <snip> :> < tired DBA - diff spindles for indices...> :> < wants opinions...> David Williams <djw@smooth1.demon.co.uk> replied: : <great set of examples of splitting out disks...> : : Notice how fairly rapidly you need lots and lots of small disks! : Also by the time you reach step 7. you probably have > 20 disks!! : This is the same conclusion I've reached after talking with numerous people, reading up, etc... The more you split out high activity disk segments for I/O the more the performance gain. The accepted wisdom is that disk I/O is the greatest bottleneck. On splitting disks... -------------------------------------------------------------------- Of course there are a few hitches. My guess is that splitting out disks is a minimal gain with a small number of cpus. My first query would be - With (1) cpu, how much splitting would be optimal? <Guess #1: is that it would still be beneficial to seperate rootdbs, log, tempdbs and the datadbs - thus 4 disks> With (2-4) cpus, how much splitting? <Guess #2: add to above, split out logical from physical log, split the logical onto 2 disks, possibly begin splitting high activity tables - thus apx ~7-8> I'd still like to get an idea of the metric invoved in determining when to start splitting out disks. For a single, small database (< 500 MB) on a single cpu, would you still want to split (as Guess #1). At what size/usage do you want to start considering more splitting or getting more cpus? On the physical device... --------------------------------------------------------------------- I've also read some performance info that states that certain parts of the disk are faster (tracks that are closer to the center are read faster). Guess #3: Thus I could see a scheme for optimizing a set of say 3 disk for dividing it into 3 chunks each. A heavily used dbspace could be put on the first chunk of each disk. I think I also saw something about the middle of the disk being optimal. Comments? Another rumour: My conception of disks was that all the read heads moved together when accessing the tracks on the disk. I've been told that some disk makers have disks that can move the read head *independantly* (at least in sets) from each other. Has anyone heard of this? On *getting* small disks ---------------------------------------------------------------------- The problem I'm having now while optimizing with smaller disks is that no one seems to make small disks anymore! The standard size seems to be about 2GB. It makes partitioning out the disk into seperate chunks (Guess 3) a less optimal solution than having smaller disks with a single chunk on each disk (I beleive this is the Informix recommended method). Are other people having this same problem of finding hardware vendors still selling small disks? RAM is good ----------------------------------------------------------------------- Heavy I/O bad, RAM (cache, etc..) good or at least thats seems to be the accepted wisdom. With that in mind, has anyone heard of the possiblity of using a RAM disk for the temp dbspace? I know RAM is cheap and if you can get it - why not use it? I'd like to figure out what kind of performance gain you can get (assuming there *is* such a solution). Since I havn't heard of anything on an Informix level (the system managing the temp db space in RAM) would it be possible to just cook a file on a ram disk each time the server started? I can see problems here already... OTOH, I think the temp space can be set to a file system dir anyway (like /tmp). Any thoughts on this? Off hand ------------------------------------------------------------------------ I think these topics should make it into the FAQ. Perhaps there not *Frequently* asked, but there some of the more interesting 8) I think it would also be a useful thing to come up with a base level matrix for performance tuning. IOW - at what point do you *need* to improve disk/memory/cpus... Even general guidelines would be good for a first analysis of a proposed system. -- ...swiftly and silently he vanished away... Glen Acord - snark@panix.com