Yakstは、海外の役立つブログ記事などを人力で翻訳して公開するプロジェクトです。
3年以上前投稿 修正あり

MySQLのALTER TABLEをモニタリングする

MySQL Performance Blogの翻訳。ALTER TABLEによるテーブル変更の進捗状況を確認するには、いくつかの方法がある。それぞれの方法と利点、欠点を紹介する。
原文 How to monitor ALTER TABLE progress in MySQL (English)
ライセンス CC BY-NC-SA
翻訳者 D98ee74ffe0fafbdc83b23907dda3665 doublemarket


February 26, 2014 By Nilnandan Joshi

Percona Supportのエンジニアとして現在関わっている案件の中で、ALTER TABLEの進捗状況を確認する方法について顧客から尋ねられた。実は、MySQL 5.5以前は、テーブルに対するALTERの実行状況を本番環境で確認するのは少々難しく、(数百万行もあるような)巨大なテーブルではなおさらだった。これは、リビルドとテーブルのロックを伴うため、パフォーマンス低下だけでなくユーザ影響もあったためだ。とはいえ、ALTERを始めてしまえば、それがいつ終わるのかを知るのはとても重要なことだ。インデックスを作成している間、fast_index_creationONならALTER TABLE中もテーブルはリビルドされないが、テーブルへのロックはかかってしまう。

fast_index_creationの機能はMySQL 5.5以降で使用可能。MySQL 5.1のInnoDB Pluginでも可。

MySQL 5.6以降で、「テーブルのコピー」や「ロック」をせずに各種ALTER TABLEを行える「Online DDL」機能が登場した。これにより、テーブルが変更されている間でもSELECTやINSERT、UPDATE、DELETEといった文を実行できるようになった。最新バージョンでは、ファイルのコピーとロックを、それぞれALGORITHMLOCKオプションで制御することも可能だ。ただし、MySQL 5.6ではまだ、カラムのADDやDROP、データタイプの変更、プライマリキーのADDやDROPについてはテーブルのリビルドが必要だ。詳しくは以下のページの表を参照しよう。

DDL操作のオンラインステータス概要

必要であればいつでも、以下の方法でALTER TABLEの進捗状況を確認できる。

Percona Toolkitを使う

ひとつの方法は、テーブルのALTERをロックなしで実行でき、かつ進捗状況を表示できるPercona Toolkitのpt-online-schema-changeを使うことだ。

nilnandan@Dell-XPS:~$ pt-online-schema-change --alter "ADD COLUMN phone INT" u=root,p=root,D=nil,t=nil_test --execute
No slaves found. See --recursion-method if host Dell-XPS has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `nil`.`nil_test`...
Creating new table...
Created new table nil._nil_test_new OK.
Altering new table...
Altered `nil`.`_nil_test_new` OK.
2014-02-07T12:20:54 Creating triggers...
2014-02-07T12:20:54 Created triggers OK.
2014-02-07T12:20:54 Copying approximately 12583349 rows...
Copying `nil`.`nil_test`: 29% 01:12 remain
Copying `nil`.`nil_test`: 60% 00:38 remain
Copying `nil`.`nil_test`: 91% 00:08 remain
2014-02-07T12:22:33 Copied rows OK.
2014-02-07T12:22:33 Swapping tables...
2014-02-07T12:22:33 Swapped original and new tables OK.
2014-02-07T12:22:33 Dropping old table...
2014-02-07T12:22:33 Dropped old table `nil`.`_nil_test_old` OK.
2014-02-07T12:22:33 Dropping triggers...
2014-02-07T12:22:33 Dropped triggers OK.
Successfully altered `nil`.`nil_test`.
nilnandan@Dell-XPS:~$

pt-online-schema-changeを使っていると、「ALTER TABLEを実行している間、データの変更(INSERT、UPDATE、DELETE)はどうなっているの?」という質問を何度もされた。

