IDS v11.10 hang: no error / logicalLog not full

Hello, I am supporting an application running on Informix (IDS v11.10UC3) and sometimes the Informix database hung. Please find below the symptoms: - The onstat commands are still running but not the commands dbaccess, onmonitor... - there is no error log in the online.log or console messages. - Server continues to perform checkpoints on database, but there is no longer console message. - command 'onmode -kuy' is not responding, need to manually kill the oninit adm process. I am not an Informix expert but I did some investigations and here are my conclusions: - the logicalLogs are not full because the old logicalLogs are correctly backed up, so they can be reused (see onstat -l below) - no thread lock on database: there are two userThreads waiting but it seems to be normal: sid 25 is waiting a network connection to execute query sid 18 is an informix thread waiting "bp_cond bf_priosweep" (I read somewhere it is a normal state for IDS) Could someone help ? Thanks, steve address number flags uniqid begin size used %used 2d2ce5b0 7 U-B---- 8908 3:53 20000 20000 100.00 2d2ce5f8 8 U-B---- 8909 3:20053 20000 20000 100.00 2d2ce640 9 U-B---- 8910 3:40053 20000 20000 100.00 2d2ce688 10 U-B---- 8911 3:60053 20000 20000 100.00 2d2ce6d0 11 U-B---- 8912 3:80053 20000 20000 100.00 2d2ce718 12 U-B---- 8913 3:100053 20000 20000 100.00 2d2ce760 13 U-B---- 8914 3:120053 20000 20000 100.00 2d2ce7a8 14 U-B---- 8915 3:140053 20000 20000 100.00 2d2ce7f0 15 U-B---- 8916 3:160053 20000 20000 100.00 2d2ce838 16 U---C-L 8917 3:180053 20000 19766 98.83 2d2ce880 17 U-B---- 8895 3:200053 20000 20000 100.00 2d2ce8c8 18 U-B---- 8896 3:220053 20000 20000 100.00 2d2ce910 19 U-B---- 8897 3:240053 20000 20000 100.00 2d2ce958 20 U-B---- 8898 3:260053 20000 20000 100.00 2d2ce9a0 21 U-B---- 8899 3:280053 20000 20000 100.00 2d2ce9e8 22 U-B---- 8900 3:300053 20000 20000 100.00 2d2cea30 23 U-B---- 8901 3:320053 20000 20000 100.00 2d2cea78 24 U-B---- 8902 3:340053 20000 20000 100.00 2d2ceac0 25 U-B---- 8903 3:360053 20000 20000 100.00 2d2ceb08 26 U-B---- 8904 3:380053 20000 20000 100.00 2d2ceb50 27 U-B---- 8905 3:400053 20000 20000 100.00 2d2ceb98 28 U-B---- 8906 3:420053 20000 20000 100.00 2d2cebe0 29 U-B---- 8907 3:440053 20000 20000 100.00 23 active, 23 total Userthreads address flags sessid user tty wait tout locks nreads nwrites 2d28e018 ---P--D 1 informix - 0 0 0 87 844 2d28e5f0 ---P--F 0 informix - 0 0 0 0 45550 2d28ebc8 ---P--F 0 informix - 0 0 0 0 8827 2d28f1a0 ---P--F 0 informix - 0 0 0 0 215 2d28f778 ---P--F 0 informix - 0 0 0 0 0 2d28fd50 ---P--F 0 informix - 0 0 0 0 0 2d290328 ---P--F 0 informix - 0 0 0 0 0 2d290900 ---P--F 0 informix - 0 0 0 0 0 2d290ed8 ---P--F 0 informix - 0 0 0 0 0 2d2914b0 ---P--- 9 informix - 0 0 0 0 0 2d291a88 ---P--B 10 informix - 0 0 0 204 128 2d292060 Y--P--- 25 myAppli - 2dec44e8 0 1 134 539 2d292638 ---PR-- 21 informix - 0 0 2 507 28 2d292c10 ---P--D 13 informix - 0 0 0 0 0 2d2931e8 Y--P--D 18 informix - a3c7008 0 0 0 0 2d2937c0 ---P--- 20 informix - 0 0 1 251 2 2d293d98 ---P--- 22 informix - 0 0 2 1518 270 17 active, 128 total, 25 maximum concurrent Conditions with waiters: cid addr name waiter waittime .... 657 2dec44e8 netnorm 57 39946 626 a3c7008 bp_cond 49 158 ... Threads tid tcb rstcb prty status vp-class name ... 57 2db9dc18 2d292060 1 cond wait netnorm 1cpu sqlexec 49 2dc44d40 2d2931e8 1 cond wait bp_cond 4cpu bf_priosweep ... Locks address wtlist owner lklist type tblsnum rowid key#/bsiz a5dd908 0 2d2937c0 0 HDR+S 100002 209 0 a5dd968 0 2d292060 0 HDR+S 100002 201 0 ab96688 0 2d292638 0 S 100002 209 0 ab96748 0 2d292638 ab96688 HDR+S 100002 207 0 ab96868 0 2d293d98 0 S 100002 209 0 ab968c8 0 2d293d98 ab96868 S 100002 207 0 6 active, 250000 total, 65536 hash buckets, 0 lock table overflows

