Configure IDS 11.50 to avoid sorts going disk or t

Good afternoon, Is there a configuration setting in ONCONFIG file to prevent sorts and other temporary operations from using files? Several sorts are creating temp or scratch files in $INFORMIXDIR/tmp. Can those operations be doing in memory instead of on disk? If so, how do you configure Informix 11.50 to use memory instead of disk? Thanks in advance. Jonathan B. Smaby Pomona College -- "If you are not willing to risk the unusual, you will have to settle for the ordinary." - Jim Rohn ------------------------------------------------------------- This message has been scanned by Postini anti-virus software.

DS_NONPDQ_QUERYMEM Sets the amount of DSS memory to use for queries that do not have positive PDQPRIORITY. It can be set to any value from 128K to 25% of DS_TOTAL_MEMORY. Also look at the environment variable DBUPSPACE which controls the amount of memory used for in-memory sorting for non-PDQ queries. 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, May 3, 2012 at 5:55 PM, Jonathan Smaby <Jonathan.Smaby@pomona.edu>wrote: > Good afternoon, > > Is there a configuration setting in ONCONFIG file to prevent sorts and > other > temporary operations from using files? Several sorts are creating temp or > scratch files in $INFORMIXDIR/tmp. Can those operations be doing in memory > instead of on disk? If so, how do you configure Informix 11.50 to use > memory > instead of disk? > > Thanks in advance. > > Jonathan B. Smaby > Pomona College > -- > "If you are not willing to risk the unusual, you will have to settle for > the > ordinary." - Jim Rohn > > ------------------------------------------------------------- > This message has been scanned by Postini anti-virus software. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340d59534f3b04bf28f01f

The simplest solution is to utilize the onconfig parameter DS_NON_PDQ_QUERY_MEM. This will allow you to increase the memory to sort, hash join, ... other memory operations without having to enable PDQ. The default is 128KB which is very small. Setting this to 1024 or even higher can greatly improve the performance of a system which sorts lots of rows. Here is a query I like to use to see how sort operation is occurring: select * from sysmaster:sysshmhdr where name matches "*sort*"; number name value 118 pf_totalsorts 40 119 pf_memsorts 40 120 pf_disksorts 0 121 pf_maxsortspace 0 John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 05/03/2012 02:55:45 PM: > From: "Jonathan Smaby" <Jonathan.Smaby@pomona.edu> > To: ids@iiug.org, > Date: 05/03/2012 02:57 PM > Subject: Configure IDS 11.50 to avoid sorts going disk .... [26964] > Sent by: ids-bounces@iiug.org > > Good afternoon, > > Is there a configuration setting in ONCONFIG file to prevent sorts and other > temporary operations from using files? Several sorts are creating temp or > scratch files in $INFORMIXDIR/tmp. Can those operations be doing in memory > instead of on disk? If so, how do you configure Informix 11.50 to use memory > instead of disk? > > Thanks in advance. > > Jonathan B. Smaby > Pomona College > -- > "If you are not willing to risk the unusual, you will have to settle for the > ordinary." - Jim Rohn > > ------------------------------------------------------------- > This message has been scanned by Postini anti-virus software. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Outstanding and thank-you Art and John. What values for Non-PDQ memory settings do you recommend for the following results of John's sysmaster query: Database selected. number name value 118 pf_totalsorts 7980287 119 pf_memsorts 7962003 120 pf_disksorts 18284 121 pf_maxsortspace 634048 4 row(s) retrieved. Database closed. Thank-you in advance. Jonathan B. Smaby Pomona College -- "If you are not willing to risk the unusual, you will have to settle for the ordinary." - Jim Rohn -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John Miller iii Sent: Thursday, May 03, 2012 3:07 PM To: ids@iiug.org Subject: Re: Configure IDS 11.50 to avoid sorts going d.... [26967] The simplest solution is to utilize the onconfig parameter DS_NON_PDQ_QUERY_MEM. This will allow you to increase the memory to sort, hash join, ... other memory operations without having to enable PDQ. The default is 128KB which is very small. Setting this to 1024 or even higher can greatly improve the performance of a system which sorts lots of rows. Here is a query I like to use to see how sort operation is occurring: select * from sysmaster:sysshmhdr where name matches "*sort*"; number name value 118 pf_totalsorts 40 119 pf_memsorts 40 120 pf_disksorts 0 121 pf_maxsortspace 0 John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 05/03/2012 02:55:45 PM: > From: "Jonathan Smaby" <Jonathan.Smaby@pomona.edu> > To: ids@iiug.org, > Date: 05/03/2012 02:57 PM > Subject: Configure IDS 11.50 to avoid sorts going disk .... [26964] > Sent by: ids-bounces@iiug.org > > Good afternoon, > > Is there a configuration setting in ONCONFIG file to prevent sorts and other > temporary operations from using files? Several sorts are creating temp > or > scratch files in $INFORMIXDIR/tmp. Can those operations be doing in memory > instead of on disk? If so, how do you configure Informix 11.50 to use memory > instead of disk? > > Thanks in advance. > > Jonathan B. Smaby > Pomona College > -- > "If you are not willing to risk the unusual, you will have to settle > for the > ordinary." - Jim Rohn > > ------------------------------------------------------------- > This message has been scanned by Postini anti-virus software. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ------------------------------------------------------------- This message has been scanned by Postini anti-virus software.

