mysql에서 시간 초과 탐색

1. 타임아웃 변수에 대해 얼마나 알고 계시나요?

mysql을 열고 show variables like '%timeout%' 명령을 사용하여 살펴보세요. 결과를 보면 아래와 같습니다. 타임아웃 관련 변수가 너무 많아서 오줌을 싸더군요. . mysql에 대한 이해가 너무 부족한 것 같습니다. 그런데, 이러한 시간 초과는 무엇을 의미합니까? 오후에 몇 가지 작은 실험을 해보고 마침내 한두 가지 실수가 있으면 언제든지 알려주십시오. 아아.

mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_ti、eout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 3600     |
| wait_timeout                | 28800    |
+-----------------------------+----------+

2. 분석

타임아웃에서 자주 사용되는 것들을 찾아 하나씩 분석해 보겠습니다.

2.1 connect_timeout

connect_timeout은 연결 과정 중 Handshake 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 10 seconds as of MySQL 5.0.52 and 5 seconds before that

mysql의 기본 원칙은 요청을 수신하기 위한 수신 스레드 루프 를 갖는 것입니다. 요청이 오면 스레드를 생성합니다(또는 스레드에서 가져옵니다. 스레드 풀) 이 요청을 처리합니다. mysql 연결은 TCP 프로토콜을 사용하므로 이전에 TCP 3방향 핸드셰이크가 수행되어야 합니다. TCP 3방향 핸드셰이크가 성공한 후 클라이언트는 차단에 들어가 서버의 메시지를 기다립니다. 이때 서버는 요청을 처리하기 위해 스레드를 생성하거나 스레드 풀에서 스레드를 가져옵니다. 주요 확인 부분에는 호스트와 사용자 이름 및 비밀번호 확인이 포함됩니다. 사용자에게 권한을 부여하기 위해 grant 명령을 사용할 때 호스트가 지정되므로 우리는 호스트 확인에 익숙합니다. 사용자 이름과 비밀번호 인증을 위해 서버는 먼저 난수를 생성하여 클라이언트에 보냅니다. 클라이언트는 난수와 비밀번호를 사용하여 여러 sha1 암호화를 수행한 다음 확인을 위해 서버에 보냅니다. 통과하면 전체 연결 핸드셰이크 프로세스가 완료됩니다. (구체적인 Handshake 과정은 추후 찾아 분석해보겠습니다.)

전체 Connection Handshake에서 다양한 오류 가능성이 있을 수 있음을 알 수 있습니다. 따라서 connect_timeout 값은 시간 초과 시간을 나타냅니다. 다음 telnet 명령을 실행하여 간단히 테스트할 수 있으며 클라이언트가 시간 초과되고 10초 후에 반환되는 것을 확인할 수 있습니다.

telnet localhost 3306

시간 초과 전 mysql의 상태 연결은 다음과 같습니다.

256 | unauthenticated user | localhost:60595 | NULL | Connect | NULL | Reading from net | NULL

2.2 Interactive_timeout & wait_timeout

먼저 공식 문서를 살펴보겠습니다. 문서에서 wait_timeout과 Interactive_timeout은 모두 비활성 연결 시간 제한을 참조합니다. 연결 스레드가 시작되면 wait_timeout은 대화형 모드인지 비대화형 모드인지에 따라 이 두 값 중 하나로 설정됩니다. mysql -uroot -p 명령을 실행하여 mysql에 로그인하면 wait_timeout이 Interactive_timeout 값으로 설정됩니다. wait_timeout 시간 내에 아무 작업도 수행하지 않으면 다시 작업할 때 시간 초과 메시지가 표시되며 이는 mysql 클라이언트가 다시 연결된다는 의미입니다.

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> set global interactive_timeout=3; ##设置交互超时为3秒

mysql을 다시 입력하면

mysql> show variables like '%timeout%'; ##wait_timeout已经被设置为3秒
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 3        |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 3        |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 3600     |
| wait_timeout                | 3        |
+-----------------------------+----------+

wait_timeout이 Interactive_timeout 값으로 설정된 것을 확인할 수 있습니다. 이런 식으로 3초 후에 다른 명령을 실행하면 다음 프롬프트가 나타납니다.

mysql> show variables like '%timeout%';
ERROR 2006 (HY000): MySQL server has gone away  ##超时重连
No connection. Trying to reconnect...
Connection id:    50
Current database: *** NONE ***

+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 3        |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 3        |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 3600     |
| wait_timeout                | 3        |
+-----------------------------+----------+

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 50 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 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

같은 방식으로 테스트해 보겠습니다(먼저 innodb 엔진에서 하나의 열만 포함하고 열 이름은 a인 테이블 테스트를 생성합니다.):

mysql> CREATE TABLE `test` ( `a` int primary key) engine=innodb;

먼저 세 개의 테스트 조각을 삽입합니다. data

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)

rrree

그런 다음 innodb_rollback_on_timeout=ON이면 동일한 트랜잭션 2가 시간 초과되지만 이때 시작하면 새 트랜잭션을 열려면 요청 잠금이 롤백됩니다. 이전처럼 시간 초과된 문을 롤백하는 대신 전체 트랜잭션 시간이 초과됩니다. <… . 메타데이터 잠금은 mysql 메타데이터 잠금에 참여할 수 있습니다. 트랜잭션이 직렬화될 수 있도록 하기 위해 myisam 또는 innodb 엔진의 테이블이든 트랜잭션이 세션에서 열려 있는 한 작업 테이블의 메타데이터 잠금이 유지됩니다. 이때 다른 세션에서 해당 테이블의 메타데이터를 수정하려는 경우 타임아웃될 때까지 차단됩니다.

##事务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; ##
这里我们直接开启另外的事务(或者直接commit当前事务)，则原来的事务只会回滚第二条语句，最终结果就是test表中只剩下2和3.如果这里我们显示的rollback，则会回滚整个事务，保持1，2，3不变。

테스트 예:

myisam 엔진 테이블 myisam_test를 사용하여 테스트합니다. 레코드(1,1)가 있습니다. 이제 세션을 열고 select 문을 실행합니다. 또한 세션을 연 다음

테이블 삭제와 같은 테이블에 대한 메타데이터 작업을 수행하면 lock_wait_timeout 초 후 시간 초과가 발생할 때까지 작업이 차단되는 것을 확인할 수 있습니다.

This variable specifies the timeout in seconds for attempts to acquire metadata locks. The permissible values range from 1 to 31536000 (1 year). The default is 31536000.

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

테이블 구조 변경을 위한 메타데이터 작업 지침은 다음과 같습니다.

##第一个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

물론 또 하나, myisam 테이블 잠금 및 동시 삽입에 대해서는 이 블로그 myisam table lock이 매우 상세하게 설명되어 있습니다. , 관심이 있으시면 확인해 보세요.

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。

mysql -uroot -h 127.0.0.1 -P 3306 --local-infile=1

导入过程设置iptables禁用3306端口。

iptables -A INPUT -p tcp --dport 3306 -j DROP
iptables -A OUTPUT -p tcp --sport 3306 -j DROP

可以看到连接状态为reading from net，然后经过net_read_timeout秒后关闭。

3.总结

经过几个实验可以发现，connect_timeout在握手认证阶段（authenticate）起作用，interactive_timeout 和wait_timeout在连接空闲阶段（sleep）起作用，而net_read_timeout和net_write_timeout则是在连接繁忙阶段（query）或者网络出现问题时起作用。

【相关推荐】

1. 免费mysql在线视频教程

2. MySQL最新手册教程

3. 数据库设计那些事

위 내용은 mysql에서 시간 초과 탐색의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!