Parallel index building

Hi *, I have a large table fragmented in three dbspaces on three disks. When I do an create unique index then the iostat tells me the DBMS is reading just from one disk not from three. I'm using Solaris 2.6, IDS 7.31UC2 and the following DSS Settings: MAX_PDQPRIORITY 100 DS_MAX_QUERIES 64 DS_MAX_SCANS 64 DS_TOTAL_MEMORY 256000 The system is an 4 way PIII 500 Acer Altos with 512MB RAM. So how can I speed up the index building? Can it be done parallel? Thanks for your help Thomas

Thomas Mieslinger <thomas.mieslinger@germanparcel.de> writes: > Hi *, > > I have a large table fragmented in three dbspaces on three disks. When I > do an create unique index then the iostat tells me the DBMS is reading > just from one disk not from three. > > I'm using Solaris 2.6, IDS 7.31UC2 and the following DSS Settings: > MAX_PDQPRIORITY 100 > DS_MAX_QUERIES 64 > DS_MAX_SCANS 64 > DS_TOTAL_MEMORY 256000> > The system is an 4 way PIII 500 Acer Altos with 512MB RAM. > > So how can I speed up the index building? Can it be done parallel? Yes it can:) I build large indexes in parallell and smaller ones in serial at the same time -works nice (remember to set PDQPRIORITY in your session btw). Update statistics will also benefit from some parallism. Thomas

Thomas Parsli schrieb: > > Thomas Mieslinger <thomas.mieslinger@germanparcel.de> writes: > > > Hi *, > > > > I have a large table fragmented in three dbspaces on three disks. When I > > do an create unique index then the iostat tells me the DBMS is reading > > just from one disk not from three. > > > > I'm using Solaris 2.6, IDS 7.31UC2 and the following DSS Settings: > > MAX_PDQPRIORITY 100 > > DS_MAX_QUERIES 64 > > DS_MAX_SCANS 64 > > DS_TOTAL_MEMORY 256000> > > > The system is an 4 way PIII 500 Acer Altos with 512MB RAM. > > > > So how can I speed up the index building? Can it be done parallel? > > Yes it can:) > > I build large indexes in parallell and smaller ones in serial at the > same time -works nice (remember to set PDQPRIORITY in your session btw). > Update statistics will also benefit from some parallism. How can I improve a single index build on a large table? Witch settings should I change? -- Thomas Mieslinger Mobil: +49 170 510 6095 Koenigstrasse 44 Fon: +49 661 901 3975 36037 Fulda Fax: +49 661 901 4605 Germany eMail: thomas@mieslinger.de

Yes it can. You have to enable parallel sorting for this to occur. Hence in the environment that will be requesting the CREATE INDEX: PSORT_NPROCS=8 PSORT_DBTEMP=<colon (:) list of at least 3 filesystem on separate disks> PSORT_DBTEMP=fs1:fs2:fs3:fs4 export PSORT_NPROCS PSORT_DBTEMP If you leave out PSORT_DBTEMP DBSPACETEMP will be used but is slightly slower. If you do not have at least three temp dbspaces in DBSPACETEMP then PSORT_DBTEMP, if you have three+ filesystems to use, will be MUCH faster. The filesystems are used in round robin fashion so they must all be large enough to hold 1/N of the sort files as they will be filled evenly. PSORT works for statistics updates also BTW! FYI oncheck NEVER uses parallel sorting to create indexes so if it wants to rebuild an index answer "N" and do it yourself using the PSORT parameters. Art S. Kagel Thomas Mieslinger wrote: > > Hi *, > > I have a large table fragmented in three dbspaces on three disks. When I > do an create unique index then the iostat tells me the DBMS is reading > just from one disk not from three. > > I'm using Solaris 2.6, IDS 7.31UC2 and the following DSS Settings: > MAX_PDQPRIORITY 100 > DS_MAX_QUERIES 64 > DS_MAX_SCANS 64 > DS_TOTAL_MEMORY 256000> > The system is an 4 way PIII 500 Acer Altos with 512MB RAM. > > So how can I speed up the index building? Can it be done parallel? > > Thanks for your help > > Thomas

In article <37F3A2D0.82649EF9@bloomberg.net>, Art S. Kagel <kagel@bloomberg.net> writes >Yes it can. You have to enable parallel sorting for this to occur. >Hence in the environment that will be requesting the CREATE INDEX: > >PSORT_NPROCS=8 >PSORT_DBTEMP=<colon (:) list of at least 3 filesystem on separate disks> >PSORT_DBTEMP=fs1:fs2:fs3:fs4 > >export PSORT_NPROCS PSORT_DBTEMP > >If you leave out PSORT_DBTEMP DBSPACETEMP will be used but is slightly >slower. If you do not have at least three temp dbspaces in DBSPACETEMP >then PSORT_DBTEMP, if you have three+ filesystems to use, will be MUCH >faster. The filesystems are used in round robin fashion so they must all >be large enough to hold 1/N of the sort files as they will be filled >evenly. PSORT works for statistics updates also BTW! > Art, I always thought you also recommended:- PSORT_MAXALLOC=10240 PDQPRIORITY=100 ?? >FYI oncheck NEVER uses parallel sorting to create indexes so if it wants >to rebuild an index answer "N" and do it yourself using the PSORT >parameters. > >Art S. Kagel > >Thomas Mieslinger wrote: >> >> Hi *, >> >> I have a large table fragmented in three dbspaces on three disks. When I >> do an create unique index then the iostat tells me the DBMS is reading >> just from one disk not from three. >> >> I'm using Solaris 2.6, IDS 7.31UC2 and the following DSS Settings: >> MAX_PDQPRIORITY 100 >> DS_MAX_QUERIES 64 >> DS_MAX_SCANS 64 >> DS_TOTAL_MEMORY 256000>> >> The system is an 4 way PIII 500 Acer Altos with 512MB RAM. >> >> So how can I speed up the index building? Can it be done parallel? >> >> Thanks for your help >> >> Thomas -- David Williams