ここでその質問への答えを簡単に説明しよう。pt-online-schema-changeを実行している時にデータディレクトリを確認すると、

root@Dell-XPS:/var/lib/mysql/nil# ll
total 830524
drwx------ 2 mysql mysql 4096 Feb 7 12:20 ./
drwxr-xr-x 5 mysql mysql 4096 Feb 7 12:05 ../
-rw-rw---- 1 mysql mysql 65 Jan 31 12:12 db.opt
-rw-rw---- 1 mysql mysql 8616 Feb 7 12:06 nil_test.frm
-rw-rw---- 1 mysql mysql 822083584 Feb 7 12:18 nil_test.ibd
-rw-rw---- 1 mysql mysql 8648 Feb 7 12:20 _nil_test_new.frm
-rw-rw---- 1 mysql mysql 28311552 Feb 7 12:20 _nil_test_new.ibd
-rw-rw---- 1 mysql mysql 944 Feb 7 12:20 nil_test.TRG
-rw-rw---- 1 mysql mysql 40 Feb 7 12:20 pt_osc_nil_nil_test_del.TRN
-rw-rw---- 1 mysql mysql 40 Feb 7 12:20 pt_osc_nil_nil_test_ins.TRN
-rw-rw---- 1 mysql mysql 40 Feb 7 12:20 pt_osc_nil_nil_test_upd.TRN

元のテーブルに対する更新を新しいテーブル(*_nil_test_new)の行に対しても行うために、トリガ(拡張子TRGのファイルと、INSERT、UPDATE、DELETEごとの拡張子TRNのファイル)が作られていることがわかる。つまり、コピー中に元のテーブルに発生した変更は、新しいテーブルにも適用される。

注意 : このツールは既にテーブルにトリガが定義されていると正常に動作しない。

pt-online-schema-changeを使いたくなくて、mysqlコマンドのプロンプトから普通のALTER TABLEを実行したい場合はどうしたらよいだろうか?調べてみたところ、innodb_file_per_tableONの時は、ALTER TABLEの進捗を計算するたくさんの方法があることが分かった(innodb_file_per_tableはMySQL 5.6からデフォルトでONだ)。

一時表領域のサイズから進捗を割り出す

innodb_file_per_table=1の時、ALTER TABLEを始めると、InnoDBは#sql-1c80_27.ibdのような、#sqlから始まり拡張子が.ibdのランダムな名前の一時表領域ファイルを、データディレクトリ内に作成する。

root@Dell-XPS:/var/lib/mysql/nil# ll
...
-rw-rw---- 1 mysql mysql 8682 Feb 7 13:33 nil_test.frm
-rw-rw---- 1 mysql mysql 335544320 Feb 7 13:34 nil_test.ibd
-rw-rw---- 1 mysql mysql 8716 Feb 7 13:35 #sql-1c80_27.frm
-rw-rw---- 1 mysql mysql 23068672 Feb 7 13:35 #sql-1c80_27.ibd

テーブルを変更している間、InnoDBはnil_test.ibdのようなibdファイルを読み込み、新しいページを#sql-1c80_27.ibdの方に書き込んでいく。そのため、nil_test.ibdと一時ファイルである#sql-1c80_27.ibdのサイズの差から、以下のようにALTER TABLEの進捗状況が確認できる。

#!/bin/bash
while true
do
A=`du -m '#sql'*'.ibd' 2>/dev/null|cut -f 1`;
# if $A -lt 0 ;
if [[ -z "$A" ]] ;
then
echo "Done";
exit 0 ;
fi
TABLENAME='nil_test';
TT=$TABLENAME.ibd;
B=`du -m $TT |cut -f 1`;
echo "ALTER TABLE $TABLENAME...$A MB written to tmp tablespace out of $B MB";
sleep 10
done

ALTERを実行している時にこのスクリプトをデータディレクトリで実行すると、以下のように進捗がわかる。

