Re: Trying to chase down "too many connection" problems with DB

[Shawn Heisey]

On 3/24/2018 5:04 PM, Mark Thomas wrote:

Regarding your configuration:


Generally, that looks OK but I'd strongly recommend that you use
"autoReconnect=false" in the URL. autoReconnect is known to be
problematic with connection pools.

The removeAbandonedTimeout looks low but if all the queries are expected
to be well under 30s then that should be OK.


Somehow I did not see this part of your email at all when I read it the 
first time.  I just noticed it.  My previous reply probably has you 
scratching your head a little bit.  I'm sorry about that!


The timeout of 30 seconds is EXTREMELY low.  And if it were being 
honored, we would have customers lining up outside the office with 
pitchforks.  The webapp has reporting ability for users with elevated 
privileges, and a lot of those reports take a minute or two to run, 
sometimes even longer.  So if the pool were killing connections after 30 
seconds, the reporting mechanism would be failing a LOT.  If you check 
the *planned* configuration, you'll see that I have increased this 
timeout to 3600.  I wanted to make it 900, but some of the developers 
are worried that 900 is too aggressive.


Because there are no pitchforks, I know that abandoned connection 
removal is NOT happening, even though it IS configured.  Plus, there's 
the fact that there are MANY connections with idle times of several 
hours.  So that's a big part of what I'm trying to figure out -- why the 
configuration we have isn't doing what it has configured.  Is there 
something incorrect, or something missing?  Or is it maybe a bug in the 
pool code that's been fixed sometime in the last five years?


The autoReconnect setting has likely been in use on our configurations 
for YEARS.  I will make sure we remove it in any config changes.


Thanks,
Shawn


-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: Trying to chase down "too many connection" problems with DB

[Shawn Heisey]

On 3/24/2018 5:04 PM, Mark Thomas wrote:

There are two pools available.



org.apache.tomcat.jdbc.pool.DataSourceFactory is a different pool
developed in the Tomcat project (generally called JDBC pool).


OK, so that means that the currently active config is using dbcp.  The 
*new* config that I'm building is using the tomcat pool, with the 
factory specified in the Tomcat JDBC pool documentation.  I do have at 
least one of the tomcat-specific configuration options in that config 
I'm building.  See the initial message on the thread for examples of 
both configurations.


Which directs me back to the second question I asked in the initial 
message:  Why does the application on the staging server appear to open 
a HUGE number of connections on startup when configured with the tomcat 
pool (which overloads the server and makes startup fail), and doesn't do 
that when configured with dbcp?  The number of connections that I am 
told were opened (I wasn't involved with the restart and can't confirm 
what they said) is far more than should have been possible.  The configs 
are fairly similar, but I have a lot more options on the new config, 
designed to try and keep the connection count down.


I should figure out which version of the MySQL driver we have.  I know 
for sure that there have been bugs in older releases and I have had 
trouble when not using the latest.


Thanks,
Shawn


-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: Connection closed error and certificateVerification="required"

[Mark Thomas]

On 23/03/18 15:00, Richard Tearle wrote:
> On 22 March 2018 at 23:06, Mark Thomas  wrote:
>> On 22/03/18 15:27, Richard Tearle wrote:
>>> On 22 March 2018 at 14:49, Mark Thomas  wrote:
>>
>> 
>>
>> OK. Time to think about this. NIO + JSSE works whereas NIO + OpenSSL
>> doesn't with the same configuration apart from the presence of the
>> native library.
>>
>> That points to something OpenSSL specific.
>>
>> Disabling client verification fixes the problem.
>>
>> So it looks to be something to do with how OpenSSL handles client
>> verification. It feels like configuration at this point rather than a
>> bug but it needs some more thought.
>>
>> There will probably be some configuration combinations to experiment
>> with but if they fail, something we can use to reproduce this is going
>> to be the next step.
>>
>> Mark
>>
> 
> That's fine and many thanks for your help - I can get quite a good turn around
> on testing various configuration changes. Anything that looks
> promising, I'll run
> for 8 hours, and we can usually get an inkling after an hour.

