parameter settings for MySQL connection timeout

A few failures have occurred recently due to a database connection timeout. Troubleshooting the configuration of the next my.cnf, and finally the problem with the code design. Just go through all the timeout parameters, first check the database to see what timeouts:

mysql> show global variables like  "%timeout%"; +-----------------------------+----- -----+| variable_name                | value    |+-----------------------------+----------+| connect_timeout              | 10        | |  delayed_insert_timeout      | 300       ||  innodb_flush_log_at_timeout | 1        | |  innodb_lock_wait_timeout    | 120      | |  innodb_rollback_on_timeout  | off      | |  interactive_timeout         | 28800     | |  lock_wait_timeout           | 31536000 | |  net_read_timeout            | 30        | |  net_write_timeout           | 60        | |  rpl_stop_slave_timeout      | 31536000 | |  slave_net_timeout           | 3600      | |  wait_timeout                 | 28800    |+-----------------------------+----------+12 rows in  set  (0.00 SEC)

Let's analyze the meaning of each parameter:

Connect_timeout

Manual Description:
The number of seconds the MYSQLD server waits for a connect packet before responding with bad handshake. The default value is ten seconds as of MySQL 5.1.23 and 5 seconds before that.
Increasing the Connect_timeout value might help if clients frequently encounter errors of the form Lost connection to MYSQ L server at ' XXX ', System Error:errno.
Explanation: When getting the link, waiting for the handshake time-out, only valid at login, login success This parameter does not matter. The main purpose is to prevent the network bad when the application reconnection causes the number of connections to rise too fast, the general default.

Delayed_insert_timeout

Manual Description:
How many seconds a insert DELAYED handler thread should wait for INSERT statements before terminating.
Explanation: This is a time-out parameter designed for MyISAM insert delay, which waits for the INSERT statement before the Insert delay aborts.

Innodb_lock_wait_timeout

Manual Description:
The timeout in seconds an InnoDB transaction could wait for a row lock before giving up. The default value is seconds. A transaction that tries to access a row that's locked by another InnoDB transaction would hang for at the most this many Seco NDS before issuing the following error:

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

When A-lock wait timeout occurs, the current statement are not executed. The current transaction isn't rolled back. (to the entire transaction roll back, start the server with THE–INNODB_ROLLBACK_ON_TIMEOUT option, available as for M Ysql 5.1.15. See also section 13.6.12, "InnoDB Error Handling".)
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 it own lock table immediately and rolls back one transaction. The lock wait timeout value does not apply to such a wait.
for the built-in InnoDB, this variable can is set only at server startup. For InnoDB Plugin, it can is set at the startup or changed at runtime, and have both global and session values.
Explanation: The description is long, in short, the query time-out when a transaction encounters a lock wait. It's not like a dead lock, InnoDB. Once a deadlock is detected, the cost of the transaction is immediately rolled back, and the lock wait is a deadlock-free case where a transaction holds the lock resource required by another transaction, and the rollback is definitely the query that requested the lock.

Innodb_rollback_on_timeout

Manual Description:
In MySQL 5.1, InnoDB rolls back is the last statement on a transaction timeout by default. If–innodb_rollback_on_timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire TRANSAC tion (the same behavior as in MySQL 4.1). This variable is added in MySQL 5.1.15.
Explanation: If this parameter is closed or not present, the transaction will roll back the entire transaction if it encounters a timeout and only rolls back the last query of the transaction.

Interactive_timeout/wait_timeout

the Number of seconds The server waits for activity in an interactive connection before closing it. An interactive client is defined as a client this uses the Client_interactive option to Mysql_real_connect (). See ALSO
explanation: how long a thread with a persistent sleep state is closed. Each time the thread is used, it wakes up to the acrivity state, becomes the interactive state after the query executes, and starts the timer again. The wait_timeout differs in that it is only the thread that acts on TCP/IP and the socket link, meaning the same. General setting is 8 hours, The general website is visited during the day, and it is usually more than 8 hours from night to morning, so the visit will be the problem.

Net_read_timeout/net_write_timeout

Manual Description:
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/IPconnections, not to connections made through Unix socket files, named pipes, or shared memory. When the server was reading from the client, Net_read_timeout was the timeout value controlling when to abort. When the server was writing to the client, Net_write_timeout was the timeout value controlling when to abort. See also Slave_net_timeout.
On Linux, the No_alarm build flag affects timeout behavior as indicated in the description of the Net_retry_count System V Ariable.
Explanation: This parameter is only valid for TCP/IP links, which is the time-out for the database to wait for the receiving client to send the network packet and send the network packet to the client, which is a valid parameter for the line friend in the activity state.

Slave_net_timeout

Manual Description:
The number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the R EAD, 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–maste R-connect-retry option, and the number of reconnection attempts is limited by the–master-retry-count option. The default is 3600 seconds (one hour).
Explanation: This is slave to determine whether the host hangs out of the timeout setting, in the set time still not get the master response to the master hung off

This article is from the creator think blog, so be sure to keep this source http://strongit.blog.51cto.com/10020534/1713504

parameter settings for MySQL connection timeout