[Klloyd]

HOST Type: HP 9000/800
HOST os level: HP-UX 11.00
Informix rev: Informix Dynamic Server Version 7.31.UD4

Hi,

I have a very intensive database application that is running into problems during peak usage. The error message in the application log is:

" d=(Tue 11/25/03 01:59:21 CST) p=13643 Could not open the
[application] Database, sqlerror = -25588
d=(Tue 11/25/03 01:59:34 CST) p=13646 Open Database, retn=-25588, sqlcode=-25588, sqlerrd=0.

There is nothing in the online.log to indicate a problem, and there is nothing in the unix error log indicating a hardware problem either.

The funny thing is that informix is definitely bound to only 200 userthreads. I verified this by waiting till the problem appeared and issuing a onstat -u. I then killed the pid of a couple of aux program threads and watched as they were immediately replaced by new ones -- but again, the total never exceeds 200 user threads. This application has been running flawlessly till lately.

Any suggestions on where to start trouble shooting? Any help is greatly appreciated. Below are some of the settings I have checked and rechecked. Let me know if you need more information.

sqlhosts:

#demo_on onipcshm on_hostname on_servername
#demo_se seipcpip se_hostname sqlexec

onlinedsa onipcshm pmine1 xyz
onlinedsasoc onsoctcp pmine1 infmx_pmine1

Net configurations from onstat -c:

NETTYPE ipcshm,3,200,CPU # Configure poll thread(s) for nettype
NETTYPE soctcp,3,50,NET # Configure poll thread(s) for nettype

Number of CPUs:

NUMCPUVPS 3 # Number of user (cpu) vps
SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one

Enviornment variables:

SQLEXEC=/usr/informix/7.31/lib/sqlturbo
PATH=/hekimian/nd@3.0.2v/bin:/usr/local/bin:./:/usr/bin:/etc:/usr/sbin::/usr/ucb:/hekimian/pmint/users/pmisys/bin:/usr/bin/X11:/sbin
INFORMIX_VERSION=7
START_INFORMIX_ENGINE=oninit
INFORMIXBIN=/usr/informix/7.31/bin
INFORMIXDIR=/usr/informix/7.31
INFORMIXSERVER=onlinedsa

[gurey]

Originally posted by Klloyd
HOST Type: HP 9000/800
HOST os level: HP-UX 11.00
Informix rev: Informix Dynamic Server Version 7.31.UD4

Hi,

I have a very intensive database application that is running into problems during peak usage. The error message in the application log is:

" d=(Tue 11/25/03 01:59:21 CST) p=13643 Could not open the
[application] Database, sqlerror = -25588
d=(Tue 11/25/03 01:59:34 CST) p=13646 Open Database, retn=-25588, sqlcode=-25588, sqlerrd=0.

There is nothing in the online.log to indicate a problem, and there is nothing in the unix error log indicating a hardware problem either.

The funny thing is that informix is definitely bound to only 200 userthreads. I verified this by waiting till the problem appeared and issuing a onstat -u. I then killed the pid of a couple of aux program threads and watched as they were immediately replaced by new ones -- but again, the total never exceeds 200 user threads. This application has been running flawlessly till lately.

Any suggestions on where to start trouble shooting? Any help is greatly appreciated. Below are some of the settings I have checked and rechecked. Let me know if you need more information.

sqlhosts:

#demo_on onipcshm on_hostname on_servername
#demo_se seipcpip se_hostname sqlexec

onlinedsa onipcshm pmine1 xyz
onlinedsasoc onsoctcp pmine1 infmx_pmine1

Net configurations from onstat -c:

NETTYPE ipcshm,3,200,CPU # Configure poll thread(s) for nettype
NETTYPE soctcp,3,50,NET # Configure poll thread(s) for nettype

Number of CPUs:

NUMCPUVPS 3 # Number of user (cpu) vps
SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one

Enviornment variables:

SQLEXEC=/usr/informix/7.31/lib/sqlturbo
PATH=/hekimian/nd@3.0.2v/bin:/usr/local/bin:./:/usr/bin:/etc:/usr/sbin::/usr/ucb:/hekimian/pmint/users/pmisys/bin:/usr/bin/X11:/sbin
INFORMIX_VERSION=7
START_INFORMIX_ENGINE=oninit
INFORMIXBIN=/usr/informix/7.31/bin
INFORMIXDIR=/usr/informix/7.31
INFORMIXSERVER=onlinedsa

Hi,

I see a problem in your definition.
Look parameters NUMCPUVP=3 and look second field of NETTYPE.
The sum of second field of NETTYPE=NUMCPUVP and you have 3+3=6.
NETTYPE ipcshm,3 this will should be 1 or 2
NETTYPE soctcp,3 this will should be 2 or 1
NUMCPUVPS 3

Now the sum of conexion for soctcp = 150 is right this ?
if all soctcp connection are then not sufficient.

Gustavo.

[Klloyd]

Gustavo,

Muchas gracias por su repuesto! Pardon my cander but, could my engine have run at all with the NETTYPE settings being that far off?

Nevertheless I have taken your observations to heart and have
investigated the subject of NETTYPE settings in the manual. While the explanation is very complex and I had to read it a couple of times, I THINK it says that the number of poll threads assigned to the connection type should be no more than NUMCPUVPS for ANY connection type.

If I had more experience as an Informix DBA, I would be more confident in my interpretation of the manual. Do you think I am reading it correctly?

Gracias otra vez.

Servidor de usted,

Kevin Lloyd

[fprose]

NETTYPE is missunderstood and somewhat forgiving. You never really know your settings are wrong until you run into a situration like yours.

[Klloyd]

Hi,

I found the problem. The reason that informix was bound to 200 userthreads was because the SEMMNU kernel parameter was set to 80. We bumped it up to 2048 and the problem cleared. Now informix is using 260 userthreads at peak performance.

By the way, I found this in the machine notes right under my nose! This is a lesson well learned -- when dealing with performance issues, consult the machine notes first.

Thanks for everybody's help

Kevin