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

[Shawn Heisey]

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=truezeroDateTimeBehavior=round" 
/>


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

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

Looking at Tomcat 7.0 jdbc documentation, the first thing that I notice 
is that the factory we have configured is not even mentioned in the 
documentation.


One person (who replied privately and not to commons-user) said that 
BOTH factory and type attribute values mentioned in the Tomcat 7.0 docs 
are completely wrong!  And another (who did reply via the list) said 
that the factory mentioned in the documentation is "the alternative 
pool" and that I should be using the one documented here instead.  But 
this page is REALLY lean on details of how to actually set up the resource:


https://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html#Database_Connection_Pool_(DBCP)_Configurations

=

So now, with the sordid past covered, I have three main questions:

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 

Re: Connection closed error and certificateVerification="required"

[Richard Tearle]

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.

-- 
Richard

-- 
This email is sent on behalf of Northgate Public Services (UK) Limited and 
its associated companies including Rave Technologies (India) Pvt Limited 
(together "Northgate Public Services") and is strictly confidential and 
intended solely for the addressee(s). 
If you are not the intended recipient of this email you must: (i) not 
disclose, copy or distribute its contents to any other person nor use its 
contents in any way or you may be acting unlawfully;  (ii) contact 
Northgate Public Services immediately on +44(0)1442 768445 quoting the name 
of the sender and the addressee then delete it from your system.
Northgate Public Services has taken reasonable precautions to ensure that 
no viruses are contained in this email, but does not accept any 
responsibility once this email has been transmitted.  You should scan 
attachments (if any) for viruses.

Northgate Public Services (UK) Limited, registered in England and Wales 
under number 00968498 with a registered address of Peoplebuilding 2, 
Peoplebuilding Estate, Maylands Avenue, Hemel Hempstead, Hertfordshire, HP2 
4NW.  Rave Technologies (India) Pvt Limited, registered in India under 
number 117068 with a registered address of 2nd Floor, Ballard House, Adi 
Marzban Marg, Ballard Estate, Mumbai, Maharashtra, India, 41.

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

Re: mod_jk: sessions connecting although worker is oset to DIS

[Rainer Jung]

Am 23.03.2018 um 11:19 schrieb Martin Knoblauch:

Hi Rainer,

  so basically I took the Apache path and ended up with the following brute
force method:

RewriteCond "%{REQUEST_METHOD}" "GET"
RewriteRule ^/xxx/facelets/logon.xhtml$ - [E=login_jsid:%{HTTP_COOKIE}]
CustomLog "/opt/xxx/apache2/logs/login_log" xxxlogheader env=login_jsid
RequestHeader unset Cookie env=login_jsid

GET requests on the login page from the same client/browser now end up on
different nodes.

Looking at JK_STICKY_IGNORE, this seems also to work as well:


RewriteCond "%{REQUEST_METHOD}" "GET"
RewriteRule ^/xxx/facelets/logon.xhtml$ - [E=JK_STICKY_IGNORE]
CustomLog "/opt/xxx/apache2/logs/login_log" xxxlogheader
env=JK_STICKY_IGNORE

I like this actually better, as it does not need to mess with the Cookie.
This is better when components like SiteMinder (for SSO) are involved. I
will give that into our testing/integration environments to see whether we
have any bad side effects.

So thanks again for the valuable input. I will also have a look at the
filter/valve suggestions. But they look more intrusive and getting approval
[did I say big professional organisation somewhere :-)] for that might be
more difficult.


Thanks for the feedback, let us know if you run into surprises :)

As I understand Chris, he plans to backport the tomcat valve so that it 
will be a configurable standard part of all supported Tomcat versions. 
That at least would lower the future cost of getting it used in 
enterprise environments (from code addition down to config addition).


Regards,

Rainer

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

Re: mod_jk: sessions connecting although worker is oset to DIS

[Martin Knoblauch]

Hi Rainer,

 so basically I took the Apache path and ended up with the following brute
force method:

