You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by Shawn Heisey <ap...@elyograg.org> on 2018/03/21 02:21:29 UTC
[DBCP] troubleshooting pool activity (tomcat version)
I've written before, trying to track down problems with our database
server getting too many connections.
Based on what I can see of how my programs (using dbcp2) are working,
everything seems to be fine there. I've added logging to tell me how
many idle and active connections there are in the pool, and those
numbers stay low.
So now I need to track down what's happening in our webapps, most of
which are Liferay-based, and all of which are running in Tomcat. And
I've learned that they're using the Tomcat implementation of dbcp for
database access. I think it's Tomcat version 7, but I will need to
check to make sure.
I've figured out how to log the number of active and idle connections,
by casting the DataSource to the tomcat dbcp object actually in use.
Once I can get the developers to actually update a production system
with that code, I will be able to see whether (as I suspect) the
"active" count in the pool is staying abnormally high. I'm betting that
there's somewhere in the webapp code that a connection is retrieved from
the pool, used for some work, and never closed.
I have a main question, and then a semi-related but very different question.
Main Question: Does dbcp by chance record a stacktrace of the code that
requests a connection from the pool? I would like to poke my way
through the active connections (entry point being the DataSource
implementation), and ask them where in our code they were requested. I
have to do this in the Tomcat fork of dbcp, and I know I'm not on a
Tomcat mailing list, but I'm hoping that whatever you can tell me will
apply to that version too. If I can get a stacktrace of where each
connection was requested, I can pinpoint problematic code a lot faster.
There is a LOT of code that uses the database, scattered across a lot of
git repositories. If I could grep the code easily to find them all, I
would.
Side question: Why has Tomcat maintained what looks like a fork of dbcp
for such a long time period? If they really believe their
implementation has an advantage, it seems like everyone would benefit if
they worked to get the upstream library to offer the same advantages.
Am I drifting into flamewar territory by even wondering about this? I
did find the tomcat documentation page about their connection pool. It
basically reads to me as "Commons DBCP is substandard and bloated.
These are the many ways that our implementation is better."
https://tomcat.apache.org/tomcat-9.0-doc/jdbc-pool.html
I haven't compared DBCP with other pool implementations, but my work
with DBCP has never run into any problems that weren't my own fault.
This mailing list has shown a high degree of patience with my dumb
questions.
Thanks,
Shawn
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [DBCP] troubleshooting pool activity (tomcat version)
Posted by Bernd Eckenfels <ec...@zusammenkunft.net>.
I think it is best you direct your tomcat pool related questions and comments about their documentation to the tomcat mailing lists. (On the other hand the tomcat documentation on their own pool including a description why they don't use dbcp 1.x looks rather comprehensive to me: https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html#Introduction)
Gruss
Bernd
--
http://bernd.eckenfels.net
________________________________
From: Shawn Heisey <ap...@elyograg.org>
Sent: Saturday, March 24, 2018 2:09:59 AM
To: user@commons.apache.org
Subject: Re: [DBCP] troubleshooting pool activity (tomcat version)
On 3/23/2018 5:19 PM, Phil Steitz wrote:
> That's the documentation for the alternative pool. Use this instead:
> https://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html#Database_Connection_Pool_(DBCP)_Configurations
This does not really explain how to configure Tomcat. It says "go see
the DBCP documentation for parameters you can use." I do see some
Resource configurationsfurther down on the page ... and they do not have
a "factory" attribute at all. Is that the difference?
The only resource I have is Tomcat documentation, and whatever nuggets
were *left out* of the Tomcat documentation that I might learn here. If
this were source code, I could get it all working. Especially using
DBCP2. But there's no code for me to modify -- I only have Resource
configurations in tomcat's context.xml file.
Since there doesn't appear to be any documentation for setting it up the
way you think I should be setting it up ... can you guide me to writing
a new configuration?
The config I'm planning has evolved a little bit since the last time I
shared it. Here's an example of its current state:
<Resource name="jdbc/REDACTED"
auth="Container"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
driverClassName="com.mysql.jdbc.Driver"
type="javax.sql.DataSource"
maxActive="100"
maxIdle="5"
minIdle="2"
maxAge="7200000"
removeAbandoned="true"
removeAbandonedTimeout="3600"
logAbandoned="true"
username="REDACTED"
password="REDACTED"
testOnBorrow="true"
testOnConnect="true"
testOnReturn="true"
testWhileIdle="true"
validationQuery="SELECT 1"
validationQueryTimeout="5"
validationInterval="5000"
minEvictableIdleTimeMillis="900000"
allowMultiQueries="false"
url="jdbc:mysql://REDACTED.REDACTED.com:3306/REDACTED?autoReconnect=true&zeroDateTimeBehavior=round"
/>
If you think I should be doing something different, please tell me
exactly what you think I should change, and tell me *why* you think each
of those changes is a good idea. And then maybe I can discuss
documentation deficiencies with the Tomcat community.
> Yes, that is a borrow.
> There is another, um, "feature" that I forgot to mention in ye olde
> DBCP 1.x. As documented here
> https://commons.apache.org/proper/commons-dbcp/api-1.4/org/apache/commons/dbcp/BasicDataSource.html#getRemoveAbandoned()
> removal will only happen with your config if there are 57+
> connections out.
That seems like a REALLY bad way to handle it! Hopefully that's changed
in the newest 1.x and 2.x versions!
Does this affect Tomcat too? How about the
"org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory" that we currently
have configured? I was planning to use abandonWhenPercentageFull="50",
which is specific to Tomcat. The property description looks like it
will fix that issue. I think I'll just leave it out, which the
documentation says will make connections ALWAYS eligible for removal
once they've reached the abandoned timeout.
Thanks,
Shawn
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [DBCP] troubleshooting pool activity (tomcat version)
Posted by Shawn Heisey <ap...@elyograg.org>.
On 3/23/2018 5:19 PM, Phil Steitz wrote:
> That's the documentation for the alternative pool. Use this instead:
> https://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html#Database_Connection_Pool_(DBCP)_Configurations
This does not really explain how to configure Tomcat. It says "go see
the DBCP documentation for parameters you can use." I do see some
Resource configurationsfurther down on the page ... and they do not have
a "factory" attribute at all. Is that the difference?
The only resource I have is Tomcat documentation, and whatever nuggets
were *left out* of the Tomcat documentation that I might learn here. If
this were source code, I could get it all working. Especially using
DBCP2. But there's no code for me to modify -- I only have Resource
configurations in tomcat's context.xml file.
Since there doesn't appear to be any documentation for setting it up the
way you think I should be setting it up ... can you guide me to writing
a new configuration?
The config I'm planning has evolved a little bit since the last time I
shared it. Here's an example of its current state:
<Resource name="jdbc/REDACTED"
auth="Container"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
driverClassName="com.mysql.jdbc.Driver"
type="javax.sql.DataSource"
maxActive="100"
maxIdle="5"
minIdle="2"
maxAge="7200000"
removeAbandoned="true"
removeAbandonedTimeout="3600"
logAbandoned="true"
username="REDACTED"
password="REDACTED"
testOnBorrow="true"
testOnConnect="true"
testOnReturn="true"
testWhileIdle="true"
validationQuery="SELECT 1"
validationQueryTimeout="5"
validationInterval="5000"
minEvictableIdleTimeMillis="900000"
allowMultiQueries="false"
url="jdbc:mysql://REDACTED.REDACTED.com:3306/REDACTED?autoReconnect=true&zeroDateTimeBehavior=round"
/>
If you think I should be doing something different, please tell me
exactly what you think I should change, and tell me *why* you think each
of those changes is a good idea. And then maybe I can discuss
documentation deficiencies with the Tomcat community.
> Yes, that is a borrow.
> There is another, um, "feature" that I forgot to mention in ye olde
> DBCP 1.x. As documented here
> https://commons.apache.org/proper/commons-dbcp/api-1.4/org/apache/commons/dbcp/BasicDataSource.html#getRemoveAbandoned()
> removal will only happen with your config if there are 57+
> connections out.
That seems like a REALLY bad way to handle it! Hopefully that's changed
in the newest 1.x and 2.x versions!
Does this affect Tomcat too? How about the
"org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory" that we currently
have configured? I was planning to use abandonWhenPercentageFull="50",
which is specific to Tomcat. The property description looks like it
will fix that issue. I think I'll just leave it out, which the
documentation says will make connections ALWAYS eligible for removal
once they've reached the abandoned timeout.
Thanks,
Shawn
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [DBCP] troubleshooting pool activity (tomcat version)
Posted by Phil Steitz <ph...@gmail.com>.
On 3/22/18 6:14 PM, Shawn Heisey wrote:
> First thing to do is thank you again for taking the time to help me.
> Apache has great communities.
>
> On 3/22/2018 5:38 PM, Phil Steitz wrote:
>> You must be looking at documentation describing how to use the
>> alternative pool mentioned above (tomcat-jdbc). The config you
>> posted is correct for DBCP.
> I'm looking at Tomcat documentation.
>
> https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html
That's the documentation for the alternative pool. Use this instead:
https://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html#Database_Connection_Pool_(DBCP)_Configurations
>
> The tomcat is the one included with Liferay 6.2. It is 7.0.42.
From here
http://svn.apache.org/repos/asf/tomcat/tc7.0.x/tags/TOMCAT_7_0_42/build.properties.default
You can see the bundled DBCP / Pool are 1.4/1.5.7 which are the
latest compatible versions.
>
>> Don't look at DBCP 2 code for troubleshooting the code you are
>> running. Either look at the repackaged sources inside the tomcat
>> source, or find the version in the tomcat build files and go to the
>> old DBCP / pool sources referenced there.
> I have figured that out. Felt pretty dumb when I realized I wasn't
> looking at code from the correct Tomcat version.
>
>> Of course, you *should* upgrade both TC and the DBCP it ships so you
>> *can* look at that (much better) code. See below for one reason why.
> I hear what you're saying, and don't disagree ... but this is not the
> kind of environment where I can just do an upgrade, even though
> upgrading might make it all better.
>
> We didn't download Tomcat -- we downloaded Liferay, which came with a
> specific version of Tomcat already included. Upgrading any significant
> component (liferay, tomcat, and others) runs the risk that when we
> restart the service, our web application won't work any more. For any
> upgrade, we have to spend a lot of resources trying the upgrade in a
> staging environment, so we can be sure that everything still works.
> Because that's very time-consuming, we tend to not do a lot of
> upgrading, at least of significant components, and our versions get
> REALLY old.
>
> This is also why I'm hesitant to move away from Tomcat's DBCP
> implementation to Commons DBCP (particularly version 2), even though
> that's exactly what I want to do. Switching to a different library
> might work seamlessly ... or it might completely break the application.
> Our customers get REALLY irritated when the websites we've built for
> them don't work!
Using DBCP2 with TC 7 won't work. You need TC 8+ for that.
>
>> One thing that could be going on is that in the old 1.x DBCP,
>> abandoned connection removal only happens when borrows are
>> attempted. So if you check out a lot of connections, abandon them
>> and don't ask for more, they won't get closed as abandoned until you
>> borrow a new one. In DBCP 2, the removeAbandoned property is split
>> into two different properties: removeAbandonedOnBorrow (the old
>> behavior) and removeAbandonedOnMaintenance. The second one makes
>> abandoned connection removal run on pool maintenance (so will not
>> have to wait until a borrow is attempted).
> I don't know if anyone needs me to actually back up and describe what's
> happening that led me down this rabbit hole, but that's what I'm going
> to do:
>
> The master MySQL server in our environment has a max connection limit
> configured at 600 connections.
>
> Every now and then, we start getting website failures, because all the
> connections are in use and the connection pools can't make any more
> connections. Looking at the connections on the MySQL side, the vast
> majority are idle (the command is "Sleep" on the server processlist),
> and have been idle for several hours.
>
> There are five main webservers and a handful of ancillary systems that
> also connect to the database. When the problem happens, the connection
> count from each webserver has gotten up near 100, and sometimes over
> 100. The surplus of connections are definitely the ones configured in
> Tomcat. Liferay has its own DB config for its own data (using c3p0 for
> pooling), and although I often see a higher number of connections to
> that database than I would excpect, I've never seen the idle time on
> those connections above one minute, so I'm not concerned about that
> pool, beyond some minor tweaks.
>
> The frequency of the connection-related failures has been increasing, so
> in response, I have set up monitoring that will send us an alarm when
> the server reaches 550 connections. This has allowed us to kill idle
> connections and prevent customer-visible problems a couple of times
> already, but we still have a fundamental issue to correct.
>
> I do not yet have any information that indicates whether Tomcat's DBCP
> thinks those connections are idle or active. I have reason to suspect
> that they are active, and have not been returned to the pool (closed).
> I've worked out a way with one of our developers to add logging that
> displays the active and idle connection counts, but it's not yet in
> production. If those connections were idle, as the MySQL server thinks
> they are, it really seems like DBCP would be choosing to re-use a
> connection that it's already got, instead of trying to create a brand
> new one and failing.
>
> So I am chasing abandoned connection removal. We have it configured,
> but it's not working. The config is lacking things I think it needs,
> but as far as I could tell, there is enough for abandoned connection
> removal to work. I suspect it's not working because I'm using a
> different factory than the documentation says I should be using ... or
> because the config we've got (which I inherited and did not create) is
> incorrect. I acknowledge that the problem might be a bug in
> tomcat-dbcp, one that upgrading might fix.
>
> The Resource configuration I shared most recently is what I'm *planning*
> to put in place. This is the config I inherited that we actually have
> in place now (slightly redacted):
>
> <Resource name="jdbc/REDACTED" auth="Container"
> factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
> driverClassName="com.mysql.jdbc.Driver" type="javax.sql.DataSource"
> maxActive="60" maxIdle="10" maxWait="30000" removeAbandoned="true"
> removeAbandonedTimeout="30" username="REDACTED" password="REDACTED"
> testOnBorrow="true" validationQuery="select 1"
> url="jdbc:mysql://encore.REDACTED.com:3306/REDACTED?autoReconnect=true&zeroDateTimeBehavior=round"
> />
>
> The removeAbandonedTimeout setting in that config is at 30 seconds. If
> this were actually working, I think we'd have a lot of very irritated
> customers, unable to run reports, which usually take a few minutes to
> get results.
>
> If abandoned connection removal only occurs at time of borrow with the
> older version, I don't think that's a problem. Accessing the pages that
> fail when the problem happens does require at least one database
> lookup. In the code, the database lookup starts with a
> "ds.getConnection()" call. That's a borrow, isn't it?
Yes, that is a borrow.
There is another, um, "feature" that I forgot to mention in ye olde
DBCP 1.x. As documented here
https://commons.apache.org/proper/commons-dbcp/api-1.4/org/apache/commons/dbcp/BasicDataSource.html#getRemoveAbandoned()
removal will only happen with your config if there are 57+
connections out.
Phil
>
> Thanks,
> Shawn
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [DBCP] troubleshooting pool activity (tomcat version)
Posted by Shawn Heisey <ap...@elyograg.org>.
First thing to do is thank you again for taking the time to help me.
Apache has great communities.
On 3/22/2018 5:38 PM, Phil Steitz wrote:
> You must be looking at documentation describing how to use the
> alternative pool mentioned above (tomcat-jdbc). The config you
> posted is correct for DBCP.
I'm looking at Tomcat documentation.
https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html
The tomcat is the one included with Liferay 6.2. It is 7.0.42.
> Don't look at DBCP 2 code for troubleshooting the code you are
> running. Either look at the repackaged sources inside the tomcat
> source, or find the version in the tomcat build files and go to the
> old DBCP / pool sources referenced there.
I have figured that out. Felt pretty dumb when I realized I wasn't
looking at code from the correct Tomcat version.
> Of course, you *should* upgrade both TC and the DBCP it ships so you
> *can* look at that (much better) code. See below for one reason why.
I hear what you're saying, and don't disagree ... but this is not the
kind of environment where I can just do an upgrade, even though
upgrading might make it all better.
We didn't download Tomcat -- we downloaded Liferay, which came with a
specific version of Tomcat already included. Upgrading any significant
component (liferay, tomcat, and others) runs the risk that when we
restart the service, our web application won't work any more. For any
upgrade, we have to spend a lot of resources trying the upgrade in a
staging environment, so we can be sure that everything still works.
Because that's very time-consuming, we tend to not do a lot of
upgrading, at least of significant components, and our versions get
REALLY old.
This is also why I'm hesitant to move away from Tomcat's DBCP
implementation to Commons DBCP (particularly version 2), even though
that's exactly what I want to do. Switching to a different library
might work seamlessly ... or it might completely break the application.
Our customers get REALLY irritated when the websites we've built for
them don't work!
> One thing that could be going on is that in the old 1.x DBCP,
> abandoned connection removal only happens when borrows are
> attempted. So if you check out a lot of connections, abandon them
> and don't ask for more, they won't get closed as abandoned until you
> borrow a new one. In DBCP 2, the removeAbandoned property is split
> into two different properties: removeAbandonedOnBorrow (the old
> behavior) and removeAbandonedOnMaintenance. The second one makes
> abandoned connection removal run on pool maintenance (so will not
> have to wait until a borrow is attempted).
I don't know if anyone needs me to actually back up and describe what's
happening that led me down this rabbit hole, but that's what I'm going
to do:
The master MySQL server in our environment has a max connection limit
configured at 600 connections.
Every now and then, we start getting website failures, because all the
connections are in use and the connection pools can't make any more
connections. Looking at the connections on the MySQL side, the vast
majority are idle (the command is "Sleep" on the server processlist),
and have been idle for several hours.
There are five main webservers and a handful of ancillary systems that
also connect to the database. When the problem happens, the connection
count from each webserver has gotten up near 100, and sometimes over
100. The surplus of connections are definitely the ones configured in
Tomcat. Liferay has its own DB config for its own data (using c3p0 for
pooling), and although I often see a higher number of connections to
that database than I would excpect, I've never seen the idle time on
those connections above one minute, so I'm not concerned about that
pool, beyond some minor tweaks.
The frequency of the connection-related failures has been increasing, so
in response, I have set up monitoring that will send us an alarm when
the server reaches 550 connections. This has allowed us to kill idle
connections and prevent customer-visible problems a couple of times
already, but we still have a fundamental issue to correct.
I do not yet have any information that indicates whether Tomcat's DBCP
thinks those connections are idle or active. I have reason to suspect
that they are active, and have not been returned to the pool (closed).
I've worked out a way with one of our developers to add logging that
displays the active and idle connection counts, but it's not yet in
production. If those connections were idle, as the MySQL server thinks
they are, it really seems like DBCP would be choosing to re-use a
connection that it's already got, instead of trying to create a brand
new one and failing.
So I am chasing abandoned connection removal. We have it configured,
but it's not working. The config is lacking things I think it needs,
but as far as I could tell, there is enough for abandoned connection
removal to work. I suspect it's not working because I'm using a
different factory than the documentation says I should be using ... or
because the config we've got (which I inherited and did not create) is
incorrect. I acknowledge that the problem might be a bug in
tomcat-dbcp, one that upgrading might fix.
The Resource configuration I shared most recently is what I'm *planning*
to put in place. This is the config I inherited that we actually have
in place now (slightly redacted):
<Resource name="jdbc/REDACTED" auth="Container"
factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
driverClassName="com.mysql.jdbc.Driver" type="javax.sql.DataSource"
maxActive="60" maxIdle="10" maxWait="30000" removeAbandoned="true"
removeAbandonedTimeout="30" username="REDACTED" password="REDACTED"
testOnBorrow="true" validationQuery="select 1"
url="jdbc:mysql://encore.REDACTED.com:3306/REDACTED?autoReconnect=true&zeroDateTimeBehavior=round"
/>
The removeAbandonedTimeout setting in that config is at 30 seconds. If
this were actually working, I think we'd have a lot of very irritated
customers, unable to run reports, which usually take a few minutes to
get results.
If abandoned connection removal only occurs at time of borrow with the
older version, I don't think that's a problem. Accessing the pages that
fail when the problem happens does require at least one database
lookup. In the code, the database lookup starts with a
"ds.getConnection()" call. That's a borrow, isn't it?
Thanks,
Shawn
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [DBCP] troubleshooting pool activity (tomcat version)
Posted by Phil Steitz <ph...@gmail.com>.
On 3/21/18 12:15 PM, Shawn Heisey wrote:
> On 3/21/2018 1:31 AM, Mark Thomas wrote:
>>> and that we need to
>>> change the factory on our pool definitions.
>> I believe not.
> Tomcat's documentation seems to disagree with this point. It
> specifically says to use org.apache.tomcat.jdbc.pool.DataSourceFactory,
> and doesn't list any other valid choices.
You must be looking at documentation describing how to use the
alternative pool mentioned above (tomcat-jdbc). The config you
posted is correct for DBCP.
> But we have
> org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory in our
> configuration. I've found questions from people whose Tomcat
> installations didn't even contain that class, where the answers said to
> switch to the factory in the documentation. Apparently deb-based
> packages of Tomcat do not contain the latter class.
>
> I found a historical document for 7.0.42 with Google and it also shows
> that the factory should be set to the DataSourceFactory, not the
> BasicDataSourceFactory that we have.
>
> I do see "removeAbandoned" in the property constants in the 7.0 source
> code for BasicDataSourceFactory I had thought that it wasn't there,
> but I realized that I was looking at trunk code when I came to that
> conclusion, and that BasicDataSourceFactory is in a dbcp2 package in
> trunk.
Don't look at DBCP 2 code for troubleshooting the code you are
running. Either look at the repackaged sources inside the tomcat
source, or find the version in the tomcat build files and go to the
old DBCP / pool sources referenced there.
Of course, you *should* upgrade both TC and the DBCP it ships so you
*can* look at that (much better) code. See below for one reason why.
> So I'm not sure what to think, but I can say that the abandoned
> connection handling does not appear to actually be working. So either
> my configuration is wrong, or the factory that we are using is ignoring
> part of the config.
One thing that could be going on is that in the old 1.x DBCP,
abandoned connection removal only happens when borrows are
attempted. So if you check out a lot of connections, abandon them
and don't ask for more, they won't get closed as abandoned until you
borrow a new one. In DBCP 2, the removeAbandoned property is split
into two different properties: removeAbandonedOnBorrow (the old
behavior) and removeAbandonedOnMaintenance. The second one makes
abandoned connection removal run on pool maintenance (so will not
have to wait until a borrow is attempted).
Phil
>
> Thanks,
> Shawn
>
> p.s. I sent an earlier draft of this message, but did so from the wrong
> email address. If the moderators decide to allow that message through,
> then you may see an almost-duplicate of this message.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [DBCP] troubleshooting pool activity (tomcat version)
Posted by Shawn Heisey <ap...@elyograg.org>.
On 3/21/2018 1:31 AM, Mark Thomas wrote:
>> and that we need to
>> change the factory on our pool definitions.
> I believe not.
Tomcat's documentation seems to disagree with this point. It
specifically says to use org.apache.tomcat.jdbc.pool.DataSourceFactory,
and doesn't list any other valid choices. But we have
org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory in our
configuration. I've found questions from people whose Tomcat
installations didn't even contain that class, where the answers said to
switch to the factory in the documentation. Apparently deb-based
packages of Tomcat do not contain the latter class.
I found a historical document for 7.0.42 with Google and it also shows
that the factory should be set to the DataSourceFactory, not the
BasicDataSourceFactory that we have.
I do see "removeAbandoned" in the property constants in the 7.0 source
code for BasicDataSourceFactory. I had thought that it wasn't there,
but I realized that I was looking at trunk code when I came to that
conclusion, and that BasicDataSourceFactory is in a dbcp2 package in
trunk. So I'm not sure what to think, but I can say that the abandoned
connection handling does not appear to actually be working. So either
my configuration is wrong, or the factory that we are using is ignoring
part of the config.
Thanks,
Shawn
p.s. I sent an earlier draft of this message, but did so from the wrong
email address. If the moderators decide to allow that message through,
then you may see an almost-duplicate of this message.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [DBCP] troubleshooting pool activity (tomcat version)
Posted by Mark Thomas <ma...@apache.org>.
On 21/03/18 04:49, Shawn Heisey wrote:
> On 3/20/2018 9:49 PM, Phil Steitz wrote:
>> First, find out what version of tomcat you are running. Then look
>> in the tomcat build file sources for the properties that define the
>> dbcp and pool versions being used. In TC 7, I am pretty sure the
>> repackaged sources were always from release tags. Ask again if you
>> have trouble locating the dbcp/pool versions once you know the TC
>> version.
>
> Tomcat on the system I'm looking at is 7.0.42, bundled with Liferay 6.2.
Tomcat 7 uses DBCP 1.x (and always will).
>> In DBCP 1.x (what TC 7 used), abandoned connection tracking was in
>> the AbandonedObjectPool bundled with DBCP. Tracking can be turned
>> on by configuration of BasicDataSource to remove abandoned
>> connections and to log them. When connections are considered
>> abandoned and closed, the stack trace of the code that created them
>> is logged. That requires that they actually go past the abandoned
>> connection timeout, though and get closed by the pool.
>
> In the pool definitions (which are in tomcat's context.xml),
> removeAbandoned is true, and removeAbandonedTimeout is set to 30. (this
> is really low!) But this does not appear to actually be happening. I
> see connections on the server (which look like they are from the
> configured pool) that have been idle for HOURS.
>
> Here's a relevant definition, slightly redacted:
>
> <Resource name="jdbc/REDACTED" auth="Container"
> factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
> driverClassName="com.mysql.jdbc.Driver" type="javax.sql.DataSource"
> maxActive="60" maxIdle="10" maxWait="30000" removeAbandoned="true"
> removeAbandonedTimeout="30" username="REDACTED" password="REDACTED"
> testOnBorrow="true" validationQuery="select 1"
> url="jdbc:mysql://encore.REDACTED.com:3306/REDACTED?autoReconnect=true&zeroDateTimeBehavior=round"
> />
>
> The factory is different from the recommended value in the 7.0 docs
> (which specifically say 7.0.85, I've got 7.0.42). Those docs say it
> should be org.apache.tomcat.jdbc.pool.DataSourceFactory. I wonder if
> this means that it's ignoring some of the pool configuration.
org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory is the default
factory and the correct factory to use for Tomcat's re-packaged version
of DBCP.
For the record, the code is an exact copy of the DBCP code (at the point
the copy was updated). Tomcat doesn't make any local changes.
Another reason for the repackaging is to prevent conflicts if an
application includes its own copy of Commons DBCP.
org.apache.tomcat.jdbc.pool.DataSourceFactory is the factory to use for
Tomcat's JDBC pool implementation.
> In a surface review of our application DB code, I have encountered a
> common theme: Connections are obtained, used, and closed, all within a
> try block. It is my understanding that closes should actually be done
> in a finally block (with null checks), to be absolutely certain that a
> close cannot be skipped, regardless of any exceptions that might occur.
> I suspect that this is the root of our troubles,
+1
> and that we need to
> change the factory on our pool definitions.
I believe not.
Mark
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [DBCP] troubleshooting pool activity (tomcat version)
Posted by Shawn Heisey <ap...@elyograg.org>.
On 3/20/2018 9:49 PM, Phil Steitz wrote:
> First, find out what version of tomcat you are running. Then look
> in the tomcat build file sources for the properties that define the
> dbcp and pool versions being used. In TC 7, I am pretty sure the
> repackaged sources were always from release tags. Ask again if you
> have trouble locating the dbcp/pool versions once you know the TC
> version.
Tomcat on the system I'm looking at is 7.0.42, bundled with Liferay 6.2.
> In DBCP 1.x (what TC 7 used), abandoned connection tracking was in
> the AbandonedObjectPool bundled with DBCP. Tracking can be turned
> on by configuration of BasicDataSource to remove abandoned
> connections and to log them. When connections are considered
> abandoned and closed, the stack trace of the code that created them
> is logged. That requires that they actually go past the abandoned
> connection timeout, though and get closed by the pool.
In the pool definitions (which are in tomcat's context.xml),
removeAbandoned is true, and removeAbandonedTimeout is set to 30. (this
is really low!) But this does not appear to actually be happening. I
see connections on the server (which look like they are from the
configured pool) that have been idle for HOURS.
Here's a relevant definition, slightly redacted:
<Resource name="jdbc/REDACTED" auth="Container"
factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
driverClassName="com.mysql.jdbc.Driver" type="javax.sql.DataSource"
maxActive="60" maxIdle="10" maxWait="30000" removeAbandoned="true"
removeAbandonedTimeout="30" username="REDACTED" password="REDACTED"
testOnBorrow="true" validationQuery="select 1"
url="jdbc:mysql://encore.REDACTED.com:3306/REDACTED?autoReconnect=true&zeroDateTimeBehavior=round"
/>
The factory is different from the recommended value in the 7.0 docs
(which specifically say 7.0.85, I've got 7.0.42). Those docs say it
should be org.apache.tomcat.jdbc.pool.DataSourceFactory. I wonder if
this means that it's ignoring some of the pool configuration.
In a surface review of our application DB code, I have encountered a
common theme: Connections are obtained, used, and closed, all within a
try block. It is my understanding that closes should actually be done
in a finally block (with null checks), to be absolutely certain that a
close cannot be skipped, regardless of any exceptions that might occur.
I suspect that this is the root of our troubles, and that we need to
change the factory on our pool definitions.
Thank you to both people who have replied to this thread so far.
Shawn
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [DBCP] troubleshooting pool activity (tomcat version)
Posted by Phil Steitz <ph...@gmail.com>.
On 3/20/18 7:21 PM, Shawn Heisey wrote:
> I've written before, trying to track down problems with our
> database server getting too many connections.
>
> Based on what I can see of how my programs (using dbcp2) are
> working, everything seems to be fine there. I've added logging to
> tell me how many idle and active connections there are in the
> pool, and those numbers stay low.
>
> So now I need to track down what's happening in our webapps, most
> of which are Liferay-based, and all of which are running in
> Tomcat. And I've learned that they're using the Tomcat
> implementation of dbcp for database access. I think it's Tomcat
> version 7, but I will need to check to make sure.
>
> I've figured out how to log the number of active and idle
> connections, by casting the DataSource to the tomcat dbcp object
> actually in use. Once I can get the developers to actually update
> a production system with that code, I will be able to see whether
> (as I suspect) the "active" count in the pool is staying
> abnormally high. I'm betting that there's somewhere in the webapp
> code that a connection is retrieved from the pool, used for some
> work, and never closed.
>
> I have a main question, and then a semi-related but very different
> question.
>
> Main Question: Does dbcp by chance record a stacktrace of the
> code that requests a connection from the pool? I would like to
> poke my way through the active connections (entry point being the
> DataSource implementation), and ask them where in our code they
> were requested. I have to do this in the Tomcat fork of dbcp, and
> I know I'm not on a Tomcat mailing list, but I'm hoping that
> whatever you can tell me will apply to that version too.
First, find out what version of tomcat you are running. Then look
in the tomcat build file sources for the properties that define the
dbcp and pool versions being used. In TC 7, I am pretty sure the
repackaged sources were always from release tags. Ask again if you
have trouble locating the dbcp/pool versions once you know the TC
version.
In DBCP 1.x (what TC 7 used), abandoned connection tracking was in
the AbandonedObjectPool bundled with DBCP. Tracking can be turned
on by configuration of BasicDataSource to remove abandoned
connections and to log them. When connections are considered
abandoned and closed, the stack trace of the code that created them
is logged. That requires that they actually go past the abandoned
connection timeout, though and get closed by the pool.
> If I can get a stacktrace of where each connection was requested,
> I can pinpoint problematic code a lot faster. There is a LOT of
> code that uses the database, scattered across a lot of git
> repositories. If I could grep the code easily to find them all, I
> would.
>
> Side question: Why has Tomcat maintained what looks like a fork of
> dbcp for such a long time period? If they really believe their
> implementation has an advantage, it seems like everyone would
> benefit if they worked to get the upstream library to offer the
> same advantages. Am I drifting into flamewar territory by even
> wondering about this? I did find the tomcat documentation page
> about their connection pool. It basically reads to me as "Commons
> DBCP is substandard and bloated. These are the many ways that our
> implementation is better."
There are two different things going on here. The first is the
repackaged DBCP sources that tomcat ships as the default connection
pool. That is not really a fork - just repackaging. In the early
days of TC8, DBCP was not releasing fast enough, so IIRC a few TC
releases shipped pre-release DBCP sources, but there was never
really any forking going on. The second thing is the tomcat's
"jdbc-pool" module that is an alternative to DBCP. The content on
the web page you reference is obsolete and probably should be
fixed. It refers back to 1.x versions of DBCP that used 1.x
versions of pool. Those were in fact slow and buggy. The 2.x
versions of DBCP have comparable performance (slightly slower, but
not much) to jdbc-pool (mostly because pool 2.x is much faster).
Depending on which version of TC 7 you are running, jdbc-pool for
that version might be materially faster than DBCP version shipped
with it.
Phil
>
> https://tomcat.apache.org/tomcat-9.0-doc/jdbc-pool.html
>
> I haven't compared DBCP with other pool implementations, but my
> work with DBCP has never run into any problems that weren't my own
> fault. This mailing list has shown a high degree of patience with
> my dumb questions.
>
> Thanks,
> Shawn
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [DBCP] troubleshooting pool activity (tomcat version)
Posted by Matt Sicker <bo...@gmail.com>.
On Tue, Mar 20, 2018 at 21:21, Shawn Heisey <ap...@elyograg.org> wrote:
> Main Question: Does dbcp by chance record a stacktrace of the code that
> requests a connection from the pool? I would like to poke my way
> through the active connections (entry point being the DataSource
> implementation), and ask them where in our code they were requested.
Yes, that’s a flag you set in the data source. See for example
BasicDataSource: abandoned usage tracking.
I
> have to do this in the Tomcat fork of dbcp, and I know I'm not on a
> Tomcat mailing list, but I'm hoping that whatever you can tell me will
> apply to that version too. If I can get a stacktrace of where each
> connection was requested, I can pinpoint problematic code a lot faster.
> There is a LOT of code that uses the database, scattered across a lot of
> git repositories. If I could grep the code easily to find them all, I
> would.
IIRC, DBCP came from Tomcat in the first place. And they keep it synced
upstream.
>
> Side question: Why has Tomcat maintained what looks like a fork of dbcp
> for such a long time period? If they really believe their
> implementation has an advantage, it seems like everyone would benefit if
> they worked to get the upstream library to offer the same advantages.
> Am I drifting into flamewar territory by even wondering about this? I
> did find the tomcat documentation page about their connection pool. It
> basically reads to me as "Commons DBCP is substandard and bloated.
> These are the many ways that our implementation is better."
>
> https://tomcat.apache.org/tomcat-9.0-doc/jdbc-pool.html
>
> I haven't compared DBCP with other pool implementations, but my work
> with DBCP has never run into any problems that weren't my own fault.
> This mailing list has shown a high degree of patience with my dumb
> questions.
>
> Thanks,
> Shawn
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
>
> --
Matt Sicker <bo...@gmail.com>