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
