Re: Create index very slow

>I'm running a create index statement and its been busy for the last 7 >hours. The table's got 55 million rows (spread across 3 locations by >expression). rtpm/top/... shows my one CPU (of six) at 100% >continuously, while the others are idle. I've got affinity and all that >jazz on. My onconfig follows. > >Any insights to help me explain the delay to my boss appreciated. Tell him to buy a faster machine. ;-) >ROOTSIZE 65526 # Size of root dbspace (Kbytes) Refreshingly small. :-) ># Shared Memory Parameters > >LOCKS 50000 # Maximum number of locks That seems very low. >NUMAIOVPS 32 # Number of IO vps What about kernel AIO? ># Read Ahead Variables >#RA_PAGES # Number of pages to attempt to read ahead >RA_PAGES 128 # Number of pages to attempt to readahead >RA_THRESHOLD # Number of pages left before nextgroup Shouldn't you set something here? Not relevant to this, I know... :-) ># DBSPACETEMP: ># OnLine equivalent of DBTEMP for SE. This is the list of dbspaces ># that the OnLine SQL Engine will use to create temp tables etc. ># If specified it must be a colon separated list of dbspaces that exist ># when the OnLine system is brought online. If not specified, or if ># all dbspaces specified are invalid, various ad hoc queries will create ># temporary files in /tmp instead. > >DBSPACETEMP itemp:temp2 I'd go for at least 3. ># Parallel Database Queries (pdq) >PDQPRIORITY 0 >#MAX_PDQPRIORITY 100 # Maximum allowed pdqpriority >MAX_PDQPRIORITY 0 # Maximum allowed pdqpriority >DS_MAX_QUERIES # Maximum number of decision supportqueries >DS_TOTAL_MEMORY # Decision support memory (Kbytes) >DS_MAX_SCANS 1048576 # Maximum number of decision supportscans And this is where I think it's hitting the fan, so to speak. You can improve index build time greatly by allowing some PDQ ability and setting PSORT_NPROCS to make use of parallel CPUs. I suspect that because you've _totally_ disabled PDQ, you aren't making use of PDQ in building the index. (Safe guess, really! :) So next time, set MAX_PDQPRIORITY to 100 and allocate some DS memory. Then when you build the index, set the PSORT_NPROCS environment variable to at least 2 and PDQPRIORITY greater than 0, and your index build time should improve substantially. HTH. Get Your Private, Free Email at http://www.hotmail.com

Aww don't take advice from a clown. Especially this one. (only if he's drunk can you trust him ;-) Seriously, does MAX_PDQPRIORITY follow standard UNIX conventions? The lower the number, the higher the priority? Or should I give up sniffing glue? -Mikey Obnoxio The Clown wrote: > >I'm running a create index statement and its been busy for the last 7 > >hours. The table's got 55 million rows (spread across 3 locations by > >expression). rtpm/top/... shows my one CPU (of six) at 100% > >continuously, while the others are idle. I've got affinity and all that > >jazz on. My onconfig follows. > > > >Any insights to help me explain the delay to my boss appreciated. > > Tell him to buy a faster machine. ;-) > > > [SNIP] > ># Parallel Database Queries (pdq) > >PDQPRIORITY 0 > >#MAX_PDQPRIORITY 100 # Maximum allowed pdqpriority > >MAX_PDQPRIORITY 0 # Maximum allowed pdqpriority > >DS_MAX_QUERIES # Maximum number of decision support> queries > >DS_TOTAL_MEMORY # Decision support memory (Kbytes) > >DS_MAX_SCANS 1048576 # Maximum number of decision support> scans > > And this is where I think it's hitting the fan, so to speak. You can > improve index build time greatly by allowing some PDQ ability and > setting PSORT_NPROCS to make use of parallel CPUs. I suspect that > because you've _totally_ disabled PDQ, you aren't making use of PDQ in > building the index. (Safe guess, really! :) > > So next time, set MAX_PDQPRIORITY to 100 and allocate some DS memory. > Then when you build the index, set the PSORT_NPROCS environment variable > to at least 2 and PDQPRIORITY greater than 0, and your index build time > should improve substantially. > > HTH. > Get Your Private, Free Email at http://www.hotmail.com