Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    Denver CO, USA
    Posts
    5

    Unanswered: Informix bound to 200 user threads

    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

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780

    Re: Informix bound to 200 user threads

    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.

  3. #3
    Join Date
    Nov 2003
    Location
    Denver CO, USA
    Posts
    5
    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

  4. #4
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177
    NETTYPE is missunderstood and somewhat forgiving. You never really know your settings are wrong until you run into a situration like yours.
    Fred Prose

  5. #5
    Join Date
    Nov 2003
    Location
    Denver CO, USA
    Posts
    5

    Talking

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •