Do not set the PSORT_NPROCS ?
Frank asked why IBM's 11.70 docs say not to set PSORT_NPROCS when building indexes, and what harm it does. Art Kagel explained the interaction: with PDQPRIORITY > 0 the Memory Grant Manager/parallel query manager decides the number of sort threads (roughly two per sort) and overrides PSORT_NPROCS, so setting it is pointless; PSORT_NPROCS is only useful when PDQ can't be used (e.g. Workgroup Edition), officially taking values 2-10. He also covered memory: with PDQPRIORITY 0, index builds use DS_NONPDQ_QUERY_MEM (up to 25% of DS_TOTAL_MEMORY, default 128KB) and update statistics uses DBUPSPACE, and noted DS_TOTAL_MEMORY is a limit, not a reservation, so non-PDQ sessions can use whatever SHMVIRTSIZE remains. Frank was satisfied.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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
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
Is it an OLTP or DSS server instance? Perhaps FRANK can provide the onconfig parameters so you [Art] may recommend optimal settings?
As important as the ONCONFIG settings is how much memory and CPU horsepower is available and how much is configured in VPCLASS cpu and DS_TOTAL_MEMORY or DS_NONPDQ_QUERY_MEM. 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 11:15 PM, FRANK DEVELOPER <frankcomputer@ymail.com>wrote: > Is it an OLTP or DSS server instance? Perhaps FRANK can provide the > onconfig > parameters so you [Art] may recommend optimal settings? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e014940ea6397f904e2395fdb
Thanks Art!
One more question :-)
If
SHMVIRTSIZE is 1GB
DS_TOTAL_MEMORY is 500MBand *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
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
Thank you , Art!! Frank
On Thu, Jul 25, 2013 at 12:30 PM, Art Kagel <art.kagel@gmail.com> wrote:
> 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
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e013a0062bfebb704e2589dd2