Re: large indexes

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? > >>Periodically we must build indexes on very large tables (+3.5 gigs, >>fragmented). This takes hours. Sometimes it takes so long, that we become >>suspicious and break the process before the index is completed. However, >>when we do this we are in the dark. We don't know if something is >>"wrong", or if the index is 90% done. oncheck -pe gives us some >>information about the dbspace in qestion (oh, I forget to mention that >>the indexes are always built in their own dedicated dbspaces), but its >>obviouly not up-to-date or complete. Got any suggestions? > >>Also, it appears that while an index is being built on a table the table >>is exclusively locked and therefore unavailable to the users. Is this >>just a fact of life or does anyone (a) know a way aroundt this or (b) It is just a fact of life. >>know how to speed up the buidling of indexes. I'll throw in a last >>question. Is it worth fragmenting indexes, and if so, under what >>circumstances? By fragmenting I mean the same as fragmenting data. I >>don't mean letting the index be fragmented with the data, but instead >>into its own dedicated fragment > >>Oh some background information. The tables in questions have a row size of >>appromixately 650 bytes (please don't ask me why the table is so large), >>and 35 million rows. Each table has four indexes ranging from 8 bytes >>(excluing overhead) to 29 bytes (excluding overhead). > >We have very similar large tables, and we fragment tables and indexes >in their own dbspaces. However, there is some info you didn't provide, >such as your version of Informix, the platform, what type of disks, >raw vs cooked, RAID level, striping, etc. > >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. >I don't know if this will help your situation, but it might be a >start. > >Barry Leb > >National Linen Service >1420 Peachtree Street >MS #314 >Atlanta, GA 30309 > >(404) 853-6119 >(404) 853-6485 fax >e-mail: barryleb@mindspring.com > -- David Williams