Wait_timeout in MySQL

Source: Internet
Author: User
Tags cron script

If you have not modified the MySQL configuration, the initial value of wait_timeout is 28800 by default.

Wait_timeout has many drawbacks. It is reflected that a large number of sleep processes in MySQL cannot be released in time, dragging down system performance, but it cannot be set too small, otherwise, you may encounter problems such as "MySQL has gone away". Generally, it is a good choice to set wait_timeout to 10, but in some cases, there may also be problems. For example, if there is a Cron script, and the interval between two SQL queries is greater than 10 seconds, then this setting will be problematic (of course, this is not an unsolved problem. You can ping mysql_ping from time to time in the program so that the server knows that you are still alive and recalculates the wait_timeout Time ):

# Vi/etc/My. CNF

[Mysqld]
Wait_timeout = 10

#/Etc/init. d/MySQL restart

However, this method is too stiff, so we should try to avoid restarting the online service no matter what, to see how to set it in the MySQL command line:

Mysql> set global wait_timeout = 10;

Mysql> show global variables like 'wait _ timeout ';
+ ---------------------------- + ------- +
| Variable_name | value |
+ ---------------------------- + ------- +
| Wait_timeout | 10 |
+ ---------------------------- + ------- +

This is easy to confuse. If show variables is used for query, the settings do not seem to take effect, this is because simply using show variables is equivalent to using show session variables. Session variables are queried. The global variables are queried only when show global variables is used.

Many people complain on the network that they haven't found any changes after setting global using show variables queries. The reason is that they have obfuscated session variables and global variables. If you only want to modify session variables, you can use a syntax like set wait_timeout = 10; or set session wait_timeout = 10.

Another noteworthy problem is the initialization of the session variable wait_timeout, which has been explicitly stated in the manual and I copied it directly:

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 (as defined by the client_interactive connect option to mysql_real_connect ()).

MySQL danale Jeremy zawodny was in his article fixing
Poor MySQL Default Configuration values lists several disgusting MySQL default settings, but does not include wait_timeout. However, I think it should also be regarded as one. You 'd better remember to modify it every time you install MySQL.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.