IDS Forum

Re: Do not set the PSORT_NPROCS ?

Posted By: Art Kagel
Date: Thursday, 25 July 2013, at 12:30 p.m.

In Response To: Re: Do not set the PSORT_NPROCS ? (FRANK)

It is always whatever is left of SHMVIRTSIZE after internal data structures
(locks, dictionary cache, distribution cache, etc.) and ACTIVE dss queries.
So if no one is running with PDQPRIORITY that would be all of virtual
memory. DS_TOTAL_MEMORY is a limit not a reserve.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.

On Thu, Jul 25, 2013 at 12:25 PM, FRANK <yunyaoqu@gmail.com> wrote:

> Thanks Art!
>
> One more question :-)
>
> If
> SHMVIRTSIZE is 1GB
> DS_TOTAL_MEMORY is 500MB
> and *PDQPRIORITY is not set in the session or server side.*
>
> The available virtual shared memory size for all OLTP sessions is 1GB or
> 500MB?
>
> Thanks,
> Frank
>
> On Tue, Jul 23, 2013 at 10:45 PM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > OK, here's the skinny on PDQPRIORITY versus PSORT_NPROCS for index builds
> > and update statistics as I understand it:
> >
> > - If you do not or cannot set PDQPRIORITY to a positive value (ex:
> >
> > Workgroup Edition does not allow PDQPRIORITY) the setting PSORT_NPROCS is
> >
> > the only way to get multiple sort threads running.
> >
> > - PSORT_NPROCS - "officially" - can be set to any value from 2 to 10.
> >
> > Officially setting a value greater than 10 results in the equivalent of
> >
> > 10. The value set determines the number of parallel sort threads that are
> >
> > used.
> >
> > - My experience has been that you can set PSORT_NPROCS to any value you
> >
> > want and you will get that number of sort threads. I'm just saying... Try
> >
> > it for yourself.
> >
> > - If you set PDQPRIORITY to a positive value, you enable parallel
> >
> > processing overall and the Memory Grant Manager and parallel query
> manager
> >
> > determine the number of sort threads in an "optimal" way based on two
> sort
> >
> > threads per sort with the number of sorts equal to the number of
> partitions
> >
> > in the index for an index build. PDQPRIORITY overrides the PSORT_NPROCS
> >
> > setting.
> >
> > - The number of sorts for update statistics is not documented, but I
> >
> > suspect it has to do with the number of columns included in the command
> as
> >
> > well as other factors.
> >
> > - The percentage of resources represented by the session's effective
> >
> > PDQPRIORITY (throttled by MAX_PDQPRIORITY) is used to determine the
> amount
> >
> > of memory available for in-memory sorting during index builds and update
> >
> > statistics runs.
> >
> > - If PDQPRIORITY is zero, index builds use DS_NONPDQ_QUERY_MEM to
> >
> > determine how much memory to use for in-memory sorting. This can be up to
> >
> > 25% of DS_TOTAL_MEMORY. The default value if unset is 128KB. Update
> >
> > statistics uses DBUPSPACE still to determine memory for sorting according
> >
> > to the documentation. The default is 15MB.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> > other organization with which I am associated either explicitly,
> > implicitly, or by inference. Neither do those opinions reflect those of
> > other individuals affiliated with any entity with which I am affiliated
> nor
> > those of the entities themselves.
> >
> > On Tue, Jul 23, 2013 at 10:28 PM, FRANK <yunyaoqu@gmail.com> wrote:
> >
> > > Folks,
> > >
> > > I see the following rules on 11.70 info center.
> > >
> > > In rule 2: "Do not set the *PSORT_NPROCS* environment variable", What
> > > negative impacts it would cause if we set the *PSORT_NPROCS* in index
> > > creating process?
> > >
> > > Thanks,
> > > Frank
> > >
> > > http://pic.dhe.ibm.com/infocenter/idshelp/v117/index.jsp
> > >
> > > You can often improve the performance of an index build by taking the
> > > following steps:
> > >
> > > 1. Set PDQ priority to a value greater than 0 to obtain more memory
> than
> > >
> > > the default 128 kilobytes.
> > >
> > > When you set PDQ priority to greater than 0, the index build can take
> > >
> > > advantage of the additional memory for parallel processing.
> > >
> > > To set PDQ priority, use either the *PDQPRIORITY* environment variable
> > >
> > > or the SET PDQPRIORITY statement in SQL.
> > >
> > > 2. Do not set the *PSORT_NPROCS* environment variable. If you have a
> > >
> > > computer with multiple CPUs, the database server uses two threads per
> > sort
> > >
> > > when it sorts index keys and *PSORT_NPROCS* is not set. The number of
> > >
> > > sorts depends on the number of fragments in the index, the number of
> > >
> > > keys, the key size, and the values of the PDQ memory configuration
> > >
> > > parameters.
> > >
> > > 3. Allocate enough memory and temporary space to build the entire
> index.
> > >
> > > --001a11c2bc0240d9fe04e238ab9a
> > >
> > >
> > >
> > >
> >
> >
>
> *******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --089e0158acf0c4c8a304e238e501
> >
> >
> >
> >
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --047d7b5d8b5de8897404e258761f
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--089e0141a9ea0edea204e2588927

Messages In This Thread

• Do not set the PSORT_NPROCS ?
  FRANK -- Tuesday, 23 July 2013, at 10:28 p.m.
  • Re: Do not set the PSORT_NPROCS ?
    Art Kagel -- Tuesday, 23 July 2013, at 10:45 p.m.
    • Re: Do not set the PSORT_NPROCS ?
      FRANK DEVELOPER -- Tuesday, 23 July 2013, at 11:15 p.m.
      • Re: Do not set the PSORT_NPROCS ?
        Art Kagel -- Tuesday, 23 July 2013, at 11:19 p.m.
    • Re: Do not set the PSORT_NPROCS ?
      FRANK -- Thursday, 25 July 2013, at 12:25 p.m.
      • Re: Do not set the PSORT_NPROCS ?
        Art Kagel -- Thursday, 25 July 2013, at 12:30 p.m.
        • Re: Do not set the PSORT_NPROCS ?
          FRANK -- Thursday, 25 July 2013, at 12:35 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.