MySQLへのJDBC接続で、とあるバグを踏むまでの話 -（１）「max_allowed_packet」の基本的な働き

3エントリに渡ってお送りいたします

（１）「max_allowed_packet」の基本的な働き　<- イマココ
（２）「Connector/J」は結構賢い
（３）古いmariadb-client-javaにて、ヤバイ動き

概要：max_allowed_packetとは

平たく言うと、MySQLのクライアントーサーバー間の通信のパケットサイズの最大値の設定で、1回のリクエストでここの値より長いリクエストを送り込めない、というものです。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.4 サーバーシステム変数

どんなときに意識する？

MySQL 5.6以降のデフォルトは4MBなので、よほどのことがない限り、デフォルトのままで問題ない、と考えています。

で、「よほどのこと」の例は、

BLOBで大きなデータを格納したい。
大量のレコードをバルクインサートでインサートを高速投入したい。

あたりでしょうか。

動的変更が可能（権限さえあれば、サーバー再起動がいらない）ですが、グローバルレベル変数で、セッション変数は読み取り専用、さらに、ログイン時にグローバルの設定値をコピーしてくる性質のものですので、常時実行するようなプログラムの中で必要時だけ引き上げる、というケースは少ないかと思います。

また、各セッション毎に適用される「スレッドバッファ」に該当するものなので、「max_allowed_packet＊コネクション数」のメモリをサーバーサイドで消費する可能性があり、闇雲に引き上げるとサーバがあでメモリ不足を起こします。*1

なので、メンテナンスに際して引き上げることによって作業が高速化する、といったケースで一時的に変更する以外は、設計段階である程度計算して見積もりを行ったあとは固定しておく類のもの、と考えています。

max_allowed_packetのエラーを起こしてみる

実験用のテーブルとSQLの準備

実験用のスキーマ（データベース）に、雑にテーブルを作ります。

