Index build speedup

OS: HPUX 10.20 IFX: IDS 7.30.ucX HDWE: HP G70 (2-way box) I'm in the process of tightening up various aspects of my reorg scripts (dbspace level). I've been able to use HPL as necessary, so unloads (and probably loads) will run in a more optimum fashion. One of the more time-consuming aspects of the reload process is the index build. I've seen various recommendations concerning PDQPRIORITY, etc. Here's a snippet of what I used: Test1: I verified that PSORT_NPROCS was set to 4. When this benchmark began, the system allocated 4 segments of shared memory (8M each). Total run time was 1:13:29. Test2: I increased SHMVIRTSIZE by 36M, bounced the system and reran. I saved 3 minutes. Test3: I set PDQPRIORITY to 100. My ONCONFIG variables were: MAX_PDQPRORITY 100 All of the DS_* variables were set to default. It took two hours to create two of the seven indices. Even though I've heard that PDQ will speed up an index build, I haven't seen it in this test system. Since others have had a noticeable change (for the better), I must have missed something. Any ideas? Thanks in advance John Carlson Informix DBA WHSmith USA

I set PSORT_NPROCS = 2 x NUMCPUVPS (ie 40) and PDQPRIORITY = 10 and seem to get excellent index build times. I don't know if that is optimal but it's damn quick. Art S. Kagel "Carlson@WHSmith" wrote: > > OS: HPUX 10.20 > IFX: IDS 7.30.ucX > HDWE: HP G70 (2-way box) > > I'm in the process of tightening up various aspects of my reorg scripts > (dbspace level). I've been able to use HPL as necessary, so unloads > (and probably loads) will run in a more optimum fashion. One of the > more time-consuming aspects of the reload process is the index build. > > I've seen various recommendations concerning PDQPRIORITY, etc. Here's a > snippet of what I used: > > Test1: > I verified that PSORT_NPROCS was set to 4. When this benchmark began, > the system allocated 4 segments of shared memory (8M each). Total run > time was 1:13:29. > > Test2: > I increased SHMVIRTSIZE by 36M, bounced the system and reran. I saved 3 > minutes. > > Test3: > I set PDQPRIORITY to 100. My ONCONFIG variables were: > MAX_PDQPRORITY 100 > All of the DS_* variables were set to default. > It took two hours to create two of the seven indices. > > Even though I've heard that PDQ will speed up an index build, I haven't > seen it in this test system. Since others have had a noticeable change > (for the better), I must have missed something. Any ideas? > > Thanks in advance > > John Carlson > Informix DBA > WHSmith USA

PDQ performance is also dependent on the value that you set DS_TOTAL_MEMORY to. If it is set too small, then you will not have as much memory allocated for the various phases of the command. Carlson@WHSmith wrote: > OS: HPUX 10.20 > IFX: IDS 7.30.ucX > HDWE: HP G70 (2-way box) > > I'm in the process of tightening up various aspects of my reorg scripts > (dbspace level). I've been able to use HPL as necessary, so unloads > (and probably loads) will run in a more optimum fashion. One of the > more time-consuming aspects of the reload process is the index build. > > I've seen various recommendations concerning PDQPRIORITY, etc. Here's a > snippet of what I used: > > Test1: > I verified that PSORT_NPROCS was set to 4. When this benchmark began, > the system allocated 4 segments of shared memory (8M each). Total run > time was 1:13:29. > > Test2: > I increased SHMVIRTSIZE by 36M, bounced the system and reran. I saved 3 > minutes. > > Test3: > I set PDQPRIORITY to 100. My ONCONFIG variables were: > MAX_PDQPRORITY 100 > All of the DS_* variables were set to default. > It took two hours to create two of the seven indices. > > Even though I've heard that PDQ will speed up an index build, I haven't > seen it in this test system. Since others have had a noticeable change > (for the better), I must have missed something. Any ideas? > > Thanks in advance > > John Carlson > Informix DBA > WHSmith USA

