IDS Forum

Re: Urgent help is needed...

Posted By: Kern Doe
Date: Monday, 3 December 2007, at 9:48 a.m.

In Response To: Re: Urgent help is needed... (Art S. Kagel (Oninit LLC))

Thank you Art.
Yesterday late afternoon, the application team closed down some "weblogic" to reduce traffic. But still, one query kept getting hit frequently (it's the nature of the app) and this one did not exit out causing xlocks situation, waits on Locks and Buffers situation -- then more subsequent 'dominal effect' -- we saw more and more xlocks. My guess that something was wrong with the optimizer or something was wrong in the statistics causing the optimizer confused and could not perform well with its execution plan (onstat -P showed much higher percentage for data than btree).

My partner did a upstat high on one index column of a table that related to the "bad" query. At the same time I frequently ran my quick and dirty script to extract the columns or the index of other tables and ran similar upstat on them too.

By the evening thing started to get better -- the "bad" I was talking about, now runs and comes back in a fraction of a second -- everything else seems normal (buffers, locks, xlocks, etc.) except for some high #mutexes with waiters (we see this all frequently anyway -- haven't been able to fix it and it is a separate issue).
Thanks

----- Original Message ----
From: Art S. Kagel (Oninit LLC) <art@oninit.com>
To: ids@iiug.org
Sent: Sunday, December 2, 2007 10:54:45 PM
Subject: Re: Urgent help is needed... [10572]

Kern Doe wrote:

Kern you are right that locks and buffer waits are independent. Also, if
all of the 'X' locks you see are the HDR+X and HDR+IX locks that's not
your problem. Every application takes an intent-exclusive (IX) lock on
the database's systables table and on the tablespace tablespace
(partition header) record of every table it has an open cursor against.
If your applications haven't changed and the server tuning hasn't
changed, then locks that were not causing errors before aren't suddenly
doing so.

On the other hand, if the database is growing, as it clearly is from
that one table's extents below, then there are several things that could
be suddenly causing wait delays long enough to cause what used to be
momentary locks that did not interfere with other apps/users to be
causing lock out errors like the 'cannot position' errors you are
seeing. Possible causes include slow disk, disk and/or channel
contention, swapping problems, insufficient buffers, too few LRU queues,
slow LRU flushing due to too few CLEANERS, poor application design
holding locks, excessive sequential scans, and others.

To track down what causes we can I'd suggest posting the following
output and we'll see what we can see:

Time since the server stats were last zero'd (restart or onstat -z run) and:
onstat -p
onstat -d
onstat -D
onstat -P
onstat -c
onstat -g iov
onstat -g iof
onstat -g glo
onstat -g seg

Art S. Kagel

> Thanks for your repply Marcus.
> Ok, I would think this is normal -- but I've seen output from oncheck -cc
> beore, locks (or xlocks) will cause a different error (ISAM error: record is
> locked.)
>
> The db had been stable for a while, no change to config or params. The big
> issues are xlocks, yes I do them -- they used to come and go, but now they
> stay
>
> onstat -k | grep X
> aea8504 0 5c676d08 b7adef0 HDR+IX 1e00050 0 0
> b32e954 0 5c676d08 b572980 HDR+IX 1e00044 0 0
> b33166c 0 5c676d08 b32e954 HDR+X 250000a 2500909 0
> b3337f0 0 5c676d08 b33166c HDR+IX 400022 0 0
> b56e094 0 5c676d08 b3337f0 HDR+X 800005 18b801 0
> b572980 0 5c676d08 b7b33a4 HDR+X 700003 8fe90a 0
> b7adef0 0 5c676d08 b56e094 HDR+IX 1a00001 0 0
> b7b33a4 0 5c676d08 b56b8c4 HDR+IX 40001f 0 0
>
> No remarkable in the onlinelog, buffer is 600000,
> onstat -
> IBM Informix Dynamic Server Version 7.31.UD8 -- On-Line -- Up 07:06:15 --
> 3711120 Kbytes
>
> oncheck -pc customer also shows something weird ...
> .... cut ....
>
> TBLspace customer:informix.session_answer Index xpksess_ans fragment in
> DBspace cust5idx
>
> Physical Address 460000e
>
> Creation date 02/24/2001 10:17:57
>
> TBLspace Flags 802 Row Locking
>
> TBLspace use 4 bit bit-maps
>
> Maximum row size 332
>
> Number of special columns 0
>
> Number of keys 1
>
> Number of extents 139
>
> Current serial value 1
>
> First extent size 4
>
> Next extent size 5120
>
> Number of pages allocated 266668
>
> Number of pages used 261943
>
> Number of data pages 0
>
> Number of rows 0
>
> Partition partnum 54525963
>
> Partition lockid 35651608
>
> Extents
> .... ....
>
> 251308 528c38e 5120
>
> 256428 52b5da5 5120
>
> 261548 52bd1f3 5120
>
> Index information.
>
> Number of indexes 1
>
> Data record size 332
>
> Index record size 2048
>
> Number of records 765068
> Unknown error message 0.
>
> ----- Original Message ----
> From: Marcus Haarmann <marcus.haarmann@midoco.de>
> To: ids@iiug.org
> Sent: Sunday, December 2, 2007 4:09:47 PM
> Subject: RE: Urgent help is needed... [10570]
>
> Hi,
>
> It is normally not a problem if you have bufwaits, but these should not
> take long.
> Is this a new problem, or has the database been stable in this setup for
> a while ? Was any config param changed ?
> Was the application changed ? If you have exclusive locks not being
> released, you should check these first (onstat -k to find out the owner,
> onstat -g sql to see what is the owner doing).
> Maybe you have a problem with wrong lock-levels ?
> Pls send more details on your problem. Onstat -p output in the first
> place, maybe you have remarkable output in online.log, what is your
> current buffer size ?
> A little more info would help ..
>
> Marcus
>
> -----Original Message-----
> From: Kern Doe [mailto:kern_doe@yahoo.com]
> Sent: Sunday, December 02, 2007 9:33 PM
> To: ids@iiug.org
> Subject: Urgent help is needed... [10569]
>
> Hi Gurus,
> We are having issues with the database (IDS: 7.31 UD8 on Solaris 8)
> where we are seeing lots of waits on buffers "B" -- sometimes
> applications fail with something like "cannot position within table" --
> some processes that hold exclusive locks do not release causing other
> ones to wait on and fail (time
> out) -- I run oncheck -cc, this is what I'm getting
>
> oncheck -cc customer
> Validating database customer
>
> Validating systables for database customer Unknown error message 0.
>
> IBM says it is because something holds on to systables which I'm not
> convinced.
> Can someone who has experience with this shreds some lights.
> Thanks
> Kern --
>
> ________________________________________________________________________
> ____________
> Get easy, one-click access to your favorites.
> Make Yahoo! your homepage.
> http://www.yahoo.com/r/hs
>
> ************************************************************************
> *******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
____________________________________________________________________________________
> Never miss a thing. Make Yahoo your home page.
> http://www.yahoo.com/r/hs
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

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

____________________________________________________________________________________
Be a better sports nut! Let your teams follow you
with Yahoo Mobile. Try it now. http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ

Messages In This Thread

• Urgent help is needed...
  Kern Doe -- Sunday, 2 December 2007, at 3:33 p.m.
  • RE: Urgent help is needed...
    Marcus Haarmann -- Sunday, 2 December 2007, at 4:09 p.m.
    • Re: Urgent help is needed...
      Kern Doe -- Sunday, 2 December 2007, at 4:31 p.m.
      • Re: Urgent help is needed...
        Art S. Kagel (Oninit LLC) -- Sunday, 2 December 2007, at 10:54 p.m.
        • Re: Urgent help is needed...
          Kern Doe -- Monday, 3 December 2007, at 9:48 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.