I've taken another look at the configuration options and
disableSessionTickets="true" is the only one that stands out as a
possibility.

I think we have reached the point where we need a reproducible test case.

Mark

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: Trying to chase down "too many connection" problems with DB

[Mark Thomas]

On 24/03/18 22:28, Shawn Heisey wrote:


> The factory we have now is
> "org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory".  The factory in
> the documentation, and what I put in the configuration I'm building, is
> "org.apache.tomcat.jdbc.pool.DataSourceFactory". These are both in the
> tomcat package space.  Yet you're telling me that I'm not using the
> Tomcat pool.  How is anybody supposed to come to that conclusion on
> their own?
> 
> If the attributes on that page cannot be used with
> "org.apache.tomcat.jdbc.pool.DataSourceFactory" ... then why are they
> included on a page that says to use that factory?

There are two pools available.

You use the factory to choose between them.

org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory is a package renamed
(to avoid conflicts) exact copy of Commons DBCP. For 7.0.42 that will be:
- Commons DBCP 1.4
- Commons Pool 1.5.7

org.apache.tomcat.jdbc.pool.DataSourceFactory is a different pool
developed in the Tomcat project (generally called JDBC pool).

The default factory if you do not specify one is
org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory


Which factory you want to use is up to you. Generally,

Commons DBCP 1.x was slow compared JDBC pool to in highly concurrent
environments. I suspect this isn't an issue for most users.

Commons DBCP 1.x is provides more features and enables more of them by
default.

As of Tomcat 8 DBCP 2.x is used which addresses the performance issues.

Generally, I'd recommend DBCP unless you need a feature that is only
available in JDBC pool.

> By the way, the 9.0 version of the docs still says virtually the same
> thing.  I found a historical document using Google for the 7.0.42
> version we're running, and it ALSO has virtually the same information on
> it.
> 
> Since I can't rely on the documentation, can somebody please give me a
> configuration that will do what I'm trying to do?  And explain each
> difference from the config that I built, or point me to documentation
> that's complete enough for me to figure it out on my own?

You need to make sure you are using the docs for the pool you want to
use. There are minor differences between Commons DBCP and JDBC pool and
also differences over time.

The source for the DBCP 1.4 docs is here:
https://github.com/apache/commons-dbcp/blob/DBCP_1_4/xdocs/configuration.xml

The source for JDBC pool docs as of Tomcat 7.0.42 is here:
https://svn.apache.org/viewvc/tomcat/tc7.0.x/tags/TOMCAT_7_0_42/modules/jdbc-pool/doc/jdbc-pool.xml?view=annotate


Regarding your configuration:


Generally, that looks OK but I'd strongly recommend that you use
"autoReconnect=false" in the URL. autoReconnect is known to be
problematic with connection pools.

The removeAbandonedTimeout looks low but if all the queries are expected
to be well under 30s then that should be OK.

Mark

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: Trying to chase down "too many connection" problems with DB

[Shawn Heisey]

On 3/24/2018 3:34 PM, Christopher Schultz wrote:

Before we go too far, you have said:

1. You have 5 prod servers
2. They have several pools defined
3. The above is an example of a defined pool

Just above, that configuration says maxActive=60. 5 * 60 = 300
connections. And that's just for one pool.

Perhaps you simply have too many total connections when you add-up all
the possible connections your servers can make to your db server?


I'm aware of that, and the pool sizes probably are too big, but based on 
what I see when I look during the problem, I don't think it's what I 
need to be worried about right now.  If everything were working the way 
I would expect, each server would only need maybe a dozen connections at 
any given moment for ALL the pools, because connections would be re-used 
and new ones would not be needed.