create database bulk_test;
use bulk_test;
CREATE TABLE `loadtest` (
  `id` int(11) NOT NULL,
  `data1` varchar(1000) DEFAULT NULL,
  `data2` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

このテーブルに対して、雑にバルクインサートの量を調整して、いろいろ実験していきます。

サーバーシステム変数「max_allowed_packet」の変更

まずはデフォルトの確認。

mysql> select @@global.max_allowed_packet, @@session.max_allowed_packet;
+-----------------------------+------------------------------+
| @@global.max_allowed_packet | @@session.max_allowed_packet |
+-----------------------------+------------------------------+
|                     4194304 |                      4194304 |
+-----------------------------+------------------------------+
1 row in set (0.00 sec)

エラーを引き起こしやすいように、サイズを激しく小さくしてみます。
max_allowed_packetの最小値は1024バイトです。
my.cnfに書いて再起動でもいいですが、とりあえず動的変更で。

mysql> set @@global.max_allowed_packet = 1024;
Query OK, 0 rows affected (0.00 sec)

確認すると、グローバル変数側は更新されますが、セッション変数は変更されません。

mysql> select @@global.max_allowed_packet, @@session.max_allowed_packet;
+-----------------------------+------------------------------+
| @@global.max_allowed_packet | @@session.max_allowed_packet |
+-----------------------------+------------------------------+
|                        1024 |                      4194304 |
+-----------------------------+------------------------------+
1 row in set (0.00 sec)

再接続して、確認します。

mysql> connect;
Connection id:    10
Current database: *** NONE ***

mysql> select @@global.max_allowed_packet, @@session.max_allowed_packet;
+-----------------------------+------------------------------+
| @@global.max_allowed_packet | @@session.max_allowed_packet |
+-----------------------------+------------------------------+
|                        1024 |                         1024 |
+-----------------------------+------------------------------+
1 row in set (0.00 sec)

セッション側にも適用されていることを確認できました。

mysqlクライアントから投入試験

SQL文が1024バイト以上になるように、Insert文を作って投入します。
ポイントは、REPEAT関数ではダメで、あくまでSQL文自体の長さを稼ぐ、というところです。

mysql> insert into loadtest (id, data1,data2) values (1, 'aaaa・・・記述省略、２列あるvarchar列に1000個の「a」を並べて投入・・・);
Query OK, 1 row affected (0.00 sec)

2000 byte強のデータを送り込んでいるのに、通った！?

net_buffer_lengthのいたずら

ドキュメントをしっかり読むと、このように書いてあります。

max_allowed_packet

パケットメッセージバッファーは net_buffer_length バイトに初期化されますが、必要に応じて max_allowed_packet バイトまで大きくできます。この値はデフォルトでは小さいため、大きい (正しくない可能性がある) パケットをキャッチできません。
https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html#sysvar_max_allowed_packet

net_buffer_length

各クライアントスレッドは、接続バッファーおよび結果バッファーに関連付けられています。両者は net_buffer_length で与えられたサイズで開始されますが、必要に応じて、max_allowed_packet バイトまで動的に拡大できます。結果バッファーは、各 SQL ステートメントのあとで net_buffer_length に縮小されます。
この変数は通常は変更しませんが、メモリーが非常に少ない場合、クライアントによって送信される予想されるステートメントの長さに設定できます。ステートメントがこの長さを超えた場合、接続バッファーは自動的に拡大されます。net_buffer_length の最大値は 1M バイトに設定できます。
https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html#sysvar_net_buffer_length

どうなっているかというと、先程max_allowed_packetだけを変更したので、net_buffer_lengthにはデフォルト値が入っています。

mysql> select @@session.max_allowed_packet, @@session.net_buffer_length;
+------------------------------+-----------------------------+
| @@session.max_allowed_packet | @@session.net_buffer_length |
+------------------------------+-----------------------------+
|                         1024 |                       16384 |
+------------------------------+-----------------------------+
1 row in set (0.00 sec)

16KBですね。

というわけで、バルクインサートで、先程の例の通り、2列のvarchar列にフル桁で文字列を埋める処理を、1行～9行をインサートするファイル「bulk1.sql～bulk9.sql」を用意して、sourceコマンドを実行して試してみます。

ll bulk*
-rw-r--r-- 1 root root  2059  9月 24 22:44 2018 bulk1.sql
-rw-r--r-- 1 root root  4072  9月 24 22:44 2018 bulk2.sql
-rw-r--r-- 1 root root  6085  9月 24 22:31 2018 bulk3.sql
-rw-r--r-- 1 root root  8098  9月 24 22:45 2018 bulk4.sql
-rw-r--r-- 1 root root 10111  9月 24 22:45 2018 bulk5.sql
-rw-r--r-- 1 root root 12124  9月 24 22:46 2018 bulk6.sql
-rw-r--r-- 1 root root 14137  9月 24 22:47 2018 bulk7.sql
-rw-r--r-- 1 root root 16150  9月 24 22:47 2018 bulk8.sql
-rw-r--r-- 1 root root 18163  9月 24 22:35 2018 bulk9.sql

mysql -u root -p -D bulk_test

mysql> truncate table loadtest;
source ./bulk1.sql
Query OK, 0 rows affected (0.03 sec)

mysql> source ./bulk1.sql
Query OK, 1 row affected (0.00 sec)

mysql> truncate table loadtest;
Query OK, 0 rows affected (0.01 sec)

mysql> source ./bulk2.sql
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> truncate table loadtest;
Query OK, 0 rows affected (0.01 sec)

mysql> source ./bulk3.sql
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> truncate table loadtest;
Query OK, 0 rows affected (0.01 sec)

mysql> source ./bulk4.sql
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> truncate table loadtest;
Query OK, 0 rows affected (0.01 sec)

mysql> source ./bulk5.sql
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>
mysql> truncate table loadtest;
Query OK, 0 rows affected (0.01 sec)

mysql> source ./bulk6.sql
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> truncate table loadtest;
Query OK, 0 rows affected (0.01 sec)

mysql> source ./bulk7.sql
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql>
mysql> truncate table loadtest;
Query OK, 0 rows affected (0.01 sec)

mysql> source ./bulk8.sql
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> truncate table loadtest;
Query OK, 0 rows affected (0.01 sec)

mysql> source ./bulk9.sql
ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes

9行分のバルクインサート、「18163 byteのインサート文」を実行したところで、max_allowed_packet起因のエラーが出ました。

max_allowed_packetではなく、net_buffer_lengthの16KBに引っ張られた、と考えられます。

net_buffer_length＝max_alloewd_packetでやってみる

では、net_buffer_lengthも小さくしてみましょう。

mysql> set @@global.net_buffer_length=1024;
Query OK, 0 rows affected (0.00 sec)

mysql> connect;
Connection id:    51
Current database: bulk_test

mysql> select @@session.max_allowed_packet, @@session.net_buffer_length;
+------------------------------+-----------------------------+
| @@session.max_allowed_packet | @@session.net_buffer_length |
+------------------------------+-----------------------------+
|                         1024 |                        1024 |
+------------------------------+-----------------------------+
1 row in set (0.00 sec)

ここに、先程のバルクインサート用SQLを順に入れていきます。

mysql> truncate table loadtest;
source ./bulk1.sql
Query OK, 0 rows affected (0.02 sec)

mysql> source ./bulk1.sql
ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes

1つ目、2000 byte以上のインサートできっちり落ちました。

なお、このbulk1.sqlをベースに、「インサート文全体で1024 byte　/　1025byteなファイル」を用意したところ、以下のようになります。

ll bulk_102*
-rw-r--r-- 1 root root 1024  9月 24 23:26 2018 bulk_1024.sql
-rw-r--r-- 1 root root 1025  9月 24 23:27 2018 bulk_1025.sql

mysql -u root -p --max_allowed_packet=1024 --net_buffer_length=1024 -D bulk_test

mysql>  select @@session.max_allowed_packet, @@session.net_buffer_length;
+------------------------------+-----------------------------+
| @@session.max_allowed_packet | @@session.net_buffer_length |
+------------------------------+-----------------------------+
|                         1024 |                        1024 |
+------------------------------+-----------------------------+
1 row in set (0.00 sec)

mysql> source ./bulk_1024.sql
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> truncate table loadtest;
Query OK, 0 rows affected (0.00 sec)

mysql> source ./bulk_1025.sql
ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes

net_buffer_length < max_allowed_packetも試す

net_buffer_length：1024 byte
max_allowed_packet：4096 byte

の条件でやってみます。

mysql> set @@global.net_buffer_length=1024;
Query OK, 0 rows affected (0.00 sec)

mysql> set @@global.max_allowed_packet=4096;
Query OK, 0 rows affected (0.00 sec)

mysql> connect;
Connection id:    59
Current database: bulk_test



mysql> select @@session.max_allowed_packet, @@session.net_buffer_length;
+------------------------------+-----------------------------+
| @@session.max_allowed_packet | @@session.net_buffer_length |
+------------------------------+-----------------------------+
|                         4096 |                        1024 |
+------------------------------+-----------------------------+
1 row in set (0.00 sec)

mysql> truncate table loadtest;
source ./bulk1.sql
Query OK, 0 rows affected (0.01 sec)

mysql> source ./bulk1.sql
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> truncate table loadtest;
source ./bulk2.sql
Query OK, 0 rows affected (0.02 sec)

mysql> source ./bulk2.sql
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> truncate table loadtest;
Query OK, 0 rows affected (0.01 sec)

mysql> source ./bulk3.sql
ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes

max_allowed_packetの値を超えたところで落ちました。

まとめ

1SQLのサイズ上限は、max_allowed_packetとnet_buffer_lengthで制御される。
max_allowed_packetはグローバルにデフォルト値を持ち、コネクション確立時にセッションにコピーされ、セッション変数は読み込み専用である。（セッションに適用するにはconnectなど再接続が必要）
net_buffer_lengthはグローバルにデフォルト値を持ち、コネクション確立時にセッションにコピーされ、セッション変数も変更可能である。
net_buffer_length => max_allowed_packetの場合、net_buffer_lengthが1SQLの上限値になる。
net_buffer_length < max_allowed_packetの場合、max_allowed_packetが1SQLの上限値になる。