MySQL Timeout

MySQL offers various timeout settings that are essential for ensuring system stability, preventing resource exhaustion, and maintaining performance, especially in high-traffic environments or when dealing with complex queries. Let's explore each of them. connect_timeout connect_timeout defines the number of seconds that the MySQL server waits for a connect packet before responding with a "Bad handshake" error. This timeout prevents abandoned connection attempts from consuming server resources. ERROR: Lost connection to MySQL server at 'reading initial communication packet', system error: [errno] By default, MySQL sets connect_timeout to 10 seconds, which works well for most environments. For high-latency networks or connections routed through multiple proxies, you might need to increase this value to 15-30 seconds. Conversely, in environments vulnerable to denial-of-service attacks, a lower timeout value can help mitigate connection flooding by releasing resources more quickly. You can configure this setting in the MySQL configuration file: [mysqld] connect_timeout=20 Or dynamically at runtime (requires SYSTEM_VARIABLES_ADMIN privilege): SET GLOBAL connect_timeout = 20; Note that changing this setting dynamically affects only new connection attempts; existing connections remain unaffected. max_execution_time max_execution_time only applies to naked SELECT statements. Other statement types like INSERT, UPDATE, and DELETE are not affected by this timeout mechanism. It's also ignored in stored procedures. max_execution_time sets a limit on the maximum amount of time that a SELECT statement is permitted to execute before being terminated by the server. Introduced in MySQL 5.7.8, this setting helps prevent long-running queries from monopolizing system resources. ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded By default, max_execution_time is set to 0, which effectively disables the timeout mechanism. The value is specified in milliseconds, so setting it to 10000 would create a 10-second limit for queries. You can set it globally in the configuration file: [mysqld] max_execution_time=10000 Or at the session level, affecting only queries in the current connection: SET SESSION max_execution_time = 5000; Or at the query level using an optimizer hint: SELECT /+ MAX_EXECUTION_TIME(3000) / * FROM large_table WHERE complex_condition; innodb_lock_wait_timeout innodb_lock_wait_timeout controls how long an InnoDB transaction will wait for a row lock before giving up and rolling back the current statement. This timeout prevents transactions from being perpetually blocked when they cannot acquire necessary locks. ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction By default, this value is set to 50 seconds. When a transaction exceeds this threshold, only the current statement is rolled back by default, not the entire transaction. This behavior allows applications to catch the timeout error, potentially retry the specific operation that failed, and continue with the remainder of the transaction. You can modify this default behavior by starting the MySQL server with the --innodb-rollback-on-timeout option, which causes the entire transaction to be rolled back when a lock wait timeout occurs. The timeout can be configured at the global level: [mysqld] innodb_lock_wait_timeout=30 Or dynamically at runtime: SET GLOBAL innodb_lock_wait_timeout = 30; MySQL also supports session-level configuration: SET SESSION innodb_lock_wait_timeout = 10; For highly interactive OLTP systems, shorter timeout values (10-30 seconds) often provide better responsiveness by quickly identifying and resolving lock contention issues. For batch processing systems, longer timeout values may be more appropriate. The lock timeout mechanism interacts closely with MySQL's deadlock detection system (innodb_deadlock_detect). When deadlock detection is enabled (the default), InnoDB automatically identifies circular lock dependencies and immediately resolves them, often before the lock timeout is reached. interactive_timeout and wait_timeout interactive_timeout and wait_timeout control how long idle connections are maintained before being automatically closed by the server. These settings help prevent resource depletion from abandoned or forgotten connections. ERROR: MySQL server has gone away By default, both values are set to 28800 seconds (8 hours). The distinction between them is based on connection type: interactive_timeout applies to connections that use the CLIENT_INTERACTIVE flag (typically GUI tools and command-line clients) wait_timeout applies to non-interactive connections (application servers, scripts, automated processes) When a new connection session is established, its effective wait timeout value is initialize

Apr 23, 2025 - 05:02

MySQL offers various timeout settings that are essential for ensuring system stability, preventing resource exhaustion, and maintaining performance, especially in high-traffic environments or when dealing with complex queries. Let's explore each of them.

connect_timeout

connect_timeout defines the number of seconds that the MySQL server waits for a connect packet before responding with a "Bad handshake" error. This timeout prevents abandoned connection attempts from consuming server resources.

ERROR: Lost connection to MySQL server at 'reading initial communication packet', system error: [errno]

By default, MySQL sets connect_timeout to 10 seconds, which works well for most environments. For high-latency networks or connections routed through multiple proxies, you might need to increase this value to 15-30 seconds. Conversely, in environments vulnerable to denial-of-service attacks, a lower timeout value can help mitigate connection flooding by releasing resources more quickly.

You can configure this setting in the MySQL configuration file:

[mysqld]
connect_timeout=20

Or dynamically at runtime (requires SYSTEM_VARIABLES_ADMIN privilege):

SET GLOBAL connect_timeout = 20;

Note that changing this setting dynamically affects only new connection attempts; existing connections remain unaffected.

