index build performance
Back on that 1.1 billion row table again. Thanks to help from sources here, I was able to get the named pipes working to half the time for a load and unload for the table. Now I'm concerned with building the indexes. From sources here also, I am going to bounce the server and use the following onconfig guidelines. •BUFFERS 25% of available memory •SHMVIRTSIZE 75% of available memory •CKPTINTVL 3000 (50 min) •LRU_MAX_DIRTY 80 •LRU_MIN_DIRTY 70 •RA_PAGES 32 (16 for 4k page) •RA_THRESHOLD 30 (15 for 4k page) •DBSPACETEMP Lots •DS_TOTAL_MEMORY 90% of SHMVIRTSIZE •DS_MAX_SCANS Nbr of fragments of largest table •PHYSFILE Large Are there any other tricks to improve the performance ? Is there an easy way to estimate how big to make the PHYSFILE and the TEMP DBSPACES ? Thanks for any advice. Floyd ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Home: 703-430-0805 Cell: 703-477-6045 ======================== http://www.one.org/
Floyd Wellershaus wrote: > Back on that 1.1 billion row table again. > Thanks to help from sources here, I was able to get the named pipes > working to half the time for a load and unload for the table. > Now I'm concerned with building the indexes. > > From sources here also, I am going to bounce the server and use the > following onconfig guidelines. > 'BUFFERS 25% of available memory > 'SHMVIRTSIZE 75% of available memory > 'CKPTINTVL 3000 (50 min) > 'LRU_MAX_DIRTY 80 > 'LRU_MIN_DIRTY 70 > 'RA_PAGES 32 (16 for 4k page) > 'RA_THRESHOLD 30 (15 for 4k page) > 'DBSPACETEMP Lots > 'DS_TOTAL_MEMORY 90% of SHMVIRTSIZE > 'DS_MAX_SCANS Nbr of fragments of largest table > 'PHYSFILE Large > > Are there any other tricks to improve the performance ? > Is there an easy way to estimate how big to make the PHYSFILE and the > TEMP DBSPACES ? The Clown's advice on PSORT_DBTEMP, but I'd set that to 2 X #CPUS. For PSORT_DBTEMP set to at least 3 and as many as 6 different filesystems (preferably on separate structures) each with enough free space to hold all of the keys in the largest index. Reduce RA_THRESHOLD to 4 or 8, with modern smart cached disks, cached controllers, and caching arrays each doing its own readahead IDS's readahead is almost redundant and wastes buffers. Do you REALLY want to read another 64K whenever you use the first 4K from the last readahead block? I'm sure that your structures are fast enough that with all but 4 or 8 pages used they can get the next readahead in place in the IDS cache before you'll need it to be there. That said, it may not affect index builds either way if that's all that's going on, but if other activity is going on your proposed RA settings will kill performance for everything else without improving the index build significantly. Don't forget to set PDQPRIORITY as high as your other activity will allow. Art S. Kagel
Floyd Wellershaus wrote: > Back on that 1.1 billion row table again. > Thanks to help from sources here, I was able to get the named pipes > working to half the time for a load and unload for the table. > Now I'm concerned with building the indexes. > > From sources here also, I am going to bounce the server and use the > following onconfig guidelines. > •BUFFERS 25% of available memory > •SHMVIRTSIZE 75% of available memory > •CKPTINTVL 3000 (50 min) > •LRU_MAX_DIRTY 80 > •LRU_MIN_DIRTY 70 > •RA_PAGES 32 (16 for 4k page) > •RA_THRESHOLD 30 (15 for 4k page) > •DBSPACETEMP Lots > •DS_TOTAL_MEMORY 90% of SHMVIRTSIZE > •DS_MAX_SCANS Nbr of fragments of largest table > •PHYSFILE Large > > Are there any other tricks to improve the performance ? > Is there an easy way to estimate how big to make the PHYSFILE and the > TEMP DBSPACES ? The Clown's advice on PSORT_DBTEMP, but I'd set that to 2 X #CPUS. For PSORT_DBTEMP set to at least 3 and as many as 6 different filesystems (preferably on separate structures) each with enough free space to hold all of the keys in the largest index. Reduce RA_THRESHOLD to 4 or 8, with modern smart cached disks, cached controllers, and caching arrays each doing its own readahead IDS's readahead is almost redundant and wastes buffers. Do you REALLY want to read another 64K whenever you use the first 4K from the last readahead block? I'm sure that your structures are fast enough that with all but 4 or 8 pages used they can get the next readahead in place in the IDS cache before you'll need it to be there. That said, it may not affect index builds either way if that's all that's going on, but if other activity is going on your proposed RA settings will kill performance for everything else without improving the index build significantly. Don't forget to set PDQPRIORITY as high as your other activity will allow. Art S. Kagel