So, your largest sort was just over 625K so John's suggestion of setting DS_NONPDQ_QUERYMEM to 1024 would work, your just have to make sure that 1) DS_TOTAL_MEMORY is set to at least 4096 (to avoid the 25% limit) and that if you increase DS_TOTAL_MEMORY that SHMVIRTSIZE is big enough to permit the increase (see the onstat -g seg report for the amount of unused memory if any). If there have been additional virtual segments added you your server since the initial one, you should fold them into an increased SHMVIRTSIZE as contiguous shared memory performs better. I tend to set DS_NONPDQ_QUERYMEM to 4096 on the servers that I configure, but I'm either setting up new servers or tuning servers that I don't know well, so I have to allow extra overhead for the periodic large sort that I may not have encountered while reviewing server performance. You know your own environment, so use your judgement. If there is that quarterly report that hasn't run recently that sorts half the database, take that into consideration. 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, May 3, 2012 at 6:13 PM, Jonathan Smaby <Jonathan.Smaby@pomona.edu>wrote: > Outstanding and thank-you Art and John. > > What values for Non-PDQ memory settings do you recommend for the following > results of John's sysmaster query: > > Database selected. > > number name value > > 118 pf_totalsorts 7980287 > > 119 pf_memsorts 7962003 > > 120 pf_disksorts 18284 > > 121 pf_maxsortspace 634048 > > 4 row(s) retrieved. > > Database closed. > > Thank-you in advance. > > Jonathan B. Smaby > Pomona College > -- > "If you are not willing to risk the unusual, you will have to settle for > the > ordinary." - Jim Rohn > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John > Miller iii > Sent: Thursday, May 03, 2012 3:07 PM > To: ids@iiug.org > Subject: Re: Configure IDS 11.50 to avoid sorts going d.... [26967] > > The simplest solution is to utilize the onconfig parameter > DS_NON_PDQ_QUERY_MEM. This will allow you to increase the memory to sort, > hash > join, ... other memory operations without having to enable PDQ. The > default is > 128KB which is very small. Setting this to 1024 or even higher can greatly > improve the performance of a system which sorts lots of rows. > > Here is a query I like to use to see how sort operation is occurring: > > select * from sysmaster:sysshmhdr where name matches "*sort*";> > number name value > > 118 pf_totalsorts 40 > > 119 pf_memsorts 40 > > 120 pf_disksorts 0 > > 121 pf_maxsortspace 0 > > John F. Miller III > STSM, Embedability Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > > ids-bounces@iiug.org wrote on 05/03/2012 02:55:45 PM: > > > From: "Jonathan Smaby" <Jonathan.Smaby@pomona.edu> > > To: ids@iiug.org, > > Date: 05/03/2012 02:57 PM > > Subject: Configure IDS 11.50 to avoid sorts going disk .... [26964] > > Sent by: ids-bounces@iiug.org > > > > Good afternoon, > > > > Is there a configuration setting in ONCONFIG file to prevent sorts and > other > > temporary operations from using files? Several sorts are creating temp > > or > > > scratch files in $INFORMIXDIR/tmp. Can those operations be doing in > memory > > instead of on disk? If so, how do you configure Informix 11.50 to use > memory > > instead of disk? > > > > Thanks in advance. > > > > Jonathan B. Smaby > > Pomona College > > -- > > "If you are not willing to risk the unusual, you will have to settle > > for > the > > ordinary." - Jim Rohn > > > > ------------------------------------------------------------- > > This message has been scanned by Postini anti-virus software. > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > ------------------------------------------------------------- > This message has been scanned by Postini anti-virus software. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340d5919085104bf29cd51