mysql> ALTER TABLE nil_test ADD COLUMN phone int;
Query OK, 7582912 rows affected (58.54 sec)
Records: 7582912 Duplicates: 0 Warnings: 0
root@Dell-XPS:/var/lib/mysql/nil# ./alter_table_monitor.sh
ALTER TABLE nil_test...23 MB written in tmp file out of 485 MB
ALTER TABLE nil_test...73 MB written in tmp file out of 485 MB
ALTER TABLE nil_test...121 MB written in tmp file out of 485 MB
ALTER TABLE nil_test...173 MB written in tmp file out of 485 MB
ALTER TABLE nil_test...225 MB written in tmp file out of 485 MB
ALTER TABLE nil_test...277 MB written in tmp file out of 485 MB
ALTER TABLE nil_test...333 MB written in tmp file out of 485 MB
ALTER TABLE nil_test...381 MB written in tmp file out of 485 MB
ALTER TABLE nil_test...433 MB written in tmp file out of 485 MB
ALTER TABLE nil_test...481 MB written in tmp file out of 485 MB
Done
root@Dell-XPS:/var/lib/mysql/nil#

このスクリプトを使うにあたって考える必要があるところがいくつか存在する。

  1. $TABLENAME変数を必要に応じて変更する必要がある。ALTER TABLEでしか動かない。
  2. データディレクトリで実行しなければならない(例として/var/lib/mysql/nil)
  3. 実際の新しいテーブルはファイルサイズとは微妙に一致しないので、コマンドの結果は概算でしかない。例えば、削除された行やフラグメントのサイズは計算結果に影響してくる。

この方法では、全てのALTER TABLEの進捗状況確認はできないということをここで言っておきたい。例えば、fast_index_creationは、セカンダリインデックスが一切ない状態でテーブルをまず作成し、データがロードされてからセカンダリインデックスを作成する。つまり、セカンダリインデックスの作成中は一時表領域(.ibdファイル)は存在しない。このプロセスは非常に興味深いので、ここで少し説明しよう。

fast_index_creation」でセカンダリインデックスを追加する時には、テーブル定義を更新するために#sql***.frmは作成されるが、.ibdファイルは作成されない。ファイルソートをしてから元のテーブルの.ibdファイルにインデックスを直接作ってしまう。そのため、これらのファイルを見ていると、最初はファイルのサイズは全く増えず、その後(ファイルソートが終わると)ALTER TABLEが終わるまでファイルサイズが増えていく。

最初は以下のように、ファイルサイズは変わらない(nil_test.ibd)。

root@Dell-XPS:/var/lib/mysql/nil# ll
total 409644
drwx------ 2 mysql mysql 4096 Feb 12 10:50 ./
drwxr-xr-x 5 mysql mysql 4096 Feb 12 10:10 ../
-rwxr-xr-x 1 root root 306 Feb 7 13:35 alter_table_monitor.sh*
-rw-rw---- 1 mysql mysql 65 Jan 31 12:12 db.opt
-rw-rw---- 1 mysql mysql 8750 Feb 12 10:26 nil_test.frm
-rw-rw---- 1 mysql mysql 419430400 Feb 12 10:50 nil_test.ibd
-rw-rw---- 1 mysql mysql 8750 Feb 12 10:50 #sql-671_25.frm
...
root@Dell-XPS:/var/lib/mysql/nil# ll
total 409644
drwx------ 2 mysql mysql 4096 Feb 12 10:50 ./
drwxr-xr-x 5 mysql mysql 4096 Feb 12 10:10 ../
-rwxr-xr-x 1 root root 306 Feb 7 13:35 alter_table_monitor.sh*
-rw-rw---- 1 mysql mysql 65 Jan 31 12:12 db.opt
-rw-rw---- 1 mysql mysql 8750 Feb 12 10:26 nil_test.frm
-rw-rw---- 1 mysql mysql 419430400 Feb 12 10:50 nil_test.ibd
-rw-rw---- 1 mysql mysql 8750 Feb 12 10:50 #sql-671_25.frm