Steve Two things spring immediately to mind. Is Enterprise Replication involved anywhere with this server ? How are you connecting to the server ? Shared memory or Network/TCP (post NETTYPE lines from onconfig file). It may be you've had network errors on the physical server or another app has grabbed the port. Keith On 29 October 2010 14:12, STEVE DELEIGNIES <stevedeleignies@hotmail.com> wrote: > Hello, > > I am supporting an application running on Informix (IDS v11.10UC3) and > sometimes the Informix database hung. Please find below the symptoms: > - The onstat commands are still running but not the commands dbaccess, > onmonitor... > - there is no error log in the online.log or console messages. > - Server continues to perform checkpoints on database, but there is no longer > console message. > - command 'onmode -kuy' is not responding, need to manually kill the oninit > adm process. > > I am not an Informix expert but I did some investigations and here are my > conclusions: > - the logicalLogs are not full because the old logicalLogs are correctly > backed up, so they can be reused (see onstat -l below) > - no thread lock on database: there are two userThreads waiting but it seems > to be normal: > > sid 25 is waiting a network connection to execute query > > sid 18 is an informix thread waiting "bp_cond bf_priosweep" (I read somewhere > it is a normal state for IDS) > > Could someone help ? > Thanks, > steve > > address number flags uniqid begin size used %used > 2d2ce5b0 7 U-B---- 8908 3:53 20000 20000 100.00 > 2d2ce5f8 8 U-B---- 8909 3:20053 20000 20000 100.00 > 2d2ce640 9 U-B---- 8910 3:40053 20000 20000 100.00 > 2d2ce688 10 U-B---- 8911 3:60053 20000 20000 100.00 > 2d2ce6d0 11 U-B---- 8912 3:80053 20000 20000 100.00 > 2d2ce718 12 U-B---- 8913 3:100053 20000 20000 100.00 > 2d2ce760 13 U-B---- 8914 3:120053 20000 20000 100.00 > 2d2ce7a8 14 U-B---- 8915 3:140053 20000 20000 100.00 > 2d2ce7f0 15 U-B---- 8916 3:160053 20000 20000 100.00 > 2d2ce838 16 U---C-L 8917 3:180053 20000 19766 98.83 > 2d2ce880 17 U-B---- 8895 3:200053 20000 20000 100.00 > 2d2ce8c8 18 U-B---- 8896 3:220053 20000 20000 100.00 > 2d2ce910 19 U-B---- 8897 3:240053 20000 20000 100.00 > 2d2ce958 20 U-B---- 8898 3:260053 20000 20000 100.00 > 2d2ce9a0 21 U-B---- 8899 3:280053 20000 20000 100.00 > 2d2ce9e8 22 U-B---- 8900 3:300053 20000 20000 100.00 > 2d2cea30 23 U-B---- 8901 3:320053 20000 20000 100.00 > 2d2cea78 24 U-B---- 8902 3:340053 20000 20000 100.00 > 2d2ceac0 25 U-B---- 8903 3:360053 20000 20000 100.00 > 2d2ceb08 26 U-B---- 8904 3:380053 20000 20000 100.00 > 2d2ceb50 27 U-B---- 8905 3:400053 20000 20000 100.00 > 2d2ceb98 28 U-B---- 8906 3:420053 20000 20000 100.00 > 2d2cebe0 29 U-B---- 8907 3:440053 20000 20000 100.00 > 23 active, 23 total > > Userthreads > address flags sessid user tty wait tout locks nreads nwrites > 2d28e018 ---P--D 1 informix - 0 0 0 87 844 > 2d28e5f0 ---P--F 0 informix - 0 0 0 0 45550 > 2d28ebc8 ---P--F 0 informix - 0 0 0 0 8827 > 2d28f1a0 ---P--F 0 informix - 0 0 0 0 215 > 2d28f778 ---P--F 0 informix - 0 0 0 0 0 > 2d28fd50 ---P--F 0 informix - 0 0 0 0 0 > 2d290328 ---P--F 0 informix - 0 0 0 0 0 > 2d290900 ---P--F 0 informix - 0 0 0 0 0 > 2d290ed8 ---P--F 0 informix - 0 0 0 0 0 > 2d2914b0 ---P--- 9 informix - 0 0 0 0 0 > 2d291a88 ---P--B 10 informix - 0 0 0 204 128 > 2d292060 Y--P--- 25 myAppli - 2dec44e8 0 1 134 539 > 2d292638 ---PR-- 21 informix - 0 0 2 507 28 > 2d292c10 ---P--D 13 informix - 0 0 0 0 0 > 2d2931e8 Y--P--D 18 informix - a3c7008 0 0 0 0 > 2d2937c0 ---P--- 20 informix - 0 0 1 251 2 > 2d293d98 ---P--- 22 informix - 0 0 2 1518 270 > 17 active, 128 total, 25 maximum concurrent > > Conditions with waiters: > cid addr name waiter waittime > ..... > 657 2dec44e8 netnorm 57 39946 > 626 a3c7008 bp_cond 49 158 > .... > > Threads > tid tcb rstcb prty status vp-class name > .... > 57 2db9dc18 2d292060 1 cond wait netnorm 1cpu sqlexec > 49 2dc44d40 2d2931e8 1 cond wait bp_cond 4cpu bf_priosweep > .... > > Locks > address wtlist owner lklist type tblsnum rowid key#/bsiz > a5dd908 0 2d2937c0 0 HDR+S 100002 209 0 > a5dd968 0 2d292060 0 HDR+S 100002 201 0 > ab96688 0 2d292638 0 S 100002 209 0 > ab96748 0 2d292638 ab96688 HDR+S 100002 207 0 > ab96868 0 2d293d98 0 S 100002 209 0 > ab968c8 0 2d293d98 ab96868 S 100002 207 0 > 6 active, 250000 total, 65536 hash buckets, 0 lock table overflows > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

