IDS Forum

Re: Configure IDS 11.50 to avoid sorts going d....

Posted By: Art Kagel
Date: Thursday, 3 May 2012, at 7:02 p.m.

In Response To: RE: Configure IDS 11.50 to avoid sorts going d.... (Jonathan Smaby)

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

Messages In This Thread

• Configure IDS 11.50 to avoid sorts going disk or t
  Jonathan Smaby -- Thursday, 3 May 2012, at 5:55 p.m.
  • Re: Configure IDS 11.50 to avoid sorts going disk
    Art Kagel -- Thursday, 3 May 2012, at 6:01 p.m.
    • Re: Configure IDS 11.50 to avoid sorts going d....
      FRANK -- Tuesday, 8 May 2012, at 5:27 p.m.
      • Re: Configure IDS 11.50 to avoid sorts going d....
        Fernando Nunes -- Tuesday, 8 May 2012, at 5:35 p.m.
      • Re: Configure IDS 11.50 to avoid sorts going d....
        George_Palmer@aotx.uscourts.gov -- Tuesday, 8 May 2012, at 5:40 p.m.
      • Re: Configure IDS 11.50 to avoid sorts going d....
        Art Kagel -- Tuesday, 8 May 2012, at 5:46 p.m.
        • Re: Configure IDS 11.50 to avoid sorts going d....
          Fernando Nunes -- Tuesday, 8 May 2012, at 6:01 p.m.
          • Re: Configure IDS 11.50 to avoid sorts going d....
            FRANK -- Tuesday, 8 May 2012, at 6:32 p.m.
            • Re: Configure IDS 11.50 to avoid sorts going d....
              Art Kagel -- Tuesday, 8 May 2012, at 6:50 p.m.
          • Re: Configure IDS 11.50 to avoid sorts going d....
            Art Kagel -- Tuesday, 8 May 2012, at 6:45 p.m.
          • RE: Configure IDS 11.50 to avoid sorts going d....
            Paul Watson -- Wednesday, 9 May 2012, at 8:28 a.m.
          • Re: Configure IDS 11.50 to avoid sorts going d....
            FRANK -- Wednesday, 9 May 2012, at 1:45 p.m.
            • Re: Configure IDS 11.50 to avoid sorts going d....
              Art Kagel -- Wednesday, 9 May 2012, at 2:06 p.m.
            • Re: Configure IDS 11.50 to avoid sorts going d....
              Jonathan Leffler -- Wednesday, 9 May 2012, at 2:13 p.m.
              • Re: Configure IDS 11.50 to avoid sorts going d....
                FRANK -- Wednesday, 9 May 2012, at 2:41 p.m.
                • Re: Configure IDS 11.50 to avoid sorts going d....
                  Art Kagel -- Wednesday, 9 May 2012, at 3:04 p.m.
                  • Re: Configure IDS 11.50 to avoid sorts going d....
                    FRANK -- Wednesday, 9 May 2012, at 3:27 p.m.
                • Re: Configure IDS 11.50 to avoid sorts going d....
                  wanderlei.barboza -- Wednesday, 9 May 2012, at 3:35 p.m.
                  • Re: Configure IDS 11.50 to avoid sorts going d....
                    Art Kagel -- Wednesday, 9 May 2012, at 3:58 p.m.
  • Re: Configure IDS 11.50 to avoid sorts going disk
    John Miller iii -- Thursday, 3 May 2012, at 6:07 p.m.
    • RE: Configure IDS 11.50 to avoid sorts going d....
      Jonathan Smaby -- Thursday, 3 May 2012, at 6:13 p.m.
      • Re: Configure IDS 11.50 to avoid sorts going d....
        Art Kagel -- Thursday, 3 May 2012, at 7:02 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.