掲題のエラーで悩んでいるチームがあったので、ヒュッと「MySQL のバグじゃねっすかね？」とだけ Slack で投げていたんだけど、ちゃんと確認することにした。
原因
MySQL のバグ。
Sorts of some column types, including JSON and TEXT, sometimes exhausted the sort buffer if its size was not at least 15 times that of the largest row in the sort. Now the sort buffer need only be only 15 times as large as the largest sort key. (Bug #103325, Bug #105532, Bug #32738705, Bug #33501541)
JSON や TEXT などの一部の列タイプの並べ替えでは、サイズが並べ替えの最大行の 15 倍以上でないと、並べ替えバッファーが使い果たされることがありました。これで、ソート バッファーは最大のソート キーの 15 倍の大きさだけで済みます。 (バグ #103325、バグ #105532、バグ #32738705、バグ #33501541)
GROUP BY, ORDER BY (sort対象) した合計カラム長 > sort_buffer_size のような状態になると、 MySQL 8.0.28 以前では掲題のエラーで処理が完遂できない模様。
sort_buffer_size の動作
これ知らなかったんだけど sort_buffer_size は動的にメモリが割り当てられるらしい。
read_buffer_size 、 sort_buffer_size 、 join_buffer_size に関しては、クエリが必要とするまではメモリの割り当てはされない。しかし、クエリが必要としたときには、指定されたメモリ量の全てをすぐに割り当てる。
で、内部でどういう動作をしているか、というと以下のような感じらしい。
- ソート処理(ORDER BYやGROUP BY)実行
- インデックスが使えればfilesortをスキップ
- インデックスが使えなければ sort_buffer_size 分のスレッドバッファが割り当てられ、クイックソートを行う
- sort_buffer_size 以上にソート領域が必要になる場合、インメモリ内部一時テーブルを作成しソートを実行
- インメモリ内部一時テーブル のサイズ上限以上にソート領域が必要になる場合 ディスク内部一時テーブルを作成しソートを実行
なるほどー。 言われてみれば innodb_buffer_pool から溢れたらディスクに作業内容が移されてクソ遅くなるぞ！ みたいなの太古からパフォーマンスチューニングで言われてるし、最後はディスクベースで処理できるはずだから、エラーになるのはなんかおかしいよなあ！
という訳で試す
Docker で調達してきた MySQL 8.0.24 (今回問題を確認したバージョン) と、システムワイドに入れていた MySQL 8.0.32 で実行して比較してみる事にした。
テストに用いたのは、該当の修正コミットに添付されていたテストコード。
8.0.24
❯ docker exec -it 93b6c612a236 bash bash-4.4# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1002 Server version: 8.0.24 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | app_development | | app_test | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.03 sec) # 現在の sort_buffer_size の確認 mysql> SHOW VARIABLES LIKE 'sort_buffer_size'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | sort_buffer_size | 262144 | +------------------+--------+ 1 row in set (0.02 sec) # テストに利用するデータベース test_sort を作成 mysql> CREATE DATABASE test_sort; Query OK, 1 row affected (0.02 sec) mysql> USE test_sort; Database changed # クソデカ TEXT 型カラムを作る mysql> CREATE TABLE t1 ( a INTEGER, b TEXT ); Query OK, 0 rows affected (0.04 sec) mysql> INSERT INTO t1 VALUES (1, REPEAT('x', 40001)); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO t1 VALUES (2, REPEAT('x', 40002)); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO t1 VALUES (3, REPEAT('x', 40003)); Query OK, 1 row affected (0.02 sec) mysql> INSERT INTO t1 VALUES (4, REPEAT('x', 40005)); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO t1 VALUES (5, REPEAT('x', 40008)); Query OK, 1 row affected (0.02 sec) mysql> INSERT INTO t1 VALUES (6, REPEAT('x', 40013)); Query OK, 1 row affected (0.03 sec) # クソデカ TEXT 型より小さくなるように sort_buffer_size を変更 mysql> SET sort_buffer_size=65536; Query OK, 0 rows affected (0.00 sec) mysql> SHOW VARIABLES LIKE 'sort_buffer_size'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | sort_buffer_size | 65536 | +------------------+-------+ 1 row in set (0.00 sec) # ORDER BY する # 結果無事にエラーが発生して破壊された mysql> SELECT a, LENGTH(b) FROM t1 ORDER BY a DESC; ERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size # sort_buffer_size をデフォルトの 256KB に戻して実行してみる # 実行できる mysql> SET sort_buffer_size=DEFAULT; Query OK, 0 rows affected (0.00 sec) mysql> SELECT a, LENGTH(b) FROM t1 ORDER BY a DESC; +------+-----------+ | a | LENGTH(b) | +------+-----------+ | 6 | 40013 | | 5 | 40008 | | 4 | 40005 | | 3 | 40003 | | 2 | 40002 | | 1 | 40001 | +------+-----------+ 6 rows in set (0.00 sec)
8.0.32
❯ mysql -u donbulinux -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 8.0.32 Homebrew Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. # 現在の sort_buffer_size の確認 mysql> SHOW VARIABLES LIKE 'sort_buffer_size'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | sort_buffer_size | 262144 | +------------------+--------+ 1 row in set (0.03 sec) # テストに利用するデータベース test_sort を作成 mysql> CREATE DATABASE test_sort; Query OK, 1 row affected (0.00 sec) mysql> USE test_sort; Database changed # クソデカ TEXT 型カラムを作る mysql> CREATE TABLE t1 ( a INTEGER, b TEXT ); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO t1 VALUES (1, REPEAT('x', 40001)); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO t1 VALUES (2, REPEAT('x', 40002)); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO t1 VALUES (3, REPEAT('x', 40003)); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO t1 VALUES (4, REPEAT('x', 40005)); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO t1 VALUES (5, REPEAT('x', 40008)); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO t1 VALUES (6, REPEAT('x', 40013)); Query OK, 1 row affected (0.00 sec) # クソデカ TEXT 型より小さくなるように sort_buffer_size を変更 mysql> SET sort_buffer_size=65536; Query OK, 0 rows affected (0.00 sec) mysql> SHOW VARIABLES LIKE 'sort_buffer_size'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | sort_buffer_size | 65536 | +------------------+-------+ 1 row in set (0.00 sec) # ORDER BY する # 合計カラム長 > sort_buffer_size だけど、 8.0.24 と違ってエラーにならない # sort_buffer_size の動的割当か、(インメモリ|ディスク) 内部一時テーブルの割当がうまくいってる模様 mysql> SELECT a, LENGTH(b) FROM t1 ORDER BY a DESC; +------+-----------+ | a | LENGTH(b) | +------+-----------+ | 6 | 40013 | | 5 | 40008 | | 4 | 40005 | | 3 | 40003 | | 2 | 40002 | | 1 | 40001 | +------+-----------+ 6 rows in set (0.00 sec) # 一応デフォルトの値でも追試してみる mysql> SET sort_buffer_size=DEFAULT; Query OK, 0 rows affected (0.00 sec) # 特に問題はないのであった mysql> SELECT a, LENGTH(b) FROM t1 ORDER BY a DESC; +------+-----------+ | a | LENGTH(b) | +------+-----------+ | 6 | 40013 | | 5 | 40008 | | 4 | 40005 | | 3 | 40003 | | 2 | 40002 | | 1 | 40001 | +------+-----------+ 6 rows in set (0.01 sec)
MySQL のバグトラッキング
bungs.mysql で全部見れる、とずっと思っていたのだけど、じつはそうじゃなかった。
リリースノートなどに現れる、整数8桁のバグIDのものは中の人にしか見られない
ヴォッまじだ。 という事で教えに従って ID を用いて Github で検索した所、引用らしきものを無事発見できたのだった。
Search · repo:mysql/mysql-server 32738705 · GitHub
この記事のおかげで修正を見つけられたのでだいぶ助かった。
ちなみに
sort_buffer_size の値を変更したら確かに解決できるんだけど、 (動的割当がされなくても良いサイズの値を指定していればよさそう) 公式でもあんまりオススメしない、みたいな事が書いてある。
digitalocean くんなんかは「変更するな、やるにしても 5MB 超えると動的割当がクソ遅くなるからやめとけ」みたいな事が書いてある。
ので、掲題のエラーが発生した人は MySQL のバージョンアップをしたほうが良さそうです。 色々な都合で無理そうなら、sort 実行時に動的割当がされないくらいの値を sort_buffer_size なりに指定する (遅くなるらしいけど)、という感じで。