Here's a couple of SQL queries showing the current connection state on 
the main server right now (on Saturday, a very low traffic period):


https://paste.apache.org/yTNp

This is slightly redacted.  I did the redaction so that the columns 
weren't broken.  There are 414 connections right now, which you can see 
from the second query.  Only four of them are not in the Sleep state, 
which you can see from the first query. Two of those four are slave 
servers, one of them is the event scheduler daemon, and one of them is 
the query I'm running right at that moment.  Which means that NONE of 
the webservers was doing anything on the database when I ran those queries.


The two webservers running Liferay 6.2 are fourqueens and fitzgeralds.  
The three running Liferay 6.1 and the legacy application are fremont, 
frontier, and fiesta.  (Yes, they are casino names.  We inherited the 
system with that naming convention, and we have continued it.)


When we reach 600 connections, maybe 5 of the connections are actually 
doing something.  The rest are showing "Sleep", many with large idle 
times.  Which is why I think each server should have maybe a dozen total 
connections open.  Unless the code is opening connections and not 
closing them.  For that, I'm trying to get the developers to fix the 
code, and also configure abandoned connection removal, so the pool can 
clean up after bad development.


The databases with names that start with "lr" are liferay databases.  
Those connections are using c3p0.  There are more connections active 
than I think SHOULD be there, but they all have VERY low idle times, so 
they ARE getting re-used.  I have plans for that config separate from 
what we're discussing here.



This is the Tomcat documentation I'm using as a reference:

https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html

That's the wrong pool. You are using DBCP, and the docs are for
Tomcat's (non-DBCP) pool. The configurations are roughly the same for
simple configs. But make sure you are reading the right docs for the
right pool.


The factory we have now is 
"org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory".  The factory in 
the documentation, and what I put in the configuration I'm building, is 
"org.apache.tomcat.jdbc.pool.DataSourceFactory". These are both in the 
tomcat package space.  Yet you're telling me that I'm not using the 
Tomcat pool.  How is anybody supposed to come to that conclusion on 
their own?


If the attributes on that page cannot be used with 
"org.apache.tomcat.jdbc.pool.DataSourceFactory" ... then why are they 
included on a page that says to use that factory?


By the way, the 9.0 version of the docs still says virtually the same 
thing.  I found a historical document using Google for the 7.0.42 
version we're running, and it ALSO has virtually the same information on it.


Since I can't rely on the documentation, can somebody please give me a 
configuration that will do what I'm trying to do?  And explain each 
difference from the config that I built, or point me to documentation 
that's complete enough for me to figure it out on my own?


Thanks,
Shawn


-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: Tomcat stopped and Debug can't be done in Eclipse

[Konstantin Kolinko]

>
> Thanks Konstantin for giving me quite a clear detail on how to do the actual 
> debugging.
>
> The thing is that I am constantly getting 404 with certain occasion able to 
> get the tutorRegister page being displayed so I think there is still 
> something not quite right in the setting or something else I don't know.
>
> I'd like to check with you is it a good idea to have this line at the System 
> Variables(this is the Advanced System Setting at Windows 10) hard coded there 
> ?
>
> CATALINA_OPTS -Xdebug -Xrunjdwp:transport=dt_socket,address=8000, server=y, 
> suspend=n

That system variable is used only by catalina.bat,
to build up command line for java[w].exe process that runs Tomcat.

The variable has a meaning only when Tomcat is started by batch scripts:
running "startup.bat" or "catalina.bat start"

It is not used in any other situation.
It is not used when starting Tomcat from within Eclipse IDE.
It is not used when running Tomcat as a Windows service.


The system variables used by catalina.bat can be set globally,
but the recommended way is to create a file setenv.bat  - as
documented in RUNNING.txt.

Best regards,
Konstantin Kolinko

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: Trying to chase down "too many connection" problems with DB

[Christopher Schultz]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256

Shawn,

