#57 Getting connection checkout execeptions, despite very agressive settings

v1.0 (example)
open
nobody
None
5
2014-06-04
2013-11-12
SyRenity
No

Hi,

Using latest available C3P0, still getting checkout timeouts, despite aggressive settings.

Below is c3p0 prop file, will appreciate any optimizations comments:

#Start with moderate pool, and use up to 100 connections per application
c3p0.minPoolSize = 20
c3p0.maxPoolSize = 100
c3p0.acquireIncrement = 10

#Fire up sufficient worker threads
c3p0.numHelperThreads = 10

#Prune pooled connections back to minimal after some time
c3p0.maxIdleTime = 600
c3p0.maxIdleTimeExcessConnections = 300
c3p0.idleConnectionTestPeriod = 60

#Clean-up orphan connections
c3p0.unreturnedConnectionTimeout = 600
# Diagnose unreturned connections
c3p0.debugUnreturnedConnectionStackTraces  = true

#Wait up to 30 seconds to checkout a connection, before throwing out exception
c3p0.checkoutTimeout = 30000

#Database test query
c3p0.preferredTestQuery = "select * from servers limit 0,1;"

#Try to get a new connection from DB indefinitely, every 10 seconds
c3p0.acquireRetryAttempts = 0
c3p0.acquireRetryDelay = 10000

