instance and disk layout

Hi, I am currently building a new production server and would like some recommendations on disk layout. We will be using RAID 1+0. I have two instances - the first has one database in it and the second has two databases in it. I have always kept data and indexes in a separate dbspaces but usually on the same disk - would it be wise or give me better performance if I was to separate them out onto separate disks as well (so separate dbspaces on separate disks)? Also I currently have a small temp space on each disk (250mb) - should I duplicate this for the second instance but in reverse order (ie temp01 is on disk 10 temp02 is on disk 09) or does it make no difference as the engine will choose what temp space to write to (while we are on this subject I don't fully understand how the engine chooses the temp space and whether having all these small temps spaces will make it run in parallel.). Lastly should I have my logs (logical and physical) in separate dbspaces on separate disks? thanks for all your ideas regards Bridget

Bridget Reitsma wrote: > > Hi, Hi. > I am currently building a new production server and would like some > recommendations on disk layout. We will be using RAID 1+0. I have two > instances - the first has one database in it and the second has two > databases in it. Yea! > I have always kept data and indexes in a separate dbspaces but usually > on the same disk - would it be wise or give me better performance if I > was to separate them out onto separate disks as well (so separate > dbspaces on separate disks)? It depends total on the number of disks in the RAID set, whether the mirrors are on separate controllers from their primaries, the peak work load volume and its nature. If most queries will hit the indexes only (KEY-ONLY searches) or will involve table scans or hash joins it does not matter, for example. Likewise a wider stripe (more mirrored pairs) will handle a greater load than a narrower one as will the same width array using more controllers. Figure the volume and calculate the capacity of your array(s) and see if you can handle the load on one array or need two. > Also I currently have a small temp space on each disk (250mb) - should I > duplicate this for the second instance but in reverse order (ie temp01 > is on disk 10 temp02 is on disk 09) or does it make no difference as the > engine will choose what temp space to write to (while we are on this > subject I don't fully understand how the engine chooses the temp space > and whether having all these small temps spaces will make it run in > parallel.). Depends on version. 7.1x & 7.2x alternated writing to the temp spaces that qualify (ie logged temp tables to regular dbspaces in DBSPACETEMP non-logged temp tables to temporary dbspaces listed there) but each temp table was written to only one of the dbspaces listed. Versions 7.3x and 9.2x fragment all temp tables across all qualifying dbspaces listed in DBSPACETEMP so that each is written evenly and the load is spread better. Because of this round-robin fragmentation revering the order of the tempspaces between the two instances will not matter. However, note that for sorting purposes it seems to be best to have at least three temp dbspaces to avoid contention between reading and writing threads during a sort, this may be better in 7.3x (I have not tested recently) but I suspect it has not changed. > Lastly should I have my logs (logical and physical) in separate dbspaces > on separate disks? Unless you have a VERY wide very fast array (say you striped four five pair RAID10 arrays to create a HUGE checkerboard array with twenty drive pairs using eight Fiber controllers at 160MB/s), then yes, you should separate the ROOTDBS, logical logs, and physical logs onto three different 'drives'. > thanks for all your ideas > > regards Bridget You're welcome. Art S. Kagel