On Fri, Oct 29, 2010 at 2:12 PM, STEVE DELEIGNIES < stevedeleignies@hotmail.com> wrote: > Hello, > > I am supporting an application running on Informix (IDS v11.10UC3) and > sometimes the Informix database hung. Please find below the symptoms: > - The onstat commands are still running but not the commands dbaccess, > onmonitor... > - there is no error log in the online.log or console messages. > - Server continues to perform checkpoints on database, but there is no > longer > console message. > - command 'onmode -kuy' is not responding, need to manually kill the oninit > adm process. > > I am not an Informix expert but I did some investigations and here are my > conclusions: > - the logicalLogs are not full because the old logicalLogs are correctly > backed up, so they can be reused (see onstat -l below) > - no thread lock on database: there are two userThreads waiting but it > seems > to be normal: > > sid 25 is waiting a network connection to execute query > > sid 18 is an informix thread waiting "bp_cond bf_priosweep" (I read > somewhere > it is a normal state for IDS) > > Could someone help ? > Thanks, > steve > > address number flags uniqid begin size used %used > 2d2ce5b0 7 U-B---- 8908 3:53 20000 20000 100.00 > 2d2ce5f8 8 U-B---- 8909 3:20053 20000 20000 100.00 > 2d2ce640 9 U-B---- 8910 3:40053 20000 20000 100.00 > 2d2ce688 10 U-B---- 8911 3:60053 20000 20000 100.00 > 2d2ce6d0 11 U-B---- 8912 3:80053 20000 20000 100.00 > 2d2ce718 12 U-B---- 8913 3:100053 20000 20000 100.00 > 2d2ce760 13 U-B---- 8914 3:120053 20000 20000 100.00 > 2d2ce7a8 14 U-B---- 8915 3:140053 20000 20000 100.00 > 2d2ce7f0 15 U-B---- 8916 3:160053 20000 20000 100.00 > 2d2ce838 16 U---C-L 8917 3:180053 20000 19766 98.83 > 2d2ce880 17 U-B---- 8895 3:200053 20000 20000 100.00 > 2d2ce8c8 18 U-B---- 8896 3:220053 20000 20000 100.00 > 2d2ce910 19 U-B---- 8897 3:240053 20000 20000 100.00 > 2d2ce958 20 U-B---- 8898 3:260053 20000 20000 100.00 > 2d2ce9a0 21 U-B---- 8899 3:280053 20000 20000 100.00 > 2d2ce9e8 22 U-B---- 8900 3:300053 20000 20000 100.00 > 2d2cea30 23 U-B---- 8901 3:320053 20000 20000 100.00 > 2d2cea78 24 U-B---- 8902 3:340053 20000 20000 100.00 > 2d2ceac0 25 U-B---- 8903 3:360053 20000 20000 100.00 > 2d2ceb08 26 U-B---- 8904 3:380053 20000 20000 100.00 > 2d2ceb50 27 U-B---- 8905 3:400053 20000 20000 100.00 > 2d2ceb98 28 U-B---- 8906 3:420053 20000 20000 100.00 > 2d2cebe0 29 U-B---- 8907 3:440053 20000 20000 100.00 > 23 active, 23 total > > Userthreads > address flags sessid user tty wait tout locks nreads nwrites > 2d28e018 ---P--D 1 informix - 0 0 0 87 844 > 2d28e5f0 ---P--F 0 informix - 0 0 0 0 45550 > 2d28ebc8 ---P--F 0 informix - 0 0 0 0 8827 > 2d28f1a0 ---P--F 0 informix - 0 0 0 0 215 > 2d28f778 ---P--F 0 informix - 0 0 0 0 0 > 2d28fd50 ---P--F 0 informix - 0 0 0 0 0 > 2d290328 ---P--F 0 informix - 0 0 0 0 0 > 2d290900 ---P--F 0 informix - 0 0 0 0 0 > 2d290ed8 ---P--F 0 informix - 0 0 0 0 0 > 2d2914b0 ---P--- 9 informix - 0 0 0 0 0 > 2d291a88 ---P--B 10 informix - 0 0 0 204 128 > 2d292060 Y--P--- 25 myAppli - 2dec44e8 0 1 134 539 > 2d292638 ---PR-- 21 informix - 0 0 2 507 28 > 2d292c10 ---P--D 13 informix - 0 0 0 0 0 > 2d2931e8 Y--P--D 18 informix - a3c7008 0 0 0 0 > 2d2937c0 ---P--- 20 informix - 0 0 1 251 2 > 2d293d98 ---P--- 22 informix - 0 0 2 1518 270 > 17 active, 128 total, 25 maximum concurrent > > Conditions with waiters: > cid addr name waiter waittime > ..... > 657 2dec44e8 netnorm 57 39946 > 626 a3c7008 bp_cond 49 158 > .... > > Threads > tid tcb rstcb prty status vp-class name > .... > 57 2db9dc18 2d292060 1 cond wait netnorm 1cpu sqlexec > 49 2dc44d40 2d2931e8 1 cond wait bp_cond 4cpu bf_priosweep > .... > > Locks > address wtlist owner lklist type tblsnum rowid key#/bsiz > a5dd908 0 2d2937c0 0 HDR+S 100002 209 0 > a5dd968 0 2d292060 0 HDR+S 100002 201 0 > ab96688 0 2d292638 0 S 100002 209 0 > ab96748 0 2d292638 ab96688 HDR+S 100002 207 0 > ab96868 0 2d293d98 0 S 100002 209 0 > ab968c8 0 2d293d98 ab96868 S 100002 207 0 > 6 active, 250000 total, 65536 hash buckets, 0 lock table overflows > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > In situations like this you should always open a PMR with IBM. What exactly happens besides the "onmode -kuy" not working? When you say dbaccess does not work, what happens? It just hangs or does it give any error? The already establish sessions are able to work? During the years I've seen some issues where the existing sessions work and the new don't connect. In many situations the problem was not with Informix. But only support could give you more info.... Things you may want to check are your number of open files, the DNS state, or a truss/strace of one of the CPUVPs involved in the connection establishement (not sure if it's the ADM or MSC VP). As an example, I once hit a very interesting problem... When I got to the customer site all their instances were hanged... Every already connected session was working but no new connections were possible. They had a problem with DNS during the night, so the conclusion was "informix has freaking out because of a temporary failure in DNS". After a lot of debugging it was possible to conclude the following: - They had a middleware application doing UDP broadcasts over the network - They hit a bug in OS function gethostbyaddr(). This is used by Informix to do the reverse DNS during connection. This function tries to connect using UDP with the DNS. For that it opens a UDP port... if/when this port matched the interval of ports that were being broadcast, the function would keep reading the UDP data, and would never return - So Informix blocked new connections because the OS function used did not return So, please open a PMR, so that tech support can check what's going on in the engine... They should provide you with instructions to run when that happens again, so that they have info to work on... Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf3054a4755a74380493c1dca0

