Re: index build performance

Posted in 2007

Floyd Wellershaus — Wed, 18 Apr 2007 09:41:22 -0700 (PDT) — source: Usenet: comp.databases.informix

Thanks. Yes the table has 6 fragments. I have 7 cpu's on the box. So I assume, based on your recommendation to set PSORT_NPROCS to 7 and have 7 temp dbspaces and set PSORT_DBTEMP to those. btw, we have nothing else running at the time, so I was planning on using pdqpriority 100 but for safety leaving MAXPDQPRIORITY to 25. Sound good ? Thanks. ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Home: 703-430-0805 Cell: 703-477-6045 ======================== http://www.one.org/ ----- Original Message ---- From: Obnoxio The Clown <obnoxio@serendipita.com> To: Floyd Wellershaus <fwellers@yahoo.com> Cc: informix-list@iiug.org Sent: Wednesday, April 18, 2007 12:19:19 PM Subject: Re: index build performance Floyd Wellershaus said: > 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 ? Are the tables fragmented? If so, use PDQPRIORITY=50 and PSORT_NPROCS=4 then set PSORT_DBTEMP=/tmp1:/tmp2:/tmp3:/tmp4 where these are preferably on different spindles. If you have fewer than 4CPUs, set PSORT_NPROCS= the number of CPUs you have and reduce the number of directories in PSORT_DBTEMP. -- Bye now, Obnoxio "I'm astonished anyone pays real money for this crap." -- Cosmo -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.