3 Replies Latest reply: Feb 29, 2012 2:03 PM by Clovis Wichoski RSS

the right value for JDBC connectiontimeout parameter on a gigabit ethernet?

Clovis Wichoski
Currently Being Moderated

Hi,

 

for years i'm getting some stuck threads, with JDBC pool, that blocks all my system and the only way to recover is reboot the system or stops tomcat,

 

now i found a connectiontimeout parameter to use with JDBC connections, is right to set a value like 10 ms or less for a gigabit ethernet? there is a way to discover why those connections never comes? if a timeout occurs there is way to log in JDBC side, and in database side?

 

the app server and db server are connected using a cross over cable.

 

my db server and JDBC version is: 7.6.03.07 running on Linux 64bit with

 

here is a sample thread that causes my system to block many other threads, note that is where the connections to database are created.

 

"http-80-5" daemon prio=10 tid=0x0000000044b3e000 nid=0x3dbd runnable

   java.lang.Thread.State: RUNNABLE

        at java.net.SocketInputStream.socketRead0(Native Method)

        at java.net.SocketInputStream.read(SocketInputStream.java:129)

        at com.sap.dbtech.rte.comm.BasicSocketComm.receiveConnect(BasicSocketComm.java:707)

        at com.sap.dbtech.rte.comm.BasicSocketComm.dbConnectExchange(BasicSocketComm.java:789)

        at com.sap.dbtech.rte.comm.BasicSocketComm.connectDB(BasicSocketComm.java:233)

        at com.sap.dbtech.rte.comm.SocketComm$1.open(SocketComm.java:38)

        at com.sap.dbtech.jdbc.DriverSapDB.openConnection(DriverSapDB.java:966)

        at com.sap.dbtech.jdbc.DriverSapDB.openByURL(DriverSapDB.java:891)

        at com.sap.dbtech.jdbc.DriverSapDB.connect(DriverSapDB.java:208)

        - locked <0x00002aaaba9903c8> (a com.sap.dbtech.jdbc.DriverSapDB)
at org.apache.tomcat.dbcp.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
at org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:294)
- locked <0x00002aaaba990530> (a org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory)
at org.apache.tomcat.dbcp.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:974)
at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:96)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
at org.exolab.castor.jdo.engine.DatabaseRegistry.createConnection(DatabaseRegistry.java:399)
at org.exolab.castor.jdo.engine.TransactionContextImpl.getConnection(TransactionContextImpl.java:203)
at org.exolab.castor.persist.TransactionContext.query(TransactionContext.java:644)
- locked <0x00002aaac72093f0> (a org.exolab.castor.jdo.engine.TransactionContextImpl)

        at org.exolab.castor.jdo.engine.OQLQueryImpl.execute(OQLQueryImpl.java:458)

        at org.exolab.castor.jdo.engine.OQLQueryImpl.execute(OQLQueryImpl.java:414)

        at com.supridatta.bean.DataPersist.consulta(DataPersist.java:500)

 

best regards

 

