MySQL System variables interactive_timeout and wait_timeout

Http://blog.chinaunix.net/u/14014/showart_490462.html

 

 

Mysql> show variables like '% timeout ';

The output is as follows:

+ ---------------------------- + ------- +
| Variable_name | value |
+ ---------------------------- + ------- +
| Connect_timeout | 5 |
| Delayed_insert_timeout | 300 |
| Interactive_timeout | 28800 |
| Net_read_timeout | 30 |
| Net_write_timeout | 60 |
| Slave_net_timeout | 3600 |
| Wait_timeout | 28800 |
+ ---------------------------- + ------- +
Interactive_timeout takes effect after the client_interactive option is set in mysql_connect () and is assigned as wait_timeout;

Mysql> set wait_timeout = 10; valid for the current interactive link;
Mysql> set interactive_timeout = 10; valid for subsequent interaction links;

The timeout time is measured in seconds and starts from the time after the last SQL statement is executed. If the current idle time exceeds this time, the variable is forcibly disconnected.

 

 

Http://blog.sina.com.cn/s/blog_473d5bba0100051j.html

 

 

 

Several variables related to timeout in MySQL (21:16:52)

Category: Professional

It is not enough to change the disconnection time of MySQL. In the past, only the value of the connect_timeout variable was changed. Now I have changed the two. I don't know enough. Continue to check if it is not enough. The number of seconds that the interactive_timeout server waits for action on an interactive connection before closing the connection. An interactive customer is defined as a customer who uses the client_interactive option for mysql_real_connect (). The default value is 28800. The number of seconds the wait_timeout server waits for action on a connection before closing the connection. The default value is 28800. That is, if nothing happens, the server closes the connection after 8 hours. Configure the wait-Timeout parameter 2007-09-19 01: 11: 28 timeout in your MySQL instance:
My understanding of the parameter "Wait-Timeout" is the maximum idle time value for the mysql client database connection.
To put it bluntly, when your MySQL connection is idle for more than a certain period of time, it will be forcibly closed. The default wait-Timeout value of MySQL is 8 hours.
Setting this value is very meaningful. For example, your website has a large number of MySQL connection requests (each MySQL connection requires memory resource overhead ), because of your program, there are a lot of connection requests idle and nothing to do, occupying the memory resources in vain, or, as a result, MySQL exceeds the maximum number of connections and cannot create a new connection, resulting in a "too connections" error. Before setting, you can check the status of your MySQL (show processlist is available). If you find that your MySQL has a large number of sleep processes, you really need to set your wait-timeout. If you set wait-Timeout = 10, all sleep threads in MySQL can only "Sleep" for up to 10 seconds, and are forcibly disabled.
This is useful for MySQL with heavy load. Configure c3p0 connection pool in hibernateHttp://www.yuloo.com/jsjks/jsj-java/2008-12-11/166978.html

Author: Release Date: 09:42:09 Source:

The connection pool algorithm provided by Hibernate is quite immature. It is only intended to help you get started quickly and is not suitable for product systems or performance tests. For the best performance and stability, you should use a third-party connection pool. You only need to replace hibernate. Connection. pool_size with the settings of a specific connection pool. This will disable the connection pool that comes with hibernate. For example, you may want to use c3p0. C3p0 is an open source JDBC connection pool that is distributed along with hibernate. It is located under the lib directory. If you set hibernate. c3p0. *, Hibernate uses c3p0connectionprovider to cache JDBC connections. If you prefer proxool, refer to hibernate. properties in the release package and go to the hibernate website for more information. This is a sample hibernate. properties file using c3p0 (from the etc directory in the hibernate package ): ########################### ### C3p0 connection pool ### ########################### # Hibernate. c3p0. max_size 2 # Hibernate. c3p0. min_size 2 # Hibernate. c3p0. Timeout 5000 #Hibernate. c3p0. max_statements100 # Hibernate. c3p0. ID le_test_period 3000 # Hibernate. c3p0. acquire_increment 2 # Hibernate. c3p0. Validate false Add the following configuration to the hibernate. cfg. xml file: <! -- Maximum number of connections --> <Property name = "hibernate. c3p0. max_size"> 20 </property> <! -- Minimum connections --> <Property name = "hibernate. c3p0. min_size"> 5 </property> <! -- Get the connection timeout time. If the timeout value exceeds this time, an exception is thrown, in milliseconds. --> <Property name = "hibernate. c3p0. Timeout"> 120 </property> <! -- Maximum number of preparedstatement --> <Property name ="Hibernate. c3p0. max_statements& Quot;> 100 </property> <! -- Check idle connections in the connection pool every 120 seconds. Unit: seconds --> <Property name = "hibernate. c3p0. idle_test_period"> 120 </property> <! -- When the connections in the connection pool are used up, c3p0 obtains the new connections --> <Property name = "hibernate. c3p0. acquire_increment"> 2 </property> <! -- Verify whether the connection is available every time --> <Property name = "hibernate. c3p0. Validate"> true </property> Complete example (hibernate. properties ): Hibernate. Connection. driver_class = org. PostgreSQL. Driver Hibernate. Connection. url = JDBC: PostgreSQL: // localhost/mydatabase Hibernate. Connection. Username = myuser Hibernate. Connection. Password = secret Hibernate. c3p0. min_size = 5 Hibernate. c3p0. max_size = 20 Hibernate. c3p0. Timeout = 1800 　　Hibernate. c3p0. max_statements= 50 Hibernate. dialect = org. hibernate. dialect. postgresqldialect