On 1/25/22 1:44 AM, Alan F wrote:
Hi Chris,
Thankyou so much for your time and detail here. I had been working on this
yesterday and posted my findings below. In the end It turned out to be my lack
of understanding on Tomcat, but hey we are always learning!
I would just like to update on my discovery of the issue I had with Tomcat
resetting connections to DB. Main issue being lack of familiarity of parameters
and how connection pooling worked.
One thing I was unable to see without using a DBA admin was the connections
resetting, no level of logging in Tomcat seemed to reveal this, and in the end
I used a simple netstat query to check connections.
watch -n1 "netstat -ant | grep ':1521.*ESTABLISHED' | nl | tail -n20 (last
number depends on how many connections to monitor.)
Obvs local ports will change outgoing therefore highlighting a new connection
when these change.
Once I had this I was able to tinker with current settings until I discovered
the issue. Which in reality wasn't an issue per se its just that I discovered a
non live Tomcat was behaving differently with pooled connections over a live
host!
Just to Recap
Symptom:
Tomcat closing DBCP pooled connections to Database every 60 seconds
Cause:
Tomcat was idle, therefore the configuration param was causing idle connections
to be reset the period specified below (60 secs)
timeBetweenEvictionRunsMillis=60000
Resolution:
The behaviour above is by design, a busy server will be less likely to trigger
this due to connections being used!
Are you sure you did not set minEvictableIdleTimeMillis somewhere? That
is the setting that determines how long a connection can sit idle in the
pool before it is eligible for eviction.
Phil
-----Original Message-----
From: Christopher Schultz <ch...@christopherschultz.net>
Sent: 24 January 2022 22:42
To: users@tomcat.apache.org
Subject: Re: Tomcat jdbc connections
Alan,
On 1/23/22 09:17, Alan F wrote:
Can I just follow up here what would be the next steps how would I go
about capturing the root cause of these very short connection times to
Oracle from Tomcat.
Honestly, I would want to know what query or queries are being run by these
short-lived connections. Something tells me that if you are able to audit those
queries, you'll immediately know what's going on.
SELECT * FROM query_details WHERE connection_id='deadbeef'
user | start_time | end_time | query
zabbix | 12:00:00 | 12:00:00 | SELECT COUNT(*) FROM some_queue
zabbix | 12:01:00 | 12:01:00 | SELECT COUNT(*) FROM some_queue
zabbix | 12:02:00 | 12:02:00 | SELECT COUNT(*) FROM some_queue
zabbix | 12:03:00 | 12:03:00 | SELECT COUNT(*) FROM some_queue
zabbix | 12:04:00 | 12:04:00 | SELECT COUNT(*) FROM some_queue
zabbix | 12:05:00 | 12:05:00 | SELECT COUNT(*) FROM some_queue
/me says "Oh, right. We have monitoring."
Would it be along the lines of Wireshark or TCP dump to see what's
occurring as I gather this won't be captured in tomcat logging via
Catalina.out? Or can it be.
I would only resort to reading TCP dumps if all else fails. Why?
1. TCP dumps are large and "expensive"
2. You are encrypting connections to your database... right?!
Knowing the nature of the queries will help. If you see no queries being executed, then
no TCP dump will help you because you won't be able to prove what component is making
those connection anyway (unless dbcp2 pushes some environmental information over to
Oracle whenever it makes a connection like "I'm connecting to Oracle on behalf of
application X on Tomcat, but I'm not going to execute any queries mmm'kay"). I don't
think dbcp does that kind of thing, so you'd have to crank-up the logging level on that
application and/or Tomcat instance to see what's happening with the connection pool.
Hope that helps,
-chris
-----Original Message-----
From: Phil Steitz <phil.ste...@gmail.com>
Sent: 21 January 2022 17:50
To: users@tomcat.apache.org
Subject: Re: Tomcat jdbc connections
On 1/21/22 9:28 AM, Alan F wrote:
Ok thanks Phil ok I checked other connections in the same host and see minIdle="2" and
initialSize="7"
Ive run a diff on this server.xml between our active prod hosts which shows
connections on Toad up for at least a day as to this idle server reconnecting
after minutes! And diff is identical apart from Cluster ips.
Most likely culprit is network or other issue causing validations to fail.
With testWhileIdle on, idle connections in the pool will be tested when visited
and closed if validation fails.
Phil
-----Original Message-----
From: Phil Steitz <phil.ste...@gmail.com>
Sent: 21 January 2022 16:10
To: users@tomcat.apache.org
Subject: Re: Tomcat jdbc connections
On 1/21/22 8:19 AM, Alan F wrote:
Thanks John,
Here is an example of a connection below I see
timeBetweenEvictionRunsMillis but not maxConnLifetimeMillis if the
server has no traffic does this mean
<Resource type="javax.sql.DataSource" auth="Container" name="XXXXXXXX"
timeBetweenEvictionRunsMillis="60000" testWhileIdle="true" testOnReturn="false"
testOnBorrow="true" removeAbandonedOnBorrow="true" maxWaitmillis="30000" defaultQueryTimeout="30"
validationQuery="select * from dual" maxTotal="10" maxIdle="5"
url="jdbc:oracle:thin:@XXXXXXX:XXXXX:XXXX" username="XXXXXXX"
password="XXXXXXXX" driverClassName="oracle.jdbc.OracleDriver" />
So above does that mean every 60 secs Eviction runs, does this mean a server
with no traffic evicts unused connections to DB?
One more note on this config. Since you have not specified minIIdle or
initialSize, the pool will not create connections until they are requested by
clients.
Phil
-----Original Message-----
From: john.e.gr...@wellsfargo.com.INVALID
<john.e.gr...@wellsfargo.com.INVALID>
Sent: 21 January 2022 14:50
To: users@tomcat.apache.org
Subject: RE: Tomcat jdbc connections
Alan,
-----Original Message-----
From: Alan F <shiva...@hotmail.com>
Sent: Friday, January 21, 2022 6:53 AM
To: Tomcat Users List <users@tomcat.apache.org>
Subject: RE: Tomcat jdbc connections
Hi Christopher
Thanks for your time here.
You mean like, a connection is made, no queries are executed, and
then the connection is terminated?
- ANSWER - DBAs are saying connections last a minute or so and are
replaced by a new set of connections 1 session each pool.
Presumably, you have an application running on Tomcat with a JDBC
connection configured. Are you using Tomcat's built-in pooling, or
is your application managing its own pooling/connections?
- ANSWER we are using dbcp2
Do you have any background tasks (in the JVM) that will run even
when there is no user activity? Cache-management? Lazy-writes?
- ANSWER I don't think so.
Are you using Tomcat's clustering? Are you using a database-backed
session management or any kind? -
- ANSWER YES clustered B node down, A node up this Tomcat node is
not live or receiving any traffic currently in test.
Are the connections definitely being made from the application
itself and/or Tomcat? Or do you just see those connections coming
from the IP where the application is running? - ANSWER im assuming
according to server.xml they are utlising these declared connections via Tomcat.
Do you have any background tasks (outside the JVM) that will run
even when there are no user actions? For example, some monitoring
system that pings the server to say "can you reach the database?" -
ANSWER NO
O
Could your pool be closing the connections after a set time? Look at
timeBetweenEvictionRunsMillis and maxConnLifetimeMillis here:
https://commons.apache.org/proper/commons-dbcp/configuration.html
B KKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKCB [
X ܚX KK[XZ[
\ \ ][ X ܚX P X ]
\X K ܙ B ܈Y][ۘ[ [X[ K[XZ[
\ \ Z[ X ]
\X K ܙ B
--------------------------------------------------------------------
- To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