The Enterprise Replication does not involve with the server. There is no line NETTYPE in my onconfig file. But my sqlhosts file defines the following entries: #demo_on onipcshm on_hostname on_servername MYAPPLY_SHM onipcshm myHost myAppli11 MYAPPLY_UA ontlitcp myHost myAppli10 My /etc/services includes: # MY_APPLI myAppli10 8000/tcp And my application use the env. INFORMIXSERVER=MYAPPLY_UA to connect database. So I conclude the application is using Network/TCP on port 8000. steve

Following the Keith suggestion on the server... Did you try access using both SERVERNAMES ? With informix user: export INFORMIXSERVER=MYAPPLY_SHMecho "select first * from systables " | dbaccess sysmaster export INFORMIXSERVER=MYAPPLY_UA echo "select first * from systables " | dbaccess sysmaster You really should define the NETTYPE for both kind of connections .... something like: NETTYPE soctcp,1,50,NET NETTYPE ipcshm,1,50,CPU --- Em sex, 29/10/10, STEVE DELEIGNIES <stevedeleignies@hotmail.com> escreveu: De: STEVE DELEIGNIES <stevedeleignies@hotmail.com> Assunto: Re: IDS v11.10 hang: no error / logicalLog not ful [21830] Para: ids@iiug.org Data: Sexta-feira, 29 de Outubro de 2010, 12:02 The Enterprise Replication does not involve with the server. There is no line NETTYPE in my onconfig file. But my sqlhosts file defines the following entries: #demo_on onipcshm on_hostname on_servername MYAPPLY_SHM onipcshm myHost myAppli11 MYAPPLY_UA ontlitcp myHost myAppli10 My /etc/services includes: # MY_APPLI myAppli10 8000/tcp And my application use the env. INFORMIXSERVER=MYAPPLY_UA to connect database. So I conclude the application is using Network/TCP on port 8000. steve ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

The onmode -kuy hangs too, and the established sessions are not working (see below "onstat -g ntt" executed at 09:00pm, the last write time is 00:03:40, when IDS stop to work). Individual thread network information (times): netscb thread name sid open read write address 2da00c88 sqlexec 25 23:06:24 00:03:40 00:03:40 10/27/10 2de54938 22 23:01:21 10/27/10 2dbc1c48 21 23:01:21 10/27/10 2db99c48 20 23:01:21 10/27/10 2d8d2c60 19 23:01:21 10/27/10 2d9f3cf8 sm_discon 8 23:01:14 10/27/10 2da2c8c8 sm_listen 5 23:01:14 10/27/10 2da118c8 tlitcplst 4 23:01:14 00:03:39 myHost|8000|tlitcp 10/27/10 2da008c8 sm_poll 3 23:01:14 10/27/10 2d03ae08 tlitcppoll 2 23:02:34 10/27/10 I did not open a PMR with IBM as I guess it requires a contract number and I could not provide it because I am working on the customer site until next week(and the contracts are in my company). I hope someone could help me before.

what does onstat - look like? Is the server waiuting on something to finish?

What's the output from onstat -d and onstat -D? If you have ONDBSPACEDOWN = 2 set in your onconfig, and a dbspace fails, the engine will hang until the device is available again. --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > DAN MUELLER > Sent: Friday, October 29, 2010 9:28 AM > To: ids@iiug.org > Subject: Re: IDS v11.10 hang: no error / logicalLog not ful [21833] > > what does onstat - look like? Is the server waiuting on something to > finish? > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.

Onstat - is replying as usual: IBM Informix Dynamic Server Version 11.10.UC3 -- On-Line -- Up 1 days 00:50:05 -- 640000 Kbytes Note that I already met the issue where 'onstat -' replied 'CKPT REQ' because an application connected to the database while starting it in single mode for backup purpose. But unfortunately this is not the case here.