それから突如.ibdファイルのサイズが増えていく。

root@Dell-XPS:/var/lib/mysql/nil# ll
total 417836
drwx------ 2 mysql mysql 4096 Feb 12 10:50 ./
drwxr-xr-x 5 mysql mysql 4096 Feb 12 10:10 ../
-rwxr-xr-x 1 root root 306 Feb 7 13:35 alter_table_monitor.sh*
-rw-rw---- 1 mysql mysql 65 Jan 31 12:12 db.opt
-rw-rw---- 1 mysql mysql 8750 Feb 12 10:26 nil_test.frm
-rw-rw---- 1 mysql mysql 427819008 Feb 12 10:50 nil_test.ibd
-rw-rw---- 1 mysql mysql 8750 Feb 12 10:50 #sql-671_25.frm
....
root@Dell-XPS:/var/lib/mysql/nil# ll
total 487456
drwx------ 2 mysql mysql 4096 Feb 12 10:51 ./
drwxr-xr-x 5 mysql mysql 4096 Feb 12 10:10 ../
-rwxr-xr-x 1 root root 306 Feb 7 13:35 alter_table_monitor.sh*
-rw-rw---- 1 mysql mysql 65 Jan 31 12:12 db.opt
-rw-rw---- 1 mysql mysql 8750 Feb 12 10:50 nil_test.frm
-rw-rw---- 1 mysql mysql 499122176 Feb 12 10:51 nil_test.ibd
root@Nil-Dell-XPS:/var/lib/mysql/nil#

これは、1つだけセカンダリインデックスが張られている場合に起こる。複数のセカンダリインデックスがある場合、各インデックスについて処理が実行されるので、ファイルサイズの増加と一時停止が複数回、すなわちセカンダリインデックスの回数分だけ起きることになる。

information_schema.GLOBAL_TEMPORARY_TABLESのレコード数から進捗を割り出す

ALTER TABLEの実行中、information_schemaのGLOBAL_TEMPORARY_TABLESを見ることで、テンポラリテーブルに挿入されたレコードの数が分かる。

mysql> select * from GLOBAL_TEMPORARY_TABLES \G;
*************************** 1. row ***************************
SESSION_ID: 38
TABLE_SCHEMA: nil
TABLE_NAME:?#sql-1c80_27
ENGINE: InnoDB
NAME: #sql-696_26
TABLE_ROWS: 623711
AVG_ROW_LENGTH: 42
DATA_LENGTH: 26787840
INDEX_LENGTH: 0
CREATE_TIME: 2014-02-11 10:37:34
UPDATE_TIME: NULL
1 row in set (0.00 sec)
.......
mysql> select * from GLOBAL_TEMPORARY_TABLES \G;
*************************** 1. row ***************************
SESSION_ID: 38
TABLE_SCHEMA: nil
TABLE_NAME:?#sql-1c80_27
ENGINE: InnoDB
NAME: #sql-696_26
TABLE_ROWS: 7017408
AVG_ROW_LENGTH: 42
DATA_LENGTH: 299663360
INDEX_LENGTH: 0
CREATE_TIME: 2014-02-11 10:37:34
UPDATE_TIME: NULL
1 row in set (0.01 sec)

グローバルカウンタのHandler_read_rnd_next変数から割り出す

ALTER TABLEの実行中、"SHOW GLOBAL STATUS LIKE 'Handler_read_rnd%'"あるいはmysqladmin extendedで確認することができる。Baron Schwartzの2008年の記事MySQLのクエリの完了時間を見積もるを参照しよう。とても詳しく説明されている。

注意 : これはinnodb_file_per_table = ONでなくても使える方法だが、他に同時に実行されているトランザクションがない状態である必要がある。

