IDS Forum

RE: How to check informix DB bottlenecks

Posted By: Wyza, Jonathon
Date: Wednesday, 3 March 2010, at 8:41 a.m.

In Response To: Re: How to check informix DB bottlenecks (JACK PAPA)

Pings of 45, or even 160 seem obscenely high. On our network (with 3 switches between me and the core and then from the core to the server) my ping to the server is .15ms. (yes that is less than 1ms) Pings like the ones you are referring to are something I'd expect if you were using VPN over the internet.

Jonathon Wyza
CX & CBORD System Administrator
CX Programmer/Analyst
Administrative Computing
Bethel College
(574)-257-3381
AIM: Iamwyza
jonathon.wyza@bethelcollege.edu
==========================
SLES 10 SP2 & IDS 10.0 HC9

" I would love to change the world, but they won't give me the source code." -- Unknown

-----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.

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.