mysql timeoutç¥å¤å°
ââââââbyç³é
æé¥
1.timeoutåéç¥å¤å°
æå¼mysqlï¼ç¨
show variables like '%timeout%'
å½ä»¤ä¸çï¼ä¸çä¸ç¥éï¼ä¸çåä¸è·³ï¼ç»æå¦ä¸é¢æ示ï¼è¿ä¹å¤timeoutç¸å ³åéï¼ä¸ä¸å°±åå°¿äºããåæ¥å¯¹mysqlçäºè§£åæ¥æ¯å¦æ¤çä¸å¤ï¼å¥½äºï¼è¿ä¹äºtimeout究ç«åèªæ¯ä»ä¹ææï¼è±äºä¸ä¸åå»å¦ä¹ ï¼åäºå 个å°å®éªï¼æ»ç®æç½äºä¸äºï¼å¦æé误ï¼è¯·ä¸åèµæåã
mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | |
| delayed_insert_timeout | |
| innodb_flush_log_at_timeout | |
| innodb_lock_wait_timeout | |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | |
| lock_wait_timeout | |
| net_read_timeout | |
| net_write_timeout | |
| rpl_stop_slave_timeout | |
| slave_net_timeout | |
| wait_timeout | |
+-----------------------------+----------+
2.åæ
ä¸é¢ä»timeoutéé¢æ¾äºæ¯è¾å¸¸ç¨çåºæ¥é个åæä¸ã
2.1 connect_timeout
connect_timeoutæçæ¯è¿æ¥è¿ç¨ä¸æ¡æçè¶ æ¶æ¶é´ï¼å¨5.0.52以åé»è®¤ä¸º10ç§ï¼ä¹åçæ¬é»è®¤æ¯5ç§ãå®æ¹ææ¡£æ¯è¿æ ·è¯´çï¼
connect_timeout: The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake. The default value is seconds as of MySQL and seconds before that
mysqlçåºæ¬åçåºè¯¥æ¯æ个çå¬çº¿ç¨å¾ªç¯æ¥æ¶è¯·æ±ï¼å½æ请æ±æ¥æ¶ï¼å建线ç¨ï¼æè ä»çº¿ç¨æ± ä¸åï¼æ¥å¤çè¿ä¸ªè¯·æ±ãç±äºmysqlè¿æ¥éç¨TCPåè®®ï¼é£ä¹ä¹åå¿å¿ æ¯éè¦è¿è¡TCPä¸æ¬¡æ¡æçãTCPä¸æ¬¡æ¡ææåä¹åï¼å®¢æ·ç«¯ä¼è¿å ¥é»å¡ï¼çå¾ æå¡ç«¯çæ¶æ¯ãæå¡ç«¯è¿ä¸ªæ¶åä¼å建ä¸ä¸ªçº¿ç¨(æè ä»çº¿ç¨æ± ä¸åä¸ä¸ªçº¿ç¨)æ¥å¤ç请æ±ï¼ä¸»è¦éªè¯é¨åå æ¬hoståç¨æ·åå¯ç éªè¯ãhostéªè¯æ们æ¯è¾çæï¼å 为å¨ç¨grantå½ä»¤ææç¨æ·çæ¶åæ¯ææå®hostçãç¨æ·åå¯ç 认è¯åæ¯æå¡ç«¯å çæä¸ä¸ªéæºæ°åéç»å®¢æ·ç«¯ï¼å®¢æ·ç«¯ç¨è¯¥éæºæ°åå¯ç è¿è¡å¤æ¬¡sha1å å¯ååéç»æå¡ç«¯éªè¯ãå¦æéè¿ï¼æ´ä¸ªè¿æ¥æ¡æè¿ç¨å®æãï¼å ·ä½æ¡æè¿ç¨åç»æ¾å°èµæååæï¼
ç±æ¤å¯è§ï¼æ´ä¸ªè¿æ¥æ¡æå¯è½ä¼æåç§å¯è½åºéãæ以è¿ä¸ªconnect_timeoutå¼å°±æ¯æè¿ä¸ªè¶ æ¶æ¶é´äºãå¯ä»¥ç®åæµè¯ä¸ï¼è¿è¡ä¸é¢çtelnetå½ä»¤ä¼åç°å®¢æ·ç«¯ä¼å¨10ç§åè¶ æ¶è¿åã
telnet localhost 3306
å¨è¶ æ¶ä¹åmysqlä¸è¯¥è¿æ¥ç¶æå¦ä¸ï¼
2.2 interactive_timeout & wait_timeout
è¿æ¯å çå®æ¹ææ¡£ï¼ä»ææ¡£ä¸æ¥çwait_timeoutåinteractive_timeouté½æ¯æä¸æ´»è·çè¿æ¥è¶ æ¶æ¶é´ï¼è¿æ¥çº¿ç¨å¯å¨çæ¶åwait_timeoutä¼æ ¹æ®æ¯äº¤äºæ¨¡å¼è¿æ¯é交äºæ¨¡å¼è¢«è®¾ç½®ä¸ºè¿ä¸¤ä¸ªå¼ä¸çä¸ä¸ªãå¦ææ们è¿è¡
mysql -uroot -p
å½ä»¤ç»éå°mysqlï¼wait_timeoutå°±ä¼è¢«è®¾ç½®ä¸ºinteractive_timeoutçå¼ãå¦ææ们å¨wait_timeoutæ¶é´å 没æè¿è¡ä»»ä½æä½ï¼é£ä¹å次æä½çæ¶åå°±ä¼æç¤ºè¶ æ¶ï¼è¿æ¯mysql clientä¼éæ°è¿æ¥ã
The number of seconds the server waits for activity on a noninteractive connection before closing it.
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()).
æµè¯å¦ä¸ï¼
éæ°è¿å ¥mysqlï¼è¿æ¶åå¯ä»¥çå°ï¼
mysql> show variables like '%timeout%'; ##wait_timeoutå·²ç»è¢«è®¾ç½®ä¸ºç§
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | |
| delayed_insert_timeout | |
| innodb_flush_log_at_timeout | |
| innodb_lock_wait_timeout | |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | |
| lock_wait_timeout | |
| net_read_timeout | |
| net_write_timeout | |
| rpl_stop_slave_timeout | |
| slave_net_timeout | |
| wait_timeout | |
+-----------------------------+----------+
å¯ä»¥çå°wait_timeout被设置为äºinteractive_timeoutçå¼ï¼è¿æ ·ï¼æ们3ç§ååæ§è¡å ¶ä»å½ä»¤ï¼ä¼æ示å¦ä¸ï¼
mysql> show variables like '%timeout%';
ERROR (HY000): MySQL server has gone away ##è¶
æ¶éè¿
No connection. Trying to reconnect...
Connection id:
Current database: *** NONE ***
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | |
| delayed_insert_timeout | |
| innodb_flush_log_at_timeout | |
| innodb_lock_wait_timeout | |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | |
| lock_wait_timeout | |
| net_read_timeout | |
| net_write_timeout | |
| rpl_stop_slave_timeout | |
| slave_net_timeout | |
| wait_timeout | |
+-----------------------------+----------+
2.3 innodb_lock_wait_timeout & innodb_rollback_on_timeout
è¿æ¯å ç¥åºå®æ¹ææ¡£ï¼ä»ææ¡£ä¸çï¼è¿ä¸ªå¼æ¯é对innodbå¼æçï¼æ¯innodbä¸è¡éççå¾ è¶ æ¶æ¶é´ï¼é»è®¤ä¸º50ç§ãå¦æè¶ æ¶ï¼åå½åè¯å¥ä¼åæ»ãå¦æ设置äºinnodb_rollback_on_timeoutï¼åä¼åæ»æ´ä¸ªäºå¡ï¼å¦åï¼åªåæ»äºå¡çå¾ è¡éçè¿ä¸ªè¯å¥ã
The length of time in seconds an InnoDB transaction waits for a row lock before giving up. The default value is seconds. A transaction that tries to access a row that is locked by another InnoDB transaction waits at most this many seconds for write access to the row before issuing the following error:
ERROR (HY000): Lock wait timeout exceeded; try restarting transaction
åæ ·æ¥æµè¯ä¸(å å建ä¸ä¸ªinnodbå¼æç表testï¼åªæä¸åï¼åå为a)ï¼
é¦å æå ¥ä¸æ¡æµè¯æ°æ®
mysql> select * from test;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
å½åinnodb_rollback_on_timeout=OFFï¼è®¾ç½®innodb_lock_wait_timeout=1ï¼æ们å¼å¯ä¸¤ä¸ªäºå¡
##äºå¡1 å è¡é
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test where a=2 for update;
+---+
| a |
+---+
| 2 |
+---+
1 row in set (0.01 sec)
##äºå¡2ï¼è¯·æ±è¡é
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from test where a=1;
Query OK, 1 row affected (0.00 sec)
mysql> delete from test where a=2; ##请æ±è¡éè¶
æ¶
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from test;
+---+
| a |
+---+
| 2 |
| 3 |
+---+
2 rows in set (0.00 sec)
mysql> begin; ##è¿éæ们ç´æ¥å¼å¯å¦å¤çäºå¡ï¼ååæ¥çäºå¡åªä¼åæ»ç¬¬äºæ¡è¯å¥ï¼æç»ç»æå°±æ¯test表ä¸åªå©ä¸2å3.å¦æè¿éæ们æ¾ç¤ºçrollbackï¼åä¼åæ»æ´ä¸ªäºå¡ï¼ä¿æ1ï¼2ï¼3ä¸åã
é£ä¹å¦æinnodb_rollback_on_timeout=ON,åæ ·äºå¡2ä¼è¶ æ¶ï¼ä½æ¯è¿ä¸ªæ¶åå¦ææ们beginå¼å¯æ°çäºå¡ï¼é£ä¹ä¼åæ»è¯·æ±éè¶ æ¶çæ´ä¸ªäºå¡ï¼èä¸æ¯ååé¢é£æ ·åªåæ»äºè¶ æ¶çé£æ¡è¯å¥ã
2.4 lock_wait_timeout
ææ¡£ä¸æè¿°å¦ä¸ï¼ç®å说æ¥lock_wait_timeoutæ¯å æ°æ®éçå¾ è¶ æ¶ï¼ä»»æéå æ°æ®çè¯å¥é½ä¼ç¨å°è¿ä¸ªè¶ æ¶åæ°ï¼é»è®¤ä¸ºä¸å¹´ãå æ°æ®éå¯ä»¥åå mysql metadata lockï¼ä¸ºäºä¿è¯äºå¡å¯ä¸²è¡åï¼ä¸ç®¡æ¯myisamè¿æ¯innodbå¼æç表ï¼åªè¦æ¯å¼å§ä¸ä¸ªäºå¡ï¼å°±ä¼è·åæä½è¡¨çå æ°æ®éï¼è¿æ¶åå¦æå¦ä¸ä¸ªäºå¡è¦å¯¹è¡¨çå æ°æ®è¿è¡ä¿®æ¹ï¼åä¼é»å¡ç´å°è¶ æ¶ã
This variable specifies the timeout in seconds for attempts to acquire metadata locks. The permissible values range from to ( year). The default is
This timeout applies to all statements that use metadata locks. These include DML and DDL operations on tables, views, stored procedures, and stored functions, as well as LOCK TABLES, FLUSH TABLES WITH READ LOCK, and HANDLER statements
æµè¯ä¾åï¼
æ们ç¨ä¸ä¸ªmyisamå¼æç表myisam_testæ¥æµè¯ãå ¶ä¸æä¸æ¡è®°å½(1,1)ï¼ç°å¨æ们å å¼å¯ä¸ä¸ªsessionï¼ç¶åæ§è¡ä¸ä¸ªselectè¯å¥ãå¦å¤æå¼ä¸ä¸ªsessionï¼ç¶åæ§è¡è¡¨çå æ°æ®æä½ï¼å¦å é¤è¡¨ï¼ä¼åç°æä½é»å¡ç´å°lock_wait_timeoutç§åæç¤ºè¶ æ¶ã
##第ä¸ä¸ªsessionï¼è·åmetadata lock
mysql> show create table myisam_test;
-----------------------------------------------------------+
| Table | Create Table |
+-----------------------------------------------------------
| myisam_test | CREATE TABLE `myisam_test` (
`i` int(11) NOT NULL,
`j` int(11) DEFAULT NULL,
PRIMARY KEY (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from myisam_test;
+---+------+
| i | j |
+---+------+
| 2 | 1 |
+---+------+
1 row in set (0.00 sec)
##å¦ä¸ä¸ªsessionï¼å é¤è¡¨æ示è¶
æ¶
mysql> drop table myisam_test;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
å ¶ä¸æ´æ¹è¡¨ç»æçå æ°æ®æä½æ令æå¦ä¸è¿äºï¼
DROP TABLE t;
ALTER TABLE t ...;
DROP TABLE nt;
ALTER TABLE nt ...;
LOCK TABLE t ... WRITEï¼
å½ç¶ï¼å¤è¯´ä¸å¥ï¼å¯¹äºmyisam表çå é以å并åæå ¥çï¼è¿ç¯å客myisam表éé常详ç»ï¼æå ´è¶£çå¯ä»¥ççã
2.5 net_read_timeout & net_write_timeout
ææ¡£ä¸æè¿°å¦ä¸ï¼å°±æ¯è¯´è¿ä¸¤ä¸ªåæ°å¨ç½ç»æ¡ä»¶ä¸å¥½çæ åµä¸èµ·ä½ç¨ãæ¯å¦æå¨å®¢æ·ç«¯ç¨load data infileçæ¹å¼å¯¼å ¥å¾å¤§çä¸ä¸ªæ件å°æ°æ®åºä¸ï¼ç¶åä¸éç¨iptablesç¦ç¨æmysqlç3306端å£ï¼è¿ä¸ªæ¶åæå¡å¨ç«¯è¯¥è¿æ¥ç¶ææ¯reading from netï¼å¨çå¾ net_read_timeoutåå ³é该è¿æ¥ãåçï¼å¨ç¨åºéé¢æ¥è¯¢ä¸ä¸ªå¾å¤§ç表æ¶ï¼å¨æ¥è¯¢è¿ç¨ä¸åæ ·ç¦ç¨æ端å£ï¼å¶é ç½ç»ä¸éçæ åµï¼è¿æ ·è¯¥è¿æ¥ç¶ææ¯writing to netï¼ç¶åå¨net_write_timeoutåå ³é该è¿æ¥ãslave_net_timeout类似ã
The number of seconds to wait for more data from a connection before aborting the read. 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
æµè¯ï¼
æå建ä¸ä¸ª120Mçæ°æ®æ件data.txtãç¶åç»éå°mysqlã
å¯¼å ¥è¿ç¨è®¾ç½®iptablesç¦ç¨3306端å£ã
iptables -A INPUT -p tcp --dport -j DROP
iptables -A OUTPUT -p tcp --sport -j DROP
å¯ä»¥çå°è¿æ¥ç¶æ为reading from netï¼ç¶åç»è¿net_read_timeoutç§åå ³éã
3.æ»ç»
ç»è¿å 个å®éªå¯ä»¥åç°ï¼connect_timeoutå¨æ¡æ认è¯é¶æ®µï¼authenticateï¼èµ·ä½ç¨ï¼interactive_timeout åwait_timeoutå¨è¿æ¥ç©ºé²é¶æ®µï¼sleepï¼èµ·ä½ç¨ï¼ènet_read_timeoutånet_write_timeoutåæ¯å¨è¿æ¥ç¹å¿é¶æ®µï¼queryï¼æè ç½ç»åºç°é®é¢æ¶èµ·ä½ç¨ã
4.åèèµæ
- mysql timeoutè°ç ä¸å®ä¾
- mysql timeout解æ
- mysql system variables