巨大なサイズのテーブルに対するALTER TABLE実行時間の概算方法

モチベーション

ALTER TABLE実行にかかる時間を見積もれるようになりたい。

背景

巨大なテーブルに対してALTER TABLEを実行する機会があった。何も考えずに実行してしまった結果、これがいつまでも経っても終わらない。経験豊かなエンジニアはまず、なぜ何も考えずにALTER TABLEをしたのかと疑問に思うだろうが自分は気にも留めていなかった。クエリ実行から丸２日経過して流石に不安になり実行時間を見積もってみると一週間以上かかることがわかった。同じ過ちを起こさない自戒の意味を込めて、この記事では以下について記録しておく。

ALTER TABLEの挙動
ALTER TABLE実行後に実行時間を概算方法する方法
ALTER TABLE実行前に実行時間を概算方法する方法

ALTER TABLEの挙動

MySQL、特にストレージエンジンにMyISAMやInnoDBを利用している時のALTER TABLEの挙動は以下の通りである。(漢(オトコ)のコンピュータ道: ALTER TABLEを上手に使いこなそうより引用)

テーブルをTL_WRITE_ALLOW_READロックする。このロックは特殊なロックで、テーブルロックの一種であるが、他のセッションからのREADを許可し、WRITEをブロックする。
新しいテーブル定義を使って空のテンポラリテーブルを作成する。
古いテーブルから新しいテーブルへデータをコピーする。
テーブルの名前を付け替えて、新しいテーブルを古いテーブルと同じ名前にする。古いテーブルは破棄する。
新しいテーブルへブロックしていたWRITEをリダイレクトする。

上述の通り、ALTER TABLEの挙動は上書きではなく、新しいテーブル定義で一時テーブルを作り、そこにデータを挿入していく。また、セッションを抜けるタイミングで自動的にDROPされる。この一時テーブルだが、tmp_table_sizeの値と比較して小さい場合はMEMORYテーブルとしてメモリ上に、大きい場合はディスク上に生成される。したがって、巨大なテーブルに対してALTER TABLE実行する場合対象のテーブルのサイズ分以上の空き容量がディスクに必要になるのを忘れてはならない。50GBのテーブルを対象としているときは50GB以上の空き領域が必要。ディスク上に一時テーブルの格納場所だが、ほとんどの場合、元のテーブルと同じディレクトリにsql_XXXXというファイル名で生成する。ただし、ALTER TABLE でインプレース手法 (オンライン DDL) が使用された場合、InnoDB は一時ファイルを一時ファイルディレクトリ(–tmpdirや環境変数TMPDIRで指定)に生成する。

一方でパフォーマンスを稼ぐためにメモリ上に一時テーブルを作りたい場合は、MEMORYストレージエンジンのテーブル最大サイズを定義するmax_heap_table_sizeも合わせて増やす必要がある。

ALTER TABLEを実行すると一時テーブルを生成すると述べたが場合によってはその限りではない。例えば、ADD PARTITION、DROP PARTITION、COALESCE PARTITION、REBUILD PARTITION、REORGANIZE PARTITION を含む ALTER TABLE は一時テーブルを生成しない(old_alter_table システム変数を ON に設定するかalter_specification 句の 1 つとして ALGORITHM=COPYを明示的に指定することで一時テーブルを生成することもできる)

ALTER TABLE実行時間の概算方法

Data set

MySQLが公式に公開しているテスト用DBを利用する。

GitHub - datacharmer/test_db: A sample MySQL database with an integrated test suite, used to test your applications and database servers

mysql> use employees;
mysql> show tables;
+----------------------+
| Tables_in_employees  |
+----------------------+
| current_dept_emp     |
| departments          |
| dept_emp             |
| dept_emp_latest_date |
| dept_manager         |
| employees            |
| salaries             |
| titles               |
+----------------------+
8 rows in set (0.00 sec)

mysql> select count(*) from salaries;
+----------+
| count(*) |
+----------+
|  2844047 |
+----------+


