IDS Forum

RE: Update statistics performance

Posted By: Jonathan Leffler
Date: Thursday, 22 July 2004, at 8:37 p.m.

In Response To: RE: Update statistics performance (Sebastian, Norma J.)

If I read the original question correctly, you're using 7.31.UC5.

You should be on 7.31.UDn for a fairly large value of n -- you need to
upgrade, preferably to 9.40, but at least to 7.31.UDn.

That's in part on simple pragmatics - there have been security fixes in
7.31.UD7 (and 9.30.UC7, and 9.40.UC3), so you should upgrade for those
fixes alone.

IDS 9.40 definitely has performance improvements in update statistics -
but I believe they were proven in 7.31.UDx first.

--
Jonathan Leffler (jleffler@us.ibm.com)
STSM, Informix Database Engineering, IBM Data Management
4100 Bohannon Drive, Menlo Park, CA 94025
Tel: +1 650-926-6921 Tie-Line: 630-6921
"I don't suffer from insanity; I enjoy every minute of it!"



forum.subscriber@iiug.org wrote on 07/22/2004 04:52:21 AM:

> Ken,
> Maybe perf tuning to deal with the 61 foreground writes (these are
> sqlexec user threads writing to disk, this is bad), the read cache %
> of 62.78% (kinda low), and the 3670835 seqscans (this might not be
bad)....
>
> Perf tuning those might help the perf of your update stats?
>
>
> Fg Writes LRU Writes Chunk Writes
> 61 5808527 35281444
>
> I'm not sure on this.... But maybe your LRU writes are too low in
> comparison to your chunk writes. Aren't chunk writes done during
> checkpoints (no prob there with that number), and LRU writes done
> during min/max dirty infx monitoring of the buffer cache? ... If
> so, perhaps getting more self-cleaning via LRU writes instead of
> letting the checkpoints do all the work might help.... BUT then you
> didn't say anything about checkpoint issues...
>
> I could be way off base, but had to toss that in .... (I'm dealing
> with the nitty gritty perf issues on my system now so it catches my
eye).
>
> Norma Jean
>
>
>
>
> -----Original Message-----
> From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]
> On Behalf Of Kenneth Makobe
> Sent: Thursday, July 22, 2004 12:39 AM
> To: ids@iiug.org
> Subject: Update statistics performance [3275]
>
> Hi all
> Update Statistic takes a long duration of up to 13 hours to
> complete, our DB is more than 35 gb. I tried to use PDQ ,
> PSORT_NPROCS and DBSPACETEMP and the was only a slight improvement
> i.e from 13 to 12 hours. our IDS version is
> 17.31 uc5 Kindly advice on how this duration can be reduced Below
> is the profile and i/o queue stats and onstat -F profile
>
> Profile
> dskreads pagreads bufreads Êched dskwrits pagwrits bufwrits Êched
> 229690758 36261273 617122098 62.78 28229782 19567333 287833239 90.19
>
> isamtot open start read write rewrite delete commit
> rollbk
> 144865383 30923634 247207201 3231528422 186584701 26339127 233319 47761
> 4
>
> gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
> 0 0 0 0 0 0 0
>
> ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
> 0 0 0 529902.17 13244.00 1267 2534
>
> bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
> 30833338 50871 3330742991 0 0 4886 396427 3670835

>
> ixda-RA idx-RA da-RA RA-pgsused lchwaits
> 6695267 15958151 175978636 198623320 15834291
>
> AIO I/O queues:
> q name/id len maxlen totalops dskread dskwrite dskcopy
> kio 0 0 0 0 0 0 0
> kio 1 0 16 57579748 52927126 4652622 0
> kio 2 0 0 0 0 0 0
> kio 3 0 16 108007501 96187935 11819566 0
> kio 4 0 16 97292492 85534898 11757594 0
> adt 0 0 0 0 0 0 0
> msc 0 0 1 9313 0 0 0
> aio 0 0 2 36214109 2 36213915 0
> omstat -F
>
>
> Fg Writes LRU Writes Chunk Writes
> 61 5808527 35281444
>
> address flusher state data
> 60050500 0 I 0 = 0X0
> 600509ec 1 I 0 = 0X0
> 60050ed8 2 I 0 = 0X0
> 600513c4 3 I 0 = 0X0
> 600518b0 4 I 0 = 0X0
> 60051d9c 5 I 0 = 0X0
> 60052288 6 I 0 = 0X0
> 60052774 7 C 79 = 0X4f
> 60052c60 8 I 0 = 0X0
> 6005314c 9 I 0 = 0X0
> 60053638 10 I 0 = 0X0
> 60053b24 11 I 0 = 0X0
> 60054010 12 I 0 = 0X0
> 600544fc 13 I 0 = 0X0
> 600549e8 14 C 78 = 0X4e
> 60054ed4 15 I 0 = 0X0
> states: Exit Idle Chunk Lru
>
>
> -----------------------------------------
> ============================================================
> The information contained in this message may be privileged
> and confidential and protected from disclosure. If the
> reader of this message is not the intended recipient, or an
> employee or agent responsible for delivering this message to
> the intended recipient, you are hereby notified that any
> reproduction, dissemination or distribution of this
> communication is strictly prohibited. If you have received
> this communication in error, please notify us immediately by
> replying to the message and deleting it from your computer.
>
> Thank you.
> Tellabs
> ============================================================
>

Messages In This Thread

• Update statistics performance
  Kenneth Makobe -- Thursday, 22 July 2004, at 1:39 a.m.
  • RE: Update statistics performance
    McCabe-Reed, Barbara SIK -- Thursday, 22 July 2004, at 5:17 a.m.
    • Re: RE: Update statistics performance
      CHUAN LU -- Saturday, 25 March 2006, at 3:20 a.m.
  • RE: Update statistics performance
    Sebastian, Norma J. -- Thursday, 22 July 2004, at 7:52 a.m.
    • RE: Update statistics performance
      Jonathan Leffler -- Thursday, 22 July 2004, at 8:37 p.m.
  • RE: Update statistics performance
    Kenneth Makobe -- Monday, 26 July 2004, at 2:52 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.