Re: max number of connections

Micha answered Murray Wood: > > This is very abnormal and suggests a fault with OS or Informix setup / > > configuration. ...> > The logs show nothing. The problem is that it happens say once per day and > one cannot wait at the terminal the whole day long to type an onstat query. > We have a watchdog program that finds the hangup and sends an error message > but till we get it, it is already over. Can you monitor the watchdog program to gather the diagnostics you need? Alternately, can you build a test instance to reproduce the hang on? Then you could add one connection at a time until freeze. If NETTYPE is not set, the values are (according to the Administrator's Guide for Informix Dynamic Server at http://publibfi.boulder.ibm.com/epubs/pdf/4354.pdf ): protocol: On UNIX: nettype field from the sqlhosts file or registry (optionally minus the database server prefix of on or ol) poll_threads: 1 connections: 50 VP_class: NET if it is for DBSERVERALIASES; CPU if it is for DBSERVERNAME Does the hang occur when you cross the 50 connections threshhold? On Windows, I have seen the number of user connections set too low to handle the threads, but never on UNIX. I have seen UNIX boxes that could not handle more than a certain number of connections due to kernel parameters, or simply being out of resources. (Sorry, I could not find the specific case to tell you what changed; it was a very long time ago and the customer eventually switched boxes.) However, I would expect to see "Allocating shared memory" messages in the online log. I guess the real question is whether the freeze is actually caused by the connections, or if this is simply a red herring. Sincerely, Christopher Coleman Database Analyst Pharmacy Division Mediware Information Systems, Inc.

Christopher wrote: > Can you monitor the watchdog program to gather the diagnostics you > need? I now have a cron job that runs each minute and gets some stats. The problem is, I don't really know what to look for. Some of the sessions that hang are shown in onstat -g ses as ready, flags ---P--- and some as cond wait(netnorm), flags Y--P---.Strangely, last parsed SQL statement does not correspond to the current one, I log each query before I send it to the database to know where it hangs just in case it would be a locking problem (but it is not, the queries are different each time) and this does not match, so it seems to hang somewhere before parsing the query(?). Talking about statistics, is such an output of onstat -u normal: Userthreads address flags sessid user tty wait tout locks nreads nwrites 57e32014 ---P--D 1 root - 0 0 0 18898 118981 57e32500 ---P--F 0 root - 0 0 0 0 1491850 57e329ec ---P--F 0 root - 0 0 0 0 873978 57e32ed8 ---P--F 0 root - 0 0 0 0 659777 57e333c4 ---P--F 0 root - 0 0 0 0 158694 57e338b0 ---P--F 0 root - 0 0 0 0 19194 57e33d9c ---P--F 0 root - 0 0 0 0 0 57e34288 ---P--F 0 root - 0 0 0 0 0 57e34774 ---P--F 0 root - 0 0 0 0 0 57e34c60 ---P--- 11 root - 0 0 0 0 24508 57e3514c ---P--B 12 root - 0 0 0 1 608 57e35638 Y--P--- 2378693 dbusr - 5d2b383c 0 1 21 46252 57e35b24 ---P--D 2377253 root - 0 0 0 0 0 57e36010 ---P--D 202 root - 0 0 0 0 15 57e364fc Y--P--- 2378694 dbusr - 5810f70c 0 2 42 45610 57e369e8 ---P--D 2377254 root - 0 0 0 0 0 57e36ed4 Y--P--- 2378692 dbusr - 5811fe9c 0 2 9 45869 57e373c0 Y--P--- 2378697 dbusr - 57fbccb0 0 2 67 237360 57e378ac Y--P--D 2100323 root - 6f636c73 0 0 0 0 57e37d98 ---P--D 2068683 root - 0 0 0 0 0 57e38284 Y--P--- 2378698 dbusr - 57fbd384 0 2 87 239418 57e38770 Y--P--D 1818167 root - 6f636c73 0 0 0 0 57e38c5c Y--P--D 642 root - 5f667361 0 0 0 0 57e39148 ---P--D 45169 root - 0 0 0 0 0 57e39634 ---P--D 2123470 root - 0 0 0 0 0 57e3a00c Y--P--D 2036576 root - 26 0 0 0 0 57e3a4f8 Y--P--D 1712438 root - 81747d4 0 0 0 0 57e3a9e4 ---P--D 1766649 root - 0 0 0 0 0 57e3b3bc ---P--D 2137462 root - 0 0 0 0 0 57e3b8a8 Y--P--- 2378696 dbusr - 57fbc5fc 0 2 75 238932 57e3c76c ---P--D 1921344 root - 0 0 0 0 0 57e3cc58 Y--P--D 1753031 root - 5f736e69 0 0 0 0 57e3d144 S--P--D 2107187 root - 6d726f 0 0 0 0 57e3d630 Y--P--D 2085350 root - 6e74656e 0 0 0 0 57e3db1c Y--P--D 2169476 root - 6574656e 0 0 0 0 57e3e008 ---P--D 2373171 root - 0 0 0 0 0 57e3e4f4 Y--P--D 2092561 root - 5f667361 0 0 0 0 57e3e9e0 Y--P--D 2036942 root - 68736168 0 0 0 0 57e3f8a4 Y--P--- 2378699 dbusr - 57fbda58 0 2 98 238709 57e3fd90 S--P--D 2078079 root - 6d726f 0 0 0 0 57e40768 Y--P--D 2246211 root - 59d46014 0 0 0 0 57e40c54 Y--P--D 2377252 root - 57f06b04 0 0 7109 1359 57e4162c ---P--D 2184866 root - 0 0 0 0 0 57e41b18 ---P--D 2362493 root - 0 0 0 0 0 57e42004 Y--P--- 2412305 dbusr - 58010930 0 1 0 1 57e429dc Y--P--- 2378695 dbusr - 581d5bc0 0 2 28 45939 46 active, 128 total, 58 maximum concurrent There are so many root sessions, the two with the S flag have it all the time, that does not look right? > > > Alternately, can you build a test instance to reproduce the hang on? > Then you could add one connection at a time until freeze. > No, I have to misuse the production one :-) Actually, I have tried to reproduce it and this time I could not, even with 10 new connections like before, but I did not look at onstat -u to check the actual current number. > Does the hang occur when you cross the 50 connections threshhold? > This may well be - as shown above, there are 46 active ones and max was 58. --Micha