Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

    Using connection pooling

    Hi sirs,
    I am using SmartClient 6.5.1 with Tomcat 6.0 and ORACLE 11. I want to activte the connection pooling. After setting the parameter "pooling.default.whenExhaustedAction":"block" the JVM can only read one file.
    For the next file the JVM get a time out. It seems, there is no pool.
    Where is my mistake?

    Here is a part of my configview:

    Merged Configuration (interpolated):
    "datasources.enableUpToDateCheck":"true",
    "datasources.pool.enabled":"true",
    "datasources.pool.maxActive":"5",
    "datasources.pool.maxIdle":"5",
    "datasources.pool.minEvictableIdleTimeMillis":"5000",
    "datasources.pool.numTestsPerEvictionRun":"5",
    "datasources.pool.testOnBorrow":"false",
    "datasources.pool.testOnReturn":"true",
    "datasources.pool.testWhileIdle":"true",
    "datasources.pool.timeBetweenEvictionRunsMillis":"10000",
    "datasources.simpleModeList":"supplyItem supplyCategory animals
    ....
    "pooling.default.maxActive":"5",
    "pooling.default.maxIdle":"5",
    "pooling.default.maxWait":"5000",
    "pooling.default.minEvictableIdleTimeMillis":"5000",
    "pooling.default.numTestsPerEvictionRun":"5",
    "pooling.default.testOnBorrow":"false",
    "pooling.default.testOnReturn":"true",
    "pooling.default.testWhileIdle":"true",
    "pooling.default.timeBetweenEvictionRunsMillis":"10000",
    "pooling.default.whenExhaustedAction":"block",
    .....
    "sql.Oracle.database.supportsSQLLimit":"false",
    "sql.Oracle.database.type":"oracle",
    "sql.Oracle.driver":"oracle.jdbc.pool.OracleDataSource",
    "sql.Oracle.driver.context":"_container_",
    "sql.Oracle.driver.databaseName":"xxx",
    "sql.Oracle.driver.driverType":"thin",
    "sql.Oracle.driver.networkProtocol":"tcp",
    "sql.Oracle.driver.password":"xxx",
    "sql.Oracle.driver.portNumber":"1521",
    "sql.Oracle.driver.serverName":"xxxx",
    "sql.Oracle.driver.user":"xxxx",
    "sql.Oracle.interface.type":"dataSource",
    "sql.Oracle.pool.enabled":"true",
    "sql.Oracle_JNDI.driver.context":"sqlDataSources",
    "sql.Oracle_JNDI.driver.name":"Oracle",
    "sql.oracle.pingTest":"select 1 from dual",
    "sql.oracle.quoteColumnNames":"true",
    ....
    "sql.pool.enabled":"true",
    "sql.pool.maxActive":"5",
    "sql.pool.maxIdle":"5",
    "sql.pool.minEvictableIdleTimeMillis":"5000",
    "sql.pool.numTestsPerEvictionRun":"5",
    "sql.pool.testOnBorrow":"true",
    "sql.pool.testWhileIdle":"true",
    "sql.pool.timeBetweenEvictionRunsMillis":"10000",
    "sql.poold.testOnReturn":"true",
    "sql.postgresql.quoteColumnNames":"true",
    "sql.sqlserver.quoteColumnNames":"false",
    ....

    and here is my startup protocol:
    .....
    === 2009-02-05 14:51:33,593 [main] INFO PreCache - Isomorphic PreCache servlet loading
    === 2009-02-05 14:51:33,859 [main] DEBUG XML - Parsed XML from D:\Tomcat 6.0\webapps\SDE\isomorphic\system\schema\DataSource.ds.xml: 16ms
    === 2009-02-05 14:51:33,859 [main] DEBUG XML - Parsed XML from D:\Tomcat 6.0\webapps\SDE\isomorphic\system\schema\builtinTypes.xml: 0ms
    Problem loading builtinTypes.xml
    Exception when loading from D:/Tomcat 6.0/webapps/SDE/isomorphic/system/schema/builtinTypes.xml:
    java.util.NoSuchElementException: Timeout waiting for idle object
    at org.apache.commons.pool.impl.GenericKeyedObjectPool.borrowObject(GenericKeyedObjectPool.java:827)
    at com.isomorphic.pool.PoolManager.borrowObject(PoolManager.java:70)
    at com.isomorphic.datasource.DataSourceManager.getDataSource(DataSourceManager.java:56)
    at com.isomorphic.datasource.ValidationContext.getType(ValidationContext.java:160)
    at com.isomorphic.datasource.BasicDataSource.handleExtraElementValue(BasicDataSource.java:763)
    at com.isomorphic.datasource.BasicDataSource.elementAsRecord(BasicDataSource.java:739)
    at com.isomorphic.datasource.BasicDataSource.toRecord(BasicDataSource.java:431)
    at com.isomorphic.datasource.BasicDataSource.toRecords(BasicDataSource.java:393)
    at com.isomorphic.datasource.DataSource.recordsFromXML(DataSource.java:586)
    at com.isomorphic.xml.XML.toDSRecords(XML.java:250)

    ....

    Thank you for your efforts

    best regards

    #2
    Hi there -

    I think the problem is the small value you've provided for the datasources.poool.maxActive. Our default value here is -1, which allows the app to borrow as many as required by multiple concurrent threads. SmartClient itself is holding on to instances in-memory, but outside the pool, so attempts to fetch additional DataSource objects are failing.

    I recommend setting this at least at 100, or -1 for no limit. You may also want to review the maxActive settings for pooling.default and your sql db connection (if you're using that).

    Let us know how that works out...

    Comment


      #3
      Hi sir,
      I think my problem is to activate the pooling connection for the sql db connection. Have I to do this in the server.properties? - I don't now the syntax for this properties, so I am changed all default properties for pooling founded in the configview.

      sql.Oracle.driver.user: xxxx
      sql.Oracle.driver.password: xxxx
      sql.Oracle.database.type: oracle
      sql.Oracle.driver: oracle.jdbc.pool.OracleDataSource
      sql.Oracle.driver.networkProtocol: tcp
      sql.Oracle.driver.context: _container_
      sql.Oracle.database.supportsSQLLimit: false
      sql.Oracle.interface.type: dataSource
      sql.Oracle.driver.driverType: thin
      sql.Oracle.pool.enabled: true

      sql.Oracle.?????.maxActive: 5
      sql.Oracle.?????.maxIdle: 5
      sql.Oracle.?????.maxWait: 5000
      sql.Oracle.?????.minEvictableIdleTimeMillis:5000
      sql.Oracle.?????.numTestsPerEvictionRun: 5
      sql.Oracle.?????.timeBetweenEvictionRunsMillis: 10000
      sql.Oracle.?????.whenExhaustedAction: block
      sql.Oracle.?????.testOnBorrow: false
      sql.Oracle.?????.testOnReturn: true

      Comment


        #4
        Like so:

        sql.Oracle.pool.maxActive: 5
        sql.Oracle.pool.maxIdle: 5
        sql.Oracle.pool.maxWait: 5000
        sql.Oracle.pool.minEvictableIdleTimeMillis:5000
        sql.Oracle.pool.numTestsPerEvictionRun: 5
        sql.Oracle.pool.timeBetweenEvictionRunsMillis: 10000
        sql.Oracle.pool.whenExhaustedAction: block
        sql.Oracle.pool.testOnBorrow: false
        sql.Oracle.pool.testOnReturn: true

        Hope this helps...

        Comment


          #5
          Thank you,
          I will test my db connection
          Now I am back to my start-up problem: not closed sessions in the oracle db:
          ♀SID SER# BOX USERNAME OS_USER COMMAND STATUS EVENT SEKUNDEN MINUTEN
          ----- ----- ------------------ ---------- -------- ------------------ -------- ------------------------------ ---------- ----------
          43 14 ALLEIN\HAUPT-PC BASF HAUPT-PC Select ACTIVE SQL*Net message to client 0 0
          121 818 haupt-pc BASF No command INACTIVE SQL*Net message from client 21 ,35
          108 333 haupt-pc BASF No command INACTIVE SQL*Net message from client 21 ,35
          84 160 haupt-pc BASF No command INACTIVE SQL*Net message from client 21 ,35
          52 594 haupt-pc BASF No command INACTIVE SQL*Net message from client 21 ,35
          73 691 haupt-pc BASF No command INACTIVE SQL*Net message from client 21 ,35
          42 290 haupt-pc BASF No command INACTIVE SQL*Net message from client 22 ,366666667
          39 1325 haupt-pc BASF No command INACTIVE SQL*Net message from client 32 ,533333333
          105 150 haupt-pc BASF No command INACTIVE SQL*Net message from client 32 ,533333333
          55 1025 haupt-pc BASF No command INACTIVE SQL*Net message from client 36 ,6
          86 91 haupt-pc BASF No command INACTIVE SQL*Net message from client 42 ,7
          104 108 haupt-pc BASF No command INACTIVE SQL*Net message from client 42 ,7
          75 408 haupt-pc BASF No command INACTIVE SQL*Net message from client 42 ,7
          131 2340 haupt-pc BASF No command INACTIVE SQL*Net message from client 42 ,7
          87 540 haupt-pc BASF No command INACTIVE SQL*Net message from client 51 ,85
          77 112 haupt-pc BASF No command INACTIVE SQL*Net message from client 52 ,866666667
          65 577 haupt-pc BASF No command INACTIVE SQL*Net message from client 53 ,883333333
          68 71 haupt-pc BASF No command INACTIVE SQL*Net message from client 53 ,883333333
          112 478 haupt-pc BASF No command INACTIVE SQL*Net message from client 54 ,9
          97 84 haupt-pc BASF No command INACTIVE SQL*Net message from client 55 ,916666667
          94 74 haupt-pc BASF No command INACTIVE SQL*Net message from client 56 ,933333333
          106 99 haupt-pc BASF No command INACTIVE SQL*Net message from client 57 ,95
          117 141 haupt-pc BASF No command INACTIVE SQL*Net message from client 57 ,95
          122 749 haupt-pc BASF No command INACTIVE SQL*Net message from client 57 ,95
          149 794 haupt-pc BASF No command INACTIVE SQL*Net message from client 58 ,966666667
          76 112 haupt-pc BASF No command INACTIVE SQL*Net message from client 59 ,983333333
          139 175 haupt-pc BASF No command INACTIVE SQL*Net message from client 59 ,983333333
          14 281 haupt-pc BASF No command INACTIVE SQL*Net message from client 60 1
          74 94 haupt-pc BASF No command INACTIVE SQL*Net message from client 60 1
          126 43 haupt-pc BASF No command INACTIVE SQL*Net message from client 60 1
          100 148 haupt-pc BASF No command INACTIVE SQL*Net message from client 61 1,01666667
          15 816 haupt-pc BASF No command INACTIVE SQL*Net message from client 61 1,01666667
          83 1501 haupt-pc BASF No command INACTIVE SQL*Net message from client 62 1,03333333
          133 514 haupt-pc BASF No command INACTIVE SQL*Net message from client 62 1,03333333
          129 782 haupt-pc BASF No command INACTIVE SQL*Net message from client 62 1,03333333
          82 1548 haupt-pc BASF No command INACTIVE SQL*Net message from client 63 1,05
          118 820 haupt-pc BASF No command INACTIVE SQL*Net message from client 63 1,05
          63 1016 haupt-pc BASF No command INACTIVE SQL*Net message from client 63 1,05
          125 525 haupt-pc BASF No command INACTIVE SQL*Net message from client 63 1,05
          135 41 haupt-pc BASF No command INACTIVE SQL*Net message from client 63 1,05
          71 1477 haupt-pc BASF No command INACTIVE SQL*Net message from client 63 1,05
          95 1876 haupt-pc BASF No command INACTIVE SQL*Net message from client 73 1,21666667
          113 1192 haupt-pc BASF No command INACTIVE SQL*Net message from client 614 10,2333333
          62 1533 haupt-pc BASF No command INACTIVE SQL*Net message from client 615 10,25

          After reaching the maxActive a get a timeout. This is ok. But somebody forget to close the connections. Sometimes they are closed after view minutes. Sometimes they are closed after an hour. Here is my tomcat-connection definiton:

          Name: Catalina:type=Connector,port=8081
          modelerType: org.apache.catalina.mbeans.ConnectorMBean
          port: 8081
          redirectPort: 8443
          maxHttpHeaderSize: 8192
          secure: false
          maxThreads: 40
          acceptCount: 150
          bufferSize: 2048
          scheme: http
          emptySessionPath: false
          compression: off
          protocolHandlerClassName: org.apache.coyote.http11.Http11AprProtocol
          keepAliveTimeout: 5000
          threadPriority: 5
          tcpNoDelay: true
          protocol: HTTP/1.1
          maxKeepAliveRequests: 1
          disableUploadTimeout: true
          enableLookups: false
          maxPostSize: 2097152
          proxyPort: 0
          connectionLinger: -1
          connectionTimeout: 20000
          xpoweredBy: false
          allowTrace: false
          connectionUploadTimeout: 300000
          useBodyEncodingForURI: false

          and there a request:

          === 2009-02-09 16:52:59,125 [81-1] DEBUG RPCManager - Processing 1 requests.
          === 2009-02-09 16:52:59,125 [81-1] DEBUG RPCManager - Request #1 (DSRequest) payload: {
          criteria:{},
          operationConfig:{dataSource:"HISTORIE", operationType:"fetch"},
          startRow:0,
          endRow:75,
          appID:"builtinApplication",
          operation:"HISTORIE_fetch",
          oldValues:{}
          }
          === 2009-02-09 16:52:59,125 [81-1] INFO IDACall - Performing 1 operation(s)
          === 2009-02-09 16:52:59,125 [81-3] DEBUG RPCManager - Content type for RPC transaction: text/plain; charset=UTF-8
          === 2009-02-09 16:52:59,125 [81-3] DEBUG RPCManager - non-DMI response, dropExtraFields: false
          === 2009-02-09 16:52:59,125 [81-1] DEBUG AppBase - [builtinApplication.HISTORIE_fetch] No userTypes defined, allowing anyone access to all operations for this application
          === 2009-02-09 16:52:59,125 [81-1] DEBUG AppBase - [builtinApplication.HISTORIE_fetch] No public zero-argument method named '_HISTORIE_fetch' found, performing generic datasource operation
          === 2009-02-09 16:52:59,125 [81-1] INFO SQLDataSource - [builtinApplication.HISTORIE_fetch] Performing fetch operation with
          criteria: {} values: {}
          === 2009-02-09 16:52:59,125 [81-1] WARN SQLWhereClause - [builtinApplication.HISTORIE_fetch] empty condition
          === 2009-02-09 16:52:59,125 [81-1] DEBUG SQLDataSource - [builtinApplication.HISTORIE_fetch] Executing row count query: SELECT COUNT(*) FROM HISTORIE WHERE ('1'='1')
          === 2009-02-09 16:52:59,125 [81-1] INFO SQLDriver - [builtinApplication.HISTORIE_fetch] Executing SQL query on 'Oracle': SELECT COUNT(*) FROM HISTORIE WHERE ('1'='1')
          === 2009-02-09 16:52:59,125 [81-1] DEBUG SQLDataSource - [builtinApplication.HISTORIE_fetch] JDBC driver windowed select rows 0->75, result size 75. Query: SELECT "DATUM", "TEXT", "ID" FROM HISTORIE WHERE ('1'='1')
          === 2009-02-09 16:52:59,125 [81-1] INFO DSResponse - [builtinApplication.HISTORIE_fetch] DSResponse: List with 1 items
          === 2009-02-09 16:52:59,125 [81-1] DEBUG RPCManager - Content type for RPC transaction: text/plain; charset=UTF-8

          Where is responsible for closeing the connections? SQLDriver? SQLDataSource ?

          Best regards
          Last edited by STR; 9 Feb 2009, 08:12.

          Comment

          Working...
          X