ONDBSPACEDOWN 1 # Dbspace down option: 0 = CONTINUE, 1 = ABORT, 2 = WAIT and onstat results below: onstat -D IBM Informix Dynamic Server Version 11.10.UC3 -- On-Line -- Up 1 days 00:54:22 -- 640000 Kbytes Dbspaces address number flags fchunk nchunks pgsize flags owner name 2d1797f8 1 0x60001 1 1 2048 N B informix rootdbs 2d2cecb8 2 0x60001 2 1 2048 N B informix datadbs 2d2cee18 3 0x60001 3 1 2048 N B informix llog_dbs 2d2bcc30 4 0x42001 4 1 2048 N TB informix tmp1_dbs 4 active, 2047 maximum Chunks address chunk/dbs offset page Rd page Wr pathname 2d179958 1 1 2 13150 110927 /db/db_myApply/root.dbs 2d2bcd90 2 2 0 236793574 229041 /db/db_myApply/data.dbs 2d179b78 3 3 0 68 444739 /db/db_myApply/llog.dbs 2d179d50 4 4 0 940948 967263 /db/db_myApply/tmp1.dbs 4 active, 32766 maximum NOTE: The values in the "page Rd" and "page Wr" columns for DBspace chunks are displayed in terms of system base page size. Expanded chunk capacity mode: always onstat -d IBM Informix Dynamic Server Version 11.10.UC3 -- On-Line -- Up 1 days 00:54:32 -- 640000 Kbytes Dbspaces address number flags fchunk nchunks pgsize flags owner name 2d1797f8 1 0x60001 1 1 2048 N B informix rootdbs 2d2cecb8 2 0x60001 2 1 2048 N B informix datadbs 2d2cee18 3 0x60001 3 1 2048 N B informix llog_dbs 2d2bcc30 4 0x42001 4 1 2048 N TB informix tmp1_dbs 4 active, 2047 maximum Chunks address chunk/dbs offset size free bpages flags pathname 2d179958 1 1 2 500000 390995 PO-B /db/db_myApply/root.dbs 2d2bcd90 2 2 0 5000000 3178443 PO-B /db/db_myApply/data.dbs 2d179b78 3 3 0 500000 39947 PO-B /db/db_myApply/llog.dbs 2d179d50 4 4 0 1500000 1499897 PO-B /db/db_myApply/tmp1.dbs 4 active, 32766 maximum NOTE: The values in the "size" and "free" columns for DBspace chunks are displayed in terms of "pgsize" of the DBspace to which they belong. Expanded chunk capacity mode: always

I only tried to connect to the database with STEMATCH_UA, but not with STEMATCH_SHM. I will try it next time. Please note this issue does not occure every day. It happened once last month and twice this week.

