Tomcat 9 / MySQL 5.6 connection pool trouble

[JohnBoy Amato]

Hi all - I am having quite a lot of trouble with our Tomcat 9 connection pool to our MySQL 5.6.36 database.  For reasons I can't explain, it will at random times start spewing the dreaded "com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Too many connections" exceptions.

I've read a LOT about this from the various and usual internet haunts on the subject, and I've tried many of the suggested solutions.  But none of them are really working.

So I'm not even sure what's best to ask this group.  Is there anyone in this community with extensive experience with Tomcat's DBCP, and how to properly tune it for a fairly heavy-volume production server?  And/or I suppose it's possibe the problem is with MySQL, so I suppose I'm looking for expertise there as well.

Or if anyone could point me to a consulting group (not even sure if that's Ok here, apologies if it's not) that I could reach out to.

Thanks in advance!

[Tim Holloway]

Welcome to the Ranch.

First thing I'd check is to make sure you're not leaking Connections. That's a common cause of this sort of error. And no, you can't just discard them and wait for the garbage collector. GC may not get around to it in time.

Another thing to look at is how long you're holding onto the Connections. Ideally, you should grab one, do your work, and close the Connection as fast as you can. And incidentally, NEVER try to store a Connection in an HttpSession!

If you have a long-running process, you might want to look at making it run in batch mode rather than straight off a Servlet request. Also don't spawn threads to do database work off servlet requests. It not only violates JEE standards, it can crash other webapps or even Tomcat itself.

If you're pooling many more JDBC Connections than Http Connections, then something's awry. Most webapps will have many functions that don't need a Connection at all, and rarely should any function require more than one at a time.

[JohnBoy Amato]

Hi Tim - all very good advice, and among the the things I've read while trying to figure this out.

Couple questions:

1)  Spawning threads from servlets - I've read a lot about this, and although a lot (most) of the voices out there assert the same thing:  that you're not supposed to spawn your own (native Java) threads from a servlet.  But I've never read a convincing argument of *why*.  I've had some conversations with some Java developers, and also read in some places that it's actually fine, as long as you are careful and understand what you're doing.  It's all too easy to get into race conditions, etc. using threads.  But if you're spawning off a thread to simply fire off an email to someone without holding up the page response to them - is there really any harm in that?

2)  What's the best way to detect connection leaks?  I have removeAbandonedOnBorrow="true", removeAbandonedTimeout="60", and logAbandoned="true" set up in the connection pool.  At times, I've seen in the logs during/after a crash that it claimed a certain connection was not returned to the pool.  And it even gives you the class and line number.  When I go check these out, the code is very obviously doing the right thing.  So it makes me think that it's getting confused about something else, and reporting this erroneously?

Thanks for your insights...

[Tim Holloway]

Here's a reason why.

The J2EE and JEE specifications explicitly forbid that servlets or EJBs should spawn threads.

Why? Well, webapps are not programs. They don't consist of a main task (thread) that sits around and runs. Instead they're collections of servlets (including JSPs, which compile to become servlets) and the servlets are code, not executable threads.

When an HTTP(S) request comes in to a JEE webapp server, the server digests it and decodes the URL to determine which servlet within which webapp the request should call. The webapp server has a pool of execution threads. It pulls a free thread from the pool, uses that thread to call the servlet's service() method, and at that point user application code runs until  it returns from the service() method (which may in turn have called doGet(), doPost() or whatever. Returning from the service() method results in the execution thread being returned to the thread pool for re-use.

As with database connections, there are only a limited number of Threads in the service thread pool, so a servlet should do its job and return as soon as possible so that the thread can be returned to the pool and used for some other incoming Request - which may or may not be from the same user.

Now consider what happens if you spawn a child thread during a service() method call. If you don't terminate that child thread before you return from service(), then you've just handed a "dirty" thread to the pool. Since all assets in a pool should be interchangeable, but the thread you've been using has lint hanging off of it, the state of the thread being handed to the next requester is polluted. Not good. Contrariwise, if you hold off returning from the service() method until all child threads have terminated, you're starving other users because while waiting, your thread cannot be returned to the pool. Either of which means poor performance and/or unpredictable results.

So by forbidding servlets to spawn threads you avoid those problems.

A servlet's init() method doesn't run under a pooled thread, so it can spawn children and in the past we sometimes did, but there are better places these days.


As far as detecting orphan connections, what happens is that the pool manager constructs an Exception as part of the connection allocation process, but doesn't throw it. Instead it stores it next to the connection id along with a timeout timer. If the timer expires, this exception's stack is then printed out, which indicates what the calling path from the application to the pool manager was when the connection was allocated. Knowing where the connection was allocated often helps track down what happened to it afterwards. It's another reason not to hold onto connections for too long, though, since the more you do with a connection, the easier it is to lose track of where it ends up.

[JohnBoy Amato]

Yes.  I've read all those same explanations, and I truly appreciate the effort.  It just doesn't make any sense to me.  Maybe it's because I've done so much multi-threaded programming over the years; I'm too close to the "problem".

If you instead code for the email to be sent in the same thread as the servlet, then you're delaying that thread's return to the pool, which would gum things up just about as much as if you launched it in a separate thread.  It's the same amount of work whether you make the user/browser wait for it or not.  As long as Tomcat's thread pool is properly managed (and so is the JVM's and the OS's), then it really should just work without goofing anything up.  Unless of course the developer does something stupid, but that can happen in single-threaded programming too.

But without diving into a deep discussion on threading (which probably ought to be in another topic), what alternative do you recommend?  What I've read is to use the ExecutorService to run things asynchronously.  And I have implemented that as a test and it does seem to achieve the same thing as a native Java thread.  Have you any experience with that?

[Tim Holloway]

Actually, the one that would spring to mind for many people is JDBC database requests. JDBC operates synchronously and it requires a request/response cycle of its own to do its work. So the application thread has to go on a wait for the response.

The key is that these operations do not spawn threads under the servlet's thread and they take a short enough amount of time to be tolerable. A longer-running database operation, in particular would violate the allowable latency time and warrant some sort of off-line or out-of-band processing.

The Executor service is new and I'm not totally informed about it, but yes, its effect looks like a child thread. But it isn't. It was developed because very often webapps with long-running services would spawn up one or more "machine" threads in the ServletContextListener, establish a queue of requests, and thereby hand off the long-running request to run safely in the background. Subsequent client requests could poll the machine for work status and thereby not have to hang waiting for results.

Which was a nuisance to implement, so Executor attempts to make a similar process that's built into modern JEE.

So yes, I recommend the Executor these days. Unlike brute-force thread spawning, it knows how to play nice with its framework.