Shulou(Shulou.com)06/01 Report--

Long long ago，there was a story about the Arguments between DBAs and the Developers...

This story does not have all kinds of happy waves of prince and princess in fairy tales. Then follow me and reveal why the story happened.

Cause of events:

A student developer of a project suddenly asked us to adjust the MySQL connection wait timeout parameter wait_timeout. Ask us to adjust from 28800s to 31536000s (i.e. one year)

The tomcat error log of the application-side test environment is as follows:

Yeah. The error is obvious. Most of the solutions to this problem require the database to change the connection wait timeout. Is this solution feasible?

Unfortunately, this is not feasible.

The main reason is performance considerations. The Wait_timeout parameter means that MySQL will disconnect connections that have not operated for a specified time. This value directly affects the concurrency performance of MySQL databases because Connection is set by the max_connections parameter.

In high concurrency scenarios, because our connection count is capped, too long a wait_timeout value will cause the connection to remain unreleased for a long time (e.g., change to one year as suggested by the developer). If the developer does not explicitly close the connection in the code, or does not define the connection recycling method when using the connection pool, then the number of connections will increase over time, and finally reach the setting value of the parameter max_connections, reporting the classic 1040 error, too many connections...

So to avoid this situation, DBAs stand is very clear, absolutely do not allow easy modification of production environment database parameters. The reason for this is given later. So now let's move on to focus on the problem at hand.

Since changing database parameters is not allowed, but the problem remains, how do you locate the real cause of the problem? Here I will guide you to master the basic troubleshooting method according to my thinking when dealing with this problem.

Problem Location:

If there is a connection problem, then there are two main parts involved: the database and the application connection pool of the webserver. Because of my overconfidence in my own work (laughs), I asked a few questions about the webserver side:

1. Are connection pools used? If not, is there an explicit closure of the connection in the code?

2. Is the connection pool configured with automatic connection recovery mechanism (for example, tomcat may involve removeAbandoned and removeAbandonedTimeout parameters)?

The answer given by the development students is:

1. Connection pooling (dbcp native) is indeed used.

2. After coordinating with OPS related students, we found that tomcat does lack parameters for connection recovery.

Solution:

OK, so I can basically locate the problem, the following is to give the solution:

1. Tomcat adds removeAbandoned=true, removeAbandonedTimeout=60, testOnBorrow=true, and validationQuery=select now(). The goal is to let the connection pool of webserver determine whether the database side has released the connection before connecting, and if it has, it will recover and rebuild the connection. Note: I hesitate to give the removeAbandoned and removeAbandonedTimeout parameters, because these two parameters may cause the connection to be forcibly reclaimed by the connection pool if it is not processed within the time set by removeAbandonedTimeout, thus causing the request to be disconnected without returning data. So these two parameters are not usually set in production environments, but after learning that it is a test environment, I decided to try to see if I can locate the problem is the connection pool.

2. If this doesn't work, try replacing a third-party connection pool like c3p0.

3. If replacing c3p0 doesn't work, check if the machine in the test environment has a firewall turned on, or if there is a problem with selinux policy. This was mainly because I found that the disconnect times in the error logs were very regular. As shown below:

Therefore, there may be some mechanism that causes artificial disconnection. Therefore, in the case that the first two methods cannot be solved, you can try to use tools such as tcpdump to grab packages and check the current system network environment.

After some painful attempts, everything worked fine after replacing the third-party connection pool for c3p0.

Postscript:

This short story, I summarized the following points please learn from:

1. All the faults you see are just a representation of them at some point in time. So how do you quickly locate the problem based on these appearances? In addition to the accumulation of work experience, but also master the ability to connect scattered knowledge points. This can be very useful when dealing with faults.

2. Developers should not easily ask DBAs to modify databases. Including parameters, data, etc. As mentioned above, it is not allowed to easily modify the parameters of the production database, which is also what I want to emphasize every time I share. Data is the core of a company, and databases are the tools to store that core. The biggest characteristic of database is stability, and it needs stability most. No matter how fancy the front-end application is developed and how complex the logic is implemented, if the database is not stable and is constantly changing, then the application will only show incorrect data (wrong data) or inconsistent data (dirty data). So please be very careful with every request you make to the database team.

If you can have a solution before the database, don't put it on the database.

3. Learn as much as you can about the world outside your job. To do technology, you should have the spirit of asking questions. Learn more about what has nothing to do with your job, even the essential work of others. You will find that your career path will be wider and wider.

PS：

MySQL Connection Pool and Thread Pool

Many people confuse these two concepts. In the traditional one-thread-per-connection configuration, connections and threads are in a 1-to-1 relationship. But after the concept of thread pool was introduced, this is no longer the case. Here I use not DBA can understand the language simple explanation:

Connection pooling is implemented on the Client side. Because frequent connection creation and release on the Client side increases the average response time of requests, the Client side tends to create connections in advance and complete all requests to the database through these connections. When the Client side requests are busy, the database concurrency pressure can also be relieved through the request queuing mechanism.

Thread pools are implemented on the Server side (database side). Thread pools are similar to connection pools in that MySQL pre-allocates thread resources in the thread pool. In addition to performing the same functions as connection pools, such as thread multiplexing, request queues, etc., it also logically transforms the 1-to-1 relationship in one-thread-per-connection into many-to-1. MySQL's thread pool is divided into multiple groups, and one or more worker threads are forked out of each group. For each connection (socket connection) initiated by the client-side connection pool, there is not one worker thread that monopolizes the thread pool, but one worker thread that handles multiple connections. As shown below:

I don't know how to explain it, do you understand?

Perry.Zhang

01.26.2016

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.