IDS Forum

Re: Optimising index builds

Posted By: david@smooth1.co.uk
Date: Sunday, 19 January 2014, at 1:23 p.m.

In Response To: Re: Optimising index builds (NEIL TRUBY)

> On 19 January 2014 at 11:10 NEIL TRUBY <neil.truby@ardenta.com> wrote:
>
> >> Is DS_TOTAL_MEMORY set high enough to permit significant in-memory sorting?
>
> I changed DS_TOTAL_MEMORY to 300GB and reduced BUFFERPOOL to 160GB. Now
> nothing gets written out to the DBTEMP_SORT file system and all is sorted in
> memory.
>
> It still takes an hour!
>
> I guess then I am constrained by the speed I can read the data in. But I'm
> surprised that it's not faster. Thinking about it the data is not on
> Solid-State as I said previously, but still on pretty fast SAS drives in a
> fast and modern storage array.
>

export LIGHT_SCANS=FORCE

May sure light scans can be used,for 11.70 as per
http://pic.dhe.ibm.com/infocenter/idshelp/v117/index.jsp?topic=%2Fcom.ibm.perf.doc%2Fids_prf_237.htm

"The query meets one of the following locking conditions:
* The isolation level is Dirty Read (or the database has no transaction
logging).
* The table has at least a shared lock on the entire table and the isolation
level is notCursor Stability.
Note: A sequential scan in Repeatable Read isolation automatically acquires a
share lock on the table."
Run onstat -g scn and see what type of scan is being used.

Run onstat -z then after 30 seconds onstat -g ioa and send the output.

Run onstat -g mgm and make sure PDQ memory is being used and no gating is
occuring.

Run onstat -g ppf twice and diff the output, see what and how many parttitions
are being accessed.

Run onstat -g pqs twice and diff the output, what query operators are being
used?

Run WSTATS 1 and QTSTATS 1 in onconfig and run onstat -g wst/ onstat -g qst

Run onstat -g ses to get the thread ids and run onstat -g tpf twice for the
thread ids and diff the output.

Run iostat -x 4 and check /var/log/messages.

If still nothing obvious send us the output - for those entries above which
required diffs just send the diffs done several times 30 seconds apart.

Regards,
David.

>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>

Messages In This Thread

• Optimising index builds
  NEIL TRUBY -- Saturday, 18 January 2014, at 3:49 p.m.
  • Re: Optimising index builds
    Madison Pruet -- Saturday, 18 January 2014, at 4:05 p.m.
    • Re: Optimising index builds
      NEIL TRUBY -- Saturday, 18 January 2014, at 4:11 p.m.
      • Re: Optimising index builds
        Art Kagel -- Saturday, 18 January 2014, at 10:19 p.m.
  • Re: Optimising index builds
    Art Kagel -- Saturday, 18 January 2014, at 10:17 p.m.
    • Re: Optimising index builds
      NEIL TRUBY -- Sunday, 19 January 2014, at 6:10 a.m.
      • Re: Optimising index builds
        david@smooth1.co.uk -- Sunday, 19 January 2014, at 1:23 p.m.
        • Re: Optimising index builds
          NEIL TRUBY -- Sunday, 19 January 2014, at 2:15 p.m.
          • RE: Optimising index builds
            Andrew Ford -- Monday, 20 January 2014, at 10:17 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.