Chris,
Thanks. Lots to go through...
On 3/26/2019 9:00 AM, Christopher Schultz wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256
Richard,
On 3/25/19 14:15, Richard Huntrods wrote:
<sigh> It's time to update my application to use "real" (i.e.
current best practices) data connection pooling.
:)
My application is Java Servlets, no beans, no JSP. Database is
MySQL.
System etc. details: Ubuntu live server 18.04.2, built March 6,
2019.
MySQL - latest installed via 'apt-get install mysql-server' after
system build.
So... MariaDB, then? Or does Ubuntu still stock MySQL binaries?
Seems to be MySQL. See next...
OpenJVM - 11? - again, latest version installed via 'apt-get
install default-jdk' at same time.
Should be pretty easy to determine this:
$ java -version
I typed 'java -version' and this was the output:
openjdk version "10.0.2" 2018-07-17
OpenJDK Runtime Environment (build 10.0.2+13-Ubuntu-1ubuntu0.18.04.4)
OpenJDK 64-Bit Server VM (build 10.0.2+13-Ubuntu-1ubuntu0.18.04.4, mixed
mode)
I also typed 'mysql -version' and got this (still not sure if Ubuntu
uses MariaDB or MySQL by default):
mysql Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using EditLine wrapper
Tomcat 8.5.39 - just updated the same day it came out.
Sounds good so far.
This system has been running in production since the early 2001's.
OS has changed over the years from Sun Solaris 8.x to Solaris 10.x
and now to Ubuntu 18.04 (server). Java has been updated over the
years as well, as has Tomcat and MySQL. Through all that the system
works quite perfectly.
Except... there are occasional hangs that implicate the 'home
grown' data connection pool. I wrote this by hand (in Java) back
in 2001 because there was nothing much available back then. Since
it kept working, I didn't have the time/inclination to change over
the years.
You may find that your home-grown connection pool is actually okay,
but it's being used incorrectly by client code (which is also your
code). IF you have problems with the client code, the "real"
connection-pool can help you tolerate them, but it won't magically fix
them.
I had problems some years ago with one particular version of the
connector which had a memory leak (in the connector). I avoided that
version and have had no particular problems. Some years ago I did a
pretty exhaustive examination of my application using various metering
tools to see if I was creating memory leaks with my database code. I
found one (forgot to close the connection), fixed it and there weren't
any more.
I also encapsulate ALL my database access into a single "DBMS.java"
class which is used by all the servlets to access data. The DBMS class
calls the various pool creation and management classes as needed, so all
my data "stuff" is in one place (or set of classes).
That makes it simple but also makes it more complex as the "roll my own"
pool is quite integrated into the DBMS code. I'll have to simplify and
then do the testing as you suggest below.
But the latest connector (mysql-connector-java-8.0.15.jar, a.k.a.
"com.mysql.cj.jdbc.Driver" is giving me some hiccups. I thought
rather than trying to debug my own connection pool, it was time to
switch over to a proper "modern" supported connection pooling
system.
Which brings me to my question.
Would the community please weigh in on the BEST tutorials /
documents regarding creating a Tomcat/MySQL database connection
pool for Servlets (not JSP or beans) with some good code examples
and server.xml examples?
I've already done some extensive internet searches, but when you
are doing something for the first time it's hard to tell the
difference between "really really good" and "blogger who has not
really tried it".
You will want Tomcat to create the connection pool for you. Anything
else is a waste of time. Here's what happens:
Here we are in total agreement. I *want* Tomcat to manage the pool as I
suspect pool timeouts are the overall issue that I'm seeing. Basically,
after several hours of inactivity (the application isn't used a lot
these days), it just "loses" it's connection and then subsequent data
accesses generate exceptions as the connection is no longer present. It
does not happen when the system is being used and data accessed
regularly, only if the system sits idle for several hours.
So at least to me it's clearly an issue with the home-grown connection
pool "losing" touch with the database but not in a way that is evident
to my current code. I've resorted to "tricks" using cron and another
servlet to regularly access the database to keep the pool open, but I
figured a Tomcat managed pool would have more capability to handle such
things.
I could rewrite my own pool, but at this point I'd rather use Tomcat
pools as I can just offload that portion of my code to a community
resource, which I suspect is much better if only because of all the
eyeballs on the Tomcat code.
1. During application startup, Tomcat creates a javax.sql.DataSource
object and squirrels it away using a path in the JNDI space that you
get to specify the tail-end of.
2. When your application needs a connection, you grab the DataSource
from JNDI (it's like a singleton in-memory Map of URLs to objects) and
ask it for a java.sql.Connection object.
3. When you are done with the connection, you call Connection.close on
it and it goes back into the pool.
Yes, my code does all that now. I learned my lessons some years ago
chasing that one memory leak. :-)
So, fairly simple.
It's all fairly easy, though changing all your code to work with it
might not be. Here's basically what you need.
1. Tomcat configuration. This goes into META-INF/context.xml:
<Resource name="jdbc/myDS"
description="My database connection"
auth="Container"
type="javax.sql.DataSource"
maxActive="1"
maxIdle="1"
maxWait="10000"
url="jdbc:mysql://host/database"
username="scott"
password="tiger"
driverClassName="com.mysql.jdbc.Driver"
removeAbandoned="true"
removeAbandonedTimeout="30"
logAbandoned="true"
testOnBorrow="true"
validationQuery="/* ping */ SELECT 1"
initialSize="1"
maxTotal="1"
maxWaitMillis="10000"
removeAbandonedOnBorrow="true"
closeMethod="close"
/>
You can look-up the meanings of all of the above settings here:
https://commons.apache.org/proper/commons-dbcp/configuration.html
Awesome. Thanks!
Note that I have the above configured for "Development Mode" which
means that there is exactly 1 connection in the pool, period. If you
have bad code which fails to return the connection (leaks) or tries to
check out more than one connection from the pool (potential deadlock),
then your application will freeze in development.
In production, feel free to crank-up the maxActive, maxIdle, and
maxTotal settings.
2. Get a connection object in your code:
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
...
Connection conn = null;
try {
try
{
Context ctx = new InitialContext();
DataSource ds =
(DataSource)ctx.lookup("java:/comp/env/jdbc/myDS"); // NOTE: Suffix
matches <Resource name="[name]"
if(null == ds)
throw new ServiceException("Cannot obtain DataSource");
conn = ds.getConnection();
}
catch (NamingException ne)
{
// Error "Cannot obtain database connection"
}
catch (SQLException sqle)
{
// Error "Cannot obtain database connection"
}
// Your code goes here
} finally {
try { conn.close(); } catch (SQLException sqle)
{ /* whatever you want */ }
}
3. Actually, the Connection.close is up there in the example above.
I would highly recommend that you create a "getConnection" method
which handles the details of fetching the DataSource and Connection
and the various Exceptions that can be thrown in there. You don't want
all that garbage to get in the way of your "real code" in your
data-access methods.
Always remember to close the connection in a finally block. And your
Statement and ResultSets as well.
Hope that helps.
Yes, very much. Thanks a lot. Much to digest and try now...
Cheers,
-Richard
- -chris
-----BEGIN PGP SIGNATURE-----
Comment: Using GnuPG with Thunderbird - https://www.enigmail.net/
iQIzBAEBCAAdFiEEMmKgYcQvxMe7tcJcHPApP6U8pFgFAlyaTIkACgkQHPApP6U8
pFjpEw/7BeomASCkf1O86Wlx8mz0DxIB09yCQxVw7VhsR41/oB5UHomBO9fTMQRg
uOnLJjdy1SjSCPHUahB/AL+La9Vzx3+qmlfABCQmZ7SatQ7aocivUVzfI/ZYyAmO
9QcA3bdz+qHJdegD4t+jcqFM6lctjR0OE4CpO2Ik6OgizStk/oUOPDgoHvs25+Zd
zWOVMdXtomjZ/dmIYvj0NNVqksGF/RNAz0JOQN2T/AT97U677y4rfO+cdhR+urOA
aW4LmynPFdb2D+y3MCYgxerZkBQZluDtM3B2R0w1Hb0EL/7Ew8SKsBB1QA3CikeF
qQFqlSDOZzpRoua6pP+r94ZKWXvAGULQJFFw4tgyYJka2BLxKOFr1paIa/E3SEcW
io6OmLKn/m6iA7AI0G7peSdHPkc4byXWVEQAq9jN0boD3RKw6Sz1+HfM6mAxPEmh
BDbtg2/m5lwb4jO4xNF5ybxR5sU065Pc4Su5polJ/c8QuLLiCJMmpGM5UuhTpQBV
uUsl+cdsNud+ruoAu36au33YTIw3aImlEXXb3ZlntUpaGearXoV3vtJ5QmYVuKVm
dyCTAZALgNiPFYGOr7PGvZZ7jD2w0l+9trCfzZE0JwnydIi8yhX2kUWqjl6N2bou
C2iswKlBZp+wAwgSGa4+hAMLgUlpOjLiXvwLaE4idxWVjpAm7wY=
=/swK
-----END PGP SIGNATURE-----
---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
