Thread: Re: [c3p0-users] Collecting historical information about each acquisition attempt
Status: Beta
Brought to you by:
swaldman
From: Steve W. <swa...@mc...> - 2007-02-09 07:59:33
|
Troy, This is a nice addition, but I'm not sure I'd add the API as is. To summarize (for other list members), Troy's patch keeps track of c3p0's attempts to acquire a Connection from a database, markin the start and end times of the acquisition attempt, whether the attempt succeeded, and the Throwable associated with any failures. It would add a method to poolable DataSources by which one could capture information about recent acquisition attempts. This opens the door to a more general issue of which internal-ish events should be tracked and/or exposed to clients for debugging/ custom logging/whatever. The most obvious candidates include acquisitions, check-outs, check-ins, Connection tests, and Connection destruction. Possible solutions include no exposure except via logging (usually verbose at DEBUG levels and failure-only at INFO), minimal pollable exposure (added as of 0.9.1, via getLastXXXFailure() methods, including getLastAcquisitionFailure() proposed by Troy), exposure via event-type-specific polling APIs (what Troy has now proposed for acquisitions), or exposure via one or more publish/ subscribe-style events. I'm kind of hoping the very minimal approach currently implemented (make available the last failure, and hope it's representative) will be good enough for most users. There's an advantage to a failure-only approach, in that no extra overhead is added to ordinary, successful operations. If users want more information, probably the right thing to do would be to define an publish/subscribe event mechanism. The advantage is that one homogenous API could cover most event types, and clients are notified when relevant without having to poll, and we avoid the memory-management issues associated with keeping internal logs. (In the proposed implementation, consider what happens to long running DataSources if users fail to poll. This is solvable with a maximum event length, or by using SoftReferences, etc, but has to be dealt with.) A publish and subscribe event mechanism has its own issues -- the event notifications have to be handled by threads that don't hold pool locks (to avoid deadlocks), and potentially should be handled asynchronously to prevent a bad event handler from killing pool performance (implying more thread overhead). c3p0's internal resource pool has an asynchronous event generation mechanism implemented, but it's remained hidden and disused to avoid the extra complexity. At this stage, I think the main the to figure out what information c3p0's users really want or need, to decide what trade-offs in performance, threading complexity, or API complexity are worth making. Troy, what motivated you to want this detailed logging? Were there Connection acquisition problems you couldn't resolve by checking representative last failures? Do others need any information not currently exposed by c3p0, that should be exposed either via a polling or publish/subscribe API? Anyway, many thanks to Troy for the proposal and the patch. Troy and others, what information would be useful to have that c3p0 does not currently expose? smiles, Steve On Feb 8, 2007, at 11:03 AM, Troy Whittier wrote: > Resending so that message is under 40kb > > On 2/2/07, Troy Whittier <whi...@gm...> wrote: > C3P0 users, > > Recently I submitted a change to get the last acquisition failure > that C3P0 encountered while acquiring a new database connection. > Since then I have discovered a fundamental flaw in using this to > diagnose problems in a system that has been up for an extended > period of time. It lacks details of when the failure occurred and > how often it is happening. > > In order to resolve this issue I have attached a cvs diff > consisting of changes that I have made to c3p0. The changes > consist of collecting useful information around acquiring > connections and providing an API to poll a history of each c3p0 > acquire attempt. > > At the end of the email I including a extract of a log file > produced from our application illustrating a problem that these > changes helped identity. In our project, these changes have > already proven to be useful in diagnosing and monitoring database > connection problems. > > Steve, if possible could you deprecate or remove > "getLastAcquisitionFailure" and instead include the attached changes? > > > Thanks, > > Troy Whittier > > > > AILED in acquiring resource: java.sql.SQLException: Io exception: > The Network Adapter could not establish the connection, start time: > Wed Jan 31 15:09:18 MST 2007, elapsed time: 5000ms > FAILED in acquiring resource: java.sql.SQLException: Io exception: > The Network Adapter could not establish the connection, start time: > Wed Jan 31 15:09:23 MST 2007, elapsed time: 4002ms > FAILED in acquiring resource: java.sql.SQLException: Io exception: > The Network Adapter could not establish the connection, start time: > Wed Jan 31 15:09:23 MST 2007, elapsed time: 4008ms > SUCCEEDED in acquiring resource, start time: Wed Jan 31 15:09:29 > MST 2007, elapsed time: 310ms > SUCCEEDED in acquiring resource, start time: Wed Jan 31 15:09:29 > MST 2007, elapsed time: 314ms > FAILED in acquiring resource: java.sql.SQLException: Io exception: > The Network Adapter could not establish the connection, start time: > Wed Jan 31 15:09:23 MST 2007, elapsed time: 7994ms > FAILED in acquiring resource: java.sql.SQLException: Io exception: > The Network Adapter could not establish the connection, start time: > Wed Jan 31 15:09:29 MST 2007, elapsed time: 1946ms > FAILED in acquiring resource: java.sql.SQLException: Io exception: > The Network Adapter could not establish the connection, start time: > Wed Jan 31 15:09:29 MST 2007, elapsed time: 1946ms > FAILED in acquiring resource: java.sql.SQLException: Io exception: > The Network Adapter could not establish the connection, start time: > Wed Jan 31 15:09:27 MST 2007, elapsed time: 4004ms > FAILED in acquiring resource: java.sql.SQLException: Io exception: > The Network Adapter could not establish the connection, start time: > Wed Jan 31 15:09:29 MST 2007, elapsed time: 2946ms > FAILED in acquiring resource: java.sql.SQLException: Io exception: > The Network Adapter could not establish the connection, start time: > Wed Jan 31 15:09:29 MST 2007, elapsed time: 2601ms > FAILED in acquiring resource: java.sql.SQLException: Io exception: > The Network Adapter could not establish the connection, start time: > Wed Jan 31 15:09:27 MST 2007, elapsed time: 4999ms > SUCCEEDED in acquiring resource, start time: Wed Jan 31 15:09:31 > MST 2007, elapsed time: 1323ms > > <c3p0.diff> > ---------------------------------------------------------------------- > --- > Using Tomcat but need to do more? Need to support web services, > security? > Get stuff done quickly with pre-integrated technology to make your > job easier. > Download IBM WebSphere Application Server v.1.0.1 based on Apache > Geronimo > http://sel.as-us.falkag.net/sel? > cmd=lnk&kid=120709&bid=263057&dat=121642______________________________ > _________________ > c3p0-users mailing list > c3p...@li... > https://lists.sourceforge.net/lists/listinfo/c3p0-users |
From: Troy W. <whi...@gm...> - 2007-02-13 19:38:11
|
Steve, A couple of weeks ago we had a problem acquiring connections from our Oracle database. In some cases we were able to fill our connection pool (minPoolSize=20 and maxPoolSize=20) within 60 seconds but more often it was taking a very long time. In some cases C3P0 would only fill the pool with 17 connections and stop trying to acquire the last 3 connections required to fill the pool. Faced with this problem I wanted answers to a few basic questions: How often is C3P0 attempting to acquire connections? How often is C3P0 getting an exception? How long does it take Oracle to acquire a new connection? After thinking about the problem I considered the following approaches: Install the debug version of the oracle thin driver and turn on logging. Modify C3P0's log levels to provide the detail that I need. Turning on logging is typically easy and a viable approach. However, large amounts of logging on a system close to production being tested under heavy load is undesirable. Therefore we took the approach to only log "actual" acquisition failures when we failed to checkout a connection from C3P0 when the total number of connections is less than the maxPoolSize. The NEW API that I added (pollHistoricalAquisitionEvents) provides that ability. The API adds the ability for an application to choose how and when to log a historical snapshot of database connection attempts. The main problem with the API that I suggested earlier (getLastAcquisitionFailure) is that it provides no context. It simply lacks the time the exception was thrown and how often it has been happening. So the core question: Is troubleshooting connection failures a C3P0 feature? Because right now C3P0 mostly hides connection failures. Getting to the root cause is hard. In other words, if any attempt to get a connection fails I want to know about the details (i.e. when and how often it has happened). If it's not a C3P0 feature, what is the best way to troubleshoot these kinds of problems and to make the root cause obvious in a live system? A common email on the user list is: why can't I get a connection from the pool? Most often the real problem isn't C3P0's fault, it's some driver mismatch or database problem. In cases where you cannot even get a database connection, getLastAquisitionFailure works great. For this purpose it has served us well and I would like to express my gratitude for providing the API in the current C3P0 distribution. I hope that this answers your questions. I appreciate that your stringent in making these changes. My interests are in making C3P0 a very easy, useful, powerful and efficient package for managing a database connection pool. You're doing a great job in accomplishing this goal. In the end our problem was an invalid DNS entry for the database servers which often pointed us to nowhere (even though many connect attempts succeeded). Thanks, Troy On 2/9/07, Steve Waldman <swa...@mc...> wrote: > > Troy, > > This is a nice addition, but I'm not sure I'd add the API as is. > > To summarize (for other list members), Troy's patch keeps track of > c3p0's attempts to acquire a Connection from a database, markin the > start and end times of the acquisition attempt, whether the attempt > succeeded, and the Throwable associated with any failures. It would > add a method to poolable DataSources by which one could capture > information about recent acquisition attempts. > > This opens the door to a more general issue of which internal-ish > events should be tracked and/or exposed to clients for debugging/ > custom logging/whatever. The most obvious candidates include > acquisitions, check-outs, check-ins, Connection tests, and Connection > destruction. Possible solutions include no exposure except via > logging (usually verbose at DEBUG levels and failure-only at INFO), > minimal pollable exposure (added as of 0.9.1, via getLastXXXFailure() > methods, including getLastAcquisitionFailure() proposed by Troy), > exposure via event-type-specific polling APIs (what Troy has now > proposed for acquisitions), or exposure via one or more publish/ > subscribe-style events. > > I'm kind of hoping the very minimal approach currently implemented > (make available the last failure, and hope it's representative) will > be good enough for most users. There's an advantage to a failure-only > approach, in that no extra overhead is added to ordinary, successful > operations. If users want more information, probably the right thing > to do would be to define an publish/subscribe event mechanism. The > advantage is that one homogenous API could cover most event types, > and clients are notified when relevant without having to poll, and we > avoid the memory-management issues associated with keeping internal > logs. (In the proposed implementation, consider what happens to long > running DataSources if users fail to poll. This is solvable with a > maximum event length, or by using SoftReferences, etc, but has to be > dealt with.) A publish and subscribe event mechanism has its own > issues -- the event notifications have to be handled by threads that > don't hold pool locks (to avoid deadlocks), and potentially should be > handled asynchronously to prevent a bad event handler from killing > pool performance (implying more thread overhead). c3p0's internal > resource pool has an asynchronous event generation mechanism > implemented, but it's remained hidden and disused to avoid the extra > complexity. > > At this stage, I think the main the to figure out what information > c3p0's users really want or need, to decide what trade-offs in > performance, threading complexity, or API complexity are worth > making. Troy, what motivated you to want this detailed logging? Were > there Connection acquisition problems you couldn't resolve by > checking representative last failures? Do others need any information > not currently exposed by c3p0, that should be exposed either via a > polling or publish/subscribe API? > > Anyway, many thanks to Troy for the proposal and the patch. Troy and > others, what information would be useful to have that c3p0 does not > currently expose? > > smiles, > Steve > > > On Feb 8, 2007, at 11:03 AM, Troy Whittier wrote: > > > Resending so that message is under 40kb > > > > On 2/2/07, Troy Whittier <whi...@gm...> wrote: > > C3P0 users, > > > > Recently I submitted a change to get the last acquisition failure > > that C3P0 encountered while acquiring a new database connection. > > Since then I have discovered a fundamental flaw in using this to > > diagnose problems in a system that has been up for an extended > > period of time. It lacks details of when the failure occurred and > > how often it is happening. > > > > In order to resolve this issue I have attached a cvs diff > > consisting of changes that I have made to c3p0. The changes > > consist of collecting useful information around acquiring > > connections and providing an API to poll a history of each c3p0 > > acquire attempt. > > > > At the end of the email I including a extract of a log file > > produced from our application illustrating a problem that these > > changes helped identity. In our project, these changes have > > already proven to be useful in diagnosing and monitoring database > > connection problems. > > > > Steve, if possible could you deprecate or remove > > "getLastAcquisitionFailure" and instead include the attached changes? > > > > > > Thanks, > > > > Troy Whittier > > > > > > > > AILED in acquiring resource: java.sql.SQLException: Io exception: > > The Network Adapter could not establish the connection, start time: > > Wed Jan 31 15:09:18 MST 2007, elapsed time: 5000ms > > FAILED in acquiring resource: java.sql.SQLException: Io exception: > > The Network Adapter could not establish the connection, start time: > > Wed Jan 31 15:09:23 MST 2007, elapsed time: 4002ms > > FAILED in acquiring resource: java.sql.SQLException: Io exception: > > The Network Adapter could not establish the connection, start time: > > Wed Jan 31 15:09:23 MST 2007, elapsed time: 4008ms > > SUCCEEDED in acquiring resource, start time: Wed Jan 31 15:09:29 > > MST 2007, elapsed time: 310ms > > SUCCEEDED in acquiring resource, start time: Wed Jan 31 15:09:29 > > MST 2007, elapsed time: 314ms > > FAILED in acquiring resource: java.sql.SQLException: Io exception: > > The Network Adapter could not establish the connection, start time: > > Wed Jan 31 15:09:23 MST 2007, elapsed time: 7994ms > > FAILED in acquiring resource: java.sql.SQLException: Io exception: > > The Network Adapter could not establish the connection, start time: > > Wed Jan 31 15:09:29 MST 2007, elapsed time: 1946ms > > FAILED in acquiring resource: java.sql.SQLException: Io exception: > > The Network Adapter could not establish the connection, start time: > > Wed Jan 31 15:09:29 MST 2007, elapsed time: 1946ms > > FAILED in acquiring resource: java.sql.SQLException: Io exception: > > The Network Adapter could not establish the connection, start time: > > Wed Jan 31 15:09:27 MST 2007, elapsed time: 4004ms > > FAILED in acquiring resource: java.sql.SQLException: Io exception: > > The Network Adapter could not establish the connection, start time: > > Wed Jan 31 15:09:29 MST 2007, elapsed time: 2946ms > > FAILED in acquiring resource: java.sql.SQLException: Io exception: > > The Network Adapter could not establish the connection, start time: > > Wed Jan 31 15:09:29 MST 2007, elapsed time: 2601ms > > FAILED in acquiring resource: java.sql.SQLException: Io exception: > > The Network Adapter could not establish the connection, start time: > > Wed Jan 31 15:09:27 MST 2007, elapsed time: 4999ms > > SUCCEEDED in acquiring resource, start time: Wed Jan 31 15:09:31 > > MST 2007, elapsed time: 1323ms > > > > <c3p0.diff> > > ---------------------------------------------------------------------- > > --- > > Using Tomcat but need to do more? Need to support web services, > > security? > > Get stuff done quickly with pre-integrated technology to make your > > job easier. > > Download IBM WebSphere Application Server v.1.0.1 based on Apache > > Geronimo > > http://sel.as-us.falkag.net/sel? > > cmd=lnk&kid=120709&bid=263057&dat=121642______________________________ > > _________________ > > c3p0-users mailing list > > c3p...@li... > > https://lists.sourceforge.net/lists/listinfo/c3p0-users > > |