Re: Tomcat jdbc connections
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=6 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 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 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 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 So above does that mean every 60 secs Eviction runs, does this mean a server with no
Re: Tomcat jdbc connections
Alan, On 1/25/22 03:44, 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=6 Resolution: The behaviour above is by design, a busy server will be less likely to trigger this due to connections being used! Hmm. I wouldn't expect any connections to be (re)established if those connections aren't being used. Maybe your application uses a bunch of connections on startup? -chris -Original Message- From: Christopher Schultz 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 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 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 So above does that mean every 60 secs Eviction runs, does this mean a server with no traffic evicts unused conne
RE: [External] Re: Tomcat jdbc connections
> 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. Have you explored Oracle listener / client tracing? Ref: https://docs.oracle.com/cd/E11882_01/network.112/e10835/sqlnet.htm#NETRF397
RE: Tomcat jdbc connections
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=6 Resolution: The behaviour above is by design, a busy server will be less likely to trigger this due to connections being used! -Original Message- From: Christopher Schultz 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 > 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 >> 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
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 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 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 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 Sent: 21 January 2022 14:50 To: users@tomcat.apache.org Subject: RE: Tomcat jdbc connections Alan, -Original Message- From: Alan F Sent: Friday, January 21, 2022 6:53 AM To: Tomcat Users List 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
RE: Tomcat jdbc connections
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. 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. -Original Message- From: Phil Steitz 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 > 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 >> >> > timeBetweenEvictionRunsMillis="6" testWhileIdle="true" >> testOnReturn="false" >> testOnBorrow="true" removeAbandonedOnBorrow="true" maxWaitmillis="3" >> defaultQueryTimeout="30" validationQuery="select * from dual" maxTotal="10" >> maxIdle="5" >> url="jdbc:oracle:thin:@XXX:X:" username="XXX" >> password="" 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 >> >> Sent: 21 January 2022 14:50 >> To: users@tomcat.apache.org >> Subject: RE: Tomcat jdbc connections >> >> Alan, >> >> >>> -Original Message- >>> From: Alan F >>> Sent: Friday, January 21, 2022 6:53 AM >>> To: Tomcat Users List >>> 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 >> timeBetweenEvict
RE: Tomcat jdbc connections
Thanks for your input Phil! Arghh will keep looking. -Original Message- From: Phil Steitz 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 > 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 >> >> > timeBetweenEvictionRunsMillis="6" testWhileIdle="true" >> testOnReturn="false" >> testOnBorrow="true" removeAbandonedOnBorrow="true" maxWaitmillis="3" >> defaultQueryTimeout="30" validationQuery="select * from dual" maxTotal="10" >> maxIdle="5" >> url="jdbc:oracle:thin:@XXX:X:" username="XXX" >> password="" 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 >> >> Sent: 21 January 2022 14:50 >> To: users@tomcat.apache.org >> Subject: RE: Tomcat jdbc connections >> >> Alan, >> >> >>> -Original Message- >>> From: Alan F >>> Sent: Friday, January 21, 2022 6:53 AM >>> To: Tomcat Users List >>> 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 CB >> [ 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
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
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
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
Sent: 21 January 2022 14:50
To: users@tomcat.apache.org
Subject: RE: Tomcat jdbc connections
Alan,
-Original Message-
From: Alan F
Sent: Friday, January 21, 2022 6:53 AM
To: Tomcat Users List
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 CB [
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
RE: Tomcat jdbc connections
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. -Original Message- From: Phil Steitz 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 > > timeBetweenEvictionRunsMillis="6" testWhileIdle="true" > testOnReturn="false" > testOnBorrow="true" removeAbandonedOnBorrow="true" maxWaitmillis="3" > defaultQueryTimeout="30" validationQuery="select * from dual" maxTotal="10" > maxIdle="5" > url="jdbc:oracle:thin:@XXX:X:" username="XXX" > password="" 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 > > Sent: 21 January 2022 14:50 > To: users@tomcat.apache.org > Subject: RE: Tomcat jdbc connections > > Alan, > > >> -Original Message- >> From: Alan F >> Sent: Friday, January 21, 2022 6:53 AM >> To: Tomcat Users List >> 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 CB > [ 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
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 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 Sent: 21 January 2022 14:50 To: users@tomcat.apache.org Subject: RE: Tomcat jdbc connections Alan, -Original Message- From: Alan F Sent: Friday, January 21, 2022 6:53 AM To: Tomcat Users List 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 CB [ 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
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 So above does that mean every 60 secs Eviction runs, does this mean a server with no traffic evicts unused connections to DB? Not until they have been idle for minEvictableIdleTimeMillis ms. Since you have not specified this property, that will default to 30 mins (see [1]), so while the check will run every 60 secs, no connections should be removed for being idle too long until they have been idle in the pool for 30 mins. One thing that could cause connections to get closed by the pool under the config above is validation failures. Can your DBAs see the validation queries succeeding? Phil [1] https://commons.apache.org/proper/commons-dbcp/configuration.html -Original Message- From: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 Sent: Friday, January 21, 2022 6:53 AM To: Tomcat Users List 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 CB [ 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
RE: Tomcat jdbc connections
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 So above does that mean every 60 secs Eviction runs, does this mean a server with no traffic evicts unused connections to DB? -Original Message- From: 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 > Sent: Friday, January 21, 2022 6:53 AM > To: Tomcat Users List > 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 CB [ 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
RE: Tomcat jdbc connections
Alan, > -Original Message- > From: Alan F > Sent: Friday, January 21, 2022 6:53 AM > To: Tomcat Users List > 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
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 -Original Message- From: Christopher Schultz Sent: 20 January 2022 17:34 To: users@tomcat.apache.org Subject: Re: Tomcat jdbc connections Alan, On 1/20/22 09:33, Alan F wrote: > I have an issue with connections on Tomcat9 Oracle showing connections > made for about 2seconds then dropped again. Is this normal when the > server is not being used? You mean like, a connection is made, no queries are executed, and then the connection is terminated? 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? Do you have any background tasks (in the JVM) that will run even when there is no user activity? Cache-management? Lazy-writes? Are you using Tomcat's clustering? Are you using a database-backed session management or any kind? 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? 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?" -chris - 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
Re: Tomcat jdbc connections
Alan, On 1/20/22 09:33, Alan F wrote: I have an issue with connections on Tomcat9 Oracle showing connections made for about 2seconds then dropped again. Is this normal when the server is not being used? You mean like, a connection is made, no queries are executed, and then the connection is terminated? 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? Do you have any background tasks (in the JVM) that will run even when there is no user activity? Cache-management? Lazy-writes? Are you using Tomcat's clustering? Are you using a database-backed session management or any kind? 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? 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?" -chris - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: Tomcat jdbc connections
On 20/01/2022 14:33, Alan F wrote: I have an issue with connections on Tomcat9 Oracle showing connections made for about 2seconds then dropped again. Is this normal when the server is not being used? Seems unlikely. Can you provide the DataSource configuration? Remember to obfuscate any sensitive data. Mark - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Tomcat jdbc connections
I have an issue with connections on Tomcat9 Oracle showing connections made for about 2seconds then dropped again. Is this normal when the server is not being used? - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
