System Freezes

#1

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 averages and our oninit processes at
normal levels. I'm looking at the various system reports in OAT, but
don't see anything that jumps out as the culprit. I'd appreciate any
troubleshooting ideas.

#2

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 (AT) iiug (DOT) 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 (AT) midwestern (DOT) edu>wrote:

Quote:

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 systemis
completely unresponsive to the point that I cannot even type any commandsat
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 unableto
type any onstat commands until the system comes back. By that time,
everything looks pretty normal with low load averages and our oninit
processes at normal levels. I’m looking at the various system reports in
OAT, but don’t see anything that jumps out as the culprit. I’d appreciate
any troubleshooting ideas.

_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list

#3

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 (AT) gmail (DOT) com]
Sent: Tuesday, January 11, 2011 2:46 PM
To: Rubinstein, James
Cc: informix-list (AT) iiug (DOT) 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 (AT) iiug (DOT) 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 (AT) midwestern (DOT) 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 averages and our oninit processes at
normal levels. I'm looking at the various system reports in OAT, but
don't see anything that jumps out as the culprit. I'd appreciate any
troubleshooting ideas.

_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list

#4

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 (AT) iiug (DOT) 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 (AT) midwestern (DOT) edu>wrote:

Quote:

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 havesome
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 (AT) gmail (DOT) com]
*Sent:* Tuesday, January 11, 2011 2:46 PM
*To:* Rubinstein, James
*Cc:* informix-list (AT) iiug (DOT) 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 (AT) iiug (DOT) 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 (AT) midwestern (DOT) 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 systemis
completely unresponsive to the point that I cannot even type any commandsat
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 unableto
type any onstat commands until the system comes back. By that time,
everything looks pretty normal with low load averages and our oninit
processes at normal levels. I’m looking at the various system reports in
OAT, but don’t see anything that jumps out as the culprit. I’d appreciate
any troubleshooting ideas.

_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list

#5

On Tue, Jan 11, 2011 at 9:29 PM, Rubinstein, James <JRUBIN (AT) midwestern (DOT) edu>wrote:

Quote:

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 systemis
completely unresponsive to the point that I cannot even type any commandsat
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 unableto
type any onstat commands until the system comes back. By that time,
everything looks pretty normal with low load averages and our oninit
processes at normal levels. I’m looking at the various system reports in
OAT, but don’t see anything that jumps out as the culprit. I’d appreciate
any troubleshooting ideas.

It may look as I'm defending Informix, but I find it very hard to believe
that the symptoms you describe can be caused by any kind of bad SQL.

I'd start looking at memory usage etc. Check the OS ratios for filesystem
cache vs program memory. Check you memory usage. Try to keep a "top" or
similar tool open and see if you notice something. Take frequent snapshots
of paging status (so that you can compare before/after counters.
Also check your system logs.
Last time I saw something similar (not on HP) it was the filesystem/program
memory ratios. If froze the machine whenever a filesystem intensive
operation was run.

By no means I'm insinuating it's everything ok with Informix, but whatever
happens with it should not cause that effect.

Regards.

--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

#6

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, # Txns
blocked 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, # Txns
blocked 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, # Txns
blocked 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, # Txns
blocked 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, # Txns
blocked 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, # Txns
blocked 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, # Txns
blocked 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, # Txns
blocked 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, # Txns
blocked 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, # Txns
blocked 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, # Txns
blocked 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, # Txns
blocked 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, # Txns
blocked 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, # Txns
blocked 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, # Txns
blocked 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, # Txns
blocked 1, Plog used 469, Llog used 685

From: Art Kagel [mailto:art.kagel (AT) gmail (DOT) com]
Sent: Tuesday, January 11, 2011 3:05 PM
To: Rubinstein, James
Cc: informix-list (AT) iiug (DOT) 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 (AT) iiug (DOT) 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 (AT) midwestern (DOT) 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 (AT) gmail (DOT) com]
Sent: Tuesday, January 11, 2011 2:46 PM
To: Rubinstein, James
Cc: informix-list (AT) iiug (DOT) 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 (AT) iiug (DOT) 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 (AT) midwestern (DOT) 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 averages and our oninit processes at
normal levels. I'm looking at the various system reports in OAT, but
don't see anything that jumps out as the culprit. I'd appreciate any
troubleshooting ideas.

_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list

#7

Thanks Fernando. The UNIX system logs and the Informix logs show
nothing out of the usual. The only thing that changed in our system,
and it corresponded with these freezes, is that we rolled out a group of
perl CGI web pages along with a handful of new tables to store the data
for this new system. We have used Cognos Impromtu and have had a person
or two use Microsoft Access to run queries from time to time and I have
experienced these types of freezes when those applications have run some
pretty large queries. I am also surprised that the OS seems locked by
database activity, but I have seen it.

I have top and dbtop open, but those freeze too for 20-30 seconds so I
cannot see what is going on during the freeze. When the system again
becomes responsive, the output from those tools seems normal. There
shouldn't be anything going on in our system this week related to the
filesystem that wasn't occurring last week. Again, the only difference
is a handful of CGI's that are running SELECT's, INSERT's, and UPDATE's
to our database.

From: Fernando Nunes [mailto:domusonline (AT) gmail (DOT) com]
Sent: Tuesday, January 11, 2011 3:07 PM
To: Rubinstein, James
Cc: informix-list (AT) iiug (DOT) org
Subject: Re: System Freezes

On Tue, Jan 11, 2011 at 9:29 PM, Rubinstein, James
<JRUBIN (AT) midwestern (DOT) 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 averages and our oninit processes at
normal levels. I'm looking at the various system reports in OAT, but
don't see anything that jumps out as the culprit. I'd appreciate any
troubleshooting ideas.

