[JAVA] Pursuing the mystery that the number of DB connections in Tomcat increases to only 8-A day of an OSS support engineer
** "I can only make 8 tomcat DB connections, why?" **
I had such a question and investigated the cause. It was a surprising punch line, so I will leave a record of that time here.
The context.xml I received with this question had the following definition:
<Resource name="jdbc/MyDB"
auth="Container"
type="javax.sql.DataSource"
driverClassName="com.mysql.jdbc.Driver"
factry="org.apache.tomcat.jdbc.pool.DataSourceFactory"
url="jdbc:mysql://dbserver.example.com:3306/db4app"
username="dbuser"
password="xxxxxxxxxx"
initialSize="20"
maxActive="20" />
When I started Tomcat, I thought that I could make 20 connections in the connection pool, but I could only make 8 connections. Certainly, with this setting, initialSize and maxActive are 20, so 20 connections will be created during startup and should not increase or decrease at 20 after that.
Organize the problem
When I received this question, the first question I wondered was, "How did the questioner check the number of connections?" It was unclear on what basis I decided that I could only do eight, so I decided to ask about it.
Then, the following command is issued to DB (MySQL) and confirmed.
mysql> show processlist;
+------+--------+---------------------------------+--------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+--------+---------------------------------+--------+---------+------+-------+------------------+
| 1142 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 1614 | dbuser | application.example.co.jp:37110 | db4app | Sleep | 746 | | NULL |
| 1615 | dbuser | application.example.co.jp:37111 | db4app | Sleep | 746 | | NULL |
| 1616 | dbuser | application.example.co.jp:37112 | db4app | Sleep | 746 | | NULL |
| 1617 | dbuser | application.example.co.jp:37113 | db4app | Sleep | 746 | | NULL |
| 1618 | dbuser | application.example.co.jp:37114 | db4app | Sleep | 746 | | NULL |
| 1619 | dbuser | application.example.co.jp:37115 | db4app | Sleep | 746 | | NULL |
| 1620 | dbuser | application.example.co.jp:37116 | db4app | Sleep | 746 | | NULL |
| 1621 | dbuser | application.example.co.jp:37117 | db4app | Sleep | 746 | | NULL |
+------+--------+---------------------------------+--------+---------+------+-------+------------------+
9 rows in set (0.00 sec)
The result of show processlist; contains as many records as there are connections from the client, so the confirmation method should be correct. It is obviously strange that there are only 8 records (*) that represent the connection from the application even though the initialSize is 20.
*: The above records are 9 records, but 8 records are excluded except for 1 record of the connection that executes show process list; . |
Then, the next possibility I came up with was that the configuration file I was looking at was different in the first place. But that doesn't seem to be the case. This is because if you set initialSize of context.xml to 1, the above command will return 1 record, if you set it to 5, it will return 5 records, and if you set it to 8, it will return 8 records. Because it returns. And, strangely, even if it is set to 9 or higher, it remains 8 cases and does not change.
--initialSize =" 1 ": Number of connections 1: large_blue_circle:
--initialSize =" 3 ": Number of connections 3: large_blue_circle :: large_blue_circle :: large_blue_circle:
--initialSize =" 5 ": Number of connections 5: large_blue_circle :: large_blue_circle :: large_blue_circle :: large_blue_circle :: large_blue_circle:
--initialSize =" 8 ": Number of connections 8: large_blue_circle :: large_blue_circle :: large_blue_circle :: large_blue_circle :: large_blue_circle :: large_blue_circle :: large_blue_circle :: large_blue_circle:
--initialSize =" 9 ": Number of connections 8: large_blue_circle :: large_blue_circle :: large_blue_circle :: large_blue_circle :: large_blue_circle :: large_blue_circle :: large_blue_circle :: large_blue_circle:
--initialSize =" 20 ": Number of connections 8: large_blue_circle :: large_blue_circle :: large_blue_circle :: large_blue_circle :: large_blue_circle :: large_blue_circle :: large_blue_circle :: large_blue_circle:
In other words, there is no doubt that the initialSize of context.xml is working, but something other than maxActive limits the creation of more than 8 connections.
try
Just in case, I decided to actually try it in the local environment. Since I already had a web application that has a definition for connecting to MySQL as JDBC in context.xml, I left the definition such as the connection URL (url) as it is and received the rest of context.xml. I modified it to be the same as (corrected initialSize and maxActive to 20, and deleted the remaining definitions such as minIdle). Then I started Tomcat and checked it. However, the event did not reproduce, and when I executed the command, 20 records were returned.
"Isn't that the number of connections that the client can handle on the MySQL server side, not Tomcat?"
If the MySQL server limits it, you should have the same problem with the MySQL command line client. So I asked him to start 9 clients at the same time and try connecting to the MySQL server. Then, unlike the case of Tomcat, the answer was that the command returned 9 records.
Certainly, if MySQL limits it with parameters such as max_connections, Tomcat should return an error and some information (message like Too many connections) should be output to the log. However, there were no messages in the log that seemed to be relevant.
"Neither Tomcat nor MySQL? Maybe there is a special mechanism in the OR mapper or app? Or is the firewall disconnected?"
I've been thinking about various possibilities, but I'm stuck with 8. So I decided to google with the keywords "" 8 connections "" initialSize "" show process list "". Then I found a question on StackOverflow for someone who was experiencing a similar phenomenon.
- initialSize == 1 - at Tomcat startup 1 connections are created
- initialSize == 5 - at Tomcat startup 5 connections are created
- initialSize >= 9 - at Tomcat startup 8 connections are created
** "That's exactly it!" ** I thought so, but there was one condition that didn't quite match this question.
That is, ** StackOverflow questioners use "Commons DBCP", while we use "Tomcat JDBC Connection Pool" **. There are two types of DB connection pools in Tomcat: the "Commons DBCP" used in older versions and the "Tomcat JDBC Connection Pool" used in newer versions.
As you can see by looking at the context.xml posted at the beginning, if you have the following definition,
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
Since "Tomcat JDBC Connection Pool" is used, unfortunately it is limited by the default of 8 because "maxTotal of" Commons DBCP "is not set in the answer on this page, so specify maxTotal`. The "white" measure should not be applicable in this case.
That said, the events were so similar that I couldn't get away from this Stack Overflow page right away. "If" Commons DBCP "is used, it makes sense ..."
Solve the mystery
I thought for a moment, "Maybe the questioner is using an older version?", But from the Tomcat log I received with context.xml, I'm sure that the version I'm using is 7.0.92. , There is no possibility.
2019/08/27 15:44:34 org.apache.catalina.startup.VersionLoggerListener log
information: Server version: Apache Tomcat/7.0.92
I knew from many @IT articles (below) that the "Tomcat JDBC Connection Pool" was introduced in version 7.
Is there any other possibility that "Commons DBCP" will be used?
"This one line should have been the only definition to switch between the two ..."
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
"And the received context.xml should have this definition ... "
factry="org.apache.tomcat.jdbc.pool.DataSourceFactory"
"Hmm ... Hmm ... Hmm? ...": scream:
In other words, typo ignores this definition, unintentionally uses "Commons DBCP", and the unspecified default value 8 of maxTotal works, so only 8 connections can be created. That's why. And while the setting of the maximum number of connections is different for "Commons DBCP" for maxTotal and" Tomcat JDBC Connection Pool "for maxActive, the setting for the initial number of connections is the same for initialSize. It means that it was producing a mysterious behavior.
If you think about it later, you may have noticed earlier that it is maxTotal if you get the attribute value of the connection pool with a JMX client (JConsole etc.). If you understand the cause, you might think that ...
Finally
Actually, the setting I wrote at the beginning of this page was also factry, but did anyone notice it? As is often the case, everyone, be careful with typo. In such a case, copy and paste from a highly accurate site (such as the official document of Tomcat) is sure.
Recommended Posts