Take a look inside commons-pool for the instrumentation (e.g., JMX). You can also track usage on borrow and other leaks.
Also, Tomcat uses DBCP as it is. On 1 March 2018 at 21:46, Shawn Heisey <apa...@elyograg.org> wrote: > On 3/1/2018 4:46 PM, Gary Gregory wrote: > >> I do not think this is a question I, or anyone here, can answer >> generically. I can read between that lines that you must feel frustrated >> and I certainly empathize with that. I think you might want to debug your >> application and come up with some parameters for us to start helping you. >> A >> reproducible example is always best but I understand it might be hard to >> provide in this particular case. >> > > There is a lot of frustration. Until today all of it was directed at our > developers, for creating programs and configs that make way too many > connections to the DB. > > But then today, I had that small eureka moment, thinking "wait a minute > ... how can this even be happening at all, if the connection pool has > connections that the DB server says are active and idle?" > > Reiterating something I said before: I know you can't help me with the > pools that the Tomcat servers are creating for our webapps. So I'll limit > the rest of the discussion to my own program, which uses DBCP, and has the > same problems. > > Please tell me what information you'd like me to provide. Anything that is > in my power, I will get it to you. > > This is how I set up DBCP in my code: > > /* > * Create a datasource (connection pool) for the master database server. > */ > ConnectionFactory cfMaster = new DriverManagerConnectionFactory(masterUrl, > dbUser, dbPass); > PoolableConnectionFactory pcfMaster = new > PoolableConnectionFactory(cfMaster, > null); > pcfMaster.setValidationQuery(validationQuery); > pcfMaster.setValidationQueryTimeout(Const.FIVE_SECONDS / 1000); > opMaster = new GenericObjectPool<>(pcfMaster); > opMaster.setMaxWaitMillis(Const.THIRTY_SECONDS); > opMaster.setMaxIdle(numShards); > opMaster.setMaxTotal(numShards * 5); > opMaster.setNumTestsPerEvictionRun(numShards * 5); > opMaster.setTimeBetweenEvictionRunsMillis(Const.FIVE_SECONDS); > opMaster.setMinEvictableIdleTimeMillis(Const.ONE_MINUTE * 5); > opMaster.setTestOnCreate(true); > opMaster.setTestOnBorrow(true); > opMaster.setTestOnReturn(true); > opMaster.setTestWhileIdle(true); > pcfMaster.setPool(opMaster); > dsMaster = new PoolingDataSource<>(opMaster); > > The JDBC driver we use is MySQL. As of a few weeks ago, it was the newest > stable version available, 5.1.something. Also at that time, I was using > the latest DBCP and POOL versions. If any new versions have come out very > recently, I probably don't have them yet. > > Typically the numShards value we're using is 6, to help with understanding > the code above. > > Observations: When the MySQL server has reached its connection limit, at > least one of the idle connections is from this program using DBCP. But > when the program attempts to use the DB, it gets the "Too many connections" > error response -- which means that it must be opening a brand new > connection, despite the fact that there SHOULD be at least one that is > ready and sitting in the pool. > > The code that uses the DB is basic JDBC code. It calls getConnection() on > the dataSource, verifies that the connection is valid, creates a statement, > executes it, and if it was a query, processes the resultset. Then it > closes any resultset, closes the statement, and closes the connection. As > I understand it, that close should return the connection to the pool, still > open, and ready for re-use. This all happens within a single thread. I > went through this code pretty closely for another issue on this mailing > list. It's possible that I missed something, but it looks very clean. > > I was going to add some debug logging to my code, but I can't see any way > with PoolingDataSource to get the number of active and idle connections, > just to make SURE that the pool really has what I think it does. > > I have a code change ready to switch everything to BasicDataSource and add > the debug logging. It's generally less verbose code, and looks to be just > as configurable as PoolingDataSource. Would that change be a good idea? > > > Thanks, > Shawn > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: user-unsubscr...@commons.apache.org > For additional commands, e-mail: user-h...@commons.apache.org > > -- Matt Sicker <boa...@gmail.com>