Then what considerations should I include concerning DS_TOTAL_MEMORY?? It defaults to 512K (divided by a default of 4 queries). John Carlson Informix DBA WHSmith USA Madison Pruet wrote: > > PDQ performance is also dependent on the value that you set DS_TOTAL_MEMORY > to. If it is set too small, then you will not have as much memory allocated > for the various phases of the command. > > Carlson@WHSmith wrote: > > > OS: HPUX 10.20 > > IFX: IDS 7.30.ucX > > HDWE: HP G70 (2-way box) > > > > I'm in the process of tightening up various aspects of my reorg scripts > > (dbspace level). I've been able to use HPL as necessary, so unloads > > (and probably loads) will run in a more optimum fashion. One of the > > more time-consuming aspects of the reload process is the index build. > > > > I've seen various recommendations concerning PDQPRIORITY, etc. Here's a > > snippet of what I used: > > > > Test1: > > I verified that PSORT_NPROCS was set to 4. When this benchmark began, > > the system allocated 4 segments of shared memory (8M each). Total run > > time was 1:13:29. > > > > Test2: > > I increased SHMVIRTSIZE by 36M, bounced the system and reran. I saved 3 > > minutes. > > > > Test3: > > I set PDQPRIORITY to 100. My ONCONFIG variables were: > > MAX_PDQPRORITY 100 > > All of the DS_* variables were set to default. > > It took two hours to create two of the seven indices. > > > > Even though I've heard that PDQ will speed up an index build, I haven't > > seen it in this test system. Since others have had a noticeable change > > (for the better), I must have missed something. Any ideas? > > > > Thanks in advance > > > > John Carlson > > Informix DBA > > WHSmith USA

If you're not careful with PDQ, you might be hurting yourself more than you're helping. Be sure to set SHMTOTAL to something that will easily fit into the memory (RAM) of the machine, even with all the other processes running. Also, set DS_TOTAL_MEMORY to something that will fit within SHMTOTAL, less the resident and message portions. Basically do an onstat -g seg, and subtract the size of the M and R segments from SHMTOTAL. Then set DS_TOTAL_MEMORY to 80% of the difference. If you tune these parameters too high, you wind up swapping your virtual segments out to disk, and that will counteract any benefits PDQ may otherwise have given you. Other ways to speed up index builds include having lots of temp spaces on different spindles, and tuning the engine to checkpoint less frequently (large CKPTINTVL and high LRU_MIX_DIRTY and LRU_MAX_DIRTY). In my testing, setting PSORT_NPROCS to 2 has worked best, even on a 12-way machine, but that's going to vary from machine to machine. HTH, - Tom Girsch "Carlson@WHSmith" <carlson1@bellsouth.net> wrote in message news:384D6799.9B3F9EA0@bellsouth.net... > OS: HPUX 10.20 > IFX: IDS 7.30.ucX > HDWE: HP G70 (2-way box) > > I'm in the process of tightening up various aspects of my reorg scripts > (dbspace level). I've been able to use HPL as necessary, so unloads > (and probably loads) will run in a more optimum fashion. One of the > more time-consuming aspects of the reload process is the index build. > > I've seen various recommendations concerning PDQPRIORITY, etc. Here's a > snippet of what I used: > > Test1: > I verified that PSORT_NPROCS was set to 4. When this benchmark began, > the system allocated 4 segments of shared memory (8M each). Total run > time was 1:13:29. > > Test2: > I increased SHMVIRTSIZE by 36M, bounced the system and reran. I saved 3 > minutes. > > Test3: > I set PDQPRIORITY to 100. My ONCONFIG variables were: > MAX_PDQPRORITY 100 > All of the DS_* variables were set to default. > It took two hours to create two of the seven indices. > > Even though I've heard that PDQ will speed up an index build, I haven't > seen it in this test system. Since others have had a noticeable change > (for the better), I must have missed something. Any ideas? > > Thanks in advance > > > John Carlson > Informix DBA > WHSmith USA

