[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.

quest.png

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.

Google

ggl.png

"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.

-What can you do with the new features in Tomcat 7? (Extra edition): What is Tomcat JDBC Pool that exceeds Commons DBCP (1/2)

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:

sol.png

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.

cci95logo.gif

Recommended Posts

Pursuing the mystery that the number of DB connections in Tomcat increases to only 8-A day of an OSS support engineer
Follow a mysterious event where the log level suddenly changes-A day of an OSS support engineer
A fix to prevent the increase in the number of DB connections in the custom authentication provider of the Cognos SDK sample
A program that counts the number of words in a List
A story that I realized that I had to study as an engineer in the first place
Creating an ArrayList that allows you to throw in and retrieve the coordinates of a two-dimensional plane
[Swift] How to get the number of elements in an array (super basic)
28th day of an engineer who will become a full-fledged person in 100 days
Count the number of occurrences of a string in Ruby
A program (Java) that outputs the sum of odd and even numbers in an array
Determine that the value is a multiple of 〇 in Ruby
How to change a string in an array to a number in Ruby
How to derive the last day of the month in Java
Find the number of days in a month with Kotlin
[Swift] When you want to know if the number of characters in a String matches a certain number ...
About the method to convert a character string to an integer / decimal number (cast data) in Java