はじめに
Percona Liveなどのイベントでも度々話題になっている gh-ost について調べてみました。
「gh-ost」（GitHub’s Online Schema Transmogrifier/Transfigurator/Transformer/Thingy）は、Githubが開発するオープンソースのスキーママイグレーションツールです。
その綴りから、「ゴースト」と呼ばれることもあります。
なお、gh-ost の詳細については以下の翻訳記事が非常に参考になります。
gh-ost：GitHubのMySQL向けオンライン・スキーマ・マイグレーションツール
既存ソリューションとの違い
gh -ost と同様の特徴を持つ機能（ツール）として、「オンラインDDL」「pt-online-schema-change」がありますが、両者には以下のような欠点があると gh-ost の作者は指摘しています。
- ALTER文に長い時間がかかる場合、その処理がスレーブに伝播した時に大幅なレプリケーション遅延が発生する
- pt-online-schema-changeのトリガを利用したデータ同期処理が、深刻なロック競合を引き起こす場合がある
- ALTER処理を一度実行すると完了するまで手動停止できない（pt-online-schema-changeなら自動停止の基準を設定することは可能）
gh-ost は、上記のような課題を克服するために開発されたツールです。
制限事項
gh-ost を使う上で、以下のような制限事項（必要事項）を理解しておく必要があります。
- binlog_format=ROW / binlog_row_image=FULL が設定されている
- 外部キー、トリガー、generated columnなどはサポートされていない
- マイグレーション対象のカラムに NULL が含まれていてはいけない
- PK もしくはユニークキーが設定されている
- AWS RDSは利用制限あり / Google Cloud SQL はサポート外
- 以下の権限を持ったMySQLユーザが存在する
(1) ALTER, CREATE, DELETE, DROP, INDEX, INSERT, LOCK TABLES, SELECT, TRIGGER, UPDATE on \<変更対象のテーブル>
もしくは
(2) SUPER, REPLICATION SLAVE on *.*
※ 詳細は以下のマニュアルを参照してください
Requirements and limitations
使い方
gh-ost はデフォルトでスレーブに接続し、そこからバイナリログを利用してマスタに変更を反映させます。
もちろん、スレーブが存在しない環境などではマスタに対して直接 gh-ost を実行することも可能です。
インストール
Githubから最新のバイナリをダウンロードし、解凍すれば準備は完了です。
[code lang=text]
[root@test-4U-1 ~]# wget https://github.com/github/gh-ost/releases/download/v1.0.42/gh-ost-binary-linux-20170914095800.tar.gz
…
[root@test-4U-1 ~]# tar zxvf gh-ost-binary-linux-20170914095800.tar.gz
gh-ost
[root@test-4U-1 ~]# ls -lh gh-ost
-rwxr-xr-x 1 501 games 7.9M 9月 14 15:58 gh-ost
[/code]
マスタ・スレーブの場合
スレーブに対して gh-ost を実行します。ツールを制御するための様々なオプションが用意されているため、状況に合わせて使い分けることができます。
[code lang=text]
./gh-ost –user="root" –password="MySQL5.7" –host=192.168.20.21 –port=3306 –database="d1" \
–table="t1" –alter="ADD COLUMN col_new INT, ADD INDEX idx_1(col1)" –test-on-replica –execute
[/code]
シングルマスタの場合
マスタに対して直接 gh-ost を実行します。
ベンチマーク
弊社の検証環境で ALTER TABLE（オンラインDDL） / ALTER TABLE（ALGORITHM=COPY） / pt-online-schema-change / gh-ost の4パターンを実行し、所要時間を比較してみました。なお、全てシングルマスタの環境です。
テスト環境
実メモリ62GB、CPU8コアの物理サーバです。OSは CentOS 7.4 、MySQLは現時点で最新版の MySQL 5.7.20 を使用します。my.cnfは以下の部分のみ変更しております。
|
1 2 3 4 5 6 7 8 |
[root@test-4U-1 ~]# cat << EOT >> /etc/my.cnf > innodb_buffer_pool_size=32G > innodb_buffer_pool_dump_at_shutdown=OFF > innodb_buffer_pool_load_at_startup=OFF > server_id=10 > log_bin=mysql-bin > binlog_format=ROW > EOT |
テストデータは、以下のようなrubyスクリプトを利用して2000万行のランダムデータを生成しています。
なお、3つのテーブル全てに同じレコードが格納されています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
[root@test-4U-1 ~]# cat create_test_data.rb # CREATE TABLE `alter_test_1` ( # `main_id` int(10) unsigned NOT NULL DEFAULT '0', # `sub_id` int(10) unsigned NOT NULL, # `created_at` datetime NOT NULL, # `col1` varchar(30) DEFAULT NULL, # `col2` varchar(200) NOT NULL DEFAULT '', # `col3` varchar(30) NOT NULL DEFAULT '', # `col4` int(10) unsigned NOT NULL DEFAULT '0', # `col5` varchar(20) NOT NULL DEFAULT '', # `col6` varchar(50) NOT NULL DEFAULT '', # `col7` varchar(50) DEFAULT NULL, # `col8` varchar(50) DEFAULT NULL, # `updated_at` datetime DEFAULT NULL, # PRIMARY KEY (`main_id`,`sub_id`), # KEY idx_1(`col1`), # KEY idx_2(`col2`,`col3`), # KEY idx_3(`updated_at`) # ) ENGINE=InnoDB DEFAULT CHARSET=utf8; (ARGV[0].to_i..ARGV[1].to_i).each do |n| main_id = n sub_id = rand(9999999) created_at = (Time.now - rand(99999999)).strftime("%Y-%m-%d %T") col1 = (0...20).map { (65 + rand(26)).chr }.join col2 = (0...100).map { (65 + rand(26)).chr }.join col3 = (0...20).map { (65 + rand(26)).chr }.join col4 = rand(9999999) col5 = (0...20).map { (65 + rand(26)).chr }.join col6 = (0...30).map { (65 + rand(26)).chr }.join col7 = (0...30).map { (65 + rand(26)).chr }.join col8 = (0...30).map { (65 + rand(26)).chr }.join updated_at = (Time.now - rand(99999999)).strftime("%Y-%m-%d %T") puts "INSERT INTO alter_test_1 VALUES( '#{main_id}', '#{sub_id}', '#{created_at}', '#{col1}', '#{col2}', '#{col3}', '#{col4}', '#{col5}', '#{col6}', '#{col7}', '#{col8}', '#{updated_at}');" end [root@test-4U-1 ~]# ruby create_test_data.rb 1 20000000 | mysql -u root -pMySQL5.7 d1 [root@test-4U-1 ~]# ls -lh /var/lib/mysql/d1/ 合計 13G -rw-r----- 1 mysql mysql 8.8K 11月 9 14:28 alter_test_1.frm -rw-r----- 1 mysql mysql 13G 11月 9 20:08 alter_test_1.ibd -rw-r----- 1 mysql mysql 65 11月 9 14:27 db.opt [root@test-4U-1 ~]# mysql -u root -pMySQL5.7 -e "select count(*) from d1.alter_test_1" +----------+ | count(*) | +----------+ | 20000000 | +----------+ |
|
1 2 3 4 5 6 7 8 9 |
[root@test-4U-1 ~]# mysql -u root -pMySQL5.7 d1 mysql> CREATE TABLE alter_test_2 LIKE alter_test_1; mysql> CREATE TABLE alter_test_3 LIKE alter_test_1; mysql> CREATE TABLE alter_test_4 LIKE alter_test_1; mysql> INSERT INTO alter_test_2 SELECT * FROM alter_test_1; mysql> INSERT INTO alter_test_3 SELECT * FROM alter_test_1; mysql> INSERT INTO alter_test_46 SELECT * FROM alter_test_1; |
計測準備
バッファプールの影響を無くすため、各ツールのツール実行前にメモリをクリアします。
|
1 2 3 4 |
[root@test-4U-1 ~]# systemctl stop mysqld [root@test-4U-1 ~]# sync [root@test-4U-1 ~]# echo 3 > /proc/sys/vm/drop_caches [root@test-4U-1 ~]# systemctl start mysqld |
計測：ALTER TABLE（オンラインDDL）
|
1 2 3 4 5 |
[root@test-4U-1 ~]# time mysql -u root -pMySQL5.7 d1 -e "ALTER TABLE alter_test_1 ENGINE=InnoDB" | tee ./online_ddl.log real 14m29.237s user 0m0.005s sys 0m0.007s |
計測：ALTER TABLE（ALGORITHM=COPY）
|
1 2 3 4 5 |
[root@test-4U-1 ~]# time mysql -u root -pMySQL5.7 d1 -e "ALTER TABLE alter_test_2 ENGINE=InnoDB, ALGORITHM=COPY" | tee ./alter_copy.log real 231m51.744s user 0m0.007s sys 0m0.004s |
※ 3時間51分程度
計測：pt-online-schema-change
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
[root@test-4U-1 ~]# yum install perl-DBD-MySQL [root@test-4U-1 ~]# wget percona.com/get/pt-online-schema-change [root@test-4U-1 ~]# chmod +x pt-online-schema-change [root@test-4U-1 ~]# ./pt-online-schema-change --execute --alter="ENGINE=InnoDB" \ D=d1,t=alter_test_3,u=root,p=MySQL5.7,h=localhost ... Altered `d1`.`_alter_test_3_new` OK. 2017-11-14T18:22:00 Creating triggers... 2017-11-14T18:22:00 Created triggers OK. 2017-11-14T18:22:00 Copying approximately 18007035 rows... ... 2017-11-14T22:13:42 Copied rows OK. 2017-11-14T22:13:42 Analyzing new table... 2017-11-14T22:13:42 Swapping tables... 2017-11-14T22:13:42 Swapped original and new tables OK. 2017-11-14T22:13:42 Dropping old table... 2017-11-14T22:13:51 Dropped old table `d1`.`_alter_test_3_old` OK. 2017-11-14T22:13:51 Dropping triggers... 2017-11-14T22:13:51 Dropped triggers OK. Successfully altered `d1`.`alter_test_3`. ... |
※ 約 3時間 51 分 程度
計測：gh-ost
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
[root@test-4U-1 ~]# time ./gh-ost --execute --allow-on-master --alter="ENGINE=InnoDB" \ --user="root" --password="MySQL5.7" --host=localhost --database="d1" --table="alter_test_4" 2017/11/16 17:21:48 binlogsyncer.go:75: [info] create BinlogSyncer with config &{99999 mysql localhost 3306 root MySQL5.7 false false <nil>} 2017/11/16 17:21:48 binlogsyncer.go:241: [info] begin to sync binlog from position (mysql-bin.000010, 300561947) 2017/11/16 17:21:48 binlogsyncer.go:134: [info] register slave for master server localhost:3306 2017/11/16 17:21:49 binlogsyncer.go:568: [info] rotate to (mysql-bin.000010, 300561947) ... # Migrating `d1`.`alter_test_4`; Ghost table is `d1`.`_alter_test_4_gho` # Migrating test-4U-1:3306; inspecting test-4U-1:3306; executing on test-4U-1 # Migration started at Thu Nov 16 17:21:48 +0900 2017 # chunk-size: 1000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: ; critical-load: ; nice-ratio: 0.000000 # throttle-additional-flag-file: /tmp/gh-ost.throttle # Serving on unix socket: /tmp/gh-ost.d1.alter_test_4.sock Copy: 20000000/20000000 100.0%; Applied: 0; Backlog: 0/1000; Time: 3h54m11s(total), 3h54m9s(copy); streamer: mysql-bin.000015:650153753; State: migrating; ETA: due # Done real 234m13.495s user 7m30.079s sys 2m49.346s |
※ 3時間53分程度
ベンチマーク（更新クエリ実行）
上記の例を見てわかる通り、基本的にどの方法も実行時間に大きな差はありません。
次に ALTER TABLE 実行中に、並行して更新クエリを実行した場合を見てみましょう。
方法は簡単で、ALTER TABLE実行中に別コンソールで以下のコマンドを実行し、
500万行のINSERTを行います（ツール毎に対象テーブルは変更します）。
|
1 |
[root@test-4U-1 ~]# ruby create_test_data.rb 20000001 25000000 | mysql -u root -pMySQL5.7 d1 |
また、innodb_online_alter_log_max_size変数の設定値を引き上げる必要があります。/etc/my.cnf に以下の設定を追記しておきましょう。
innodb_online_alter_log_max_size = 1G
計測：ALTER TABLE（オンラインDDL）
|
1 2 3 4 5 6 |
[root@test-4U-1 ~]# sh -c "ruby create_test_data_t1.rb 20000001 25000000 | mysql -u root -pMySQL5.7 d1" & [1] 12979 [root@test-4U-1 ~]# date; mysql -u root -pMySQL5.7 d1 -e "ALTER TABLE alter_test_1 ENGINE=InnoDB" ; date 2017年 11月 20日 月曜日 16:08:36 JST 2017年 11月 20日 月曜日 18:41:49 JST |
※ 2時間33分程度
計測：pt-online-schema-change
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
[root@test-4U-1 ~]# sh -c "ruby create_test_data_t2.rb 20000001 25000000 | mysql -u root -pMySQL5.7 d1" & [root@test-4U-1 ~]# time ./pt-online-schema-change --execute --alter="ENGINE=InnoDB" D=d1,t=alter_test_2,u=root,p=MySQL5.7,h=localhost ... 2017-11-18T17:35:19 Creating triggers... 2017-11-18T17:35:19 Created triggers OK. 2017-11-18T17:35:19 Copying approximately 17773430 rows... 2017-11-18T23:30:30 Copied rows OK. 2017-11-18T23:30:30 Analyzing new table... 2017-11-18T23:30:30 Swapping tables... 2017-11-18T23:30:30 Swapped original and new tables OK. 2017-11-18T23:30:30 Dropping old table... 2017-11-18T23:30:33 Dropped old table `d1`.`_alter_test_2_old` OK. 2017-11-18T23:30:33 Dropping triggers... 2017-11-18T23:30:33 Dropped triggers OK. Successfully altered `d1`.`alter_test_2`. |
※ 5時間55分程度
計測：gh-ost
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
[root@test-4U-1 ~]# sh -c "ruby create_test_data_t3.rb 20000001 25000000 | mysql -u root -pMySQL5.7 d1" & [root@test-4U-1 ~]# time ./gh-ost --execute --allow-on-master --alter="ENGINE=InnoDB" --user="root" --password="MySQL5.7" --host=localhost --database="d1" --table="alter_test_3" | tee ./gh_ost.log ... Copy: 20000000/20000000 100.0%; Applied: 2554744; Backlog: 0/1000; Time: 7h20m0s(total), 7h19m58s(copy); streamer: mysql-bin.000020:811358234; State: migrating; ETA: due # Migrating `d1`.`alter_test_3`; Ghost table is `d1`.`_alter_test_3_gho` # Migrating test-4U-1:3306; inspecting test-4U-1:3306; executing on test-4U-1 # Migration started at Sun Nov 19 04:30:51 +0900 2017 # chunk-size: 1000; max-lag-millis: 1500ms; dml-batch-size: 10; max-load: ; critical-load: ; nice-ratio: 0.000000 # throttle-additional-flag-file: /tmp/gh-ost.throttle # Serving on unix socket: /tmp/gh-ost.d1.alter_test_3.sock Copy: 20000000/20000000 100.0%; Applied: 2554744; Backlog: 0/1000; Time: 7h20m1s(total), 7h19m58s(copy); streamer: mysql-bin.000020:811363039; State: migrating; ETA: due # Done |
※ 7時間20分程度
計測結果まとめ
実施したベンチマークの結果をまとめると、以下のようになりました。
| オンラインDDL | ALGORITHM=COPY | pt-online-schema-change | gh-ost | |
|---|---|---|---|---|
| ALTERのみ | 14分29秒 | 3時間51分 | 3時間51分 | 3時間53分 |
| ALTER + INSERT | 2時間33分程度 | ~~~~~ | 5時間55分程度 | 7時間20分程度 |
| 並列実行INSERT | 3時間50分 | ~~~~~ | 8時間33分 | 4時間13分 |
最適な選択肢はやはり標準のオンラインDDLと考えられます。
オンラインDDLが使えない場合は、pt-online-schema-change か gh-ost の二択となりますが、ALTER自体を早く終わらせたい場合は前者、並列で実行される更新クエリへの影響を最小限にしたい場合は後者が適しているように考えられます。
ただし、サーバスペックやテーブル定義・サイズなどによって変わってくる可能性は高いため、実際に利用する場合は自分の環境で入念に検証を行うことをお勧めします。