Just for the heck of it, post your ONCONFIG file, sqlhosts file, and platform information. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) 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 Fri, Oct 29, 2010 at 9:12 AM, STEVE DELEIGNIES < stevedeleignies@hotmail.com> wrote: > Hello, > > I am supporting an application running on Informix (IDS v11.10UC3) and > sometimes the Informix database hung. Please find below the symptoms: > - The onstat commands are still running but not the commands dbaccess, > onmonitor... > - there is no error log in the online.log or console messages. > - Server continues to perform checkpoints on database, but there is no > longer > console message. > - command 'onmode -kuy' is not responding, need to manually kill the oninit > adm process. > > I am not an Informix expert but I did some investigations and here are my > conclusions: > - the logicalLogs are not full because the old logicalLogs are correctly > backed up, so they can be reused (see onstat -l below) > - no thread lock on database: there are two userThreads waiting but it > seems > to be normal: > > sid 25 is waiting a network connection to execute query > > sid 18 is an informix thread waiting "bp_cond bf_priosweep" (I read > somewhere > it is a normal state for IDS) > > Could someone help ? > Thanks, > steve > > address number flags uniqid begin size used %used > 2d2ce5b0 7 U-B---- 8908 3:53 20000 20000 100.00 > 2d2ce5f8 8 U-B---- 8909 3:20053 20000 20000 100.00 > 2d2ce640 9 U-B---- 8910 3:40053 20000 20000 100.00 > 2d2ce688 10 U-B---- 8911 3:60053 20000 20000 100.00 > 2d2ce6d0 11 U-B---- 8912 3:80053 20000 20000 100.00 > 2d2ce718 12 U-B---- 8913 3:100053 20000 20000 100.00 > 2d2ce760 13 U-B---- 8914 3:120053 20000 20000 100.00 > 2d2ce7a8 14 U-B---- 8915 3:140053 20000 20000 100.00 > 2d2ce7f0 15 U-B---- 8916 3:160053 20000 20000 100.00 > 2d2ce838 16 U---C-L 8917 3:180053 20000 19766 98.83 > 2d2ce880 17 U-B---- 8895 3:200053 20000 20000 100.00 > 2d2ce8c8 18 U-B---- 8896 3:220053 20000 20000 100.00 > 2d2ce910 19 U-B---- 8897 3:240053 20000 20000 100.00 > 2d2ce958 20 U-B---- 8898 3:260053 20000 20000 100.00 > 2d2ce9a0 21 U-B---- 8899 3:280053 20000 20000 100.00 > 2d2ce9e8 22 U-B---- 8900 3:300053 20000 20000 100.00 > 2d2cea30 23 U-B---- 8901 3:320053 20000 20000 100.00 > 2d2cea78 24 U-B---- 8902 3:340053 20000 20000 100.00 > 2d2ceac0 25 U-B---- 8903 3:360053 20000 20000 100.00 > 2d2ceb08 26 U-B---- 8904 3:380053 20000 20000 100.00 > 2d2ceb50 27 U-B---- 8905 3:400053 20000 20000 100.00 > 2d2ceb98 28 U-B---- 8906 3:420053 20000 20000 100.00 > 2d2cebe0 29 U-B---- 8907 3:440053 20000 20000 100.00 > 23 active, 23 total > > Userthreads > address flags sessid user tty wait tout locks nreads nwrites > 2d28e018 ---P--D 1 informix - 0 0 0 87 844 > 2d28e5f0 ---P--F 0 informix - 0 0 0 0 45550 > 2d28ebc8 ---P--F 0 informix - 0 0 0 0 8827 > 2d28f1a0 ---P--F 0 informix - 0 0 0 0 215 > 2d28f778 ---P--F 0 informix - 0 0 0 0 0 > 2d28fd50 ---P--F 0 informix - 0 0 0 0 0 > 2d290328 ---P--F 0 informix - 0 0 0 0 0 > 2d290900 ---P--F 0 informix - 0 0 0 0 0 > 2d290ed8 ---P--F 0 informix - 0 0 0 0 0 > 2d2914b0 ---P--- 9 informix - 0 0 0 0 0 > 2d291a88 ---P--B 10 informix - 0 0 0 204 128 > 2d292060 Y--P--- 25 myAppli - 2dec44e8 0 1 134 539 > 2d292638 ---PR-- 21 informix - 0 0 2 507 28 > 2d292c10 ---P--D 13 informix - 0 0 0 0 0 > 2d2931e8 Y--P--D 18 informix - a3c7008 0 0 0 0 > 2d2937c0 ---P--- 20 informix - 0 0 1 251 2 > 2d293d98 ---P--- 22 informix - 0 0 2 1518 270 > 17 active, 128 total, 25 maximum concurrent > > Conditions with waiters: > cid addr name waiter waittime > ..... > 657 2dec44e8 netnorm 57 39946 > 626 a3c7008 bp_cond 49 158 > .... > > Threads > tid tcb rstcb prty status vp-class name > .... > 57 2db9dc18 2d292060 1 cond wait netnorm 1cpu sqlexec > 49 2dc44d40 2d2931e8 1 cond wait bp_cond 4cpu bf_priosweep > .... > > Locks > address wtlist owner lklist type tblsnum rowid key#/bsiz > a5dd908 0 2d2937c0 0 HDR+S 100002 209 0 > a5dd968 0 2d292060 0 HDR+S 100002 201 0 > ab96688 0 2d292638 0 S 100002 209 0 > ab96748 0 2d292638 ab96688 HDR+S 100002 207 0 > ab96868 0 2d293d98 0 S 100002 209 0 > ab968c8 0 2d293d98 ab96868 S 100002 207 0 > 6 active, 250000 total, 65536 hash buckets, 0 lock table overflows > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016364edf528dd7130493c2a3db

