Analysis of mysql interactive connection and non-interactive connection; analysis of mysql interactive connection

Analysis of mysql interactive connection and non-interactive connection; analysis of mysql interactive connection

Interactive operations: in layman's terms, you open the mysql client on your local machine, that is, the black window, and perform various SQL operations in the black window, of course, it must be the tcp protocol.

Non-interactive operation: You call a program in your project. For example, if one is a tomcat web server and the other is a database server, how can the two communicate? In java web, we usually choose hibernate or jdbc to connect. This is a non-interactive operation.

Previously, I basically had no relationship with these two attributes. They all used the default value recommended by the mysql service provider, which is 8 hours.

However, since yesterday, a space has been rented on the new network, and the wait_time of mysql has been set to 10 s, a series of problems have been introduced.

Or the title can be changed to "mysql automatically closed in 8 hours". You searched Baidu for the title, but you did not understand it, today, I will explain these two values to everyone.

I. Concepts

1) interactive_time: If the free Ns (N is the value of this attribute), the mysql connection is automatically closed. What mysql connection is closed? Before that, What Are mysql interactive operations and non-interactive operations? As mentioned in this article, there are two ways to operate mysql: Interactive and non-interactive. This property controls interactions. That is, you open a black window on the mysql client. after entering the operation, you will not perform the operation after Ns, and then you want to continue the operation. Sorry, mysql will close your connection before, and mysql will automatically reconnect you.

2) wait_time: If the free Ns (N is the value of this attribute), it will automatically kill some of the mysql connection threads. The connection here refers to a non-interactive connection.

To sum up, we use a more formal term:

(1) interactive_timeout:

Parameter description: The number of seconds before the server closes the interactive connection. Interactive clients are defined as clients that use the CLIENT_INTERACTIVE option in mysql_real_connect.

Default Value of the parameter: 28800 seconds (8 hours)

(2) wait_timeout:

Parameter description: The number of seconds waiting for activity before the server closes a non-interactive connection.

When a thread starts, the wait_timeout value of the session is initialized based on the global wait_timeout value or the global interactive_timeout value, depending on the client type (defined by the CLIENT_INTERACTIVE connection option of mysql_real_connect ).

Default Value of the parameter: 28800 seconds (8 hours)

Another concept is introduced here: mysql_real_connect (). It is easy to understand that no matter what the connection is, interactive or non-interactive, you must complete the execution before you operate mysql, you can log on to mysql or get a connection to mysql.

Ii. How to view and reset these two values

This is what I modified. This is the method for viewing.

You can modify these two values in two ways.

1) modify the attribute values of the current session. The current session is the connection pool you have obtained. For example, the session that you opened in the black window. This modification is relatively simple. Just set wait_timeout = 10;. How do you know that this modification only modifies the current session? It's easy. You closed the black window, re-opened it, and re-checked it. You didn't change it.

2) modify the global attribute value. Generally, this is mostly used. You can find my under your database installation package. ini, add wait_timeout = 10 at the bottom, and then restart the mysql service. I am talking about restarting the service, instead of closing the black window and restarting a black window. Find the service in my PC Right-click the service.

Now let's talk about it here and continue later.

1. An error occurred during mysql8 hours. 

1) exception concept.

Everyone knows that an exception occurs when mysql is automatically disconnected within 8 hours. This is because of this value. The default value of mysql is 8 hours. If you feel that no one is connected to me in the database within 8 hours, well, I will kill all existing non-interactive connections. In ssh, we generally use a data pool. When tomcat is started, apply to mysql for N (this N is configured by you) as a non-interactive connection. When you want to connect to the database in the future, there is no need for a request to re-obtain the mysql connection, as long as it is obtained from the data pool. However, if you do not send a request within 8 hours, mysql will automatically kill all non-interactions, the database connection in your database connection pool is actually null and does not exist. At this time, you do not judge whether to continue to use this link to request data. Of course, an exception will be thrown, the thrown exception Communications link failure due to underlying exception.

2) How can this problem be solved?

The reason for this exception is that there is a connection that does not exist in the connection pool, and you still don't know. Do you have to use the originally closed connection to request it? This is easy to handle. You cannot control the mysql server (if you are using space). Then you know how to control your database connection pool and add a verification function to the connection pool, after obtaining the connection from the pool, verify whether the link is valid before using it. If it is valid, you can directly use it. If it is invalid, apply for a new connection, in this way, this exception will not occur. Of course, the performance will definitely decrease. We will talk about why performance is low. Now, let's look at how to enable the database connection pool to verify whether the database can be reused effectively:

The connection pool I used is c3p0, which is recommended. Of course, you have to analyze the performance advantages and disadvantages of each connection pool based on your project. Here, I will not analyze why I chose c3p0.

<Property name = "testconnectioncheckin" value = "true"/> // check <property name = "testconnectioncheckout" value = "true"/> when returning data to the connection pool to check <! -- Because of high performance consumption, use it only when needed. If it is set to true, the validity of each connection is verified when it is submitted. We recommend that you use idleConnectionTestPeriod or automaticTestTable to improve the connection test performance. Default: false --> <property name = "testconnectioncheckout"> false </property> <! -- If it is set to true, the connection validity will be verified when the connection is obtained. Default: false --> <property name = "testConnectionOnCheckin"> true </property>

Therefore, this connection pool is maintained through two actions,

3) performance issues of the Solution

A) Performance Optimization of detection effectiveness

Because it is necessary to check whether the link is valid at any time, so the efficiency will be reduced for example. How can this problem be checked? I do not know the default check method, but as mentioned above, if you use the automaticTestTable method to verify the connection validity, the performance will be improved. Let's look at this property.

1 <property name = "automaticTestTable" value = "C3P0TestTable"/>

What does this attribute mean? That is, it will automatically help you create a table named C3P0TestTable, which is very simple and the most important thing is that there is no data in it. When detecting, whether the table can be accessed through a connection. If the table can be accessed, the link is valid; otherwise, the link has been killed by mysql.

Is this table created by our programmers? NO, you don't need to worry about it. As long as you configure this, it will automatically create a table for you.

B) Optimization of testconnectioncheckout performance. This attribute means that when you take it out of the connection pool, you actually submit it at every link. When you visit the database with this link in the epicenter, you need to check it, when I take it out of the connection pool, I will check it. I have to wait until it is submitted. This will design a connection submission problem. By default, when you send a db request, it will be automatically executed immediately and commit will be immediately executed. If you configure it in a transaction, it is an action method corresponding to a biz method, in this way, you only need to perform a check to reduce the number of checks.

That is to say, the purpose of Performance Optimization for this solution is to reduce the number of detection times and optimize detection methods.

Ii. How many wait_timeout values should I set for mysql?

If you are not an IDC and you are not renting an external server, you can set it to the default value of 8 hours. However, if the IDC is rented out, you have to reset it. For example, the new network is set to 10 s.

However, this setting is not as easy as that of xinnet, because I found that the service pressure on this server on xinnet is not very high, however, it sets a value as small as 10 s, which will consume server resources more. Yes, you must admit that if this value is too large, it may lead to a large number of useless idle connections, which puts too much pressure on the database, but the business pressure on the server of xinnet is not great, you set it to such a small value. Obviously, you are deliberately increasing the business pressure on system servers.

Therefore, the configuration depends on the pressure on your server, rather than simply writing a number.

The above analysis of mysql interactive connection & non-interactive connection is all the content shared by Alibaba Cloud xiaobian. I hope to give you a reference and support for the customer's home.