RE: System Freezes

I appreciate your help. At this point, I'm trying to rewrite 6 months of development on a production system. I'm trying to rule out the most obvious things, but if I could narrow it down, that would help tremendously. We didn't see these problems during development and training, but then again, we didn't have this sort of traffic (not that we are that big or have that much traffic that our system shouldn't be able to handle this). At any rate, here's what I see for recent checkpoints in the Informix log: 12:07:43 Checkpoint Completed: duration was 0 seconds. 12:07:43 Checkpoint Statistics - Avg. Txn Block Time 0.012, # Txnsblocked 0, Plog used 786, Llog used 14896 12:23:01 Checkpoint Completed: duration was 0 seconds. 12:23:01 Checkpoint Statistics - Avg. Txn Block Time 0.033, # Txnsblocked 0, Plog used 625, Llog used 695 12:38:10 Checkpoint Completed: duration was 0 seconds. 12:38:10 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txnsblocked 1, Plog used 728, Llog used 608 12:53:24 Checkpoint Completed: duration was 2 seconds. 12:53:24 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txnsblocked 0, Plog used 14145, Llog used 4239 13:08:38 Checkpoint Completed: duration was 0 seconds. 13:08:38 Checkpoint Statistics - Avg. Txn Block Time 0.013, # Txnsblocked 1, Plog used 484, Llog used 14446 13:23:47 Checkpoint Completed: duration was 0 seconds. 13:23:47 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txnsblocked 0, Plog used 1253, Llog used 1243 13:39:13 Checkpoint Completed: duration was 0 seconds. 13:39:13 Checkpoint Statistics - Avg. Txn Block Time 0.030, # Txnsblocked 0, Plog used 524, Llog used 589 13:54:17 Checkpoint Completed: duration was 0 seconds. 13:54:17 Checkpoint Statistics - Avg. Txn Block Time 0.029, # Txnsblocked 0, Plog used 688, Llog used 747 14:09:43 Checkpoint Completed: duration was 0 seconds. 14:09:43 Checkpoint Statistics - Avg. Txn Block Time 0.026, # Txnsblocked 0, Plog used 676, Llog used 14701 14:24:52 Checkpoint Completed: duration was 0 seconds. 14:24:52 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txnsblocked 0, Plog used 1216, Llog used 1444 14:40:03 Checkpoint Completed: duration was 0 seconds. 14:40:03 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txnsblocked 0, Plog used 1329, Llog used 1589 14:55:27 Checkpoint Completed: duration was 0 seconds. 14:55:27 Checkpoint Statistics - Avg. Txn Block Time 0.029, # Txnsblocked 1, Plog used 868, Llog used 781 15:10:56 Checkpoint Completed: duration was 0 seconds. 15:10:56 Checkpoint Statistics - Avg. Txn Block Time 0.030, # Txnsblocked 0, Plog used 1053, Llog used 14927 15:26:12 Checkpoint Completed: duration was 0 seconds. 15:26:12 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txnsblocked 0, Plog used 831, Llog used 5127 15:41:25 Checkpoint Completed: duration was 0 seconds. 15:41:25 Checkpoint Statistics - Avg. Txn Block Time 0.024, # Txnsblocked 0, Plog used 602, Llog used 790 15:56:38 Checkpoint Completed: duration was 0 seconds. 15:56:38 Checkpoint Statistics - Avg. Txn Block Time 0.023, # Txnsblocked 1, Plog used 469, Llog used 685 From: Art Kagel [mailto:art.kagel@gmail.com] Sent: Tuesday, January 11, 2011 3:05 PM To: Rubinstein, James Cc: informix-list@iiug.org Subject: Re: System Freezes That's OK, the AUS refresh knows enough to compile any SPL routines with PDQPRIORITY 0, so red herring. This is going to be a tough one to crack. Any indications of long checkpoints with blocked sessions in the message log? 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 Tue, Jan 11, 2011 at 4:54 PM, Rubinstein, James <JRUBIN@midwestern.edu> wrote: No, we don't have many stored procedures. If we are using any, they are nothing that we don't use elsewhere throughout the system. We do have some pretty complex SQL. I've looked with set explain and I'm going to try to improve it, but I don't have anything that takes more than a second or two to run with dbaccess. Of course, I do have much more web traffic firing this SQL than I can generate with dbaccess. As far as updstats goes, it looks like the scheduler is running "aus_refresh_stats" with these parameters: Parameter Name: AUD_PDQ Value: 10 ValueType: Numeric From: Art Kagel [mailto:art.kagel@gmail.com] Sent: Tuesday, January 11, 2011 2:46 PM To: Rubinstein, James Cc: informix-list@iiug.org Subject: Re: System Freezes Does the new application use many stored procedures? Is it possible that the stored procedures were recompiled (update statistics for procedure/function ....) with PDQPRIORITY set to something other than zero? Just one idea? 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 Tue, Jan 11, 2011 at 4:29 PM, Rubinstein, James <JRUBIN@midwestern.edu> wrote: I'm running IDS 11.50.FC6 on HPUX 11.31. We recently rolled out an internally developed web-based system (Apache perl/mod_perl) and immediately started noticing that our HPUX system becomes completely unresponsive, for 20-30 seconds at a time, many times throughout the day. My first thought was network problems, but we have pretty much ruled this out since I can connect to a twin HPUX server which seems fine during the outages. During these system freezes, any connections to the database fail and the system is completely unresponsive to the point that I cannot even type any commands at the shell. I have seen this behavior in the past when the oninit processes use a lot of CPU resources, but it is usually pretty easy to track these down to some bad SQL/report writing. In this case, I'm trying to figure out what may be causing the system freezes. I have top and the dbtop utility from IIUG, but those don't refresh during or freezes. I am also unable to type any onstat commands until the system comes back. By that time, everything looks pretty normal with low load aver