On 3/24/18 12:08 AM, Shawn Heisey wrote:
> This message is long.  Lots of details, a fair amount of history.

Thanks for the back-story and the configurations. It really helps
explain what is going on.

> Here's a redacted configuration that we have in place for one of
> the DB connection pools on the production servers:
> 
>  factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory" 
> driverClassName="com.mysql.jdbc.Driver"
> type="javax.sql.DataSource" maxActive="60" maxIdle="10"
> maxWait="3" removeAbandoned="true" removeAbandonedTimeout="30"
> username="REDACTED" password="REDACTED" testOnBorrow="true"
> validationQuery="select 1" 
> url="jdbc:mysql://REDACTED.REDACTED.com:3306/REDACTED?autoReconnect=tr
uezeroDateTimeBehavior=round"
>
> 
/>

Before we go too far, you have said:

1. You have 5 prod servers
2. They have several pools defined
3. The above is an example of a defined pool

Just above, that configuration says maxActive=60. 5 * 60 = 300
connections. And that's just for one pool.

Perhaps you simply have too many total connections when you add-up all
the possible connections your servers can make to your db server?

> This is the Tomcat documentation I'm using as a reference:
> 
> https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html

That's the wrong pool. You are using DBCP, and the docs are for
Tomcat's (non-DBCP) pool. The configurations are roughly the same for
simple configs. But make sure you are reading the right docs for the
right pool.

- -chris
-BEGIN PGP SIGNATURE-
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQJRBAEBCAA7FiEEMmKgYcQvxMe7tcJcHPApP6U8pFgFAlq2xHkdHGNocmlzQGNo
cmlzdG9waGVyc2NodWx0ei5uZXQACgkQHPApP6U8pFg8ChAAqwt7pUtJZtiuxbdp
aVwh9DliMYKBZeyD/X6LOQnXyorgaQYYbhfcQ0RWPRmsk/Bbnpxt5p9bLfrmvlrZ
g7wPALxMVLHW/ZSscY+f4yr2PyCb7RycTMlB3mz4YIzQUzfTUptW6ZAghrWfif+H
Oi9aV2kFZ7NbAEBCjmhQooMKvd958EGvTHBDEZlaJlgsK6m0RvHqfhVDZCLNzbFW
+20/uyjgRiuVwWPR1GBuxT6R7TktGeYCwkSqUxa+FSVIH22vjr61vJSw7+2QifYW
dlaPb0ORLPVxS8Kygq2iRKYU6vZzKMazX+Dnf7vXo6CJ4unqjhFgFBimVuNzq7wR
5ZiInmpRXQwofaJVPJ6JBw13u36Okb5+dm5m+NljhF0VNptWwQz9etr3zSZJrQQS
JE7QdkRcHd2WbZkoJA2zR15kXYJkdlZsOspcz3VVZOC7yy/WS1oDQ/i7BRDLPnXL
vaK3K+2ka+Sgg9JgXepiKtwxb7x/uF05I56lmTLSz6pIs5ZYT0jue4r5yshtt4cH
af8pyDf5dbr2OIelhIz9Bh/7Ugfkj+5WkoCKQ1GEmg+I6S/c7rady7+ckovIctwn
lL7Anugqdy5bbIpmWjNuYPM6Hwiqetxdu9dMO6PR4QtdiAtNpWqopydS7Og/GSh7
hMyaWAnPZf0GdRGRVGG6yqTwgYg=
=TU0a
-END PGP SIGNATURE-

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: Trying to chase down "too many connection" problems with DB

[Shawn Heisey]

On 3/24/2018 5:36 AM, Filippo Machi wrote:

Hello Shawn, about this question, are you sure that none of the webapps
running on those tomcats are connecting to the database without using the
pools configured in the context.xml? Creating other pools or performing
direct connections? That could explain while changing the configuration you
are not able to limit the number of connections neither to mitigate the
problem with removedAbandoned configuration.


