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

In article <54qju7$qge@panix3.panix.com>, Glen Acord <snark@panix.com> writes > >: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. > Correct. >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 - > Depends, remember you have several users on the machine. Whilst one users process may block waiting for I/O. Other users may be submitting disk I/O requests. Also under Online 7 using KAIO (Kernel Asynchronous I/O) a process does not have to block waiting for I/O. >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> > As much as possible. The only real limitation is the number of disks you have. >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. > When performance is too low :->. My general rule is too split as much as possible. >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? > I'd get more CPU's when under heavy load all the CPU's get >90% usage. i.e. the machine runs too slowly and is CPU bound. >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). > Correct. Informix recommends putting your data near the centre of the disks. This reduces disk head movement. >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? > Not quite. Normally you have one read/write head. The disks spins underneath it and it moves side to side to access different tracks. Some REALLY expensive mainframe systems have heads that do not move (one head per track)!. > >On *getting* small disks >The problem I'm having now while optimizing with smaller disks is that >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). > Correct - amazing really. 2Gb drives seem to be the smallest you can get. >Are other people having this same problem of finding hardware vendors >still selling small disk > Yes. >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 saw a post recently that said that Solaris tmpfs filesystem type is really a RAM disk. Having said that a temp dbspace is contained within Online and so Online buffering is used instead. Same applies to dbspaces listed in the DBSPACETEMP enviroment variable. You would get better performance increase Online BUFFERS rather than using a RAM disk. This is because it takes time to execute the UNIX file system code which is not executed when Online buffers are accessed. >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 Depend on the application is use. The only generaly guidelines can be more disks, more RAM , more CPU's. Whenever one becomes the bottleneck get more of it!!. >a first analysis of a proposed system. > -- David Williams