mysqlのタイムアウト変数を調べる
- coldplay.xixi転載
- 2020-12-22 09:37:392001ブラウズ
#mysql ビデオ チュートリアルこのコラムではタイムアウト変数について紹介しています
mysql を開いて
show variables like '%timeout%' コマンドを見ると、よくわからないけどびっくりします。結果は以下の通りです。タイムアウト関連の変数はたくさんありますが、そしてあなたは死ぬほど怖いのです。 。私の mysql の理解が非常に不十分であることがわかりました。さて、これらのタイムアウトは何を意味しますか? 私は午後を勉強し、いくつかの小さな実験をして、ようやく 1 ～ 2 つのことを理解しました。間違いがあれば、遠慮なくご連絡ください。教えてください。ああ。 <pre class="brush:php;toolbar:false">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_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| wait_timeout | 28800 |
+-----------------------------+----------+</pre>
タイムアウトでよく使用されるものをいくつか見つけて、1 つずつ分析してみましょう。
2.1 connect_timeoutconnect_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 3 ウェイ ハンドシェイクが成功すると、クライアントはブロッキングに入り、サーバーからのメッセージを待ちます。この時点で、サーバーはリクエストを処理するためにスレッドを作成します (またはスレッド プールからスレッドを取得します)。主な検証部分には、ホスト、ユーザー名、およびパスワードの検証が含まれます。ユーザーを認可するためにgrantコマンドを使用するときにホストが指定されるため、ホスト検証についてはよく知られています。ユーザー名とパスワードの認証では、サーバーはまず乱数を生成してクライアントに送信し、クライアントはその乱数とパスワードを使用して複数の SHA1 暗号化を実行し、検証のためにサーバーに送信します。合格した場合、接続ハンドシェイク プロセス全体が完了します。 (特定のハンドシェイク プロセスは後で見つけて分析します)
接続ハンドシェイク全体でさまざまなエラーが発生する可能性があることがわかります。したがって、connect_timeout 値はタイムアウト時間を指します。次の telnet コマンドを実行して簡単にテストできます。クライアントがタイムアウトして 10 秒後に戻ることがわかります。
telnet localhost 3306
タイムアウト前の mysql の接続ステータスは次のとおりです:
256 | unauthenticated user | localhost:60595 | NULL | Connect | NULL | Reading from net | NULL2.2 interactive_timeout & wait_timeout
まずは公式ドキュメントを見てみましょう。 wait_timeout と interactive_timeout はどちらも非アクティブ接続タイムアウトを指します。接続スレッドが開始されると、wait_timeout は対話モードか非対話モードかに応じて、これら 2 つの値のいずれかに設定されます。
mysql -uroot -p コマンドを実行して mysql にログインすると、wait_timeout は、interactive_timeout の値に設定されます。 wait_timeout 時間内に操作を実行しない場合、再度操作するときにタイムアウトが要求されます。これは、mysql クライアントが再接続することを意味します。 <pre class="brush:php;toolbar:false">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()).</pre>テストは次のとおりです:
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 エンジンで、列が 1 つだけあるテーブル テストを作成し、列名は a です):
mysql> CREATE TABLE `test` ( `a` int primary key) engine=innodb;
最初に 3 つの部分を挿入します。テストデータ
mysql> select * from test; +---+ | a | +---+ | 1 | | 2 | | 3 |
現在innodb_rollback_on_timeout=OFF、innodb_lock_wait_timeout=1に設定し、2つのトランザクションを開きます
##事务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; ##这里我们直接开启另外的事务(或者直接commit当前事务)，则原来的事务只会回滚第二条语句，最终结果就是test表中只剩下2和3.如果这里我们显示的rollback，则会回滚整个事务，保持1，2，3不变。
次に、innodb_rollback_on_timeout=ONの場合、同じトランザクション2がタイムアウトになりますが、この時点で新しいトランザクションのオープンを開始すると、リクエストのロックがロールバックされます。以前のようにタイムアウトしたステートメントを単にロールバックするのではなく、トランザクション全体がタイムアウトになります。
2.4 lock_wait_timeoutドキュメントでは次のように説明されています。簡単に言うと、lock_wait_timeout はメタデータ ロック待機タイムアウトです。このタイムアウト パラメータは、メタデータをロックするすべてのステートメントで使用されます. デフォルトは 1 年です。メタデータ ロックは、mysql メタデータ ロックに参加できます。myisam または innodb エンジンのテーブルであっても、トランザクションがセッションで開かれている限り、トランザクションをシリアル化できることを保証するために、操作テーブルのメタデータ ロックはこのとき、別のセッションがテーブルのメタデータを変更しようとした場合、タイムアウトになるまでブロックされます。
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
テスト例:
myisam エンジン テーブル myisam_test を使用してテストします。レコード (1,1) があるので、セッションを開き、select ステートメントを実行します。さらに、セッションを開いてから、テーブルの削除などのテーブルに対するメタデータ操作を実行すると、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。
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）或者网络出现问题时起作用。
以上がmysqlのタイムアウト変数を調べるの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。