通常あらゆるケースにおいて、MariaDBでの"SHOW PROCESSLIST"でクエリの進捗が見えるような機能をMySQL自体が提供していないと、ALTER TABLEの進捗状況を確認するのは非常に難しい。

MariaDB 5.5をローカルにインストールして、"SHOW PROCESSLIST"で確認してみたが、同じように、information_schema.processlistテーブルに対してクエリを発行しても進捗状況を確認できる。しかし、双方の出力結果は一致しないようだ。そして、information_schema.processlistテーブルの方が、正確な進捗状況のように見える。

[root@percona-pxc3 nil]# mysql -uroot -proot -e "select ID, USER, HOST, DB, TIME, STATE, INFO, STAGE, MAX_STAGE, PROGRESS from information_schema.processlist where ID = 2; show processlist"
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
| ID | USER | HOST | DB | TIME | STATE | INFO | STAGE | MAX_STAGE | PROGRESS |
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
| 2 | root | localhost | nil | 3 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 1 | 2 | 4.279 |
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
| 2 | root | localhost | nil | Query | 3 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 2.140 |
| 29 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
[root@percona-pxc3 nil]#
...............
...........
[root@percona-pxc3 nil]# mysql -uroot -proot -e "select ID, USER, HOST, DB, TIME, STATE, INFO, STAGE, MAX_STAGE, PROGRESS from information_schema.processlist where ID = 2; show processlist"
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
| ID | USER | HOST | DB | TIME | STATE | INFO | STAGE | MAX_STAGE | PROGRESS |
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
| 2 | root | localhost | nil | 25 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 1 | 2 | 45.613 |
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
| 2 | root | localhost | nil | Query | 25 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 22.807 |
| 34 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
[root@percona-pxc3 nil]#
..............
.........
[root@percona-pxc3 nil]# mysql -uroot -proot -e "select ID, USER, HOST, DB, TIME, STATE, INFO, STAGE, MAX_STAGE, PROGRESS from information_schema.processlist where ID = 2; show processlist"
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
| ID | USER | HOST | DB | TIME | STATE | INFO | STAGE | MAX_STAGE | PROGRESS |
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
| 2 | root | localhost | nil | 54 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 1 | 2 | 98.300 |
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
| 2 | root | localhost | nil | Query | 54 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 49.157 |
| 39 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
[root@percona-pxc3 nil]#
[root@percona-pxc3 nil]# mysql -uroot -proot -e "select ID, USER, HOST, DB, TIME, STATE, INFO, STAGE, MAX_STAGE, PROGRESS from information_schema.processlist where ID = 2; show processlist"
+----+------+-----------+------+------+-------+------+-------+-----------+----------+
| ID | USER | HOST | DB | TIME | STATE | INFO | STAGE | MAX_STAGE | PROGRESS |
+----+------+-----------+------+------+-------+------+-------+-----------+----------+
| 2 | root | localhost | nil | 0 | | NULL | 0 | 0 | 0.000 |
+----+------+-----------+------+------+-------+------+-------+-----------+----------+
+----+------+-----------+------+---------+------+-------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+------+-----------+------+---------+------+-------+------------------+----------+
| 2 | root | localhost | nil | Sleep | 1 | | NULL | 0.000 |
| 40 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
+----+------+-----------+------+---------+------+-------+------------------+----------+
[root@percona-pxc3 nil]#

結論

ご存知の通り、ALTER TABLEはサーバのハングアップやクラッシュを引き起こすことがある。そのため、ALTER TABLEを本番サーバで実行する時は、しっかりと計画を立てる必要がある。MySQLはいわゆる「プログレスバー」を提供していないので、上に書いたような方法で進捗状況を確認する必要がある。ここに書いた以外にも方法をご存じのようなら、コメントで教えてくれれば、喜んで記事に追加したい。

次の記事
InnoDBのプライマリキーとセカンダリキー
前の記事
MySQLの過去を振り返る Part 6 エンジンは動き出す

Feed small 記事フィード