Exploring timeout variables in Mysql

Few days back I was checking one of my mysql server's settings. I found that there are a number of timeout settings. Since I am using mysql for last few years, I am well aware of wait_timeout and connect_timeout variables. As if you are using mysql in production then in most of the cases you have to tune these two variables. But Mysql provides a number of other timeout variables also. 

 

If you run show variables like '%timeout' query then you will get a number of different timeout variables, in my case I got 9 such variables.

mysql> show variables like '%timeout';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| connect_timeout            | 10    |
| delayed_insert_timeout     | 300   |
| innodb_lock_wait_timeout   | 50    |
| interactive_timeout            | 28800 |
| net_read_timeout               | 30    |
| net_write_timeout          | 60    |
| slave_net_timeout          | 3600  |
| table_lock_wait_timeout    | 50    |
| wait_timeout               | 5     |
+----------------------------+-------+
9 rows in set (0.00 sec)

Why are there so many timeout variables, and what are their purpose?

MySQL uses different timeout variables at different stages. When a connection is just being established connect_timeout is used. When server waits for another query to be sent to it wait_timeout (or interactive_timeout for applications which specified they are interactive during connection). If query is being read or result set is being sent back, net_read_timeout and net_write_timeout are used. innodb_lock_wait_timeout is used with Innodb tables in case getting locks on table rows. delayed_insert_timeout is used in case you are using delayed insert queries. slave_net_timeout is used in case of replication when a slave is reading data from the master.

Lets look into some more details about these variables:

connect_timeout:

The number of seconds the mysqld server waits for a connect packet before responding with Bad handshake. As of MySQL 5.1.23 the default value is 10 seconds and before that it was 5 seconds. Increasing the connect_timeout value might help if clients frequently encounter errors of the form Lost connection to MySQL server.

delayed_insert_timeout:

You can delay insert queries from happening until the table is free by using the delayed hint in your SQL statement. For example:

INSERT DELAYED INTO table (id) VALUES (123);

The above SQL statement will return quickly, and mysql server will store the insert statement in a memory queue until the table you are inserting into is free from reads. The downside to this is that you don't really know how long its going to take for your INSERT to happen. INSERT DELAYED handler thread in Mysql server will wait for delayed_insert_timeout seconds before terminating.

innodb_lock_wait_timeout:

The timeout in seconds an InnoDB transaction may wait for a row lock before giving up. innodb_lock_wait_timeout applies to InnoDB row locks only. A MySQL table lock does not happen inside InnoDB and this timeout does not apply to waits for table locks. InnoDB does detect transaction deadlocks in its own lock table immediately and rolls back one transaction. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction will hang for at most this many seconds before issuing the following error:

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

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(). interactive_timeout is the amount of seconds during inactivity that MySQL will wait before it will close a connection for a interactive connection.

net_read_timeout:

The number of seconds to wait for more data from a connection before aborting the read. 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. When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort. net_read_timeout rarely becomes the problem unless you have extremely poor network, because in most cases query is generated and sent as single packet to the server and application can’t switch doing something else and leaving server with partial query received.

net_write_timeout:

When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort. It defines the number of seconds to wait for a block to be written to a connection before aborting the write. Before MySQL 5.1.41, this timeout applies only to TCP/IP connections, not to connections made using Unix socket files, named pipes, or shared memory. If you do not fetch data for long enough MySQL Server may think client is dead and close connection. This well may happen if you need long processing for each row or have long periodic data flushes. Also, result set comes back in multiple pieces and if you're using mysql_use_result you can do any work between fetches, which potentially could take a lot of time.

slave_net_timeout:

The number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the read, and tries to reconnect. The first retry occurs immediately after the timeout. The interval between retries is controlled by the MASTER_CONNECT_RETRY option for the CHANGE MASTER TO statement or --master-connect-retry option, and the number of reconnection attempts is limited by the --master-retry-count option. The default is 3600 seconds.

table_lock_wait_timeout:
As per the mysql manual this variable is not used.

wait_timeout:

The number of seconds the server waits for activity on a noninteractive connection before closing it. This timeout applies only to TCP/IP and Unix socket file connections, not to connections made using 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 (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). Setting a value too low may cause connections to drop unexpectedly. Setting a value too high may cause stale connections to remain open, preventing new access to the database. For wait_timeout, this value should be set as low as possible without affecting availability and performance.