mysqlでのタイムアウトの調査

1. タイムアウト変数についてどれくらい知っていますか?

mysql を開いて、show variables like '%timeout%' コマンドを使用して調べてみてください。わかってはいますが、実際に見てみるとショックを受けるでしょう。結果は以下の通りです。タイムアウトに関する変数がたくさんあり、死ぬほど怖くなります。 。私の mysql の理解が不十分であることがわかりました。ところで、これらのタイムアウトは何を意味するのでしょうか?ああ、教えてください。 show variables like '%timeout%'命令一看，不看不知道，一看吓一跳，结果如下面所示，这么多timeout相关变量，一下就吓尿了。。原来对mysql的了解原来是如此的不够，好了，这么些timeout究竟各自是什么意思，花了一下午去学习，做了几个小实验，总算明白了一二，如有错误，请不吝赐教啊。

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.分析

下面从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 10 seconds as of MySQL 5.0.52 and 5 seconds before that

mysql的基本原理应该是有个监听线程循环接收请求，当有请求来时，创建线程（或者从线程池中取）来处理这个请求。由于mysql连接采用TCP协议，那么之前势必是需要进行TCP三次握手的。TCP三次握手成功之后，客户端会进入阻塞，等待服务端的消息。服务端这个时候会创建一个线程(或者从线程池中取一个线程)来处理请求，主要验证部分包括host和用户名密码验证。host验证我们比较熟悉，因为在用grant命令授权用户的时候是有指定host的。用户名密码认证则是服务端先生成一个随机数发送给客户端，客户端用该随机数和密码进行多次sha1加密后发送给服务端验证。如果通过，整个连接握手过程完成。（具体握手过程后续找到资料再分析）

由此可见，整个连接握手可能会有各种可能出错。所以这个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

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()).

2. 分析

タイムアウトからよく使用されるものをいくつか見つけて、1 つずつ分析してみましょう。

2.1 connect_timeout

connect_timeout は、接続プロセス中のハンドシェイク タイムアウトを指します。5.0.52 以降のデフォルトは 10 秒で、以前のバージョンのデフォルトは 5 秒です。公式ドキュメントには次のように書かれています:

mysql> set global interactive_timeout=3; ##设置交互超时为3秒

mysql の基本原則は、リスニング スレッド ループを持たせることです

リクエストを受信すると、リクエストを処理するためにスレッドを作成します (またはスレッド プールからスレッドを取得します)。 mysql 接続では TCP プロトコルが使用されるため、TCP スリーウェイ ハンドシェイクが事前に実行されている必要があります。 TCP 3 ウェイ ハンドシェイクが成功すると、クライアントはブロッキングに入り、サーバーからのメッセージを待ちます。この時点で、サーバーはリクエストを処理するためにスレッドを作成します (またはスレッド プールからスレッドを取得します)。主な検証部分には、ホスト、ユーザー名、およびパスワードの検証が含まれます。ユーザーを認可するためにgrantコマンドを使用するときにホストが指定されるため、ホスト検証についてはよく知られています。ユーザー名とパスワードの認証では、サーバーは最初に乱数を生成してクライアントに送信し、クライアントはその乱数とパスワードを使用して複数の試行を実行しますsha1

暗号化され、検証のためにサーバーに送信されます。合格した場合、接続ハンドシェイク プロセス全体が完了します。 (具体的なハンドシェイクプロセスは後で見つけて分析します)

接続ハンドシェイク全体でさまざまなエラーが発生する可能性があることがわかります。したがって、connect_timeout 値はタイムアウト時間を指します。次の telnet コマンドを実行して簡単にテストできます。クライアントがタイムアウトし、10 秒後に戻ることがわかります。

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        |
+-----------------------------+----------+

タイムアウト前の、mysql での接続ステータス

は次のとおりです:

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.2 interactive_timeout & wait_timeout

まず公式ドキュメントを読みましょう。ドキュメントによると、wait_timeout と interactive_timeout は両方とも、接続スレッドが開始されると、対話モードかどうかに応じて、これら 2 つの値のいずれかに設定されます。または非対話型モード。 mysql -uroot -p コマンドを実行して mysql にログインすると、wait_timeout は、interactive_timeout の値に設定されます。 wait_timeout 時間内に操作を実行しない場合、再度操作するときにタイムアウトが要求されます。これは、mysql クライアントが再接続することを意味します。

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

テストは次のとおりです:

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

mysql を再入力すると、次のことがわかります:

mysql> select * from test;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |

このようにして、後で他のコマンドを実行すると、wait_timeout が interactive_timeout の値に設定されていることがわかります。 3 秒間、次のプロンプトが表示されます:

##事务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.3 innodb_lock_wait_timeout & innodb_rollback_on_timeout

まず公式ドキュメントを取り出してみましょう。この値は innodb エンジンの値です。デフォルトは 50 秒です。タイムアウトになると、現在のステートメントはロールバックされます。 innodb_rollback_on_timeout が設定されている場合、トランザクション全体がロールバックされます。それ以外の場合、トランザクションが行ロックを待機しているステートメントのみがロールバックされます。

##事务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不变。

同じ方法でテストしてみましょう (まず innodb エンジンのテーブル テストを作成します。列は 1 つだけで、列名は a です): 🎜

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

🎜最初に 3 つのテスト データを挿入します🎜

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

🎜 現在の innodb_rollback_on_timeout =OFF、innodb_lock_wait_timeout=1 に設定し、2 つのトランザクションを有効にします 🎜

DROP TABLE t;
ALTER TABLE t ...;
DROP TABLE nt;
ALTER TABLE nt ...;
LOCK TABLE t ... WRITE；

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

🎜 次に、innodb_rollback_on_timeout=ON の場合、同じトランザクション 2 がタイムアウトになりますが、この時点で新しいトランザクションを開き始めると、ロックを要求したトランザクション全体がtimeout は、以前のようにタイムアウトをロールバックするのではなく、ロールバックされます。 🎜🎜2.4 lock_wait_timeout🎜🎜 簡単に言うと、lock_wait_timeout はメタデータをロックするすべてのステートメントで使用されるタイムアウト パラメーターです。デフォルトは 1 年です。メタデータ ロックは、myisam エンジンまたは innodb エンジンのテーブルであっても、トランザクションがセッションで開かれている限り、トランザクションを確実にシリアル化できるようにするために、mysql メタデータ ロックに参加できます。このとき、別のセッションがテーブルのメタデータを変更しようとすると、タイムアウトになるまでブロックされます。 🎜

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

🎜テスト例: 🎜テストには myisam エンジン テーブル myisam_test を使用します。レコード (1,1) があります。まずセッションを開き、次に select ステートメントを実行します。さらに、セッションを開いてから、🎜delete🎜table などのテーブル メタデータ操作を実行すると、lock_wait_timeout 秒後にタイムアウトが発生するまで操作がブロックされることがわかります。 🎜

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

🎜テーブル構造を変更するためのメタデータ操作手順は次のとおりです: 🎜rrreee🎜もちろん、もう 1 つ、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。

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 中国語 Web サイトの他の関連記事を参照してください。