That code isn't familiar to me.  I do have access to it, but there is a 
LOT of it.  I've looked over a little bit of it.


I cannot be ABSOLUTELY sure that this isn't happening, but I very much 
doubt that it is.  All of the developers know that there are connection 
pools provided in the servlet context, so I don't think they would try 
to handle it themselves.  I will ask, and hope they don't bite my head 
off TOO severely. :)


I have already risked death by telling the developers that they're 
closing JDBC resources incorrectly, which I suspect is the root cause of 
our troubles.  I will be pursuing a solution to this with them.


I will be attempting to lower the MySQL server-side wait_timeout from 
eight hours to one hour.  Which is one of those things that shouldn't be 
necessary, but might clean things up when the developer's don't.  I 
don't know if closing the connection on the server side will clean up 
all the objects in the application, or if there might be a resource 
leak.  If there is a leak, then we are already facing it, because 
connections ARE closed at eight hours idle by the server.  But I'd 
rather have that problem than the problem we currently have.


If there is anyone else with ideas, please reply to the original message!

Thanks,
Shawn


-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: Tomcat stopped and Debug can't be done in Eclipse

[Karen Goh]

On Sat, 3/17/18, Konstantin Kolinko  wrote:

 Subject: Re: Tomcat stopped and Debug can't be done in Eclipse
 To: "Tomcat Users List" 
 Date: Saturday, March 17, 2018, 10:25 PM
 
 /Hi!
 
 2018-03-17 10:11 GMT+03:00 Karen Goh :
 >
 > I have added this in
 my JVM under the tomcat argument for remote debug
 configuration :
 >
 >
 
-Dcatalina.opts="-agentlib:jdwp=transport=dt_socket,address=8000,server=y,suspend=n"
 
 The above line is wrong.
 
 There is no system property
 "catalina.opts".
 Those arguments
 are for java (java.exe, javaw.exe).
 
https://docs.oracle.com/javase/8/docs/technotes/tools/windows/java.html#BABDJJFI

Thanks Konstantin for giving me quite a clear detail on how to do the actual 
debugging.

The thing is that I am constantly getting 404 with certain occasion able to get 
the tutorRegister page being displayed so I think there is still something not 
quite right in the setting or something else I don't know.

I'd like to check with you is it a good idea to have this line at the System 
Variables(this is the Advanced System Setting at Windows 10) hard coded there ?

CATALINA_OPTS -Xdebug -Xrunjdwp:transport=dt_socket,address=8000, server=y, 
suspend=n


 
 
 
 Note that "Remote" debugging means
 that you do two steps:
 
 1.
 First, you start Tomcat as a normal "running"
 application.
 
 If you do it
 from within Eclipse, use "Run", not
 "Debug".
 
 See menu
 Run > Run Configurations...
 Add those
 options to its "Arguments" into "VM
 arguments" field (not
 "Program
 arguments")
 
 -agentlib:jdwp=transport=dt_socket,address=8000,server=y,suspend=n
 
 
 2. Then, you
 start "Debug" separately.
 
 See menu Run > Debug Configurations...
 
 Create "Remote Java
 Application".
 
 Fill in
 "Project" (your project),  "Port"
 (8000) fields and press
 "Debug" to
 start debugging.
 
 Eclipse
 will connect to Tomcat that has been started separately.
 
 
 > Below,
 it shows that Tomcat is started
 >
 > Mar 17, 2018 2:33:29 PM
 org.apache.tomcat.util.digester.SetPropertiesRule begin
 > WARNING:
 [SetPropertiesRule]{Server/Service/Engine/Host/Context}
 Setting property 'source' to
 'org.eclipse.jst.jee.server:Hi5S' did not find a
 matching property.
 > Mar 17, 2018 2:33:29
 PM org.apache.catalina.startup.VersionLoggerListener log
 > INFO: Server version:        Apache
 Tomcat/8.5.24
 > Mar 17, 2018 2:33:29 PM
 org.apache.catalina.startup.VersionLoggerListener log
 > INFO: Server built:          Nov 27
 2017 13:05:30 UTC
 > Mar 17, 2018 2:33:29
 PM org.apache.catalina.startup.VersionLoggerListener log
 > INFO: Server number:        
 8.5.24.0
 > Mar 17, 2018 2:33:29 PM
 org.apache.catalina.startup.VersionLoggerListener log
 > INFO: OS Name:              
 Windows 10
 > Mar 17, 2018 2:33:29 PM
 org.apache.catalina.startup.VersionLoggerListener log
 > INFO: OS Version:            10.0
 > Mar 17, 2018 2:33:29 PM
 org.apache.catalina.startup.VersionLoggerListener log
 > INFO: Architecture:          amd64
 > Mar 17, 2018 2:33:29 PM
 org.apache.catalina.startup.VersionLoggerListener log
 > INFO: Java Home:            
 C:\Program Files\Java\jre1.8.0_161
 > Mar
 17, 2018 2:33:29 PM
 org.apache.catalina.startup.VersionLoggerListener log
 > INFO: JVM Version:          
 1.8.0_161-b12
 > Mar 17, 2018 2:33:29 PM
 org.apache.catalina.startup.VersionLoggerListener log
 > INFO: JVM Vendor:            Oracle
 Corporation
 > Mar 17, 2018 2:33:29 PM
 org.apache.catalina.startup.VersionLoggerListener log
 > INFO: CATALINA_BASE:        
 