Clóvis

 

  • Re: the right value for JDBC connectiontimeout parameter on a gigabit ethernet?
    Jason Fay
    Currently Being Moderated

    Clovis,<br>

    Did you find a solution to this problem?  I'm seeing similar thread hangs.<br>

    <br>

    Thread 'HTTP Worker [11]', process 'server0', index '13'
    "XI SOAP[Retail1_SA_XTR_AAE/Retail1_Sender_XTR_AAE/]_19878" Id=23 is RUNNABLE (running in native)
      at java.net.SocketInputStream.socketRead0(Native Method)
      at java.net.SocketInputStream.read(SocketInputStream.java:129)
      at com.sap.dbtech.rte.comm.BasicSocketComm.receiveData(BasicSocketComm.java:725)
      at com.sap.dbtech.rte.comm.BasicSocketComm.receive(BasicSocketComm.java:814)
      at com.sap.dbtech.rte.comm.JdbcCommunication.execute(JdbcCommunication.java:41)
      at com.sap.dbtech.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:540)
      at com.sap.dbtech.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:465)
      at com.sap.dbtech.jdbc.CallableStatementSapDB.handleStreamsForPutval(CallableStatementSapDB.java:1632)
      at com.sap.dbtech.jdbc.CallableStatementSapDB.execute(CallableStatementSapDB.java:483)
      at com.sap.dbtech.jdbc.CallableStatementSapDB.execute(CallableStatementSapDB.java:315)
      at com.sap.dbtech.jdbc.CallableStatementSapDB.executeUpdate(CallableStatementSapDB.java:769)
      at com.sap.dbtech.jdbc.trace.PreparedStatement.executeUpdate(PreparedStatement.java:81)
      at com.sap.sql.jdbc.direct.DirectPreparedStatement.executeUpdate(DirectPreparedStatement.java:365)
      at com.sap.sql.jdbc.common.CommonPreparedStatement.executeUpdate(CommonPreparedStatement.java:339)
      at com.sap.engine.services.dbpool.wrappers.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:279)
      at com.sap.engine.messaging.impl.core.store.MessageStore.insertMessage(MessageStore.java:786)
    ...
    ...

     

    • Re: the right value for JDBC connectiontimeout parameter on a gigabit ethernet?
      Clovis Wichoski
      Currently Being Moderated

      Hi, Jason,

       

      i never reached to state what really happen with that, but what helps me to know what is going on the server when that stuck occurs is the follow sequence of shell scripts, if you use Linux, that is ready to work

       

      checkDbActivity.sh

      #!/bin/sh
      x_cons YOUR_DB_NAME sh ac 1

       

      taskRunningCommands.sh

      #!/bin/bash
      #set -x
      export PATH=$PATH:/pex/opt/sdb/programs/bin
      executa(){
        variaveis
        arqsCmdDB
        executaCMD
      }
      variaveis(){
        cmd="/home/sdb/scripts/tmp/taskRunningCommand.cmd"
        data=$(date +%y%m%d)
        dat=$(date +%m%y)
        hora=$(date +%H%M%S)
        dbName="YOUR_DB_NAME"
        dbmUser="dbm"
        dbmPwd="YOUR_DBM_PASSWORD"
      }
      arqsCmdDB(){
        echo "sql_execute select * from running_commands ">$cmd
        echo "sql_execute select * from sessions ">>$cmd
      }
      executaCMD(){
        dbmcli -d $dbName -u $dbmUser,$dbmPwd -i $cmd
      }
      executa

       

      the command *select * from running_commands* just returns if you activate command monitor, to activate that i use on dbmcli the follow sequence of commands.

       

      sql_execute diagnose monitor clear
      sql_execute DIAGNOSE MONITOR ROWNO 3000
      sql_execute diagnose monitor time 2000
      sql_execute diagnose monitor data on

       

      more details about this commands you can see in:

       

      https://wiki.sdn.sap.com/wiki/display/MaxDB/Command+Monitor

       

      with this, when you get stuck threads, you can check your database side, to see what task is waiting for, and what SQL command is executing at that time, i discovered, that sometimes, a bigger read transaction with isolation level 1, can block all other threads, and because of this, all my SQL that i use for reporting i append a WITH LOCK ISOLATION LEVEL 0 at end of SQL.

       

      this solved many things here, one thing that i cant solve is when pool is getting new connections in paralel, this causes thread stuck because new connections tries to lock a "catalog" table, maybe something wrong inside JDBC drive that queries this table with isolation level 1, but without sources i cant check if i'm right or no.

      to solve this, i configured my pool of connections to always have 5 ready connections, to avoid construction of connections too often.

       

      i hope that this helps, and please share your steps, as this still a obscure subject for me.

       

      best regards.

       

      Clóvis

       

Actions