MySQL Interactive connection & non-interactive connection

Interactive operation: In layman's terms, you open the MySQL client on your local computer, that is, the Black window, under the Black window for various SQL operations, of course, the TCP protocol is definitely going.

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

Reference: MySQL Timeout correlation parameter parsing http://www.cnblogs.com/cenalulu/archive/2012/06/20/2554863.html

Before, I basically do not relate to these two properties, are used by the MySQL service provider recommended default value, that is, 8 hours.

However, since yesterday, because the new network rented a space, and his MySQL wait_time set 10s, so to draw a series of problems, the way to study.

Or the title can be changed to "MySQL 8-hour auto-shutdown" problem, this title you to Baidu Search, a search a lot of, but did not speak clearly, today I will give you say these two values.

First, the concept

1) Interactive_time: means that if the spare NS (n is the value of this attribute), then the MySQL connection will be closed automatically. What kind of MySQL connection is turned off? Before, we were in the what is MySQL interactive and noninteractive operation? In this article, MySQL is a two-way operation, there are two kinds of connection, one is interactive, one is non-interactive. And this property controls the interactivity. Is that you open a MySQL client black window, into the operation, after the NS you do not operate, and then you want to continue to operate, sorry, MySQL will shut down your connection before, MySQL will help you automatically reconnect.

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

In summary, it is to use the more formal terminology to say:

(1) Interactive_timeout:

Parameter meaning: The number of seconds that the server waits for activity before closing an interactive connection. The 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 that the server waits for activity before closing 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 (the connection option for the Mysql_real_connect () Client_ Interactive definition).

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

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

Second, how to view, reset these two values

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

Modifying these two values is a two-way modification.

1) Modify these two property values 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 that session. This modification is relatively simple, the direct set wait_timeout=10; All right, how do you know that this modification is only modifying the current session? Very simple, you put this black window closed, you open again, and then re-check, you found not changed AH.

2) Modify the global property value. Generally this use more, you go to your database installation package to find My.ini, at the bottom add wait_timeout=10 on it, and then restart the MySQL service, I said restart the service, not you close this black window, restart a black window. Service in My Computer right-click Service to find.

Now let's talk about this, and I'll go ahead.

One, mysql8 hours abnormal

1) Abnormal concept.

We all know that MySQL 8-hour automatic disconnection anomaly, Baidu on a lot. Because of this value, this value MySQL default is 8 hours, so if you in 8 hours, the database feel that no one to connect me, then good, I will all the existing non-interactive connections are all killed. In SSH, we typically use a data pool. It is when Tomcat is started, you apply to MySQL to N (this is your configured) non-interactive is a connection, later want to connect with the database, 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 do not send a request within 8 hours, then MySQL automatically will all the non-interaction is the connection kill, then, your database connection pool exists in the database connection is actually NULL, does not exist, you do not judge, continue to want to use this link to request data, Of course throws an exception, throwing the exception 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 connection pool, and you do not know, you still have to use the original closed connection to request it? This is good, you control the server of MySQL (if you are space), then you know the control of their database connection pool, and let the connection pool add a verification function, is usually after the connection from the dominant, before using the first to verify the link is valid, if valid can be used directly, If it is not valid, reapply for a connection so that the exception does not occur. Of course, performance will be reduced. As to why performance is down, we'll talk about it later, now, how do I implement the ability to have the database connection pool Verify that it is valid and re-used first:

I use the connection pool is C3P0, recommended to use 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.

12	<propertyname="testConnectionOnCheckin" value="true"/>//归还给连接池时候要检查<property name="testConnectionOnCheckout"value="true"/>//从连接池中拿出来要检查

1234567

<!--因性能消耗大请只在需要的时候使用它。如果设为true那么在每个connection提交的时候都将校验其有效性。建议使用idleConnectionTestPeriod或automaticTestTable等方法来提升连接测试的性能。Default: false --><property name="testConnectionOnCheckout">false</property>   <!--如果设为true那么在取得连接的同时将校验连接的有效性。Default: false --><property name="testConnectionOnCheckin">true</property>

So that means two actions to maintain the connection pool,

3) Performance issues with the solution

A) performance optimization for detection effectiveness

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

1	<propertyname="automaticTestTable"value="C3P0TestTable"/>

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

So is this watch built by our programmers? No, you don't have to, you just have to configure, nature will help you automatically build a table of this.

B)optimization of testconnectiononcheckout performance. Because this attribute refers to you from the connection pool out of time, in each link to really commit, the epicenter of this link to go to the database access, to do the next check, not that I took out from the connection pool, I do the check, I have to wait until the submission of the time to do the check, That will be designed to a connection commit problem, in the default case, you send a DB request will be automatically executed immediately, will immediately commit, if you are configured in the transaction, is an action method corresponding to a biz method, So you just have to do a check on the line, reducing the number of detections.

In other words, the purpose of performance optimization for this solution is to reduce the number of detections and optimize the detection method.

Second, MySQL wait_timeout value should be set how much?

If you are not IDC, you are not going out of the rental server, then you can fully set the default value of 8 hours. But if IDC rents out the server, it has to be reset, such as the new network is set to 10s.

But this setting is how big, is not the new net to set casually, because I found that the new network of the server business pressure is not very big, but he set a 10s so small value, it will be more consumption of server resources. is to admit that if this value is too large, it is likely to cause a large number of useless idle connections exist, the pressure on the database is too large, but the new network of the server's business pressure is not big ah, you set to such a small number, it is obvious that you are deliberately increasing the business pressure of the system server Ah, sinners, sinners.

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

MySQL Interactive connection & non-interactive connection