Detailed introduction to MySQL database wait

wait_timeoutis a system variable in MySQL that controls the number of seconds the server waits before closing a non-interactive connection. This parameter is very important for managing client connections and resource usage.

Parameter interpretation

Parameter name：wait_timeout
default value: Usually 28800 seconds (ie 8 hours), but the specific value may vary by MySQL version and configuration.
unit:Second
scope: The minimum value is 1 second, and the maximum value depends on the system architecture and configuration.

effect

Non-interactive connection：wait_timeoutMainly affects non-interactive connections, such as those established through scripts or applications. If a non-interactive connection iswait_timeoutThere is no activity within the specified time (i.e. no queries or commands are sent), and the MySQL server will automatically close the connection.
Resource Management: By setting a reasonablewait_timeoutValue can prevent long-term idle connections from occupying server resources, thereby improving system stability and performance.
Security: Timely closing idle connections can reduce potential security risks, such as unauthorized access or malicious attacks.

Setting method

You can set it in the following wayswait_timeout：

Dynamic settings (only effective for the current session)

SET [GLOBAL | SESSION] wait_timeout = value;

GLOBAL: Set global values to affect all new sessions.
SESSION: Set the value of the current session.

Configuration file settings (effective permanently)

In MySQL configuration files (usuallyor) add or modify the following line:

[mysqld]
wait_timeout = value

Then restart the MySQL service to make the configuration take effect.

Example

Suppose you want towait_timeoutSet to 3600 seconds (i.e. 1 hour), you can add the following line to the configuration file:

[mysqld]
wait_timeout = 3600

Or dynamically set at runtime:

SET GLOBAL wait_timeout = 3600;

Things to note

Client timeout：wait_timeoutOnly affects the behavior of the server side. Client applications should also have a corresponding timeout mechanism to handle connections closed by the server.
Long business: If the client executes long-running transactions (such as large queries, batch inserts, etc.),wait_timeoutIt will not affect these transactions. Timeout will only be triggered in idle time outside the transaction.
Monitoring and logging: It is recommended to enable slow query logs and error logs to monitor and record due towait_timeoutThe connection closing event caused.
Performance impact: Frequent connection shutdown and re-establishment may have a certain impact on performance, especially in high concurrency environments. Reasonable settingwait_timeoutTo balance resource management and performance requirements.

Related parameters

interactive_timeout: This is the timeout for interactive connections (such as connections through command line clients). The default value is usually the same aswait_timeoutSame, but can be set separately.
net_read_timeoutandnet_write_timeout: These two parameters control the timeout time for the server to read and write network data.

Sample configuration

existSettings in the filewait_timeoutandinteractive_timeout：

[mysqld]
wait_timeout = 3600
interactive_timeout = 3600

Summarize

This is the end of this article about the detailed introduction of the MySQL database wait_timeout parameter. For more information about the introduction of MySQL wait_timeout parameters, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!

Detailed introduction to MySQL database wait_timeout parameters