An analysis of Mysql Interactive connection & non-interactive connection _mysql

Interactive operation: Popular, that is, you open MySQL on your computer client, that is, the black window, in the black window for a variety of SQL operations, of course, the TCP protocol is certainly gone.

Non-interactive: that is, you make a program call in your project. For example, one side is the Tomcat Web server, one side is the database server, the two how to communicate? In the Java Web, we usually choose Hibernate or JDBC to connect. Then this is a non-interactive operation.

In the past, I basically did not relate to these two attributes, are used by the MySQL service provider recommended by default, is 8 hours.

However, since yesterday, due to rent a space in the new network, and his MySQL wait_time set 10s, so lead to a series of questions, by the way to study.

Or this title can be changed to "MySQL 8-hour automatic shutdown" problem, this title you search on Baidu, a lot of search, but did not speak clearly, today I would like to say these two values.

First, the concept

1) Interactive_time: means that if the free NS (n is the value of this property), then the MySQL connection is automatically closed. Shut down what kind of MySQL connection? Before, we were in "what is MySQL interactive and non-interactive operation?" In this article, there are two ways to operate MySQL, and there are two kinds of connections, one is interactive, the other is non-interactive. And this property controls interactivity. Is that you open a MySQL client black window, after the operation, and separated the NS you do not operate, and then you want to continue operation, I'm sorry, MySQL will be closed before you that connection, MySQL will help you automatically reconnect.

2) Wait_time: means if the free NS (n is the value of this attribute), then automatically kill a part of the MySQL connection thread. The connection here refers to a non-interactive connection.

In summary, it is in the more formal terms:

(1) Interactive_timeout:

Parameter meaning: The number of seconds the server waits for activity before shutting down an interactive connection. An interactive client is defined as a client that uses the Client_interactive option in Mysql_real_connect ().

Parameter default value: 28,800 seconds (8 hours)

(2) Wait_timeout:

Parameter meaning: The number of seconds the server waits for activity before it closes a non-interactive connection.

When a thread starts, the session Wait_timeout value is initialized based on the global Wait_timeout value or global interactive_timeout value, depending on the client type (by the connection option of the Mysql_real_connect () Client_ Interactive definition).

Parameter default value: 28,800 seconds (8 hours)

Here's another idea: Mysql_real_connect (), a good understanding, is that you no matter what connection, is interactive or non-interactive, you have to operate MySQL before you have to execute the method, in fact, you can understand to login MySQL, Or get a connection to MySQL.

Ii. How to view and reset these two values

This is what I changed after. This is the view method.

Modifying these two values is divided into two types of modifications.

1 Modify the values of these two properties for the current session. The so-called current session is the connection to the connection pool you are currently getting. For example, you open the Black window of the session. This modification is relatively simple, direct set wait_timeout=10; how do you know that this modification is just the current session? Very simple, you put this black window shut, you reopen one again, again check, you found that did not change ah.

2) To modify the global property value. Generally this use more, you go to your database installation package to find My.ini, add wait_timeout=10 at the bottom can be, and then restart the MySQL service, I said restart service, not you close this black window, restart a black window. The service is in My Computer right key service to look for.

Here we go.

first, Mysql8 hour anomaly  

1) The concept of anomaly.

We all know that MySQL 8-hour automatic disconnect exception, Baidu on a lot. This is the result of this value MySQL default is 8 hours, so if you within 8 hours, the database feel that no one to connect me, then I will be all the existing non-interactive connection all kill. In SSH, we typically use a data pool. That is, when Tomcat has started, apply to MySQL to the N (this is your configuration) of the non-interactive is connected, later want to use the database connection, there is no need for a request to regain the MySQL connection, as long as from the data pool to get it. But now, if you're within 8 hours, does not send the request, then the MySQL will automatically all the non-interactive is the connection to kill, at this time, your database connection pool exists in the database connection is actually NULL, does not exist, you do not have the judgment, continues to want to use this link to request the data, Of course, the exception is thrown, and the exception thrown is Communications link failure due to underlying exception.  

2 So how to solve it?

The principle is very simple, the reason for this anomaly is not because there is no existing connection in the pool, and you do not know, you have to use the original closed connection to request it? This is good, you can not control the server MySQL (if you are space), then you know to control your database connection pool, so that the connection pool to add a verification function, that is, usually after the connection from the Chili, before the use of the verification of the link is valid, if it is valid can be used directly, If it is not valid, reapply for a connection so that the exception is not present. Certainly, the performance will be reduced. As for why performance is down, we'll talk about how to do this now, how do I get a database connection pool to verify the effectiveness of the reuse function:

I use the connection pool is C3P0, recommend this. Of course the performance advantages and disadvantages of each connection pool you have to according to your own project specific analysis, here does not analyze why I chose c3p0.

<property name= "Testconnectiononcheckin" value= "true"/>//return to connection pool to check
<property name= " Testconnectiononcheckout "value= true"/>//from the connection pool to check 
 <!--for performance consumption, use it only when needed. If set to true then the validity is officers transferred Guevara for each connection submission
. It is recommended that you use
methods such as Idleconnectiontestperiod or automatictesttable to improve the performance of your connection tests. Default:false-->
<property name= "Testconnectiononcheckout" >false</property>
  
<!-- If set to true, officers transferred Guevara the validity of the connection at the same time as the connection is made. Default:false-->
<property name= "Testconnectiononcheckin" >true</property> 

So that's two actions to maintain this connection pool, as shown in figure:

3 Performance issues for solutions

(A) Performance optimization for detection effectiveness

Because of the time to check whether the link is still valid, so efficiency such as will be reduced, then how to check it? Default check method I don't know yet, but the above paragraph says that if you use the Automatictesttable method to verify the validity of a test connection, you can improve performance. So let's take a look at this property.

1 <property name= "automatictesttable" value= "c3p0testtable"/>

What does this attribute mean? Is that he will automatically help you build a table named C3p0testtable, this table is very simple, and the most important thing is that there is no data in the test, you can access through the connection to this table can access to, if you can access to, indicating that the link is valid, Otherwise, this link has been killed by MySQL.

Is this watch built by our programmers? No, you do not care, as long as you configure it, will automatically help you create a table.

B)optimization of testconnectiononcheckout performance. Because this property is when you take it out of the connection pool, in each link to really submit, the epicenter took this link to the database visit, to do a check, not to say, I take out from the connection pool, I will do the inspection, I have to wait until the submission of the time to do the inspection, This will be designed to a connection submission problem, in the default case, you send a DB request will automatically immediately go to execute, will immediately commit, if you are configured in the transaction, is a action method corresponding to a biz method, So you just have to do a check on the line, reducing the number of tests.

In other words, the purpose of optimizing the performance of this solution is to reduce the number of detections and optimize the detection methods.

Second, the MySQL wait_timeout value should set how much?

If you are not IDC, you are not out of the rental server, then you can set the default value of 8 hours on it. But if IDC rents servers outside, it has to be reset, for example, the new network is set to 10s.

But this setting is not as easy as the new network set up, because I found that the new network of this server business pressure is not very large, but he set a 10s so small value, it will be more consumption of server resources. Yes, admittedly, if this value is too large, it's likely to cause a lot of useless idle connections, the database pressure is too large, but the new network of the server's business pressure is not big Ah, you set into such a small number, it is obvious that you are deliberately increase the system server business pressure Ah, sinners ah sinners.

So, set to how big, according to your server pressure size to configure, not just write a number on the line.

The above analysis of MySQL Interactive connection & non-interactive connection is a small series to share all the content, hope to give you a reference, but also hope that we support the cloud-dwelling community.