C:\Users\Karen.Goh\workspace\.metadata\.plugins\org.eclipse.wst.server.core\tmp2
 > Mar 17, 2018 2:33:29 PM
 org.apache.catalina.startup.VersionLoggerListener log
 > INFO: CATALINA_HOME:        
 C:\Program Files\Apache\apache-tomcat-8.5.24
 > Mar 17, 2018 2:33:29 PM
 org.apache.catalina.startup.VersionLoggerListener log
 > INFO: Command line argument:
 -agentlib:jdwp=transport=dt_socket,suspend=y,address=localhost:50906
 
 Note the above line.
 A command line argument, logged by
 VersionLoggerListener .
 
 > Mar 17, 2018 2:33:29 PM
 org.apache.catalina.startup.VersionLoggerListener log
 > INFO: Command line argument:
 
-Dcatalina.base=C:\Users\Karen.Goh\workspace\.metadata\.plugins\org.eclipse.wst.server.core\tmp2
 > Mar 17, 2018 2:33:29 PM
 org.apache.catalina.startup.VersionLoggerListener log
 > INFO: Command line argument:
 -Dcatalina.home=C:\Program
 Files\Apache\apache-tomcat-8.5.24
 > Mar
 17, 2018 2:33:29 PM
 org.apache.catalina.startup.VersionLoggerListener log
 > INFO: Command line argument:
 
-Dwtp.deploy=C:\Users\Karen.Goh\workspace\.metadata\.plugins\org.eclipse.wst.server.core\tmp2\wtpwebapps
 > Mar 17, 2018 2:33:29 PM
 org.apache.catalina.startup.VersionLoggerListener log
 > INFO: Command line argument:
 -Djava.endorsed.dirs=C:\Program
 Files\Apache\apache-tomcat-8.5.24\endorsed
 > Mar 17, 2018 2:33:29 PM
 org.apache.catalina.startup.VersionLoggerListener log
 

Re: Trying to chase down "too many connection" problems with DB

[Filippo Machi]

*1) I think this is the really burning question in my mind: Why is the
server opening NEW connections when there are dozens of them already open?
Does this mean that (as I suspect) all those connections are abandoned?  If
so, why are they not getting cleaned up, when we have removeAbandoned set
to true and removeAbandonedTimeout set to a RIDICULOUSLY low value of 30
seconds?  (We MUST increase this value.  The plan is one hour.)*
Hello Shawn, about this question, are you sure that none of the webapps
running on those tomcats are connecting to the database without using the
pools configured in the context.xml? Creating other pools or performing
direct connections? That could explain while changing the configuration you
are not able to limit the number of connections neither to mitigate the
problem with removedAbandoned configuration.
HTH
Filippo

