Re: temp dbspace

Rein Puksand wrote: > > Hi, > > On Tue, 25 Nov 2003 Art S. Kagel wrote: > > >> DBSPACETEMP temp_dbs # Default temp dbspaces > > Most sequential scans result in a sort to satisfy an ORDER BY or GROUP BY > clause so if you have three or more temp > dbspaces > listed above (or if you set the PSORT_NPROCS=12 (or more) and > PSORT_DBTEMP=<list of 3-6 filesystems> in the > environment) the improved sort speed can dramatically improve user > performance perceptions. > > Also it's a good idea to list at least one 'normal' (not temp) dbspace in > DBSPACETEMP so that logged temp tables > have a place to go instead of the ROOTDB space which is critical to > performance. List a low load dbspace, > preferably on a different disk structure than the ROOTDB, logical & > physical logs and other high activity chunks, > that is NOT a temp space for this. > > WHY using three or more temp dbspace improve performance ? Has to do with the way IDS merges sort-work files/temptables. It will create them alternating between two temp dbspaces (or PSORT_DBTEMP filesystem) and then merge them pairwise into result temp files/tables in a third and/or fourth temp dbspace (or PSORT_DBTEMP filesystem). By allocating at least 3 and as many as 6 temp dbspaces and/or PSORT_DBTEMP filesystems you minimize reading and writing to the same device during the sort. If you can get away with putting each of these onto independent drive structures and controllers even better. Obviously the full blown version of this with six temp spaces and/or temp file six filesystems on six different structures accessed by six independent controllers is extreme and can only happen on the largest systems one might set up, but whatever you can do to get close to that ideal will improve performance. Remember that 3-4 WIDE SCSI-III drives can swamp the total throughput of a single controller even though the controller can handle 15 drives! SO to be able to get maximum throughput from six drives you need at least two controllers! (And don't be fooled by Fibre Channel controllers using GB pipelines, read their specs, actual data throughput per channel is no better than wide SCSI-III! The GB pipeline just means you can connect hundreds of these channels/controllers to a single fiber loop, not that one channel can pass GB of data per second.) Art S. Kagel