Shulou(Shulou.com)05/31 Report--

This article shows you how to set the query timeout in MySQL, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

In order to optimize the query timeout setting mode of OceanBase, we specially investigate the processing of timeout in MySQL. The record is as follows.

[plain]

Mysql > show variables like'% time%'

+-+ +

| | Variable_name | Value |

+-+ +

| | connect_timeout | 10 | |

| | datetime_format |% Y-%m-%d% H:%i:%s |

| | delayed_insert_timeout | 300 | |

| | flush_time | 1800 | |

| | innodb_lock_wait_timeout | 50 | |

| | innodb_old_blocks_time | 0 | |

| | innodb_rollback_on_timeout | OFF |

| | interactive_timeout | 28800 | |

| | lc_time_names | en_US |

| | lock_wait_timeout | 31536000 | |

| | long_query_time | 10.000000 | |

| | net_read_timeout | 30 | |

| | net_write_timeout | 60 | |

| | slave_net_timeout | 3600 | |

| | slow_launch_time | 2 | |

| | system_time_zone |

| | time_format |% H:%i:%s |

| | time_zone | SYSTEM |

| | timed_mutexes | OFF |

| | timestamp | 1366027807 | |

| | wait_timeout | 28800 | |

+-+ +

21 rows in set, 1 warning (0.00 sec)

Focus on explaining several of these parameters:

Connect_timeout:

The number of seconds that the mysqld server waits for a connect packet before respondingwith Bad handshake. The default value is 10 seconds as of MySQL 5.1.23 and 5 seconds before that. Increasing the connect_timeout value might help if clients frequently encounter errors of the form Lost connection to MySQL server at 'XXX', system error: errno.

Explanation: when getting a link, the timeout for waiting for a handshake is valid only when logging in, and the parameter of login success does not matter. The main purpose is to prevent the application of reconnection from increasing the number of connections too fast when the network is not good, which is generally fine by default.

Interactive_timeout:

The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect (). See alsowait_timeout.

Explanation: how often a thread in a persistent SLEEP state is shut down. The thread is awakened to acrivity state every time it is used, becomes interactive state after Query execution, and starts timing again. Wait_timeout is different in that it only works on threads of TCP/IP and Socket links, and the meaning is the same.

MySQL can configure the timeout of the connection. If this time is too long, even to the 10min, it is likely that 3000 links are full and where the sleep is, the new links cannot come in, resulting in a failure of service. So this configuration tries to configure a logical value of 60s or 120s and so on.

Speak human words:

After typing a command at the bottom of the command line, the interval until the next command arrives is interactive_time. If this interval exceeds interactive_timeout, the connection will be automatically disconnected and the next command will fail. However, most mysql clients have an automatic reconnection mechanism, and the next command will hold the line after the reconnection.

[sql]

Mysql > set interactive_timeout = 1

Query OK, 0 rows affected (0.00 sec)

Mysql > show session variables like'% timeout%'

+-+ +

| | Variable_name | Value |

+-+ +

| | connect_timeout | 10 | |

| | interactive_timeout | 1 | |

| | wait_timeout | 28800 | |

+-+ +

10 rows in set (0.00 sec)

=

[sql]

Mysql > set wait_timeout = 1

Query OK, 0 rows affected (0.00 sec)

[go and make a cup of tea, wait]

Mysql > show session variables like'% timeout%'

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 7

Current database: * * NONE * *

+-+ +

| | Variable_name | Value |

+-+ +

| | connect_timeout | 10 | |

| | interactive_timeout | 28800 | |

| | wait_timeout | 28800 | |

+-+ +

10 rows in set (0.01 sec)

Wait_timeout:

The number of seconds the server waits for activity on a noninteractive connection (there is no active command on the connection, maybe the client has gone for coffee.) Before closing it. Before MySQL 5.1.41, this timeout applies only to TCP/IP connections, not to connections made through Unix socket files, named pipes, or shared memory.

On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client

The above is how to set query timeout in MySQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, 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.