Plateform: SunOS 5.10 Generic_138888-03 sqlhosts MYAPPLI_SHM onipcshm myHost myHost11 MYAPPLI_UA ontlitcp myHost myHost10 onconfig #************************************************************************** # # INFORMIX SOFTWARE, INC. # # Title: onconfig.std # Description: INFORMIX-OnLine Configuration Parameters # #************************************************************************** # Root Dbspace Configuration ROOTNAME rootdbs # Root dbspace name ROOTPATH /db/db_MYAPPLY/root.dbs # Path for device containing root dbspace ROOTOFFSET 4 # Offset of root dbspace into device (Kbytes) ROOTSIZE 1000000 # Size of root dbspace (Kbytes) # Disk Mirroring Configuration Parameters MIRROR 0 # Mirroring flag (Yes = 1, No = 0) MIRRORPATH # Path for device containing mirrored root MIRROROFFSET 0 # Offset into mirrored device (Kbytes) # Physical Log Configuration PHYSDBS rootdbs # Location (dbspace) of physical log PHYSFILE 200000 # Physical log file size (Kbytes) # Logical Log Configuration LOGFILES 23 # Number of logical log files LOGSIZE 40000 # Logical log size (Kbytes) # Diagnostics MSGPATH /app/myAppli/output/logs/online.log # System message log file path CONSOLE /app/myAppli/output/logs/sysmsg.log # System console message path ALARMPROGRAM /product/informix/etc/log_full.sh # Alarm program path # System Archive Tape Device TAPEDEV /dev/null TAPEBLK 16 # Tape block size (Kbytes) TAPESIZE 10240 # Maximum amount of data to put on tape (Kbytes) # Log Archive Tape Device LTAPEDEV /dev/null # Log tape device path LTAPEBLK 16 # Log tape block size (Kbytes) LTAPESIZE 10240 # Max amount of data to put on log tape (Kbytes) # Optical STAGEBLOB # INFORMIX-OnLine/Optical staging area # System Configuration SERVERNUM 3 # Unique id corresponding to a OnLine instance DBSERVERNAME MYAPPLY_UA # fault database server DBSERVERALIASES MYAPPLY_SHM # List of alternate dbservernames DEADLOCK_TIMEOUT 120 # Max time to wait of lock in distributed env. RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor NUMCPUVPS 4 # Number of user (cpu) vps SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one NOAGE 0 # Process aging AFF_SPROC 8 # Affinity start processor AFF_NPROCS 4 # Affinity number of processors # Shared Memory Parameters LOCKS 250000 # Maximum number of locks BUFFERS 250000 # Maximum number of shared buffers NUMAIOVPS 8 # Number of IO vps PHYSBUFF 128 # Physical log buffer size (Kbytes) LOGBUFF 128 # Logical log buffer size (Kbytes)LOGSMAX 30 # Maximum number of logical log files CLEANERS 8 # Number of buffer cleaner processes SHMBASE 0x0A000000L # Shared memory base address SHMVIRTSIZE 32000 # initial virtual shared memory segment size SHMADD 32000 # Size of new shared memory segments (Kbytes) SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited CKPTINTVL 300 # Check point interval (in sec) LRUS 8 # Number of LRU queues LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaning limit LRU_MIN_DIRTY 50 # LRU percent dirty end cleaning limit LTXHWM 50 # Long transaction high water mark percentage LTXEHWM 60 # Long transaction high water mark (exclusive) TXTIMEOUT 0x12c # Transaction timeout (in sec) STACKSIZE 128 # Stack size (Kbytes) # System Page Size # BUFFSIZE - OnLine no longer supports this configuration parameter. # To determine the page size used by OnLine on your platform # see the last line of output from the command, 'onstat -b'. # Recovery Variables # OFF_RECVRY_THREADS: # Number of parallel worker threads during fast recovery or an offline restore. # ON_RECVRY_THREADS: # Number of parallel worker threads during an online restore. OFF_RECVRY_THREADS 10 # Default number of offline worker threads ON_RECVRY_THREADS 1 # Default number of online worker threads # Data Replication Variables # DRAUTO: 0 manual, 1 retain type, 2 reverse type DRINTERVAL 30 # DR max time between DR buffer flushes (in sec) DRTIMEOUT 30 # DR network timeout (in sec) DRLOSTFOUND # DR lost+found file path # Read Ahead Variables RA_PAGES # Number of pages to attempt to read ahead RA_THRESHOLD # Number of pages left before next group # DBSPACETEMP: # OnLine equivalent of DBTEMP for SE. This is the list of dbspaces # that the OnLine SQL Engine will use to create temp tables etc. # If specified it must be a colon separated list of dbspaces that exist # when the OnLine system is brought online. If not specified, or if # all dbspaces specified are invalid, various ad hoc queries will create # temporary files in /tmp instead. DBSPACETEMP tmp1_dbs # Default temp dbspaces # DUMP*: # The following parameters control the type of diagnostics information which # is preserved when an unanticipated error condition (assertion failure) occurs # during OnLine operations. # For DUMPSHMEM, DUMPGCORE and DUMPCORE 1 means Yes, 0 means No. DUMPDIR /db/db_MYAPPLY/crash-dump # Preserve diagnostics in this directory DUMPSHMEM 1 # Dump a copy of shared memory DUMPGCORE 0 # Dump a core image using 'gcore' DUMPCORE 0 # Dump a core image (Warning:this aborts OnLine) DUMPCNT 1 # Number of shared memory or gcore dumps for # a single user's session # ADT* # The following parameters control the type and level of secure auditing # present in the OnLine system. By default, ADTMODE is 0 and auditing # is disabled FILLFACTOR 80 # Fill factor for building indexes # method for OnLine to use when determining current time USEOSTIME 0 # 0: use internal time(fast), 1: get time from OS(slow) # Parallel Database Queries (pdq) # OFF => 0, LOW => 1, HIGH => 100 MAX_PDQPRIORITY 100 # Maximum allowed pdqpriority DS_MAX_QUERIES # Maximum number of decision support queries DS_TOTAL_MEMORY # Decision support memory (Kbytes) DS_MAX_SCANS 1048576 # Maximum number of decision support scans DATASKIP off # List of dbspaces to skip # OPTCOMPIND # 0 => Nested loop joins will be preferred (where # possible) over sortmerge joins and hash joins. # 1 => If the transaction isolation mode is not # "repeatable read", optimizer behaves as in (2) # below. Otherwise it behaves as in (0) above. # 2 => Use costs regardless of the transaction isolation # mode. Nested loop joins are not necessarily # preferred. Optimizer bases its decision purely # on costs. #OPTCOMPIND 0 # To hint the optimizer OPTCOMPIND 2 # To hint the optimizer ONDBSPACEDOWN 1 # Dbspace down option: 0 = CONTINUE, 1 = ABORT, 2 = WAITLBU_PRESERVE 0 # Preserve last log for log backup OPCACHEMAX 0 # Maximum optical cache size (Kbytes) CDR_LOGBUFFERS 2048 # size of log reading buffer pool (Kbytes) CDR_EVALTHREADS 1,1 # evaluator threads (per-cpu-vp,additional) CDR_DSLOCKWAIT 5 # DS lockwait timeout (seconds) CDR_QUEUEMEM 4096 # Maximum amount of memory for any CDR queue (Kbytes) BAR_ACT_LOG /tmp/bar_act.log BAR_MAX_BACKUP 0 BAR_RETRY 1 BAR_NB_XPORT_COUNT 10 BAR_XFER_BUF_SIZE 31 HETERO_COMMIT 0