It may look as I'm defending Informix, but I find it very hard to
believe that the symptoms you describe can be caused by any kind of bad
SQL.
I'd start looking at memory usage etc. Check the OS ratios for
filesystem cache vs program memory. Check you memory usage. Try to keep
a "top" or similar tool open and see if you notice something. Take
frequent snapshots of paging status (so that you can compare
before/after counters.
Also check your system logs.
Last time I saw something similar (not on HP) it was the
filesystem/program memory ratios. If froze the machine whenever a
filesystem intensive operation was run.

By no means I'm insinuating it's everything ok with Informix, but
whatever happens with it should not cause that effect.

Regards.

--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

#8

Those symptoms sound like a hardware issue to me. Have you looked for error messages in /var/adm/syslog/syslog.log?

--EEM

From: informix-list-bounces (AT) iiug (DOT) org [mailto:informix-list-bounces (AT) iiug (DOT) org] On Behalf Of Fernando Nunes
Sent: Tuesday, January 11, 2011 4:07 PM
To: Rubinstein, James
Cc: informix-list (AT) iiug (DOT) org
Subject: Re: System Freezes

On Tue, Jan 11, 2011 at 9:29 PM, Rubinstein, James <JRUBIN (AT) midwestern (DOT) edu<mailto:JRUBIN (AT) midwestern (DOT) 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-30seconds 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 usea 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 fromIIUG, but those don't refresh during or freezes. I am also unable to typeany onstat commands until the system comes back. By that time, everythinglooks pretty normal with low load averages and our oninit processes at normal levels. I'm looking at the various system reports in OAT, but don't see anything that jumps out as the culprit. I'd appreciate any troubleshooting ideas.

It may look as I'm defending Informix, but I find it very hard to believe that the symptoms you describe can be caused by any kind of bad SQL.
I'd start looking at memory usage etc. Check the OS ratios for filesystem cache vs program memory. Check you memory usage. Try to keep a "top" or similar tool open and see if you notice something. Take frequent snapshots of paging status (so that you can compare before/after counters.
Also check your system logs.
Last time I saw something similar (not on HP) it was the filesystem/programmemory ratios. If froze the machine whenever a filesystem intensive operation was run.

By no means I'm insinuating it's everything ok with Informix, but whatever happens with it should not cause that effect.

Regards.

--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

#9

Nothing of interest in our system log. The problems started Friday when
I turned on my new perl CGI's (which do database operations), were no
existent over the weekend, when the scripts were not being used, and
then started again yesterday, when our university opened for business.
I don't notice any freezes during weekends of evenings (when no one is
working except for me) and they start again when the web traffic and
database activity starts up again.

From: informix-list-bounces (AT) iiug (DOT) org
[mailto:informix-list-bounces (AT) iiug (DOT) org] On Behalf Of Everett Mills
Sent: Tuesday, January 11, 2011 3:24 PM
Cc: informix-list (AT) iiug (DOT) org
Subject: RE: System Freezes

Those symptoms sound like a hardware issue to me. Have you looked for
error messages in /var/adm/syslog/syslog.log?

--EEM

From: informix-list-bounces (AT) iiug (DOT) org
[mailto:informix-list-bounces (AT) iiug (DOT) org] On Behalf Of Fernando Nunes
Sent: Tuesday, January 11, 2011 4:07 PM
To: Rubinstein, James
Cc: informix-list (AT) iiug (DOT) org
Subject: Re: System Freezes

On Tue, Jan 11, 2011 at 9:29 PM, Rubinstein, James
<JRUBIN (AT) midwestern (DOT) 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 averages and our oninit processes at
normal levels. I'm looking at the various system reports in OAT, but
don't see anything that jumps out as the culprit. I'd appreciate any
troubleshooting ideas.

It may look as I'm defending Informix, but I find it very hard to
believe that the symptoms you describe can be caused by any kind of bad
SQL.
I'd start looking at memory usage etc. Check the OS ratios for
filesystem cache vs program memory. Check you memory usage. Try to keep
a "top" or similar tool open and see if you notice something. Take
frequent snapshots of paging status (so that you can compare
before/after counters.
Also check your system logs.
Last time I saw something similar (not on HP) it was the
filesystem/program memory ratios. If froze the machine whenever a
filesystem intensive operation was run.

By no means I'm insinuating it's everything ok with Informix, but
whatever happens with it should not cause that effect.

Regards.

--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

#10

Wait, HPUX? Is this a PA-RISC system or Itanium? If PA-RISC, how many
shared memory segments (specifically the Resident and Virtual segments) does
the engine have (onstat -g seg)? What do you have RESIDENT set to? If you
have more than one virtual segment you could be seeing the PA-RISC processor
design flaw problem. Increase SHMVIRTSIZE to include the sizes of any
virtual segments besides the first (or all of the ones you see if you have
RESIDENT set to -1 - since then the first virtual segment should have been
consolidated into the resident segment) into SHMVIRTSIZE and bounce the
instance.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art (AT) iiug (DOT) 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 5:04 PM, Art Kagel <art.kagel (AT) gmail (DOT) com> wrote:

Quote:

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 (AT) iiug (DOT) 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 (AT) midwestern (DOT) 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 ortwo
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 (AT) gmail (DOT) com]
*Sent:* Tuesday, January 11, 2011 2:46 PM
*To:* Rubinstein, James
*Cc:* informix-list (AT) iiug (DOT) 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 (AT) iiug (DOT) 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 (AT) midwestern (DOT) 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 averages and our oninit
processes at normal levels. I’m looking at the various system reportsin
OAT, but don’t see anything that jumps out as the culprit. I’d appreciate
any troubleshooting ideas.

_______________________________________________
Informix-list mailing list
Informix-list (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list