You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Jerry Malcolm <te...@malcolms.com> on 2019/08/14 22:09:41 UTC
Tuning Datasource Connections
In my previous environment, I had a dedicated MySQL install where I
could set the max connections to pretty much anything I wanted. Now I'm
moving to AWS where more connections mean a higher charge. So it's time
to get real about how many connections I really need. BTW, TC 8.5.xx.
I 'thought' I understood defining a datasource. I thought that if I set
a maxTotal of 25 it sets a fixed ceiling and the datasource pool would
never request more than 25 connections from the database. But with the
following datasource definition, mySQL shows 48 connections created.,
and JConsole shows 48 connections for this datasource. In the same
MBean panel, it shows maxTotal=25 (??). My app gets a bunch of "Server
says Too Many Connections" exceptions.
What am I missing?
<Context reloadable="true" crossContext="true" >
<Resource name="jdbc/------"
auth="Container"
type="javax.sql.DataSource"
maxTotal="25"
maxIdle="5"
maxWaitMillis="10000"
removeAbandonedOnBorrow="true"
removeAbandonedTimeout="60"
logAbandoned="true"
username="----"
password="----------"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://---------------------.us-east-1.rds.amazonaws.com
/----------" />
</Context>
Re: Tuning Datasource Connections
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256
Jerry,
On 8/14/19 23:44, Jerry Malcolm wrote:
> I was able to use a global resource and put a <ResourceLink> in
> the default context file for each host.
That will work, but you will have to restart Tomcat to make any
changes to the <Resource>. It's not a great solution. The same was
true for using global context.xml as well. The best option is to put
everything in each application's private context.xml file if you can
pull it off.
Back to your math:
> However, I don't think I want a different datasource for every web
> app. Each webapp could need, say, 100 concurrent connections at
> any one instant. But if there are 10 web apps, if I set all 10 to
> have a max of 100 in order to handle the worst case scenario, then
> I'll need to have at least 1000 connections available on the
> database.
Your math is correct: even a small number of per-application
connections can add up to many total. But do you really want one
application to starve another of connections? You might have
per-client applications, and if one client uses lots of connections,
the other clients will suffer stalls. (Ignoring database performance
issues which are certainly present regardless of the details of
connection-allocation.)
Just remember that you will have to restart ALL of the applications in
the JVM in order to make a change to the global <Resource>. If you
specify the configuration in an app-private context.xml, you can
bounce the one application to get the new configuration.
> One last question... is there a way to determine the high-water
> mark for the most active connections at any one instant? And along
> with that, the max number of requests waiting at any one point if
> all connections are in use? If I have way over-allocated maxTotal,
> I'd like to know that. And likewise, if I've under-allocated
> maxTotal and often have a bunch of requests waiting, I'd like to
> know that as well. Is there a way to track that? I looked at the
> MBean data in JConsole, and nothing obvious jumped out at me.
Tomcat doesn't have anything built-in for this: you'll need some
external tools. Have a look at this one:
https://github.com/ChristopherSchultz/apache-tomcat-stuff/tree/master/bi
n/nagios
This is the tool I use to monitor various things through the jmxproxy.
It's a Perl script that connects and pulls various data. It can append
to a file if you use the "--append" argument with a filename. (I see
that it's not documented... time to fix that!). Like this:
jmxproxy --append /file/to/append
And you'll get a file that looks like this after a while:
2019-08-16 00:08:23 112
2019-08-16 00:13:23 113
2019-08-16 00:18:23 108
2019-08-16 00:23:23 114
2019-08-16 00:28:23 114
I really should fix that so it prints out in proper ISO-8601 date
format including 'T' and time zone indicator, but I haven't done it. :)
Anyhow, if you use that, you can observe your data over time, and
graphing the data will show high-water marks quite obviously.
Hope that helps,
- -chris
-----BEGIN PGP SIGNATURE-----
Comment: Using GnuPG with Thunderbird - https://www.enigmail.net/
iQIzBAEBCAAdFiEEMmKgYcQvxMe7tcJcHPApP6U8pFgFAl1WMbsACgkQHPApP6U8
pFhQxg/9Edk4PCFOeMliwHLLxaTel16b/YxWW533iy8sdbISq87pzNTJI0cUAJoE
jfZ+WXsCId5bZyDDXIDnlNDHgGeQk4zhfRM3exJZCoVgr9pkqyd2ruS4WserPc1f
UIMpWnm+ibLvrgzgI+bkzGU/tpwMnAyukf1wrVPPtJFoUpEH/9mGuqmEv0gDlBiE
byg7UyPMJZSjhSpJuOVh2opoqOrKYyT0rA/sy4i/C4Jo3CVDfqmGQlOF66ApXRJB
r7LCvTkYdS0fG04twt44tvd7QOEVbqnR7HAuHqUW+lZUhQ5nLB8bRoKLiwmTHTvO
3WfysxcLzVxfOIZvooVsp8dURiM9fYc/wY/APSkg8fxFcMPVRxxTjl0E/enjXXWj
6uhr7mtzRvcEnejXjxX85Xnq77BDHRYyMwPjVjpyRwrEUEShg4BYfLVRAoPVoFAQ
A2OqMJKvipOwPQ03WPz7TBW5FiO3nljyvJqoWGic7c0X5ROZuSoLQ/ywu/XX0sg4
iORSFHV2buHyNZr6qVOAg3ZYuqKJbYgkboeyl16JNYEVJMv0DVok3TgsxUHzF3CY
SmfTkEzwUGxTJ2POHwYd8CP/BgOm5+tnJOBi/8DoS9e5AC2dWue4SbZNSrvKJbnu
sgTfgF8kbt9TClYpukIlXXdkz9SSi1hfhDxDMcW4Jhuv5ge7Rok=
=JjYW
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Tuning Datasource Connections
Posted by Jerry Malcolm <te...@malcolms.com>.
On 8/14/2019 6:07 PM, Jerry Malcolm wrote:
>
> On 8/14/2019 5:39 PM, Mark Thomas wrote:
>> On 14/08/2019 23:35, Jerry Malcolm wrote:
>>> On 8/14/2019 5:26 PM, Mark Thomas wrote:
>>>> On 14/08/2019 23:09, Jerry Malcolm wrote:
>>>>> In my previous environment, I had a dedicated MySQL install where I
>>>>> could set the max connections to pretty much anything I wanted.
>>>>> Now I'm
>>>>> moving to AWS where more connections mean a higher charge. So
>>>>> it's time
>>>>> to get real about how many connections I really need. BTW, TC 8.5.xx.
>>>>>
>>>>> I 'thought' I understood defining a datasource. I thought that if
>>>>> I set
>>>>> a maxTotal of 25 it sets a fixed ceiling and the datasource pool
>>>>> would
>>>>> never request more than 25 connections from the database. But with
>>>>> the
>>>>> following datasource definition, mySQL shows 48 connections created.,
>>>>> and JConsole shows 48 connections for this datasource. In the same
>>>>> MBean panel, it shows maxTotal=25 (??). My app gets a bunch of
>>>>> "Server
>>>>> says Too Many Connections" exceptions.
>>>>>
>>>>> What am I missing?
>>>> What is the full path to:
>>>> - the file below
>>>> - CATALINA_BASE
>>>> - CATALINA_HOME
>>>> ?
>>>>
>>>> Mark
>>> CATALINA_BASE: /usr/share/tomcat8
>>> CATALINA_HOME: /usr/share/tomcat8
>>>
>>> /etc/tomcat8/Catalina/<hostname>/context.xml.default
>> That is the default context.xml file that gets applied to every web
>> application. I'm guessing you have two web applications so a maximum of
>> 2 * 25 connections - which is not what you want.
>>
>> If the context path is "/abcd" then you should use:
>> /etc/tomcat8/Catalina/<hostname>/abcd.xml
>>
>> (assuming /etc/tomcat8/ has been configured as the configBase).
>>
>> For the ROOT web application (context path "") the file would be:
>> /etc/tomcat8/Catalina/<hostname>/ROOT.xml
>>
>> Mark
>>
> Mark,
>
> Hmm, right... I do have it wrong. However, I don't think I want a
> different datasource for every web app. Each webapp could need, say,
> 100 concurrent connections at any one instant. But if there are 10
> web apps, if I set all 10 to have a max of 100 in order to handle the
> worst case scenario, then I'll need to have at least 1000 connections
> available on the database. Correct? But, in reality, I likely will
> never have more than 250 concurrent connections in use at any one time
> across all of the webapps. So as long as all webapps are accessing
> the same database, it seems it would make sense to have a single
> shared datasource with a max of 250 connections that are sharable by
> all of the webapps. It would save me paying for 750 more connections
> on the database than I really need.
>
> So, even though I agree I've got the config wrong currently, is there
> a way to set up a single datasource per hostname that can be shared by
> all webapps in that host with one global sharable maxTotal? I don't
> want to use global resources, since the same "jdbc/xyz" datasource
> name points to a different database per virtual host.
>
> Thanks for educating me.
>
> Jerry
>
I was able to use a global resource and put a <ResourceLink> in the
default context file for each host. I changed my code to extract the
host name from the request object and use that as the unique global
dataSource name for that host. It looks like I possibly could have
changed the name of the dataSource in the link, and used name="jdbc/xyz"
global="jdbc/<hostname>" so the code could always reference "xyz"
dataSource. But I was already into changing the datasource names to the
host names. It works, which is good enough for now.
One last question... is there a way to determine the high-water mark
for the most active connections at any one instant? And along with
that, the max number of requests waiting at any one point if all
connections are in use? If I have way over-allocated maxTotal, I'd like
to know that. And likewise, if I've under-allocated maxTotal and often
have a bunch of requests waiting, I'd like to know that as well. Is
there a way to track that? I looked at the MBean data in JConsole, and
nothing obvious jumped out at me.
Thanks.
Jerry
>>> This is running on an AWS Linux EC2.
>>>
>>>>> <Context reloadable="true" crossContext="true" >
>>>>> <Resource name="jdbc/------"
>>>>> auth="Container"
>>>>> type="javax.sql.DataSource"
>>>>> maxTotal="25"
>>>>> maxIdle="5"
>>>>> maxWaitMillis="10000"
>>>>> removeAbandonedOnBorrow="true"
>>>>> removeAbandonedTimeout="60"
>>>>> logAbandoned="true"
>>>>> username="----"
>>>>> password="----------"
>>>>> driverClassName="com.mysql.jdbc.Driver"
>>>>> url="jdbc:mysql://---------------------.us-east-1.rds.amazonaws.com
>>>>> /----------" />
>>>>> </Context>
>>>>>
>>>>>
>>>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Tuning Datasource Connections
Posted by Jerry Malcolm <te...@malcolms.com>.
On 8/14/2019 5:39 PM, Mark Thomas wrote:
> On 14/08/2019 23:35, Jerry Malcolm wrote:
>> On 8/14/2019 5:26 PM, Mark Thomas wrote:
>>> On 14/08/2019 23:09, Jerry Malcolm wrote:
>>>> In my previous environment, I had a dedicated MySQL install where I
>>>> could set the max connections to pretty much anything I wanted. Now I'm
>>>> moving to AWS where more connections mean a higher charge. So it's time
>>>> to get real about how many connections I really need. BTW, TC 8.5.xx.
>>>>
>>>> I 'thought' I understood defining a datasource. I thought that if I set
>>>> a maxTotal of 25 it sets a fixed ceiling and the datasource pool would
>>>> never request more than 25 connections from the database. But with the
>>>> following datasource definition, mySQL shows 48 connections created.,
>>>> and JConsole shows 48 connections for this datasource. In the same
>>>> MBean panel, it shows maxTotal=25 (??). My app gets a bunch of "Server
>>>> says Too Many Connections" exceptions.
>>>>
>>>> What am I missing?
>>> What is the full path to:
>>> - the file below
>>> - CATALINA_BASE
>>> - CATALINA_HOME
>>> ?
>>>
>>> Mark
>> CATALINA_BASE: /usr/share/tomcat8
>> CATALINA_HOME: /usr/share/tomcat8
>>
>> /etc/tomcat8/Catalina/<hostname>/context.xml.default
> That is the default context.xml file that gets applied to every web
> application. I'm guessing you have two web applications so a maximum of
> 2 * 25 connections - which is not what you want.
>
> If the context path is "/abcd" then you should use:
> /etc/tomcat8/Catalina/<hostname>/abcd.xml
>
> (assuming /etc/tomcat8/ has been configured as the configBase).
>
> For the ROOT web application (context path "") the file would be:
> /etc/tomcat8/Catalina/<hostname>/ROOT.xml
>
> Mark
>
Mark,
Hmm, right... I do have it wrong. However, I don't think I want a
different datasource for every web app. Each webapp could need, say,
100 concurrent connections at any one instant. But if there are 10 web
apps, if I set all 10 to have a max of 100 in order to handle the worst
case scenario, then I'll need to have at least 1000 connections
available on the database. Correct? But, in reality, I likely will
never have more than 250 concurrent connections in use at any one time
across all of the webapps. So as long as all webapps are accessing the
same database, it seems it would make sense to have a single shared
datasource with a max of 250 connections that are sharable by all of the
webapps. It would save me paying for 750 more connections on the
database than I really need.
So, even though I agree I've got the config wrong currently, is there a
way to set up a single datasource per hostname that can be shared by all
webapps in that host with one global sharable maxTotal? I don't want to
use global resources, since the same "jdbc/xyz" datasource name points
to a different database per virtual host.
Thanks for educating me.
Jerry
>> This is running on an AWS Linux EC2.
>>
>>>> <Context reloadable="true" crossContext="true" >
>>>> <Resource name="jdbc/------"
>>>> auth="Container"
>>>> type="javax.sql.DataSource"
>>>> maxTotal="25"
>>>> maxIdle="5"
>>>> maxWaitMillis="10000"
>>>> removeAbandonedOnBorrow="true"
>>>> removeAbandonedTimeout="60"
>>>> logAbandoned="true"
>>>> username="----"
>>>> password="----------"
>>>> driverClassName="com.mysql.jdbc.Driver"
>>>> url="jdbc:mysql://---------------------.us-east-1.rds.amazonaws.com
>>>> /----------" />
>>>> </Context>
>>>>
>>>>
>>>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Tuning Datasource Connections
Posted by Mark Thomas <ma...@apache.org>.
On 14/08/2019 23:35, Jerry Malcolm wrote:
> On 8/14/2019 5:26 PM, Mark Thomas wrote:
>> On 14/08/2019 23:09, Jerry Malcolm wrote:
>>> In my previous environment, I had a dedicated MySQL install where I
>>> could set the max connections to pretty much anything I wanted. Now I'm
>>> moving to AWS where more connections mean a higher charge. So it's time
>>> to get real about how many connections I really need. BTW, TC 8.5.xx.
>>>
>>> I 'thought' I understood defining a datasource. I thought that if I set
>>> a maxTotal of 25 it sets a fixed ceiling and the datasource pool would
>>> never request more than 25 connections from the database. But with the
>>> following datasource definition, mySQL shows 48 connections created.,
>>> and JConsole shows 48 connections for this datasource. In the same
>>> MBean panel, it shows maxTotal=25 (??). My app gets a bunch of "Server
>>> says Too Many Connections" exceptions.
>>>
>>> What am I missing?
>> What is the full path to:
>> - the file below
>> - CATALINA_BASE
>> - CATALINA_HOME
>> ?
>>
>> Mark
> CATALINA_BASE: /usr/share/tomcat8
> CATALINA_HOME: /usr/share/tomcat8
>
> /etc/tomcat8/Catalina/<hostname>/context.xml.default
That is the default context.xml file that gets applied to every web
application. I'm guessing you have two web applications so a maximum of
2 * 25 connections - which is not what you want.
If the context path is "/abcd" then you should use:
/etc/tomcat8/Catalina/<hostname>/abcd.xml
(assuming /etc/tomcat8/ has been configured as the configBase).
For the ROOT web application (context path "") the file would be:
/etc/tomcat8/Catalina/<hostname>/ROOT.xml
Mark
>
> This is running on an AWS Linux EC2.
>
>>
>>> <Context reloadable="true" crossContext="true" >
>>> <Resource name="jdbc/------"
>>> auth="Container"
>>> type="javax.sql.DataSource"
>>> maxTotal="25"
>>> maxIdle="5"
>>> maxWaitMillis="10000"
>>> removeAbandonedOnBorrow="true"
>>> removeAbandonedTimeout="60"
>>> logAbandoned="true"
>>> username="----"
>>> password="----------"
>>> driverClassName="com.mysql.jdbc.Driver"
>>> url="jdbc:mysql://---------------------.us-east-1.rds.amazonaws.com
>>> /----------" />
>>> </Context>
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
>> For additional commands, e-mail: users-help@tomcat.apache.org
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Tuning Datasource Connections
Posted by Jerry Malcolm <te...@malcolms.com>.
On 8/14/2019 5:26 PM, Mark Thomas wrote:
> On 14/08/2019 23:09, Jerry Malcolm wrote:
>> In my previous environment, I had a dedicated MySQL install where I
>> could set the max connections to pretty much anything I wanted. Now I'm
>> moving to AWS where more connections mean a higher charge. So it's time
>> to get real about how many connections I really need. BTW, TC 8.5.xx.
>>
>> I 'thought' I understood defining a datasource. I thought that if I set
>> a maxTotal of 25 it sets a fixed ceiling and the datasource pool would
>> never request more than 25 connections from the database. But with the
>> following datasource definition, mySQL shows 48 connections created.,
>> and JConsole shows 48 connections for this datasource. In the same
>> MBean panel, it shows maxTotal=25 (??). My app gets a bunch of "Server
>> says Too Many Connections" exceptions.
>>
>> What am I missing?
> What is the full path to:
> - the file below
> - CATALINA_BASE
> - CATALINA_HOME
> ?
>
> Mark
CATALINA_BASE: /usr/share/tomcat8
CATALINA_HOME: /usr/share/tomcat8
/etc/tomcat8/Catalina/<hostname>/context.xml.default
This is running on an AWS Linux EC2.
>
>> <Context reloadable="true" crossContext="true" >
>> <Resource name="jdbc/------"
>> auth="Container"
>> type="javax.sql.DataSource"
>> maxTotal="25"
>> maxIdle="5"
>> maxWaitMillis="10000"
>> removeAbandonedOnBorrow="true"
>> removeAbandonedTimeout="60"
>> logAbandoned="true"
>> username="----"
>> password="----------"
>> driverClassName="com.mysql.jdbc.Driver"
>> url="jdbc:mysql://---------------------.us-east-1.rds.amazonaws.com
>> /----------" />
>> </Context>
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Tuning Datasource Connections
Posted by Mark Thomas <ma...@apache.org>.
On 14/08/2019 23:09, Jerry Malcolm wrote:
> In my previous environment, I had a dedicated MySQL install where I
> could set the max connections to pretty much anything I wanted. Now I'm
> moving to AWS where more connections mean a higher charge. So it's time
> to get real about how many connections I really need. BTW, TC 8.5.xx.
>
> I 'thought' I understood defining a datasource. I thought that if I set
> a maxTotal of 25 it sets a fixed ceiling and the datasource pool would
> never request more than 25 connections from the database. But with the
> following datasource definition, mySQL shows 48 connections created.,
> and JConsole shows 48 connections for this datasource. In the same
> MBean panel, it shows maxTotal=25 (??). My app gets a bunch of "Server
> says Too Many Connections" exceptions.
>
> What am I missing?
What is the full path to:
- the file below
- CATALINA_BASE
- CATALINA_HOME
?
Mark
>
> <Context reloadable="true" crossContext="true" >
> <Resource name="jdbc/------"
> auth="Container"
> type="javax.sql.DataSource"
> maxTotal="25"
> maxIdle="5"
> maxWaitMillis="10000"
> removeAbandonedOnBorrow="true"
> removeAbandonedTimeout="60"
> logAbandoned="true"
> username="----"
> password="----------"
> driverClassName="com.mysql.jdbc.Driver"
> url="jdbc:mysql://---------------------.us-east-1.rds.amazonaws.com
> /----------" />
> </Context>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org