MySQLのALTER TABLEで、無停止でテーブル定義変更できるpt-online-schema-change

運営中のサービスにどんどん機能追加していくと、ある時テーブルの定義を変更したくなったり、インデックスを追加したくなるときがあります。

運営中なので、できるだけサービスを停止させたくないですが、運営中なので、それなりにレコード数が増えていて、ALTER TABLEが数秒で終わらないレベルになっていたりします。

無停止でテーブル定義変更できるpt-online-schema-change

そこで、pt-online-schema-changeです。

スクリーンショット 2016-03-31 22.42.26

pt-online-schema-change

pt-online-schema-changeは、Percona ToolkitというMySQLの便利コマンド群のうちのひとつで、その名の通り、オンラインでテーブルの定義を変更できるツールです。

どうやって無停止で更新をかけるかという話は、次のページにとても分かりやすい図付きで説明されています。

無停止でALTERできるPercona-Toolkitのonline-schema-change

簡単にいうと、動いているテーブルとは別に、新しい定義を適用したテーブルを作って、そちらにデータをコピーした上で、新旧のテーブルを入れ替える、というものです。

750万行くらいのテーブルで検証

検証したのは750万行くらいのuserテーブルで、カラムを追加するような作業です。

ALTER TABLE USER ADD hoge text NOT NULL DEFAULT '' after fuga;

テキトーに1秒に1回くらいのページでユーザー作る処理をさせながら、実験してみることにします。

while :
do
  time curl -I http://localhost/register_user
  sleep 1
done

もうちょっとまともな負荷のかけ方がある気がしますが・・・。

普通にALTER TABLE

普通にALTER TABLEを実行すると、1分46分くらいで終了する感じでした。

mysql> ALTER TABLE USER ADD hoge text NOT NULL DEFAULT '' after fuga;
Query OK, 0 ROWS affected, 1 warning (1 MIN 46.07 sec)
Records: 0  Duplicates: 0  Warnings: 1

検証した環境がMySQL 5.6なので、ALTER TABLEの実行中も書き込みはできるのですが、一時的にかなり書き込みに時間がかかるタイミングがありました。

時刻 書き込み数
14:42 55
14:43 55
14:44 51
14:45 38
14:46 52
14:47 55
14:48 55

一番悪いときでは、数秒くらいアクセスが通らない状態で、ユーザーがページを開くのを諦めてしまうレベルです。

pt-online-schema-changeを使ってみる

実行するALTER TABLE文を含む、こういうコマンドを書いて実行します。

pt-online-schema-change --alter "add hoge text not null default '' after fuga" h=mydatabase.xxxxxxxx.ap-northeast-1.rds.amazonaws.com,D=mydatabase,t=user,u=myuser,p=******** --set-vars sql_mode=\'\' --drop-old-table --statistics --alter-foreign-keys-method auto --recursion-method processlist --dry-run

長い・・・!

オプションについては、ドキュメントを参照してください。

–dry-runのオプションがありますが、実際に実行する場合は、ここを–executeに変えて実行します。

ちなみに、実行中にテーブル一覧を確認すると、一時テーブルが作成されています。

mysql> SHOW TABLES;
+---------------------------+
| Tables_in_mydatabase      |
+---------------------------+
| _user_new                 |
# ...
| USER                      |
+---------------------------+
33 ROWS IN SET (0.01 sec)

更に、このテーブルの中を見てみると、新しいテーブル定義が適用済みで、データがどんどんコピーされてきます。

pt-online-schema-changeを実行した結果

上のコマンドを、コマンドラインに投げ込むと、ログが流れはじめ、テーブルの定義変更が始まります。

大部分はデータのコピーの時間ですが、こんな感じで進捗が表示されます。

2016-03-11T14:07:54 Copying approximately 7581843 rows...
Copying `mydatabase`.`user`:   7% 06:30 remain
Copying `mydatabase`.`user`:  13% 06:15 remain
Copying `mydatabase`.`user`:  20% 05:46 remain
# ...

だいたい6分くらいで終了しました。普通にALTER TABLEするより時間がかかります。

時刻 書き込み数
14:06 54
14:07 55
14:08 44
14:09 45
14:10 44
14:11 49
14:12 46
14:13 46
14:14 53
14:15 55
14:16 55

ただ書き込みの劣化はゆるやかで、リクエストが極端に遅延するような感じではありませんでした。

まさに無停止でのテーブル定義の変更に成功している形!

1億レコードのテーブルで実行した場合

ちなみに、1億レコードある別のテーブルでも検証してみました。

Altered `mydatabase`.`_click_new` OK.
2016-03-11T15:05:07 Creating triggers...
2016-03-11T15:05:08 Created triggers OK.
2016-03-11T15:05:08 Copying approximately 11802820 rows...
Copying `mydatabase`.`click`:   1% 31:14 remain
# ...

定義の変更には30分くらいかかりましたが、こちらもリクエストには大きな影響を与えることなく完了することができました。

Amazon EC2にPercona Toolkitをインストール

最後に、AWS環境での設定のメモをしておきます。

Percona Toolkitは、EC2にインストールしました。Chefを使っているので、レシピを探したところ、下記がありました。

percona-toolkit Cookbook – Chef Supermarket

・・・が、EC2でうまく動かせなかったので、下記のようなレシピを書いて実行しました。

package 'perl-DBD-MySQL' do
  action :install
end
package 'perl-Time-HiRes' do
  action :install
end
package 'perl-IO-Socket-SSL' do
  action :install
end
remote_file "#{Chef::Config[:file_cache_path]}/percona-toolkit-2.2.17-1.noarch.rpm" do
  source 'https://www.percona.com/downloads/percona-toolkit/2.2.17/RPM/percona-toolkit-2.2.17-1.noarch.rpm'
  not_if 'rpm -qa | grep -q percona-toolkit'
  action :create
  notifies :install, "rpm_package[percona-toolkit]", :immediately
end
rpm_package 'percona-toolkit' do
 source "#{Chef::Config[:file_cache_path]}/percona-toolkit-2.2.17-1.noarch.rpm"
 action :install
end

これで、EC2からpt-online-schema-changeコマンドが実行できます。

Amazon RDSで実行する場合

MySQLは、Amazon RDSを使っているのですが、RDSの場合は少し設定が必要なようです。こちらも参考になりました。

Amazon RDSのMySQLでオンラインメンテをどうやればいいかを調べた

RDSの場合は、トリガーを作成するために、パラメータグループからlog_bin_trust_function_creatorsを変更します。

スクリーンショット 2016-03-11 22.55.51

RDS MySQLインスタンスにてトリガを設定する」も参考になります。

まとめ

ざーっと触ってみて、こんな感じです。

  • pt-online-schema-changeを使うと、無停止でテーブルの定義変更できる
  • 少し書き込みの性能は落ちるが、ALTER TABLEするよりは軽い
  • デメリットとして、ALTER TABLEよりも処理時間はかかる
  • 億単位のレコードがあっても定義の変更ができる

この検証のあと、実際に運営中のサービスにも適用しましたが、無事にアクセスの遮断やレイテンシの上昇なしにテーブルの定義変更を終えることができました。

About katty0324

Comments are closed.

Scroll To Top