mysql> show create table salaries \G
*************************** 1. row ***************************
       Table: salaries
Create Table: CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE  COLUMNS(from_date)
(PARTITION p01 VALUES LESS THAN ('1985-12-31') ENGINE = InnoDB,
 PARTITION p02 VALUES LESS THAN ('1986-12-31') ENGINE = InnoDB,
 PARTITION p03 VALUES LESS THAN ('1987-12-31') ENGINE = InnoDB,
 PARTITION p04 VALUES LESS THAN ('1988-12-31') ENGINE = InnoDB,
 PARTITION p05 VALUES LESS THAN ('1989-12-31') ENGINE = InnoDB,
 PARTITION p06 VALUES LESS THAN ('1990-12-31') ENGINE = InnoDB,
 PARTITION p07 VALUES LESS THAN ('1991-12-31') ENGINE = InnoDB,
 PARTITION p08 VALUES LESS THAN ('1992-12-31') ENGINE = InnoDB,
 PARTITION p09 VALUES LESS THAN ('1993-12-31') ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN ('1994-12-31') ENGINE = InnoDB,
 PARTITION p11 VALUES LESS THAN ('1995-12-31') ENGINE = InnoDB,
 PARTITION p12 VALUES LESS THAN ('1996-12-31') ENGINE = InnoDB,
 PARTITION p13 VALUES LESS THAN ('1997-12-31') ENGINE = InnoDB,
 PARTITION p14 VALUES LESS THAN ('1998-12-31') ENGINE = InnoDB,
 PARTITION p15 VALUES LESS THAN ('1999-12-31') ENGINE = InnoDB,
 PARTITION p16 VALUES LESS THAN ('2000-12-31') ENGINE = InnoDB,
 PARTITION p17 VALUES LESS THAN ('2001-12-31') ENGINE = InnoDB,
 PARTITION p18 VALUES LESS THAN ('2002-12-31') ENGINE = InnoDB,
 PARTITION p19 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */

概算方法1: テーブルへのレコード挿入速度から割り出す

ストレージエンジンにInnoDBを使っていることが前提になってしまうが、ALTER TABLE対象のテーブルが保持しているレコード数/Innotopで取得できる単位秒当たりのレコード挿入数`でおおよその時間は見積もれる。実際に見てみよう。

ここでは以下を実行してデフォルトの文字コードをlatin1からutf8mb4をに変更するとともに、collationもutf8mb4_general_ciに変更する。

mysql> ALTER TABLE employees.salaries CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 2844047 rows affected (6.55 sec)
Records: 2844047  Duplicates: 0  Warnings: 0


mysql> show create table salaries \G
*************************** 1. row ***************************
       Table: salaries
Create Table: CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50500 PARTITION BY RANGE  COLUMNS(from_date)
(PARTITION p01 VALUES LESS THAN ('1985-12-31') ENGINE = InnoDB,
 PARTITION p02 VALUES LESS THAN ('1986-12-31') ENGINE = InnoDB,
 PARTITION p03 VALUES LESS THAN ('1987-12-31') ENGINE = InnoDB,
 PARTITION p04 VALUES LESS THAN ('1988-12-31') ENGINE = InnoDB,
 PARTITION p05 VALUES LESS THAN ('1989-12-31') ENGINE = InnoDB,
 PARTITION p06 VALUES LESS THAN ('1990-12-31') ENGINE = InnoDB,
 PARTITION p07 VALUES LESS THAN ('1991-12-31') ENGINE = InnoDB,
 PARTITION p08 VALUES LESS THAN ('1992-12-31') ENGINE = InnoDB,
 PARTITION p09 VALUES LESS THAN ('1993-12-31') ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN ('1994-12-31') ENGINE = InnoDB,
 PARTITION p11 VALUES LESS THAN ('1995-12-31') ENGINE = InnoDB,
 PARTITION p12 VALUES LESS THAN ('1996-12-31') ENGINE = InnoDB,
 PARTITION p13 VALUES LESS THAN ('1997-12-31') ENGINE = InnoDB,
 PARTITION p14 VALUES LESS THAN ('1998-12-31') ENGINE = InnoDB,
 PARTITION p15 VALUES LESS THAN ('1999-12-31') ENGINE = InnoDB,
 PARTITION p16 VALUES LESS THAN ('2000-12-31') ENGINE = InnoDB,
 PARTITION p17 VALUES LESS THAN ('2001-12-31') ENGINE = InnoDB,
 PARTITION p18 VALUES LESS THAN ('2002-12-31') ENGINE = InnoDB,
 PARTITION p19 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */

このクエリ実行時間は6.5秒だった。この時の単位秒当たりのレコード挿入数をinnotopで測定した結果は以下のとおり。

>innotop -uroot(実行後Rを押してRow Ops modeにする)

______________________ InnoDB Row Operations _______________________
Ins       Upd  Read      Del  Ins/Sec    Upd/Sec  Read/Sec   Del/Sec
11311328    0  13080407    0  469021.98     0.00  469020.98     0.00

___________________ Row Operation Misc ____________________
Queries Queued  Queries Inside  Rd Views  Main Thread State
             0               0         1  sleeping

_____________________________ InnoDB Semaphores _____________________________
Waits  Spins  Rounds  RW Waits  RW Spins  Sh Waits  Sh Spins  Signals  ResCnt


____________________________ InnoDB Wait Array _____________________________
Thread  Time  File  Line  Type  Readers  Lck Var  Waiters  Waiting?  Ending?

毎秒違うXXXX Ins/Secという値が表示されるがオーダーレベルではおおよそのパフォーマンスをが測定できる。saraliesテーブルには2844047のレコードが格納されていることがわかっているので2844047÷469021 = 約6秒程度の実行時間になると見積もれる。これは実測値6.5秒と比較すると妥当な計算方法であると言える。InnoDB以外のストレージエンジンを使っていて同様な方法で計算したい場合は、SHOW GLOBAL STATUS LIKE 'Handler_write'の値の変化を見て同様な方法で算出することも可能(参考：2011-06-15 MySQLでALTER TABLE文の進捗状況を確認する)

余談だが、同じテーブル定義を持ち実際より少ないレコードでALTER TABLE実行時間を見積もる場合は注意が必要だ。例えばインデックスの再構築を行う時、Btreeインデックスを利用していればレコード１行挿入の計算量はO(logn)になる。全てのレコードを挿入することを考えると計算量はO(nlogn)となる。レコード量に対して実行時間も線形に増加するわけではない。

概算方法2: 事前に算出する方法

詐欺っぽいが、結論からいうと事前に試算する方法を調査したが見つけられなかった。MySQLでクエリを実行のパフォーマンスはMySQLの設定はもちろん、利用するストレージのパフォーマンス、ホストのリソース、ネットワークの帯域など様々な要因により変化するからだろう。事前に試算するよりは同じデータ量で同じリソース下でどの程度時間がかかるのか実測値で測定するのが望ましい。計算量的にはデータの増加に伴いそのクエリの実行時間が増えるかは見積もれる(ALTERによるインデックスの再構築はO(nlogn)とか)

結論

安易に巨大なテーブルにALTER TABLEを投げない
ALTER TABLEを実行時間は対象のテーブルレコード数と単位時間当たりのレコード挿入数から試算可能
実行時間の事前試算をするにはALTER TABLE実行環境のスループットを理解する必要がある

参考

公式(基本はこれを読む)

一時テーブルと一時ファイルの違い

ALTER Tableの実行時間試算

ALTER TABLE以外でテーブル定義を変更する

大量データのあるテーブルにはALTER TABLEよりmysqldumpしてインポートのほうがよさげ - F.Ko-Jiの「一秒後は未来」

実践ハイパフォーマンスMySQL 第3版

Author: Baron Schwartz

Manufacturer: オライリージャパン

Publish date: 2013-11-25

エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド

Author: 奥野幹也

Manufacturer: 技術評論社

Publish date: 2010-06-12