max_execution_time

max_execution_time only applies to naked SELECT statements. Other statement types like INSERT, UPDATE, and DELETE are not affected by this timeout mechanism. It's also ignored in stored procedures.

max_execution_time sets a limit on the maximum amount of time that a SELECT statement is permitted to execute before being terminated by the server. Introduced in MySQL 5.7.8, this setting helps prevent long-running queries from monopolizing system resources.

ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

By default, max_execution_time is set to 0, which effectively disables the timeout mechanism. The value is specified in milliseconds, so setting it to 10000 would create a 10-second limit for queries.

You can set it globally in the configuration file:

[mysqld]
max_execution_time=10000

Or at the session level, affecting only queries in the current connection:

SET SESSION max_execution_time = 5000;

Or at the query level using an optimizer hint:

SELECT /*+ MAX_EXECUTION_TIME(3000) */ * FROM large_table WHERE complex_condition;

innodb_lock_wait_timeout

innodb_lock_wait_timeout controls how long an InnoDB transaction will wait for a row lock before giving up and rolling back the current statement. This timeout prevents transactions from being perpetually blocked when they cannot acquire necessary locks.

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

By default, this value is set to 50 seconds. When a transaction exceeds this threshold, only the current statement is rolled back by default, not the entire transaction. This behavior allows applications to catch the timeout error, potentially retry the specific operation that failed, and continue with the remainder of the transaction.

You can modify this default behavior by starting the MySQL server with the --innodb-rollback-on-timeout option, which causes the entire transaction to be rolled back when a lock wait timeout occurs.

The timeout can be configured at the global level:

[mysqld]
innodb_lock_wait_timeout=30

Or dynamically at runtime:

SET GLOBAL innodb_lock_wait_timeout = 30;

MySQL also supports session-level configuration:

SET SESSION innodb_lock_wait_timeout = 10;

For highly interactive OLTP systems, shorter timeout values (10-30 seconds) often provide better responsiveness by quickly identifying and resolving lock contention issues. For batch processing systems, longer timeout values may be more appropriate.

The lock timeout mechanism interacts closely with MySQL's deadlock detection system (innodb_deadlock_detect). When deadlock detection is enabled (the default), InnoDB automatically identifies circular lock dependencies and immediately resolves them, often before the lock timeout is reached.

interactive_timeout and wait_timeout

interactive_timeout and wait_timeout control how long idle connections are maintained before being automatically closed by the server. These settings help prevent resource depletion from abandoned or forgotten connections.

ERROR: MySQL server has gone away

By default, both values are set to 28800 seconds (8 hours). The distinction between them is based on connection type:

interactive_timeout applies to connections that use the CLIENT_INTERACTIVE flag (typically GUI tools and command-line clients)
wait_timeout applies to non-interactive connections (application servers, scripts, automated processes)

When a new connection session is established, its effective wait timeout value is initialized from either wait_timeout or interactive_timeout, depending on whether the CLIENT_INTERACTIVE flag is used. After that, the session's timeout can be modified independently.

You can configure these settings in the MySQL configuration file:

[mysqld]
interactive_timeout=3600
wait_timeout=1800

Or dynamically at runtime:

SET GLOBAL interactive_timeout = 3600;
SET GLOBAL wait_timeout = 1800;

Session-level configuration is also supported:

SET SESSION wait_timeout = 7200;

For web applications with short, frequent interactions, shorter timeout values (60-300 seconds) often provide better resource utilization.

For applications using connection pooling, configure the pool's maximum idle time to be slightly shorter than MySQL's wait_timeout to prevent "server has gone away" errors.

transaction_timeout

To prevent long-running connection holding locks for too long, you usually set wait_timeout or interactive_timeout. However, the transaction will still be open if it consists of short statements and short pauses in between. Ideally, you want to have a transaction_timeout. Unfortunately, MySQL doesn't have this, only MariaDB and PostgreSQL do.

Best Practices

When configuring MySQL timeout settings, consider these practical recommendations:

Environment-Specific Settings

Setting	High-Traffic Web Applications	Data Processing Applications
`wait_timeout`	60-300 seconds	3600-7200 seconds
`interactive_timeout`	1800 seconds	7200 seconds
`connect_timeout`	5-10 seconds	10-20 seconds
`innodb_lock_wait_timeout`	10-30 seconds	50-120 seconds
`max_execution_time`	5000-10000 milliseconds	30000-60000 milliseconds

Connection Pooling

When using connection pooling, configure the pool's maximum idle time to be 10-15% shorter than MySQL's wait_timeout. This ensures that the pool proactively refreshes connections before the database server terminates them.

For example, if wait_timeout is set to 3600 seconds (1 hour), set the connection pool's idle timeout to approximately 3200 seconds (53 minutes).

Monitoring

Monitor idle connections using SHOW PROCESSLIST and look for connections in the Sleep state. A large number of sleeping connections might indicate that your application is not properly closing connections or that your timeout values need adjustment.