MySQL 5.6.4で実装されたinnodb-sort-buffer-sizeの値

InnoDBのオンラインALTER TABLEの時に使われるパラメーター。
セッション変数のsort_buffer_sizeのように使われて、これをあふれたぶんだけsort_merge_passes相当の処理が走るので重くなる。

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_sort_buffer_size

最大値が6.7GBに見えたけど全くの空目で、最大値は64Mと小さめ。暗黙のデフォルトは1M。

実際どれくらい違うのか。ざっくりテスト。

$ perl -e 'use Digest::MD5 qw/md5_hex/; open($fh, ">/data/tmp/md5.tsv"); for ($n= 1; $n<= 10000000; $n++) {printf($fh "%d\t%s\n", $n, md5_hex($n));}'

$ cat /data/tmp/md5.sql
SELECT @@innodb_sort_buffer_size;

use d1

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (num int unsigned, val varchar(32), upd datetime default current_timestamp);

LOAD DATA INFILE '/data/tmp/md5.tsv' INTO TABLE t1(num, val);

ALTER TABLE t1 ADD KEY (val, upd), ADD KEY (upd);

DROP TABLE t1;


mysql> source /data/tmp/md5.sql
+---------------------------+
| @@innodb_sort_buffer_size |
+---------------------------+
|                   1048576 |
+---------------------------+
1 row in set (0.00 sec)

Database changed
Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 10000000 rows affected (1 min 3.48 sec)
Records: 10000000  Deleted: 0  Skipped: 0  Warnings: 0

Query OK, 0 rows affected (2 min 8.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.70 sec)


mysql> source /data/tmp/md5.sql
+---------------------------+
| @@innodb_sort_buffer_size |
+---------------------------+
|                  16777216 |
+---------------------------+
1 row in set (0.00 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 10000000 rows affected (59.01 sec)
Records: 10000000  Deleted: 0  Skipped: 0  Warnings: 0

Query OK, 0 rows affected (2 min 8.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.62 sec)


mysql> source /data/tmp/md5.sql
+---------------------------+
| @@innodb_sort_buffer_size |
+---------------------------+
|                  67108864 |
+---------------------------+
1 row in set (0.00 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 10000000 rows affected (1 min 0.29 sec)
Records: 10000000  Deleted: 0  Skipped: 0  Warnings: 0

Query OK, 0 rows affected (1 min 48.68 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.71 sec)

オンラインALTER TABLE用のパラメーターなので、ALTER TABLE .., ALGORITHM= COPYの場合はもちろん効かなかった。これを約10秒/GBの減少とみるか(ロード後で.ibdファイルは1.7GBくらい)、15%の減少とみるか。

グローバルで64Mなら、最初から最大値にしておいてもいいかな。


【2014/07/03 12:15】
実際には1つのADD KEYに対してinnodb-sort-buffer-sizeの4倍のメモリーを使うので注意。。

日々の覚書: MySQL 5.6のオンラインALTER TABLEとinnodb-sort-buffer-sizeに関する考察