Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
9 replies Latest Post - ‏2012-01-17T16:45:48Z by SystemAdmin
veramasu
veramasu
6 Posts
ACCEPTED ANSWER

Pinned topic Database connection very slow with informix

‏2012-01-12T06:29:21Z |
Hi everyone,

We are using informix in our product. in one of the backend process we are using multiple threads (MAX 32) to do some business logic and update the data into the DB. So at max we see 32 threads being active and open 32 individual connections .

The issue I have is while these threads are trying to open/close connection with the database we are observing a huge delay. It varies from 0 seconds to nearly 2 hours eventually and may even more.
We added a debug message to capture the time it takes to open and close connection(t1 before opening the connection and t2 after connection being opened) and observed that after few hours the database connection(open/close) becomes very very slow.
the periodic checkpoint interval (CKPTINTVL ) is every 2 hrs and it completed in less than 2 seconds.

We use esql routine to connect/disconnect to the DB

The pstack of the backend process is attached. We can see all 32 threads are active and it is simply hangs in open or close connection. Also point to note is the "_sqLockList" . we dont know anything about this because google search doesnt have any info of _sqLockList

It would be a great help if you can give some light on this issue.

void InitConn(const char* a_db_name)
{
$char db_name96;
$char conn_name96;
strcpy(db_name,a_db_name);
sprintf(conn_name,"conn_%d",thr_self());
$connect to $db_name as $conn_name;
if(ifx_sqlca()->sqlcode < 0)
{
throw ParserException(
ParserException::DBAccessError,
"Insert::InitConn()",
"Error while initiating connection",
ifx_sqlca()->sqlcode);
exit(1);
}
}

void closeConn()
{
$char conn_name96;
sprintf(conn_name,"conn_%d",thr_self());
$disconnect $conn_name;
if(ifx_sqlca()->sqlcode < 0)
{
throw ParserException(
ParserException::DBAccessError,
"Insert::closeConn()",
"Error while disconnecting from DB:",
ifx_sqlca()->sqlcode);
}
}

Attachments

Updated on 2012-01-17T16:45:48Z at 2012-01-17T16:45:48Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    1143 Posts
    ACCEPTED ANSWER

    Re: Database connection very slow with informix

    ‏2012-01-12T09:57:52Z  in response to veramasu
    Hi,

    can you please provide the following information?
    • operating system server/client
    • Informix version
    • CSDK version
    • THREADLIB env variable
    • Do you use sysdbopen/sysdbclose procedure?

    I did a quick test on Solaris with THREADLIB=POSIX and THREADLIB=SOL but did not see a problem.

    Best regards,
    Marion
  • veramasu
    veramasu
    6 Posts
    ACCEPTED ANSWER

    Re: Database connection very slow with informix

    ‏2012-01-12T11:38:59Z  in response to veramasu
    • operating system server/client
    SunOS qault61dc 5.10 Generic_120011-14 sun4u sparc SUNW,Ultra-60
    • Informix version
    IBM Informix Dynamic Server Version 9.40.UC8
    • CSDK version
    Will get you the CSDK version soon as we dont have access to the build server now.
    • THREADLIB env variable
    SOL
    • Do you use sysdbopen/sysdbclose procedure?
    No.
  • veramasu
    veramasu
    6 Posts
    ACCEPTED ANSWER

    Re: Database connection very slow with informix

    ‏2012-01-12T12:25:54Z  in response to veramasu
    Thanks Marion for your help.
    Also i attached the onstat -all output here.
    • SystemAdmin
      SystemAdmin
      1143 Posts
      ACCEPTED ANSWER

      Re: Database connection very slow with informix

      ‏2012-01-12T15:20:49Z  in response to veramasu
      Hi,

      it looks like you are using password encryption.
      Which INFORMIXSERVER is used by the multithreaded program?
      Please check your sqlhosts (options CSM).

      If the progarm uses the encrypted connection:
      can you do a test without encryption?

      Regards,
      Marion
  • veramasu
    veramasu
    6 Posts
    ACCEPTED ANSWER

    Re: Database connection very slow with informix

    ‏2012-01-12T16:18:27Z  in response to veramasu
    No we dont use the encrypted connection. The Informix server and the client(C++ application) are running on the same server. I hope it uses shared memory. Could you please let me know how to know whether the communication is shared memory or TCP socket based. becuase i could see some article discussing about TCP based communication and DHCP issues.
    • SystemAdmin
      SystemAdmin
      1143 Posts
      ACCEPTED ANSWER

      Re: Database connection very slow with informix

      ‏2012-01-12T22:31:44Z  in response to veramasu
      Hi,

      you can't use shm connection when you initiate multiple connections.
      This will throw error -27000
      -27000 Cannot support multiple connections over shared memory.

      You can see the connection type in onstat -g ntu:

      Individual thread network information (basic):
      netscb type thread name sid fd poll reads writes q-nrm q-pvt q-exp
      17ca4260 tlitcp sqlexec 2457004 213 5 8 8 0/ 1 1/ 1 0/ 0
      17ca5660 ipcshm sqlexec 2457003 228 5 3 3 0/ 1 1/ 1 0/ 0

      I checked our knowledge base and found two similar problems.
      The solution was to upgrade the server.
      I guess you know that 9.40 is no longer supported.

      Regards,
      Marion
      • veramasu
        veramasu
        6 Posts
        ACCEPTED ANSWER

        Re: Database connection very slow with informix

        ‏2012-01-13T06:34:02Z  in response to SystemAdmin
        We checked the sessions opened by the backend server with the informix using onstat -g ses | grep <pid>. And then we mapped to the type of connection (shm or tcp).
        it shows all these connections are tcp

        onstat -g ntu | grep 5603
        17852db0 tlitcp sqlexec 56039 205 5 3 3 0/ 1 1/ 1 0/ 0
        1966adf8 tlitcp sqlexec 56038 72 5 435881 435909 0/ 1 1/ 1 0/ 0
        16ebce40 tlitcp sqlexec 56037 71 5 4 4 0/ 1 1/ 1 0/ 0

        Do you see any issues with tcp also in 9.40? I will check the option of moving to the higher version but if we can get some point to improve the performance(open/close connection) in 9.40 itself it would be great help.
        • veramasu
          veramasu
          6 Posts
          ACCEPTED ANSWER

          Re: Database connection very slow with informix

          ‏2012-01-17T07:21:03Z  in response to veramasu
          Could you please help me on this.?
          • SystemAdmin
            SystemAdmin
            1143 Posts
            ACCEPTED ANSWER

            Re: Database connection very slow with informix

            ‏2012-01-17T16:45:48Z  in response to veramasu
            Sorry, I did not have to time to do some test with 9.40.

            You might upgrade to 9.40.UC10 if you have this version.
            This solved the problem in another case.

            If you have a support contract for continuing support you can open a PMR
            and try to get this version.
            I am not sure if this is possible but it might be the only solution.

            Regards,
            Marion