RE: Creating indexes
Bump your virtual memory as well.
Masters series documents suggest:
Buffers - set to 25% of avail memory
SHMVIRTSIZE - set to 75% of available memory
CKPTINTVL - set to 30000 (that is not a typo - you want efficient
block writes)
RA_PAGES - set to 32 (16 for 4K page)
RA_THRESHOLD - set to 30 (15 for 4k page)
DBSPACETEMP - set to as many as you can
DS_TOTAL_MEMORY - set to 90% of SHMVIRTSIZEDS_MAX_SCANS - set to number of fragments of largest table index is being
built on
PHYSFILE - set large enough to not trigger checkpoints.
LRUS - alocate 1 pair per 500-700 buffers up to the max of 128. Generally
means 128 in my world.
Of course - PSORT_NPROCS = number of cpus and PDQPRIORITY=100
While I've had to tweak these values a bit when I used them, they do make a
big difference. I get a little concerned about the psort_nprocs, you get
that many sort threads PER index build thread - that can rapidly inflate the
number of threads you get. I've seen over 400 threads while building a
large index. However, that index build screamed.
cheers
j.
> -----Original Message-----
> From: obnoxio@hotmail.com [mailto:obnoxio@hotmail.com]
> Sent: Thursday, February 01, 2001 5:07 AM
> To: informix-list@iiug.org
> Subject: Re: Creating indexes
>
>
> In the year of Our Lord Wed, 31 Jan 2001 22:19:14 -0000, "Neil Truby"
> <ntruby@netcomuk.co.uk> spake, saying:
>
> >IDS 9.20 on HP-UX 11.0, 3cpu vps, 6 physical cpus.
> >
> >What's the view on trying to optimise index builds these days?
> >
> >I ran some builds on a 28 million row table. About 3 hours
> per index, cut
> >down to 45 minutes when setting
> >
> >PDQPRIORITY=100
> >PSORT_TEMP to some file systems
> >PSORT_NPROCS=24
> >
> >I admit that I'm not certain that the fast indexes had as
> many columns as
> >the slow, but am still interested to know others' views.
>
> PSORT_NPROCS does vary, I've gone up to 32 on an 8-way box. I
> set PSORT_DBTEMP
> to 5 filesystems. Index builds are now take about a third of
> the time than with
> "sequential" processing.
>