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

つらつらと覚書。

サンプルテーブルは以下。

mysql> CREATE TABLE `t1` (
    ->   `id` int(10) unsigned NOT NULL,
    ->   `c1` varchar(32) NOT NULL,
    ->   `c2` varchar(32) NOT NULL,
    ->   PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.32 sec)

mysql> INSERT INTO t1 VALUES (1, 'one', 'eins'), (2, 'two', 'zwei'), (3, 'three', 'drei');
Query OK, 3 rows affected (0.13 sec)
Records: 3  Duplicates: 0  Warnings: 0

まずは innodb-sort-buffer-size= 1M で単一カラムに。performance-schemaはOFFにしておかないとメモリー割り当ての上位がほとんどp_s関連で占められて表示が追い出されてしまう。

$ valgrind --tool=massif bin/mysqld --no-defaults --datadir=./data --performance-schema=0 --innodb-sort-buffer-size=1M

mysql> ALTER TABLE t1 ADD KEY (c1);
Query OK, 0 rows affected (0.66 sec)
Records: 0  Duplicates: 0  Warnings: 0

$ ms_print massif.out.30840 | grep alter

ALTER TABLEに関するメモリ割り当ては下位にあるっぽいので出てこない。


innodb-sort-buffer-size= 8M で単一カラム。

$ valgrind --tool=massif bin/mysqld --no-defaults --datadir=./data --performance-schema=0 --innodb-sort-buffer-size=8M

mysql> ALTER TABLE t1 ADD KEY (c1);
Query OK, 0 rows affected (0.72 sec)
Records: 0  Duplicates: 0  Warnings: 0

$ ms_print massif.out.30908 | grep alter
| |     ->49.78% (33,554,432B) 0x99EFC0: ha_innobase::inplace_alter_table(TABLE*, Alter_inplace_info*) (handler0alter.cc:3930)
| |       ->49.78% (33,554,432B) 0x7280AB: mysql_inplace_alter_table(THD*, TABLE_LIST*, TABLE*, TABLE*, Alter_inplace_info*, enum_alter_inplace_result, MDL_request*, Alter_table_ctx*) (handler.h:2868)
| |         ->49.78% (33,554,432B) 0x72B70B: mysql_alter_table(THD*, char*, char*, st_ha_create_information*, TABLE_LIST*, Alter_info*, unsigned int, st_order*, bool) (sql_table.cc:8319)
| |           ->49.78% (33,554,432B) 0x81EE4A: Sql_cmd_alter_table::execute(THD*) (sql_alter.cc:313)

出てきた。32MB。


innodb-sort-buffer-size= 64M

$ valgrind --tool=massif bin/mysqld --no-defaults --datadir=./data --performance-schema=0 --innodb-sort-buffer-size=64M

mysql> ALTER TABLE t1 ADD KEY (c1);
Query OK, 0 rows affected (1.40 sec)
Records: 0  Duplicates: 0  Warnings: 0

$ ms_print massif.out.30969 | grep alter
| |     ->88.81% (268,435,456B) 0x99EFC0: ha_innobase::inplace_alter_table(TABLE*, Alter_inplace_info*) (handler0alter.cc:3930)
| |       ->88.81% (268,435,456B) 0x7280AB: mysql_inplace_alter_table(THD*, TABLE_LIST*, TABLE*, TABLE*, Alter_inplace_info*, enum_alter_inplace_result, MDL_request*, Alter_table_ctx*) (handler.h:2868)
| |         ->88.81% (268,435,456B) 0x72B70B: mysql_alter_table(THD*, char*, char*, st_ha_create_information*, TABLE_LIST*, Alter_info*, unsigned int, st_order*, bool) (sql_table.cc:8319)
| |           ->88.81% (268,435,456B) 0x81EE4A: Sql_cmd_alter_table::execute(THD*) (sql_alter.cc:313)

256MB。innodb-sort-buffer-size= 8Mのときの8倍になっているのは良いとして、innodb-sort-buffer-sizeそのものの4倍確保してる？


innodb-sort-buffer-size= 8Mにして、2カラムの複合インデックス。

$ valgrind --tool=massif bin/mysqld --no-defaults --datadir=./data --performance-schema=0 --innodb-sort-buffer-size=8M

mysql> ALTER TABLE t1 ADD KEY (c1, c2);
Query OK, 0 rows affected (0.71 sec)
Records: 0  Duplicates: 0  Warnings: 0

$ ms_print massif.out.31031 | grep alter
| |     ->49.78% (33,554,432B) 0x99EFC0: ha_innobase::inplace_alter_table(TABLE*, Alter_inplace_info*) (handler0alter.cc:3930)
| |       ->49.78% (33,554,432B) 0x7280AB: mysql_inplace_alter_table(THD*, TABLE_LIST*, TABLE*, TABLE*, Alter_inplace_info*, enum_alter_inplace_result, MDL_request*, Alter_table_ctx*) (handler.h:2868)
| |         ->49.78% (33,554,432B) 0x72B70B: mysql_alter_table(THD*, char*, char*, st_ha_create_information*, TABLE_LIST*, Alter_info*, unsigned int, st_order*, bool) (sql_table.cc:8319)
| |           ->49.78% (33,554,432B) 0x81EE4A: Sql_cmd_alter_table::execute(THD*) (sql_alter.cc:313)

複合インデックスにしても変わらないぽい。


同じく、単一カラムのインデックスを一気に2つ作成。

$ valgrind --tool=massif bin/mysqld --no-defaults --datadir=./data --performance-schema=0 --innodb-sort-buffer-size=8M

mysql> ALTER TABLE t1 ADD KEY (c1), ADD KEY (c2);
Query OK, 0 rows affected (0.80 sec)
Records: 0  Duplicates: 0  Warnings: 0

$ ms_print massif.out.31095 | grep alter
| |     ->66.45% (67,108,864B) 0x99EFC0: ha_innobase::inplace_alter_table(TABLE*, Alter_inplace_info*) (handler0alter.cc:3930)
| |       ->66.45% (67,108,864B) 0x7280AB: mysql_inplace_alter_table(THD*, TABLE_LIST*, TABLE*, TABLE*, Alter_inplace_info*, enum_alter_inplace_result, MDL_request*, Alter_table_ctx*) (handler.h:2868)
| |         ->66.45% (67,108,864B) 0x72B70B: mysql_alter_table(THD*, char*, char*, st_ha_create_information*, TABLE_LIST*, Alter_info*, unsigned int, st_order*, bool) (sql_table.cc:8319)
| |           ->66.45% (67,108,864B) 0x81EE4A: Sql_cmd_alter_table::execute(THD*) (sql_alter.cc:313)

これはシンプルに倍になった。

マニュアルをよく読んでみたら、

(6 /*FTS_NUM_AUX_INDEX*/ *
(3*@@global.innodb_sort_buffer_size) + 2 * (
@@global.innodb_sort_buffer_size/dict_index_get_min_size(index)*/)
* 8 /*64-bit sizeof *buf->tuples*/")

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

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

まあなんとなく4倍くらいでおかしくなさそうなアレだった。納得。