対象環境

Aurora MySQL
pt-online-schema-change 3.2.0

なにも考えずにALTER TABLEを実行するとどうなるか？

既に動いているDBのカラム操作、インデックス操作などをするALTER TABLEクエリを実行すると、書き込みLockが発生してinsertやupdateができなくなります。対象テーブルのレコード数が相当に多い場合、書き込みをしようとしたプロセスは延々と待たされ続けることになるので、実質的にサービスを止めることになり得ます。

本番DBをコピーしたテスト用DBでやろうとしていたDROP INDEXをテストで走らせてみたのですが、ゆうに2時間はかかってしまいその間ずっと書き込みLockがかかり恐怖しました。

サービスを止めずにALTER TABLEを実行する方法

MySQL 5.6から導入されたオンラインDDLを使用する
pt-online-schema-changeなどのオンラインスキーマ変更ツールを使用する
自分でオンラインスキーマ変更ツールと同じ手順でSQLを実行する

今回は2つ目のpt-online-schema-changeを使った手法についてまとめます。

pt-online-schema-changeとは？

pt-online-schema-changeコマンドの詳細ドキュメント

RDBの機能だけではオンラインに定義変更ができない場合には、新規テーブル作成 -> データ移行 -> テーブル名を変更して入れ替え -> 旧テーブル削除と言ったような方法(簡略版)でオンラインに定義変更をします。これをツール上でいい感じにやってくれるツールがpt-online-schema-changeです。

インストール方法

$ sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
$ sudo yum install percona-release percona-toolkit

$ pt-online-schema-change --version
pt-online-schema-change 3.2.0

使い方

Usage:
pt-online-schema-change [OPTIONS] DSN

Detail Usage:
pt-online-schema-change --execute --recursion-method processlist --alter "DROP INDEX idx_user_ids" h=localhost,D=testdb,t=users

DSN(Data Source Name): 対象のデータベースを指定するための識別名

使うための要求

システム要求

PROCESS, SUPER, REPLICATION SLAVE global privileges, as well as SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, and TRIGGER table privileges should be granted on server.

PROCESS、SUPER、REPLICATION SLAVEのグローバル権限、およびSELECT、INSERT、UPDATE、DELETE、CREATE、DROP、ALTER、TRIGGERテーブル権限をサーバーに付与する必要があります。

その中でも今回重要なのはPROCESS権限、SUPER権限、REPLICATION SLAVE権限の3つです。まずPROCESS権限は必須なので付けましょう。

次にSUPER権限ですが、どうしても付けたくない場合やAmazonRDSの場合はグローバルのSUPER権限を付けることができないため別の解決策を取る必要があり、log_bin_trust_function_creatorsを1に設定することによって代わりとすることができます。 mysqlコンソールから設定する場合はSET GLOBAL log_bin_trust_function_creators = 1;を実行してください。 AmazonRDSの場合はこちらを参考にしてAWS Consoleから設定してください。

そしてREPLICATION SLAVE権限は--recursion-methodオプションを適切に指定することで権限を必要としなくなります。 --recursion-methodオプションはデフォルトでprocesslist,hostsが設定されていますが、--recursion-method processlistとすることで権限が必要なくなります。

ユーザー、パスワードの指定

オプションに--userや--passwordが存在しますが、コンソールにパスワードを表示したくないので設定ファイルを使って設定します。

/etc/percona-toolkit/pt-online-schema-change.confに以下のように記述しましょう。

user=実行ユーザー名
password=実行ユーザーのパスワード

これでコマンド実行時、自動的にこのファイルからユーザー情報を読み取ってくれます。

DSNの構文

DSNは対象のDBやテーブルを指定するものです。 KeyValueで指定でき、以下のような対応付けになっています。

  KEY  COPY  MEANING
  ===  ====  =============================================
  A    yes   Default character set
  D    no    Database for the old and new table
  F    yes   Only read default options from the given file
  P    no    Port number to use for connection
  S    yes   Socket file to use for connection
  h    yes   Connect to host
  p    yes   Password to use when connecting
  t    no    Table to alter
  u    yes   User for login if not current user

