RE: Index build speedup -- part2
I had to rebuild a large index on a 36M row table last night. I set the PSORT_NPROCS to 10 (2 * NUM_CPUVPS) and PDQPRIORITY to 10. The index build ran for an hour and a half, then it ran out of temp dbspace! (We currently run with 6 temp spaces that are 50 Mb each on that box.) I set the DBSPACETEMP environment variable to a local filesystem and it created around 300 Mb of flat files and finished in 10 minutes! I was very surprised since the temp dbspaces run on a very fast EMC box. Has anyone else seen similar performance gains by using the local filesystems? Online 7.30.UC3 Sun E3000 10 CPU's 4Gb RAM Solaris 2.5.1 -Scott > -----Original Message----- > From: owner-informix-list@iiug.org > [mailto:owner-informix-list@iiug.org]On Behalf Of Carlson@WHSmith > Sent: Thursday, December 16, 1999 12:47 PM > To: informix-list@iiug.org > Subject: Index build speedup -- part2 > > > I've taken the ideas that you've given and haven't had much success (. . > . yet), but I'd like to try one more thing before I drop this issue > (until next year). > > I've heard that, sometimes, sorting to disk may be faster than sorting > to temp dbspace. I'm familiar with the PSORT_DBTEMP environment > variable, which overrides any DBSPACETEMP onconfig variable setting. > When I set and export it, however, I don't see any disk activity, or any > sort files created on the filesystem at all. Am I missing something > again? > > -- > John Carlson > Informix DBA > WHSmith USA > > #include std_disclaimer.h /* These are my opinions, not my company's > opinion */ >
In article <83duli$ke0$1@news.xmission.com>, "Scott Huppert" <shupp@cfer.com> wrote: > > I had to rebuild a large index on a 36M row table last night. I set the > PSORT_NPROCS to 10 (2 * NUM_CPUVPS) and PDQPRIORITY to 10. The index build > ran for an hour and a half, then it ran out of temp dbspace! (We currently > run with 6 temp spaces that are 50 Mb each on that box.) I set the > DBSPACETEMP environment variable to a local filesystem and it created around > 300 Mb of flat files and finished in 10 minutes! I was very surprised since > the temp dbspaces run on a very fast EMC box. Has anyone else seen similar > performance gains by using the local filesystems? YES! THANK YOU! I have been saying to use PSORT_DBTEMP set to filesystem space for index builds and UPDATE STATISTICS forever! It IS faster. I think this is because it does not purge or thrash the Informix buffer cache as sort-work temp tables in DBSPACETEMP dbspaces would do and so the datapages and index pages are more likely to be resident as the sorting progresses and as index/distribution creation begins. You in effect increased the buffer cache by at least a GB (IB the Solaris default buffer cache is 25% of RAM and you have 4GB RAM). Art S. Kagel > Online 7.30.UC3 > Sun E3000 > 10 CPU's > 4Gb RAM > Solaris 2.5.1 > > -Scott > > > -----Original Message----- > > From: owner-informix-list@iiug.org > > [mailto:owner-informix-list@iiug.org]On Behalf Of Carlson@WHSmith > > Sent: Thursday, December 16, 1999 12:47 PM > > To: informix-list@iiug.org > > Subject: Index build speedup -- part2 > > > > > > I've taken the ideas that you've given and haven't had much success (. . > > . yet), but I'd like to try one more thing before I drop this issue > > (until next year). > > > > I've heard that, sometimes, sorting to disk may be faster than sorting > > to temp dbspace. I'm familiar with the PSORT_DBTEMP environment > > variable, which overrides any DBSPACETEMP onconfig variable setting. > > When I set and export it, however, I don't see any disk activity, or any > > sort files created on the filesystem at all. Am I missing something > > again? > > > > -- > > John Carlson > > Informix DBA > > WHSmith USA > > > > #include std_disclaimer.h /* These are my opinions, not my company's > > opinion */ > > > > Sent via Deja.com http://www.deja.com/ Before you buy.