Thomas Mieslinger <miesi@konfusoft.de> writes: > Thomas Parsli schrieb: > > > > Thomas Mieslinger <thomas.mieslinger@germanparcel.de> writes: > > > > > Hi *, > > > > > > I have a large table fragmented in three dbspaces on three disks. When I > > > do an create unique index then the iostat tells me the DBMS is reading > > > just from one disk not from three. > > > > > > I'm using Solaris 2.6, IDS 7.31UC2 and the following DSS Settings: > > > MAX_PDQPRIORITY 100 > > > DS_MAX_QUERIES 64 > > > DS_MAX_SCANS 64 > > > DS_TOTAL_MEMORY 256000> > > > > > The system is an 4 way PIII 500 Acer Altos with 512MB RAM. > > > > > > So how can I speed up the index building? Can it be done parallel? > > > > Yes it can:) > > > > I build large indexes in parallell and smaller ones in serial at the > > same time -works nice (remember to set PDQPRIORITY in your session btw). > > Update statistics will also benefit from some parallism.> > How can I improve a single index build on a large table? Witch settings > should I change? Apart from all those already specified in this thread (PDQPRIOTITY, DS_* and PSORT) you could fragment data/indexes and spread them over several (different) spindles. You could also run a specific onconfig for index builds, Lynch/Canuel from ATG recommends the following: BUFFERS Low ~25% of avail mem SHMVIRTSIZE High ~75% ... CKPTINTVL High ~3000 LRUS 1 per 500-750 buffers LRU_MAX_DIRTY 80 LRU_MIN_DIRTY 70 RA_PAGES 128 RA_THRESHOLD 120DBSPACETEMP Many spaces, located on several spindles DS_TOTAL_MEMORY 90% of SHMVIRTSIZEDS_MAX_SCANS Number of fragments for your largest table Your DBSPACETEMP devices are used in round-robin btw., so space is limited by the smallest listed... Thomas

To improve an index build you mostly have to enable parallel sorting. This is done through the PSORT_ environment variables and PDQPRIORITY: PDQPRIORITY=40 PSORT_NPROCS=<N> Where <N> is a value up to 2x the number of CPUs. The manuals state that no more than 10 sort threads will be used but my testing shows sort improvement up to values of 40, perhaps because some code bug allows other resources to be allocated. PSORT_DBTEMP=<a list of AT LEAST 3 FILESYSTEMS for best performance. Note that the data will be spread roughly evenly across all of the listed filesystems and the sort will abort if any of them fill up prematurely. So make sure the FS with the least free space has enough for 1/Nth of the data. Also the FS's should be on different spindles or a LARGE stripe> export PDQPRIORITY PSORT_NPROCS PSORT_DBTEMP If PSORT_DBTEMP is not set DBSPACETEMP spaces will be used in the database which is inherently slower and will impact server performance. If you have to use DBSPACETEMP again performance is best with at least three temp dbspaces listed. Art S. Kagel Thomas Mieslinger wrote: > > Thomas Parsli schrieb: > > > > Thomas Mieslinger <thomas.mieslinger@germanparcel.de> writes: > > > > > Hi *, > > > > > > I have a large table fragmented in three dbspaces on three disks. When I > > > do an create unique index then the iostat tells me the DBMS is reading > > > just from one disk not from three. > > > > > > I'm using Solaris 2.6, IDS 7.31UC2 and the following DSS Settings: > > > MAX_PDQPRIORITY 100 > > > DS_MAX_QUERIES 64 > > > DS_MAX_SCANS 64 > > > DS_TOTAL_MEMORY 256000> > > > > > The system is an 4 way PIII 500 Acer Altos with 512MB RAM. > > > > > > So how can I speed up the index building? Can it be done parallel? > > > > Yes it can:) > > > > I build large indexes in parallell and smaller ones in serial at the > > same time -works nice (remember to set PDQPRIORITY in your session btw). > > Update statistics will also benefit from some parallism.> > How can I improve a single index build on a large table? Witch settings > should I change? > > -- > Thomas Mieslinger Mobil: +49 170 510 6095 > Koenigstrasse 44 Fon: +49 661 901 3975 > 36037 Fulda Fax: +49 661 901 4605 > Germany eMail: thomas@mieslinger.de