IDS Forum

AW: How to check informix DB bottlenecks

Posted By: Andreas.KUTSCHE@spar.at
Date: Wednesday, 3 March 2010, at 4:01 a.m.

In Response To: RE: How to check informix DB bottlenecks (Jack Parker)

Hello,

network latency might have quite an impact, if the application is reading a lot of rows one row after the other (without array fetch), or even worse, if it is reading a lot of rows through a lot of small select statements.

But from the other performance indicators given (sequential scans, cache rate) I would focus on the single table / SQL on that table.

Regards,
Andreas

>
-------------------------------------------
SPAR Österreichische Warenhandels-AG
Hauptzentrale
A - 5015 Salzburg, Europastrasse 3
FN 34170 a

Tel: +43 662 4470 84923
Fax:
Mobile: +43 664 6259575
E-Mail: Andreas.KUTSCHE@spar.at
Internet: http://www.spar.at

Wichtiger Hinweis: Der Inhalt dieser E-Mail kann vertrauliche und rechtlich geschützte Informationen, insbesondere Betriebs- oder Geschäftsgeheimnisse, enthalten, zu deren Geheimhaltung der Empfänger verpflichtet ist. Die Informationen in dieser E-Mail sind ausschließlich für den Adressaten bestimmt. Sollten Sie die E-Mail irrtümlich erhalten haben so ersuchen wir Sie, die Nachricht von Ihrem System zu löschen und sich mit uns in Verbindung zu setzen.
Über das Internet versandte E-Mails können leicht manipuliert oder unter fremdem Namen erstellt werden. Daher schließen wir die rechtliche Verbindlichkeit der in dieser Nachricht enthaltenen Informationen aus. Der Inhalt der E-Mail ist nur rechtsverbindlich, wenn er von uns schriftlich bestätigt und gezeichnet wird.
Sollte trotz der von uns verwendeten Virus-Schutzprogramme durch die Zusendung von E-Mails ein Virus in Ihre Systeme gelangen, haften wir nicht für evtl. hieraus entstehende Schäden.
Wir danken für Ihr Verständnis.

Important notice: The contents of this e-mail may contain confidential and legally protected information that is in particular related to operational and trade secrets, which the recipient is obliged to treat as confidential. The information in this e-mail is made available exclusively for use by the addressee. In the event that the e-mail may have been sent to you in error, we would ask you to kindly delete this communication from your system and to contact us.
E-mails sent via the Internet can be easily manipulated or sent out under someone else's name. We therefore do not accept legal liability for the information contained in this communication. The contents of the e-mail are only legally binding if they have been confirmed and signed by us in writing.
If, in spite of our using Antivirus protection software, a virus may have penetrated your system through the sending of this e-mail, we do not accept liability for any damage that may possibly arise as a result of this.
We trust that you appreciate our position.

-------------------------------------------
-----Ursprüngliche Nachricht-----

> Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von
> Jack Parker
> Gesendet: Mittwoch, 3. März 2010 03:55
> An: ids@iiug.org
> Betreff: RE: How to check informix DB bottlenecks [19165]
>
> .... I have a little script ... ah well.
>
> Network latency of 160 does not sound like an issue of this order.
>
> So if this was just migrated over to new hardware - how was that migration
> performed? -- regardless, that's just how it might have happened, not WHAT
> has happened.
>
> If you have pinpointed it to a single table, have a look at that table in
> particular -
>
> >From systables get the partnum (it will be 0 if it is a fragmented table
> -
> in which case, follow the tabid to sysfragments and get all of the
> partnumbers for each fragment from there). Typically, you might exclude
> the
> indexes from the list (from sysfragments) but that might be interesting as
> well.
>
> Then, in the sysmaster database.
>
> select pt.dbsname, pt.tabname, pt.partnum,
>
> ti_flags, ti_rowsize, ti_ncols, ti_nkeys, ti_nextns,
>
> ti_pagesize, date('01/01/1970') + trunc(ti_created/86400),
>
> ti_serialv, ti_fextsiz, ti_nextsiz,
>
> ti_nptotal, ti_npused, ti_npdata, ti_nrows,
>
> abs(lockreqs),
>
> abs(lockwts),
>
> abs(deadlks),
>
> abs(lktouts),
>
> abs(isreads),
>
> abs(iswrites),
>
> abs(isrewrites),
>
> abs(isdeletes),
>
> abs(bufreads),
>
> abs(bufwrites),
>
> abs(seqscans),
>
> abs(pagreads),
>
> abs(pagwrites)
> from sysptprof pt, systabinfo ti
> where pt.partnum in (LIST OF PART NUMBERS)
>
> and pt.partnum=ti.ti_partnum;
>
> This will get you all kinds of data on the table and it's activity. The
> answer may shout at you from that, if not, send it along.
>
> cheers
> j.
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of
> JACK PAPA
> Sent: Tuesday, March 02, 2010 9:27 PM
> To: ids@iiug.org
> Subject: Re: How to check informix DB bottlenecks [19162]
>
> First let me thank all of you guys for the nice inputs, really
> appreciated.
> The real problem actually is the user is complaining that they are
> experiencing too much slowness when performing a select, even just it is a
> simple select.
>
> The thing i noticed on the table they are complaining is this
>
> "extent size 256000 next size 32000 lock mode row";, the table they are
> complaining is locked on a per row basis. The thing is, on the original
> box,
> all are working normally, until it was migrated to a new box. database
> version, OS version are the same, the application is the same. The only
> changes are the network configuration and the hardware. Does the network
> plays
> a big part? on the user who is testing the application, when he tried to
> issue
> "ping hostname", the latency is quite high "160 average". from my pc, it's
> just "45".
>
> To Andrea, onstat -g ses pid|grep user|sort -n -k 9,shows these are the
> biggest pids..
>
> 93324aa0 Y--P--- 103041 user1 0 93884608 0 1 616345 158815
> 9ccf36e8 Y--P--- 102960 user1 0 9fbb8a18 0 1 743498 168588
> 9029f178 ---PR-- 110770 user1 0 0 0 2 3054975 1683
> 902a7da0 Y--P--- 2788 user1 0 94821068 0 1 8010009 1613611
>
> ----
> for pid 2788 which consumes the biggest mem.
>
> => onstat -g ses 2788
>
> IBM Informix Dynamic Server Version 9.40.UC6 -- On-Line (Prim) -- Up 2
> days
> 07:57:56 -- 2251504 Kbytes
>
> session #RSAM total used dynamic
> id user tty pid hostname threads memory memory explain
> 2788 user1 0 385090 Server1 1 1236992 1166864 off
>
> tid name rstcb flags curstk status
> 3457 sqlexec 902a7da0 Y--P--- 2264 cond wait(netnorm)
>
> Memory pools count 1
> name class addr totalsize freesize #allocfrag #freefrag
> 2788 V 9110e020 1236992 70128 2557 92
>
> name free used name free used
> overhead 0 1648 mtmisc 0 864
> resident 0 1248 scb 0 1400
> opentable 0 101104 filetable 0 8864
> ru 0 464 log 0 4208
> temprec 0 16200 keys 0 36504
> ralloc 0 868368 gentcb 0 1208
> ostcb 0 2736 sort 0 56
> sqscb 0 72144 sql 0 40
> rdahead 0 448 hashfiletab 0 280
> osenv 0 1792 buft_buffer 0 8352
> sqtcb 0 11728 fragman 0 17792
> udr 0 8152
>
> sqscb info
> scb sqscb optofc pdqpriority sqlstats optcompind directives
> 9127e2a8 91186018 0 1 0 0 1
> .............................................
> ......
>
> NOTE:
>
> The "update statistics high" is executed on a weekly basis. Before, it was
> 3x
> a week but it was stopped due to conflict with other running jobs.
>
> **************************************************************************
> **
> ***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> **************************************************************************
> *****
> Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• How to check informix DB bottlenecks
  JACK PAPA -- Tuesday, 2 March 2010, at 2:22 a.m.
  • AW: How to check informix DB bottlenecks
    Andreas.KUTSCHE@spar.at -- Tuesday, 2 March 2010, at 4:04 a.m.
    • Re: AW: How to check informix DB bottlenecks
      JACK PAPA -- Tuesday, 2 March 2010, at 4:37 a.m.
      • AW: AW: How to check informix DB bottlenecks
        Andreas.KUTSCHE@spar.at -- Tuesday, 2 March 2010, at 5:01 a.m.
      • Re: AW: How to check informix DB bottlenecks
        Art Kagel -- Tuesday, 2 March 2010, at 7:02 a.m.
      • RE: AW: How to check informix DB bottlenecks
        Jack Parker -- Tuesday, 2 March 2010, at 9:00 a.m.
  • Re: How to check informix DB bottlenecks
    Art Kagel -- Tuesday, 2 March 2010, at 6:42 a.m.
    • Re: How to check informix DB bottlenecks
      JACK PAPA -- Tuesday, 2 March 2010, at 9:26 p.m.
      • Re: How to check informix DB bottlenecks
        Art Kagel -- Tuesday, 2 March 2010, at 9:53 p.m.
      • RE: How to check informix DB bottlenecks
        Jack Parker -- Tuesday, 2 March 2010, at 9:54 p.m.
        • AW: How to check informix DB bottlenecks
          Andreas.KUTSCHE@spar.at -- Wednesday, 3 March 2010, at 4:01 a.m.
      • AW: How to check informix DB bottlenecks
        Andreas.KUTSCHE@spar.at -- Wednesday, 3 March 2010, at 3:57 a.m.
      • RE: How to check informix DB bottlenecks
        Wyza, Jonathon -- Wednesday, 3 March 2010, at 8:41 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.