この中でもh、D、tあたりをよく使うと思います。 hはホストでlocalhostや~.rds.amazonaws.comなどを指定します。次にDはデータベース名でMySQL内でshow database;した結果の中から指定できます。最後にtはテーブル名でusersと言ったようなものを指定します。

そしてDSNを指定する時に気を付けないといけないのは、複数のKeyValueを指定する時スペースを空けてはいけないということです。なのでh=localhost,D=testdb,t=usersと指定します。

実行

全部整えた後にコマンドを実行すると処理が始まり、処理の様子が随時モニタリングされます。

Altering `testdb`.`users`...
Creating new table...
Created new table testdb._users_new OK.
Altering new table...
Altered `testdb`.`_users_new` OK.
2020-05-29T14:33:16 Creating triggers...
2020-05-29T14:33:16 Created triggers OK.
2020-05-29T14:33:16 Copying approximately 100000000 rows...
Copying `testdb`.`users`:   0%  remain

テーブルコピーがどの程度進んでいるか%で表示してくれるというだけでもそれなりに助かりますね。後は待っていればテーブルがLockされることもなくオンラインに定義変更をこなしてくれます。

もしのための策

クローン環境でテストする

本番DBと可能な限り近い環境で、かつDBが壊れても問題ないところで必ず検証しておきましょう。そして起こりうる問題と影響、実行時間などを詳細にまとめチーム内で見えるようにしておくことが大事です。

バックアップ

実行するコマンドや手順、問題、影響などをまとめ、いざ実行するという直前にDBのバックアップを作成しておきましょう。 RDSで言うならスナップショットを作成します。

念のため対象テーブルに比較的影響の少ない時間に実施する

オンラインで実行しますし、実行時間のほとんどはテーブルのコピーなので、実行中に問題が起きることはほとんどないはずです。そして実行中に行われるWrite処理(INSERT, UPDATE, DELETE)にはトリガーが設置されているので、もれなく新テーブルに反映されます。 pt-online-schema-changeは枯れた技術なので大体の場合は問題ありません。

途中で実行停止してしまった場合

コマンドを実行すると最初に新テーブルの作成、トリガーの作成が行われます。そして次にテーブルコピーが始まり、実行時間のほぼ全てはずっとコピーをしています。全てのコピーが終わった後、トリガーの削除や新旧テーブルの名前変更が行われます。

最初のテーブル作成やトリガー作成、テーブルコピー中に失敗したときに大きな問題はありません。中途半端なテーブルやトリガーが消えていない場合もありますが、手動で消してやれば綺麗な状態からやり直すことができます。

DROP TRIGGER IF EXISTS `{database名}`.`pt_osc_{database名}_{テーブル名}_del`;
DROP TRIGGER IF EXISTS `{database名}`.`pt_osc_{database名}_{テーブル名}_upd`;
DROP TRIGGER IF EXISTS `{database名}`.`pt_osc_{database名}_{テーブル名}_ins`;
DROP TABLE IF EXISTS `{database名}`.`_{テーブル名}_new`;

最後のテーブル入れ替え時に落ちてしまった場合はどうなるか具体的にはわかりません。しかし、大体はテーブル入れ替えができずにそのままか、トリガーが削除されていないか、くらいだと思うので上の対処で問題ありません。もしくはテーブルが消えていなければそのまま手作業で入れ替えるのもいいでしょう。

それでも何か致命的に壊れてしまった場合はスナップショットからいい感じに復旧しましょう。

NAKKA-Kの技術ブログ

技術に関する知見や考え方などを投稿します。

激重ALTER TABLEをpt-online-schema-changeを使ってサービスを止めずに実行するための方法