If onmode -ky is hanging, then I'd be looking at onstat -g ath and see which threads are in a running state. Then I'd be attaching with a debugger to the cpuvps to see where the processes are running (where command in gdb)... From: "STEVE DELEIGNIES" <stevedeleignies@hotmail.com> To: ids@iiug.org Date: 10/29/2010 06:13 AM Subject: IDS v11.10 hang: no error / logicalLog not full [21827] Sent by: ids-bounces@iiug.org Hello, I am supporting an application running on Informix (IDS v11.10UC3) and sometimes the Informix database hung. Please find below the symptoms: - The onstat commands are still running but not the commands dbaccess, onmonitor... - there is no error log in the online.log or console messages. - Server continues to perform checkpoints on database, but there is no longer console message. - command 'onmode -kuy' is not responding, need to manually kill the oninit adm process. I am not an Informix expert but I did some investigations and here are my conclusions: - the logicalLogs are not full because the old logicalLogs are correctly backed up, so they can be reused (see onstat -l below) - no thread lock on database: there are two userThreads waiting but it seems to be normal: sid 25 is waiting a network connection to execute query sid 18 is an informix thread waiting "bp_cond bf_priosweep" (I read somewhere it is a normal state for IDS) Could someone help ? Thanks, steve address number flags uniqid begin size used %used 2d2ce5b0 7 U-B---- 8908 3:53 20000 20000 100.00 2d2ce5f8 8 U-B---- 8909 3:20053 20000 20000 100.00 2d2ce640 9 U-B---- 8910 3:40053 20000 20000 100.00 2d2ce688 10 U-B---- 8911 3:60053 20000 20000 100.00 2d2ce6d0 11 U-B---- 8912 3:80053 20000 20000 100.00 2d2ce718 12 U-B---- 8913 3:100053 20000 20000 100.00 2d2ce760 13 U-B---- 8914 3:120053 20000 20000 100.00 2d2ce7a8 14 U-B---- 8915 3:140053 20000 20000 100.00 2d2ce7f0 15 U-B---- 8916 3:160053 20000 20000 100.00 2d2ce838 16 U---C-L 8917 3:180053 20000 19766 98.83 2d2ce880 17 U-B---- 8895 3:200053 20000 20000 100.00 2d2ce8c8 18 U-B---- 8896 3:220053 20000 20000 100.00 2d2ce910 19 U-B---- 8897 3:240053 20000 20000 100.00 2d2ce958 20 U-B---- 8898 3:260053 20000 20000 100.00 2d2ce9a0 21 U-B---- 8899 3:280053 20000 20000 100.00 2d2ce9e8 22 U-B---- 8900 3:300053 20000 20000 100.00 2d2cea30 23 U-B---- 8901 3:320053 20000 20000 100.00 2d2cea78 24 U-B---- 8902 3:340053 20000 20000 100.00 2d2ceac0 25 U-B---- 8903 3:360053 20000 20000 100.00 2d2ceb08 26 U-B---- 8904 3:380053 20000 20000 100.00 2d2ceb50 27 U-B---- 8905 3:400053 20000 20000 100.00 2d2ceb98 28 U-B---- 8906 3:420053 20000 20000 100.00 2d2cebe0 29 U-B---- 8907 3:440053 20000 20000 100.00 23 active, 23 total Userthreads address flags sessid user tty wait tout locks nreads nwrites 2d28e018 ---P--D 1 informix - 0 0 0 87 844 2d28e5f0 ---P--F 0 informix - 0 0 0 0 45550 2d28ebc8 ---P--F 0 informix - 0 0 0 0 8827 2d28f1a0 ---P--F 0 informix - 0 0 0 0 215 2d28f778 ---P--F 0 informix - 0 0 0 0 0 2d28fd50 ---P--F 0 informix - 0 0 0 0 0 2d290328 ---P--F 0 informix - 0 0 0 0 0 2d290900 ---P--F 0 informix - 0 0 0 0 0 2d290ed8 ---P--F 0 informix - 0 0 0 0 0 2d2914b0 ---P--- 9 informix - 0 0 0 0 0 2d291a88 ---P--B 10 informix - 0 0 0 204 128 2d292060 Y--P--- 25 myAppli - 2dec44e8 0 1 134 539 2d292638 ---PR-- 21 informix - 0 0 2 507 28 2d292c10 ---P--D 13 informix - 0 0 0 0 0 2d2931e8 Y--P--D 18 informix - a3c7008 0 0 0 0 2d2937c0 ---P--- 20 informix - 0 0 1 251 2 2d293d98 ---P--- 22 informix - 0 0 2 1518 270 17 active, 128 total, 25 maximum concurrent Conditions with waiters: cid addr name waiter waittime ..... 657 2dec44e8 netnorm 57 39946 626 a3c7008 bp_cond 49 158 .... Threads tid tcb rstcb prty status vp-class name .... 57 2db9dc18 2d292060 1 cond wait netnorm 1cpu sqlexec 49 2dc44d40 2d2931e8 1 cond wait bp_cond 4cpu bf_priosweep .... Locks address wtlist owner lklist type tblsnum rowid key#/bsiz a5dd908 0 2d2937c0 0 HDR+S 100002 209 0 a5dd968 0 2d292060 0 HDR+S 100002 201 0 ab96688 0 2d292638 0 S 100002 209 0 ab96748 0 2d292638 ab96688 HDR+S 100002 207 0 ab96868 0 2d293d98 0 S 100002 209 0 ab968c8 0 2d293d98 ab96868 S 100002 207 0 6 active, 250000 total, 65536 hash buckets, 0 lock table overflows ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.