mysql_use_result(サーバーサイドバッファリング) vs. history list length

TL;DR

• トランザクション分離レベルがたとえREAD-UNCOMMITTEDだとしても、クエリの実行中はhistory list lengthは伸びていく

  • REPEATABLE-READ .. トランザクション中はundo recordをパージできない

  • READ-COMMITTED, READ-UNCOMMITTED .. クエリの実行中はundo recordをパージできない

• ところで、 mysql_use_result はSQLの評価が終わった後に結果セットをクライアントに送信するのを遅延させる(サーバーサイドでバッファリングさせる)ので、クライアントが結果セットを全部読みだしてくれるまでは「MySQL上ではクエリ実行中扱い」

  • というわけで、history list lengthは伸びていった

---

history list lengthが伸びるかどうかの実験用に、mysqlslapを走らせておく。

$ mysqlslap80 --auto-generate-sql --auto-generate-sql-execute-number=100000000

REPEATABLE-READ.

mysql80 12> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Query OK, 0 rows affected (0.00 sec)

mysql80 12> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql80 12> SELECT * FROM d1.t1;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
+-----+------+
1 row in set (0.00 sec)

mysql80 12> SELECT count FROM information_schema.innodb_metrics WHERE name = 'trx_rseg_history_len';
+-------+
| count |
+-------+
|  3106 |
+-------+
1 row in set (0.00 sec)

mysql80 12> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql80 12> SELECT count FROM information_schema.innodb_metrics WHERE name = 'trx_rseg_history_len';
+-------+
| count |
+-------+
|    60 |
+-------+
1 row in set (0.00 sec)

READ-COMMITTED。
「クエリが終わるまでは伸びる」をSLEEPで表現（history_list_lengthの監視は別のターミナルでやる）

mysql80 12> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Query OK, 0 rows affected (0.00 sec)

mysql80 12> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql80 12> SELECT *, SLEEP(30) FROM d1.t1;

mysql80 15> SELECT count FROM information_schema.innodb_metrics WHERE name = 'trx_rseg_history_len';  -- 別ターミナル
+-------+
| count |
+-------+
|  1022 |
+-------+
1 row in set (0.00 sec)

mysql80 12> COMMIT;  -- これを待たずにSELECTが終わった時点でhistory list lengthは減る
Query OK, 0 rows affected (0.00 sec)

READ-UNCOMMITTED

mysql80 12> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql80 12> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql80 12> SELECT *, SLEEP(30) FROM d1.t1;

mysql80 15> SELECT count FROM information_schema.innodb_metrics WHERE name = 'trx_rseg_history_len';  -- READ-UNCOMMITTEDでさえ実行中は伸びる
+-------+
| count |
+-------+
|   793 |
+-------+
1 row in set (0.00 sec)

ところで、 mysql_use_result を使ったクエリで結果セットが吸い上げられていないものは、 SHOW PROCESSLIST でも Sending to client のStateでクエリが実行中のように出力される（し、実際実行中として扱われているのであろう）

（ちなみにd1.t1のサイズが小さいと、net_bufferに収まってしまって一発で全部送りきられてしまった。ある程度のサイズがないと再現しない）

$ perl -E 'my $conn= DBI->connect("dbi:mysql:d1;mysql_socket=/usr/mysql/8.0.29/data/mysql.sock", "root", ""); my $sth= $conn->prepare("SELECT * FROM d1.t1", { mysql_use_result => 1 }); $sth->execute(); while (my $row= $sth->fetchrow_arrayref()) { sleep 100; }'

| 27 | root            | localhost | d1        | Query   |    5 | Sending to client      | SELECT * FROM d1.t1      |

mysql80 15> SELECT count FROM information_schema.innodb_metrics WHERE name = 'trx_rseg_history_len';
+-------+
| count |
+-------+
|   553 |
+-------+
1 row in set (0.00 sec)

Connector/Jとかでカーソルを使っている場合はこれにあたるかも知れない。というか当たったらしい。
誰かConnector/Jで試してみてほしい。