MySQL Timeout Explained

Originally posted in https://www.bytebase.com/blog/mysql-timeout/

Hi and welcome to Database DevOps Academy #88! We share Database DevOps insights and best practices for modern engineering organizations weekly. 🍊

In Issue #88, we explained timeout settings that are essential for ensuring system stability, preventing resource exhaustion, and maintaining performance. 🐦‍⬛

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.

By default, MySQL sets 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:

Or dynamically at runtime (requires privilege):

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

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.

By default, 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:

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

Or at the query level using an optimizer hint:

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.

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 ` 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:

Or dynamically at runtime:

MySQL also supports session-level configuration:

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 (`). 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.

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

• applies to connections that use the (typically GUI tools and command-line clients)

• 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 ` or `, depending on whether the ` flag is used. After that, the session's timeout can be modified independently.

You can configure these settings in the MySQL configuration file:

Or dynamically at runtime:

Session-level configuration is also supported:

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 ` to prevent "server has gone away" errors.

transaction_timeout

To prevent long-running connection holding locks for too long, you usually set ` or `. However, the transaction will still be open if it consists of short statements and short pauses in between. Ideally, you want to have a `. 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

Connection Pooling

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

For example, if ` 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 ` and look for connections in the state. A large number of sleeping connections might indicate that your application is not properly closing connections or that your timeout values need adjustment.

References

• MySQL Server System Variables

• InnoDB Parameters

• Server-Side SELECT Statement Timeouts