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

In this issue, the editor will bring you an example analysis of MYSQL and JAVA connection errors. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

Recently, developers told me that when they test the system, they often get kicked out of the connection to MYSQL. The specific explanation given to me is that JAVA's buffer pool connection MYSQL remains connected, but when it is used again, a connection error is reported.

Corresponding to the error time of the application, check the error logs of PROXYSQL and MYSQL, and there are indeed related errors.

PROXYSQL error log

Error log for MYSQL

So the question is, why on earth is got timeout reading communication packets?

DBER can tell you with high sounding that this is JAVA's problem, not the database's problem, but as Architector of Databases, the answer is really hated.

So we have to figure out what's going on, and the story begins. First of all, the JAVA program has a buffer pool to connect to the ProxySQL of MYSQL, and ProxySQL, as the middleware and buffer of MYSQL, transfers the connection of JAVA to the master node of MYSQL (MGR MTS).

Step by step in analyzing the problem, let's start with the root of MYSQL.

From the perspective of MYSQL, there are three reasons for Aborted_clients and Aborted_connects.

1 the client connection was terminated unexpectedly in MYSQL. As for what this accident is, it is possible that the current connection was terminated by DBA using KILL, or some other PT-KILL tool and so on, causing your connection to be stopped.

The two parameters in 2 MYSQL, wait_timeout and interactive_timeout, wait_timeout, if the connection is in the idle state for how long, the connection will be kicked out. Wait_timeout and interactive_timeout

Wait_timeout is the idle (idle time) of your connection, which will be dropped by the system KILL after how long it takes.

Interactive_timeout is the interval between the program and the database. If you have a long interval and make the database impatient, it will clean up your connection threads for you.

Programmers may ask, WHY, this is mainly because each connection will HOLD a certain amount of memory, such as sort buffer join buffer, if these BUFFER are given very large at the beginning, then if there are too many connections, the system may OOM, so the system must take care of those irresponsible connections, only know on, do not know off.

How to confirm the number of connections to your current MYSQL? the setting here is 1800 seconds, that is, 30 minutes.

Having said that, let's go on to say that PROXYSQL, as the best open source MYSQL middleware, is used by a lot of people.

In fact, proxysql is also a thread pool, my current PROXYSQL is composed of a main 34 child threads.

The connection pool in proxysql also holds free connections, and how long will PROXYSQL do a ping to keep the connection with MYSQL, the time is through mysql-ping_interval_server_msec.

On the other hand, mysql-connection_max_age_ms automatically closes the connection when the idle time exceeds the setting of mysql-connection_max_age_ms when the empty connection is not used by any session.

Mysql-ping_timeout_server is the timeout for PROXYSQL to send PING at regular intervals to get a reply in order to maintain an idle connection with the backend.

Write here, estimated to be able to send online BAIDU to a lot, about such a problem, and the solution to such a problem, most of the time is to modify the MYSQL of the two timeout time, the default is 28800 seconds that is 8 hours, they suggest changing the time to 31536000 seconds, well, I do not hit people, such programmers can earn 2000 yuan per month in Beijing is more.

Finally, my non-JAVA Developer GOOGLE solution is

Configuring Druid DatasourceStat

1 configuration is required

ValidationQuery: select 1 (such a statement to access the database to avoid performance consumption)

TestWhileIdle: true

The setting of timeBetweenEvictionRunsMillis: must be less than the timeout value of mysql or proxysql

The value of timeBetweenEvictionRunMills here should be less than 3 minutes.

So far to solve the problem of Communications link failure, inform the paragraph, so far there is no new mistake to tell me, Amitabha Buddha.

The above is the example of MYSQL and JAVA connection errors shared by Xiaobian. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.