Discussion

  • Steve Waldman
    Steve Waldman
    2013-11-13

    hi,

    your settings are aggressive in terms of your running a large pool, but you have no Connection testing configured at all, so i'd expect you might see Connection timeouts in the sense of broken Connections after a period of time. Please configure some Connection testing. (preferredTestQuery sets the Connection that would be used if you ever tested, but you don't ever test, other then when c3p0 automaically verifies Connections after client Exceptions.)

    if you are experiencing checkout timeouts, that is you see Threads stuck in awaitAvailable() for more than 30 seconds, you'll need to dig a bit to understand why. are all Connections busy? do you have a Connection leak? you are trying to debug a Connection leak with these settings, but an unreturnedConnectionTimeout of 600 secs is too long for most applications. do you have very long-lived queries? if so, then do you see 100 Connections stuck in long queries? this stuff is pretty intuitive, but you have to observe it. understand what your Threads are doing, why some clients are hung in awaitAvailable()

    but before you do anything else, configure some Connection testing. see http://www.mchange.com/projects/c3p0/#configuring_connection_testing

     
  • Steve Waldman
    Steve Waldman
    2013-11-13

    btw, one obvious possibility, since you very unusually have acquireRetryAttempts set to zero, is that you experience database outages which never lead to failures as database outages (since you've asked c3p0 to keep trying the database forever), so instead you see clients timeout as they wait for a database that is tried and tried again to no avail.

     
  • SyRenity
    SyRenity
    2013-11-13

    Hi,

    Thanks for your fast and very useful reply. I'm trying the suggested changes as below, but with testConnectionOnCheckout enabled, the pool just hangs for some reason - any idea?

    #Start with moderate pool, and use up to 100 connections per application
    c3p0.minPoolSize = 20
    c3p0.maxPoolSize = 50
    c3p0.acquireIncrement = 10
    
    #Fire up sufficient worker threads
    c3p0.numHelperThreads = 10
    
    #Prune pooled connections back to minimal after some time
    c3p0.maxIdleTime = 600
    c3p0.maxIdleTimeExcessConnections = 300
    c3p0.idleConnectionTestPeriod = 60
    
    #Clean-up orphan connections
    c3p0.unreturnedConnectionTimeout = 60
    # Diagnose unreturned connections
    c3p0.debugUnreturnedConnectionStackTraces  = true
    
    #Wait up to 30 seconds to checkout a connection, before throwing out exception
    c3p0.checkoutTimeout = 30000
    
    #Database test query
    c3p0.preferredTestQuery = "SELECT 1;"
    c3p0.testConnectionOnCheckout = true
    
     
  • SyRenity
    SyRenity
    2013-11-13

    Any idea if the pool doesn't like the (albeit not common) loadbalance JDBC feature, triggered by the following DB uri:
    jdbc:mysql:loadbalance://xxx.xxx.xxx.xxx/dbname?

    which causes it to hang?

     
    Last edit: SyRenity 2013-11-13
  • SyRenity
    SyRenity
    2013-11-14

    Traced a bit the behavior of worker threads in debugger, all of them seem to get stuck in open socket stage.

    Pretty strange considering they do work normally when the test query is disabled. Also tried upgrading to latest JDBC MySQL server, still didn't help.

     
  • Steve Waldman
    Steve Waldman
    2013-11-14

    hi, i don't know much about mysql loadbalancing :(

    do when you attempt to open new database Connections, the Thread freezes at socket open, neither succeeding nor failing with an Exception?

    if you want, you can use maxAdministrativeTaskTime to force failures in that kind of case. but it's a blunt instrument. obviously, the best thing to do is arrange for your application to be able reliably to connect to the dbms (or fail to connect with a prompt Exception if something is amiss)

    http://www.mchange.com/projects/c3p0/#maxAdministrativeTaskTime

     
  • SyRenity
    SyRenity
    2013-11-14

    Yes, this is exactly what happens, the C3P0 threads freeze on socket connection, when a testOnCheckout is enabled, as per this threads dump:
    http://pastie.org/private/feuoco5ecepasoaz5wgyw
    When disabling the testOnCheckout, it works just fine.

    The DB is perfectly available, in fact it's in local network.

    Anything else I can look at, before enabling the maxAdministrativeTaskTime?

    Thanks.

     
  • SyRenity
    SyRenity
    2013-11-14

    Btw, I disabled the loadbalancing feature of JDBC and tried again, and it still hangs.

    The issue is apparently in the testOnCheckout feature, any known issues with the latest C3P0 versions?

     
  • Steve Waldman
    Steve Waldman
    2013-11-14

    so, i can't argue with experienced events, but there's no easily conceivable reason why testConnectionOnCheckout would have anything to do with this. you are hanging in Connection acquisition. Connections can't be tested before they are acquired.

    do note that it is testConnectionOnCheckout, not "testOnCheckout". (the latter would be an unrecognized config param, but should do no harm.) http://www.mchange.com/projects/c3p0/#testConnectionOnCheckout

    putting aside my befuddlement about why Connection testing should have anything to do with acquisition failures, if testConnectionOnCheckout is a problem for you, try testConnectionOnCheckin plus a short idleConnectionTestPeriod, see http://www.mchange.com/projects/c3p0/#configuring_connection_testing

    be sure to use a good preferredTestQuery. the one you use above,

    c3p0.preferredTestQuery = "select * from servers limit 0,1;"
    

    strikes me as not so good on several levels.

    1) standard java properties file parsing won't interpret quotes in the way that you think

    2) there shouldn't be a semicolon at the end of a JDBC query

    3) selecting from a table with multiple items and limiting may be expensive compared with selecting from a table with no values ( maybe look for advice here http://stackoverflow.com/questions/3668506/efficient-sql-test-query-or-validation-query-that-will-work-across-all-or-most )

    good luck!

     
  • SyRenity
    SyRenity
    2013-11-14

    Wow - the problem was apparently that I used:
    c3p0.preferredTestQuery = "SELECT 1;"

    Instead of:
    c3p0.preferredTestQuery = SELECT 1

    Once I set it - all started to work fine.

    Now testing the new connection settings!

     
  • SyRenity
    SyRenity
    2013-11-14

    By the way, below are updated C3P0 properties, anything else I should try while testing meanwhile?

    #Start with moderate pool, and use up to 100 connections per application
    c3p0.minPoolSize = 20
    c3p0.maxPoolSize = 50
    c3p0.acquireIncrement = 10
    
    #Fire up sufficient worker threads
    c3p0.numHelperThreads = 10
    
    #Reduce pooled connections back to minimal after peaks
    c3p0.maxIdleTime = 600
    c3p0.maxIdleTimeExcessConnections = 300
    c3p0.idleConnectionTestPeriod = 60
    
    #Clean-up the stale orphan connections
    c3p0.unreturnedConnectionTimeout = 60
    # Diagnose the stale orphan connections
    c3p0.debugUnreturnedConnectionStackTraces  = true
    
    #Wait up to 30 seconds to checkout a connection, before throwing out an exception
    c3p0.checkoutTimeout = 30000
    
    #Database test query
    c3p0.preferredTestQuery = SELECT 1
    c3p0.testConnectionOnCheckout = true
    

    Thanks!

     
  • Steve Waldman
    Steve Waldman
    2013-11-14

    yay!

    for the most part, my Connection testing advice is in the green box here http://www.mchange.com/projects/c3p0/#configuring_connection_testing

    testConnectionOnCheckout with a good preferredTestQuery is the best way to start. once everything's working, you can switch to the more performant but slightly less safe testConnectionOnCheckin + idleConnectionTestPeriod

    another bit of advice is to be sure to unset debugUnreturnedConnectionStackTraces once you've eliminated any Connection leaks. it exacts the cost of extracting a stack trace on every Connection checkout, so slows you down a bit.

    i'm glad things are looking better.

     
  • SyRenity
    SyRenity
    2013-11-15

    Awesome, then for now on I'm leaving the new above properties hunting for any leaks, then will comment out debugUnreturnedConnectionStackTraces and try to optimize with testConnectionOnCheckin + idleConnectionTestPeriod.

    Any advice on other params above or they appear good?

    Thanks again.

     
  • SyRenity
    SyRenity
    2013-11-15

    By the way, started getting these kind of exceptions:
    Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

    Something on C3P0 level, or I should look at other directions?

    Thanks.