Topic
  • 9 replies
  • Latest Post - ‏2012-01-17T16:45:48Z by SystemAdmin
veramasu
veramasu
6 Posts

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

    Re: Database connection very slow with informix

    ‏2012-01-12T09:57:52Z  
    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

    Re: Database connection very slow with informix

    ‏2012-01-12T11:38:59Z  
    • 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

    Re: Database connection very slow with informix

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

    Re: Database connection very slow with informix

    ‏2012-01-12T15:20:49Z  
    • veramasu
    • ‏2012-01-12T12:25:54Z
    Thanks Marion for your help.
    Also i attached the onstat -all output here.
    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

    Re: Database connection very slow with informix

    ‏2012-01-12T16:18:27Z  
    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

    Re: Database connection very slow with informix

    ‏2012-01-12T22:31:44Z  
    • veramasu
    • ‏2012-01-12T16:18:27Z
    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.
    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

    Re: Database connection very slow with informix

    ‏2012-01-13T06:34:02Z  
    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
    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

    Re: Database connection very slow with informix

    ‏2012-01-17T07:21:03Z  
    • veramasu
    • ‏2012-01-13T06:34:02Z
    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.
    Could you please help me on this.?
  • SystemAdmin
    SystemAdmin
    1143 Posts

    Re: Database connection very slow with informix

    ‏2012-01-17T16:45:48Z  
    • veramasu
    • ‏2012-01-17T07:21:03Z
    Could you please help me on this.?
    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