On Sat, Mar 24, 2018 at 5:08 AM, Shawn Heisey  wrote:

> This message is long.  Lots of details, a fair amount of history.
>
> The primary Tomcat version we've got is 7.0.42. Specifically, it is the
> Tomcat that's included with Liferay 6.2. This is why we haven't attempted
> an upgrade even though the version we're running is five years old -- we
> don't want to rock the boat too hard and risk everything not working.
> Liferay is battle-tested with that version of Tomcat.
>
> Background:
>
> Every now and then, we find that our MySQL master has reached the
> connection limit of 600 connections, and websites stop working, because
> they are trying to open new connections and failing.  When I look at the DB
> server, it shows a HUGE number of connections (300 to 500 out of the 600
> max) that have idle times between one hour and eight hours.  The MySQL
> server-side idle timeout is at the default of 28800 seconds -- eight
> hours.  The idle connections are coming from the webservers.
>
> There are five main webservers, two of which run exclusively Liferay 6.2,
> and three that are running older apps on Tomcat version that's probably
> ANCIENT, as well as Liferay 6.1 with Tomcat 7.0.23.  I can't seem to figure
> out what version the really ancient one is.  There are no version numbers
> that I can see in the files in the program directory.  We also have a
> staging server, a dev server, and a few ancillary systems.  The staging and
> dev servers mainly use a dev/staging database server, but do need to
> connect to the main DB server for some things.
>
> When we run out of connections, each of these five webservers has nearly
> 100 (and sometimes MORE than 100) open connections to the database server.
> And the majority of them have been idle for a LONG time.
>
> A number of JNDI database resources are configured in conf/context.xml.
> So we have connection pooling.  But even though there are TONS of
> connections already established from Tomcat, and completely idle from the
> DB server's perspective, the application is STILL trying to open a NEW
> connection when somebody visits a page.  This is the precise issue that
> connection pooling SHOULD be preventing.
>
> I've discussed this with the commons-user mailing list, from a DBCP
> perspective.  I started there because there is some different code that I
> wrote, which doesn't run in Tomcat, and uses DBCP2 natively.  I'm a lot
> more familiar with my code than the code running in Tomcat.
>
> Based on my discussions with commons-user, I really think that the reason
> that the DB pools are trying to create more connections even though there's
> already a lot of them open is because the connections are actually
> abandoned.  I suspect they were never closed by the application, so the
> pool still has them as active, and thinks it can't use them.  Now that the
> discussion directly involves configuring pools using Tomcat's own DBCP
> implementation, they're not really able to help me any further.
>
> If we are dealing with abandoned connections as I suspect, then I need to
> ask why abandoned connection removal isn't actually working, and how to
> configure it so that it DOES work.
>
> We have figured out how to log the number of idle and active connections
> on the datasource, but it's going to take some time to get that logging
> into the production servers, so I don't have definitive proof that the
> connections are actually active.
>
> Here's a redacted configuration that we have in place for one of the DB
> connection pools on the production servers:
>
>  factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
> driverClassName="com.mysql.jdbc.Driver" type="javax.sql.DataSource"
> maxActive="60" maxIdle="10" maxWait="3" removeAbandoned="true"
> removeAbandonedTimeout="30" username="REDACTED" password="REDACTED"
> testOnBorrow="true" validationQuery="select 1" url="jdbc:mysql://
> REDACTED.REDACTED.com:3306/REDACTED?autoReconnect=truezeroDateTimeB
> ehavior=round" />
>
> This is the Tomcat documentation I'm using as a reference:
>
>