In article <384e7033_1@news2.one.net>, Thomas J. Girsch <tgirsch@iname.com> writes >If you're not careful with PDQ, you might be hurting yourself more than >you're helping. Be sure to set SHMTOTAL to something that will easily fit >into the memory (RAM) of the machine, even with all the other processes >running. Also, set DS_TOTAL_MEMORY to something that will fit within >SHMTOTAL, less the resident and message portions. Basically do an onstat -g >seg, and subtract the size of the M and R segments from SHMTOTAL. Then set >DS_TOTAL_MEMORY to 80% of the difference.> >If you tune these parameters too high, you wind up swapping your virtual >segments out to disk, and that will counteract any benefits PDQ may >otherwise have given you. > >Other ways to speed up index builds include having lots of temp spaces on >different spindles, and tuning the engine to checkpoint less frequently >(large CKPTINTVL and high LRU_MIX_DIRTY and LRU_MAX_DIRTY). > >In my testing, setting PSORT_NPROCS to 2 has worked best, even on a 12-way >machine, but that's going to vary from machine to machine. > On an P133 8-way NCR machine with 1Gb of RAM. Using PSORT_NPROCS=8 PDQPRIORITY=100 gave the best performance. Index builds went from 1hr per index to ~5 mins per index!! On 1 and 2 CPU Sun E450 servers with 256Mb+ of RAM using NUMCPUVPS= <number of physical processors> PSORT_NPROCS= 2*NUMCPUVPS (!!) PDQPRIORITY=100 gave the best results. Multiple sort pools allocated for the sesssion. Hence merging the initial sorted runs required less merges and went faster! I never pushed the parameters further then this.. >HTH, > >- Tom Girsch >"Carlson@WHSmith" <carlson1@bellsouth.net> wrote in message >news:384D6799.9B3F9EA0@bellsouth.net... >> OS: HPUX 10.20 >> IFX: IDS 7.30.ucX >> HDWE: HP G70 (2-way box) >> >> I'm in the process of tightening up various aspects of my reorg scripts >> (dbspace level). I've been able to use HPL as necessary, so unloads >> (and probably loads) will run in a more optimum fashion. One of the >> more time-consuming aspects of the reload process is the index build. >> >> I've seen various recommendations concerning PDQPRIORITY, etc. Here's a >> snippet of what I used: >> >> Test1: >> I verified that PSORT_NPROCS was set to 4. When this benchmark began, >> the system allocated 4 segments of shared memory (8M each). Total run >> time was 1:13:29. >> >> Test2: >> I increased SHMVIRTSIZE by 36M, bounced the system and reran. I saved 3 >> minutes. >> >> Test3: >> I set PDQPRIORITY to 100. My ONCONFIG variables were: >> MAX_PDQPRORITY 100 >> All of the DS_* variables were set to default. >> It took two hours to create two of the seven indices. >> >> Even though I've heard that PDQ will speed up an index build, I haven't >> seen it in this test system. Since others have had a noticeable change >> (for the better), I must have missed something. Any ideas? >> >> Thanks in advance >> >> >> John Carlson >> Informix DBA >> WHSmith USA > > -- David Williams

David Williams said.... > PSORT_NPROCS=8 > PDQPRIORITY=100 > > gave the best performance. Index builds went from 1hr per index > to ~5 mins per index!! > > On 1 and 2 CPU Sun E450 servers with 256Mb+ of RAM using > > NUMCPUVPS= <number of physical processors> > PSORT_NPROCS= 2*NUMCPUVPS (!!) There's quite a bit of logic to this. While one of the threads is flushing or merging sort tournament buffers to/from disk, the other can be busy actually sorting. This would tend to keep the cpuvps busy doing logic work instead of waiting for IO completion. > PDQPRIORITY=100 And as I previously mentioned, the effectiveness of PDQPRIORITY is dependant on DS total memory...... ;-) > > > gave the best results. Multiple sort pools allocated for the sesssion. > Hence merging the initial sorted runs required less merges and went > faster! I never pushed the parameters further then this.. > > >HTH, > > > >- Tom Girsch > >"Carlson@WHSmith" <carlson1@bellsouth.net> wrote in message > >news:384D6799.9B3F9EA0@bellsouth.net... > >> OS: HPUX 10.20 > >> IFX: IDS 7.30.ucX > >> HDWE: HP G70 (2-way box) > >> > >> I'm in the process of tightening up various aspects of my reorg scripts > >> (dbspace level). I've been able to use HPL as necessary, so unloads > >> (and probably loads) will run in a more optimum fashion. One of the > >> more time-consuming aspects of the reload process is the index build. > >> > >> I've seen various recommendations concerning PDQPRIORITY, etc. Here's a > >> snippet of what I used: > >> > >> Test1: > >> I verified that PSORT_NPROCS was set to 4. When this benchmark began, > >> the system allocated 4 segments of shared memory (8M each). Total run > >> time was 1:13:29. > >> > >> Test2: > >> I increased SHMVIRTSIZE by 36M, bounced the system and reran. I saved 3 > >> minutes. > >> > >> Test3: > >> I set PDQPRIORITY to 100. My ONCONFIG variables were: > >> MAX_PDQPRORITY 100 > >> All of the DS_* variables were set to default. > >> It took two hours to create two of the seven indices. > >> > >> Even though I've heard that PDQ will speed up an index build, I haven't > >> seen it in this test system. Since others have had a noticeable change > >> (for the better), I must have missed something. Any ideas? > >> > >> Thanks in advance > >> > >> > >> John Carlson > >> Informix DBA > >> WHSmith USA > > > > > > -- > David Williams