Informix Status Explanations

hi, can anyone shed light on this for me. what does this mean? status yield bufwait- does it mean i'm lacking buffer pools? what does this indicate? cond wait netnorm - session is not doing anything. running- does it mean the sql is currently running. yield lockwaits - does this mean that sql is currently to execute and is waiting for a lock to be released? Thanks!

if i have a lot of yield bufwaits, would that mean i need to increase bufferpool size?

original post: hi, can anyone shed light on this for me. what does this mean? status yield bufwait- does it mean i'm lacking buffer pools? what does this indicate? cond wait netnorm - session is not doing anything. running- does it mean the sql is currently running. yield lockwaits - does this mean that sql is currently to execute and is waiting for a lock to be released? Thanks! response: bufwait: Too small of a buffer pool can cause some buff waits, but more specifically it means the waiter is trying to access the buffer in a way not compatible to the state of the current buffer. Buffer's can get share locked (multiple threads can share lock a buffer think of this as just reading the buffer) or they can be exclusively locked (we only want 1 person modifying memory at 1 time so this would be for modification actions). So you can't have different threads share locking the buffer and exclusively locking the buffer at the same time. netnorm: the server sqlexec thread is at a point in execution where it's waiting for information from the client, usually because the client hasn't made a request for the server to do anything running: the sqlexec thread is currently running processing the current SQL operation lockwait: the sqlexec thread was trying to process some SQL operation and it encountered a lock so if you have set lock mode to wait, it then yields either the lock wait amount or indefinitely for the lock required to be released. Jacques Renaut IBM Informix Advanced Support APD Team

All of your guesses are correct. A bufwait occurs under two ceircumstances. One is when a page in the cache is locked by a session that needs to modify its contents either to modify the existing page, to read a new page into a cache page from disk, or to create a new page to be appended to a table. The other is when a session needs to modify the buffer cache's LRU queue contents. This happens when a clean page is modified - the page must be moved from the clean part of the lru queue to the dirty part, when a page is read into the cache from disk and when a page is read that has not been read for a while. In these latter two cases the page must be moved to the most-recently-used end of the lru queue from its current position in the queue. So this thread status indicates that the thread has gone into a pause loop or has gone to sleep waiting for the bufwait latch it is waiting for to be released. If it is an lru queue latch the thread may eventually rehash to a different lru queue if it has to wait too long without acquiring the current latch. If it is waiting to read in a page or to create a new page it will try again to see if the buffer latch was released of if that page is no longer the least-recently-used page. If it is waiting for a specific buffer Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Feb 2, 2012 at 12:10 PM, NATYURAL HORACIO < horacio.natyural@gmail.com> wrote: > hi, > > can anyone shed light on this for me. > what does this mean? > > status > > yield bufwait- does it mean i'm lacking buffer pools? what does this > indicate? > cond wait netnorm - session is not doing anything. > running- does it mean the sql is currently running. > yield lockwaits - does this mean that sql is currently to execute and is > waiting for a lock to be released? > > Thanks! > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9399c1f27ea1604b7fe9cc5

That may help, but the vast majority of bufwaits are caused by lru queue latches. You need to increase the number of lru queues for the specific buffer cache that it is waiting on in the BUFFERPOOL parameter lrus=<n> option. Ideally this should be close to the maximum number of concurrent sessions that are active at any given time which you can guess at by greping for sqlexec threads in the onstat -g act output and counting them during peak load periods. If that is a very large number then just max out lrus (128 for 32bit versions, 512 for 64bit versions of Informix) for each highly active cache. You can live with lower lrus settings for buffer caches that are not as hot (like historical data in wide page caches). Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Feb 2, 2012 at 12:28 PM, NATYURAL HORACIO < horacio.natyural@gmail.com> wrote: > if i have a lot of yield bufwaits, > would that mean i need to increase bufferpool size? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9399c1ff2260304b7feab22

Get my package called ratios.shr_ak from the IIUG SOftware Repository. The newratios.ksh script in there reports several calculated metrics which can help you zoom in on the causes of the most common performance problems. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Feb 2, 2012 at 12:28 PM, NATYURAL HORACIO < horacio.natyural@gmail.com> wrote: > if i have a lot of yield bufwaits, > would that mean i need to increase bufferpool size? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3ba429f12ec104b7feb3f1

So is yield buffwait a normal thing to experience? I have around 84 sessions in yield buffwait. So is yield buffwait a sort of running stste as well?

btw, i've also noticed one thing. my update and insert statements have yield lockwait- statuses. the selects mostly have yield buffwait-

OK, so inserts updates and deletes are far more likely to run into locks than selects are since the latter ignore shared locks. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Feb 2, 2012 at 12:57 PM, NATYURAL HORACIO < horacio.natyural@gmail.com> wrote: > btw, > i've also noticed one thing. > > my update and insert statements have yield lockwait- statuses. > the selects mostly have yield buffwait- > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340f9df667e004b7fefddf

Hi, So locks can only be made by statements that are in running status right? This prevents updates etc....... The only statement in running status on a particular table is a select count(*) statement in committed read.... Does this mean smething? i have 4 running statements and 789 lockwaits all waiting to insert.... Thanks

Normal but not necessarily desireable. If your Bufwaits Ratio (BR) is above 7 then these waits are slowing things up a bit. If BR is above 10 then it is a serious problem. BR is calculated from the values in the onstat -p report as: BR = (bufwaits / (pagreads + bufwrites))*100 This is one of the metrics calculated for you by the newratios.ksh script in my ratios.shr_ak package. So, on one server I manage: $ onstat -p IBM Informix Dynamic Server Version 11.70.FC2 -- On-Line (Prim) -- Up 7 days 16:35:53 -- 4217400 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 127261260 118352270 5739796051 97.78 18397182 57286121 74478172 82.27 isamtot open start read write rewrite delete commit rollbk 4604680336 12921145 15982557 2844044537 111510299 384181 338494 4021422 11320 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 91054.10 16485.30 2559 2977 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 4329079 4 12691554952 1 0 847 985636 798093 ixda-RA idx-RA da-RA RA-pgsused lchwaits 31980 163874 80925308 81079132 736689 $ bc scale=5 (4329079 / (118352270 + 74478172)) * 100 2.24500 So the BR here is 2.245 which is ideal ;-) Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Feb 2, 2012 at 12:51 PM, NATYURAL HORACIO < horacio.natyural@gmail.com> wrote: > So is yield buffwait a normal thing to experience? > I have around 84 sessions in yield buffwait. > So is yield buffwait a sort of running stste as well? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f22c681e8199504b7ff3367

No. Sessions in netnorm, bufwait, lockwait, and many other statuses can also be holding locks. Status netnorm, for example, just means that the server is waiting for the client to do something. The client may be in the middle of fetching from a cursor. The engine has recently returned a communication buffer full of data and is waiting for the client to process that block of rows and request the next buffer's worth of data. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Feb 2, 2012 at 1:12 PM, NATYURAL HORACIO <horacio.natyural@gmail.com > wrote: > Hi, > > So locks can only be made by statements that are in running status right? > This prevents updates etc....... > The only statement in running status on a particular table is a select > count(*) statement in committed read.... Does this mean smething? > > i have 4 running statements and 789 lockwaits all waiting to insert.... > Thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9399c1f1dac7004b7ff4313