RewriteCond "%{REQUEST_METHOD}" "GET"
RewriteRule ^/xxx/facelets/logon.xhtml$ - [E=login_jsid:%{HTTP_COOKIE}]
CustomLog "/opt/xxx/apache2/logs/login_log" xxxlogheader env=login_jsid
RequestHeader unset Cookie env=login_jsid

GET requests on the login page from the same client/browser now end up on
different nodes.

Looking at JK_STICKY_IGNORE, this seems also to work as well:


RewriteCond "%{REQUEST_METHOD}" "GET"
RewriteRule ^/xxx/facelets/logon.xhtml$ - [E=JK_STICKY_IGNORE]
CustomLog "/opt/xxx/apache2/logs/login_log" xxxlogheader
env=JK_STICKY_IGNORE

I like this actually better, as it does not need to mess with the Cookie.
This is better when components like SiteMinder (for SSO) are involved. I
will give that into our testing/integration environments to see whether we
have any bad side effects.

So thanks again for the valuable input. I will also have a look at the
filter/valve suggestions. But they look more intrusive and getting approval
[did I say big professional organisation somewhere :-)] for that might be
more difficult.

Cheers
Martin

On Thu, Mar 22, 2018 at 3:07 PM, Rainer Jung 
wrote:

> Am 22.03.2018 um 11:58 schrieb Martin Knoblauch:
>
>> Hi,
>>
>>   we have this annoying problem that although Tomcat workers are set to
>> DIS
>> state in mod_jk, there are new connections being opened. This prevents us
>> from "idle down" Tomcats in a timely fashion. This hurts when a restart is
>> needed. While our empathy for human clients is pretty limited, we care a
>> lot about our automated workloads :-)
>>
>> Our setup: Apache 2.4.x frontend -> mod_jk(1.2.42) balancer, sticky
>> sessions -> a bunch of Tomcat 7 (yes I know. Oldie but Goldie :-) workers.
>>
>>   As far as I understand, the problem arises with HTTP(S) clients that
>> cache
>> the session cookies. If such a client tries to open a new connection to
>> the
>> backend, the old cookie passes to a DIS worker even if it is no longer
>> valid on the Tomcat side. Tomcat/Application then sees that the cookie is
>> not valid and doles out a new cookie which then allows the new connection
>> to be established.
>>
>>   My idea is now to delete existing session cookies for certain request
>> pattern on the Apache frontend. If it works, it should force mod_jk to a
>> new ACT Tomcat. But I am nor really fond of messing with the Apache
>> configuration, as the reason lies on the mod_jk/Tomcat side.
>>
>>   So if anybody has another suggestion I would love to hear it. Before I
>> forget, changing the application itself is not an option.
>>
>
> Either your idea, or - if those unwanted new request flows start with a
> specific URL, e.g. a login - mark those URLs specifically as not using the
> cookie. If you are using a uriworkermap.properties, look for
> "sticky_ignore" below http://tomcat.apache.org/conne
> ctors-doc/reference/uriworkermap.html#Rule%20extensions. If you are
> instead using JkMount style maps, look for JK_STICKY_IGNORE in
> http://tomcat.apache.org/connectors-doc/reference/apache.
> html#Advanced%20Environment%20Variables.
>
> Whenever you set the Apache environment variable JK_STICKY_IGNORE during a
> request execution, mod_jk will ignore the cookie and distribute like if it
> didn't see one. Such environment variables (different from Unix environment
> variables) can be set e.g. with mod_setenvif based on request properties
> and also with mod_rewrite as a [E=] side effect.
>
> But that would only help if you can describe request properties that alow
> you to decide - based on the request alone - whether the cookie needs to be
> ignored or not. You can use any request info including all headers for that
> decision. What is not possible, is to first check, whether the session is
> valid.
>
> You could also write a filter that checks the cookie for validity in
> Tomcat and if it is not valid, reply with a self-referential redirect
> including a Set-Cookie-Header that deletes the cookie. This filter should
> be written, such that it does not itself create a new session.
>
> HTH,
>
> Rainer
>
>
>
> -
> To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
> For additional commands, e-mail: users-h...@tomcat.apache.org
>
>


-- 
--
Martin Knoblauch
email: k n o b i AT knobisoft DOT de
www: http://www.knobisoft.de