Re: Latchwaits

jerry (warlok!kysilk.sherwood.com!jerry@uunet.UU.NET) wrote: : Can anyone explain why the latch wait count on a system would suddenly : climb? We have a production system that has gone from 6,000 to : 300,000 when comparing the same period on two different days. I have : researched the docs and found the one or two page explaination on : latches but it does not give me enough information to figure out what : is going wrong. Informix support has been no help so far (contacted : them on Monday 2/7). The docs state that the number of latches is set : internally by a combination of "other" shared memory parameters but : they do not state what the calculation is or what the parameters are. : This is causing a very very noticable slow down on the system when a : user saves a record (times in excess of 5 min.) and we really need to : get this information ASAP. : Any help is appreciated You need to find out which latches are being waited on. Use the following scriptcalled "waiters": # tbstat -u $1 | awk '$6 != 0 {print $0}'| grep -v RSAM | grep -v active | grep -v Users | grep -v "^$" # waiters can be invoked with or without a parameter. If you invoke it as "waiters filename.out", it will look at a file name you give it as the shared memory. (You can snapshot your shared memory to a file with tbstat -o filename.out) Run waiters many times and see if you see any pattern as to which latches show up as holding other users up. Once you have a suspect or two, run multiple runs of tbstat -s and see which latches correspond to these addresses. You're not looking at a problem with NUMBER of latches parameters, you're probably looking at some antisocial behavior by an application. I've found that a common problem is waiting for the latch on the locks table. If your tbstat -u output reveals any processes that are grabbing a lot of locks, look for SQL statements doing inserts into a table from another table without locking the target table. : -- : Jerry M. Denman -- Director of Technical Services : Sherwood Systems (a division of Sherwood Mfg Co. Inc.) jerry@sherwood.com : 4760 N. Central Phone:(602) 230-8188 : Phoenix AZ, 85012 FAX:(602) 230-9491 : ------------------------------------------------------------------------------ : A true realist demands the impossible : A true idealist demands the impractical -- =========================================================================== jlumbley@netcom.com (Joe Lumbley) "If you believe that my statements Database Administrator represent my company, please send The Tigon Corporation money and I'll send you stock" Dallas, Texas Watch for my _INFORMIX DBA SURVIVAL GUIDE_ in Fall '94 from Prentice Hall! ===========================================================================