Re: large indexes

David Williams wrote: > > In article <61t3dl$86s@camel19.mindspring.com>, Barry Leb > <barryleb@atl.mindspring.com> writes > >icc@injersey.com wrote: > > > >>When building an index, is there a way to following its progress in > detai? > > > >I can tell you this much as to where we improved performance. Our > >engine is 7.14 running on Sun SparcCenter 2000e (10 cpus), Solaris > >2.4. We used to have 8 gig of tempspace as specified by DBSPACETEMP. > >Building a unique index on a 60 mil row table to appx. 4 1/2 hours. > >We increased tempspace to 14 gig and the index build dropped to 1 1/2 > >hours. We also set PDQRIORITY very high during the index build - to > a number like 90 or 100. > > > It is the number of disks involved which speeds things up as the > build > can be run in parallel on each disk. Have several temporary dbspaces > (created with TEMP=Y), have each one on a separate disk. Then in > your ONCONFIG file set DBSPACETEMP to tempdbs1:tempdbs2:tempdbs3 etc > and restart Online. > > Set PDQPRIORITy to HIGH and set the environment variable> PSORT_NPROCS > to the number of disk or cpus (the lower number) available for index > building. This is because index build involve sorts. Also set the > environment variable PSORT_DBTEMP to the same as DBSPACETEMP in your > ONCONFIG file. > > This should help. Woa! The usage and order of precedence for these parameters follows: 1) PSORT_DBTEMP environment variable is used to specify directory names if you want to store temp files in the file system. 2) DBSPACETEMP environment variable is used to specify temp dbspace names if you want to store temp files in OnLine. 3) DBSPACETEMP configuration variable is used to specify temp dbspace names if you want to store temp files in OnLine. 4) If none of the above are set, temp files are stored in /tmp. Hope that helps, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com FAQ http://www.iiug.org |///// / //| | +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| | Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////| |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| +----------------------+-----------------------------------+-----------+