運用をしているとダウンタイムかありかに関わらず、alter tableにどれくらいの時間がかかるのか作業前に把握したいことはよくあります。各種statusを見ることで一定時間でどれくらいの行を書き換えるかを把握することはでき、作業を始めてからであれば、あとどれくらい掛かりそうかは見積もれますが、alter tableの話が出た瞬間にどれくらい掛かりそうかの目処はつけたいです。

今回はalter時に実行されるDMLがなく、テーブルに断片化もないという理想的な環境で、各種Alter操作にどれくらいの時間がかかるのかを実験してみました。環境やconfigの詳細はあえて書いていませんが、どちらにしろ実際には様々な要因が絡むので、このくらいの情報があれば充分かと思います。

概要

alter tableにかかる時間を計測
on/off memoryでALTER TABLEにどれくらい時間がかわるかも実験
- コマンドごとに対象のテーブルがbuffer_poolに乗っていないように再起動する
sysbenchでテーブルを用意
alter table中に他のDMLは一切なし

実行環境　

MySQL 5.7.22
物理サーバ
CPU: かなり強い
Disk: SSD (約500GB)
buffer_pool_size = 37GB
variables
- innodb_buffer_pool_dump_at_shutdown = OFF
- innodb_buffer_pool_load_at_startup = OFF

準備

テーブルはsysbenchのものを利用して1テーブルで100MB, 1GB, 10GBのサイズになるように作成

table size
- sbtest_100m
  - 100MB (420000 rows, 104MB)
- sbtest_1g
  - 1GB (4200000 rows, 980MB)
- sbtest_10g
  - 10GB (42000000 rows, 9.6GB)

1テーブル100MBの作成コマンド. 1GB, 10GBはtable-sizeを変えて作成

sysbench /usr/share/sysbench/oltp_read_write.lua \
 --db-driver=mysql \
 --table-size=420000 \
 --tables=1 \
 --mysql-host=xxx \
 --mysql-port=yyy \
 --mysql-user=sysbench \
 --mysql-password=sysbench \
 --mysql-db=sysbench \
 --db-ps-mode=disable \
 prepare

実験するコマンド概要

参考: https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html#online-ddl-column-operations

No.	alter type	INPLACE	Rebuild	Permits Concurrent DML	Only Modify Metadata
1	Add secondary index	Yes	No	Yes	No
2	Add a column	Yes	Yes	Yes	No
3	Changing the column data type (int -> bigint)	No	Yes	No	No
4	Changing the column data type (char -> varchar(20))	No	Yes	No	No
5	Extending VARCHAR column size (varchar(20) -> varchar(600)	No(*)	Yes(*)	Yes	Yes

* varcharのサイズ変更は255byteが境目になっていて、これをまたぐ場合はAlgorithm = COPY

コマンド

1テーブル100MBのテーブルに対するコマンド. 1GB, 10GBはtable名を変えて実行

alter table sbtest_100m_1 add index idx_pad(pad);
alter table sbtest_100m_2 add column k2 int not null after k;
alter table sbtest_100m_3 modify column k bigint not null default '0';
alter table sbtest_100m_4 modify column c varchar(120) NOT NULL DEFAULT '';
alter table sbtest_100m_5 modify column c varchar(600) NOT NULL DEFAULT '';

1GB, 10GBのテーブルもテーブル名だけ変えて同様に実行

結果

各テーブル, コマンドで一回のみ実験。

off memory

(実行毎に再起動)

Num	100MB	1GB	10GB
1	4.24s	18.89 sec	3 min 41.09 sec
2	2.24s	19.01 sec	3 min 32.27 sec
3	4.84s	53.33 sec	9 min 37.79 sec
4	4.73s	51.37 sec	9 min 29.35 sec
5	4.73s	54.25 sec	9 min 38.47 sec

on memory

(alterの実行前にselect * from tableでbuffer_poolに載せる)

Num	100MB	1GB	10GB
1	1.43 sec	16.00 sec	3 min 11.50 sec
2	1.99 sec	14.19 sec	2 min 33.76 sec
3	4.59 sec	47.59 sec	9 min 24.20 sec
4	4.37 sec	46.53 sec	9 min 27.55 sec
5	4.64 sec	44.45 sec	9 min 14.97 sec