mysql連接超時的參數設置
最近系統因為數據庫連接超時的問題，出現了幾次故障。排查了下my.cnf的配置問題，最後是代碼設計有問題。正好就把所有的timeout參數都理一遍，首先數據庫裡查一下看有哪些超時：
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)
我們來分析下各個參數的意義：
connect_timeout
手冊描述:
The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake. The default value is 10 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 MySQL server at ‘XXX’, system error: errno.
解釋：在獲取鏈接時，等待握手的超時時間，只在登錄時有效，登錄成功這個參數就不管事了。主要是為了防止網絡不佳時應用重連導致連接數漲太快，一般默認即可。
delayed_insert_timeout
手冊描述：
How many seconds an INSERT DELAYED handler thread should wait for INSERT statements before terminating.
解釋：這是為MyISAM INSERT DELAY設計的超時參數，在INSERT DELAY中止前等待INSERT語句的時間。
innodb_lock_wait_timeout
手冊描述：
The timeout in seconds an InnoDB transaction may wait for a row lock before giving up. 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:
1
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
When a lock wait timeout occurs, the current statement is not executed. The current transaction is not rolled back. (To have the entire transaction roll back, start the server with the –innodb_rollback_on_timeout option, available as of MySQL 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 its 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 be set only at server startup. For InnoDB Plugin, it can be set at startup or changed at runtime, and has both global and session values.
解釋：描述很長，簡而言之，就是事務遇到鎖等待時的Query超時時間。跟死鎖不一樣，InnoDB一旦檢測到死鎖立刻就會回滾代價小的那個事務，鎖等待是沒有死鎖的情況下一個事務持有另一個事務需要的鎖資源，被回滾的肯定是請求鎖的那個Query。
innodb_rollback_on_timeout
手冊描述：
In MySQL 5.1, InnoDB rolls back only 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 transaction (the same behavior as in MySQL 4.1). This variable was added in MySQL 5.1.15.
解釋：這個參數關閉或不存在的話遇到超時只回滾事務最後一個Query，打開的話事務遇到超時就回滾整個事務。
interactive_timeout/wait_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(). See also
解釋：一個持續SLEEP狀態的線程多久被關閉。線程每次被使用都會被喚醒為acrivity狀態，執行完Query後成為interactive狀態，重新開始計時。wait_timeout不同在於只作用於TCP/IP和Socket鏈接的線程，意義是一樣的。一般設置是8小時，一般網站白天都有人訪問，從夜裡到早上一般都會超過8小時，所以再來訪問就會這個問題。
net_read_timeout / net_write_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. When the server is writing to the client, net_write_timeout is 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 variable.
解釋：這個參數只對TCP/IP鏈接有效，分別是數據庫等待接收客戶端發送網絡包和發送網絡包給客戶端的超時時間，這是在Activity狀態下的線程才有效的參數
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 (one hour).
解釋：這是Slave判斷主機是否掛掉的超時設置，在設定時間內依然沒有獲取到Master的回應就人為Master掛掉了
