kubell Creator's Note

ビジネスチャット「Chatwork」のエンジニアのブログです。

ビジネスチャット「Chatwork」のエンジニアのブログです。

読者になる

pt-online-schema-changeを用いたスキーマ変更 on Amazon Aurora MySQL

SRE AWS Database

こんにちわ。id:cw-tomitaです。
この記事は、Chatwork Advent Calendar 2020 - Qiita の3日目の記事です。

早速ですが、皆さんはauto incrementなRDBのtableのidにはどのような型を利用されていますか？ Chatworkでは多くのテーブルでint型が指定されているのですが、９年前にサービスを開始した時には想像もつかないくらいに多くのお客様に使っていただけるサービスへと成長した結果、int型なauto incrementのidを利用しているテーブルで、レコード数がintの上限を迎えそうなテーブルが複数現れ、どかっとまとめてALTER TABLEを実施するという2020年を代表する一大イベント(?)がありました。

今回は、その時に利用したpt-online-schema-changeというツールを使ってのALTER TABLEの実施に関して、記事を書きたいと思います。

tl;dr

  • テーブルのidの型を定義するときはケチらずにbigintにしよう (違

pt-online-schema-change とは？

CyberAgentさんのエンジニアブログにとても分かりやすい説明がのっており、これ以上に上手く説明できそうもないので、こちらをご覧になっていただくことで代替とし、私の方からの説明は省略したいと思います。(そして、ツールを使い始めるにあたって、イメージが掴みやすい、とても参考になる記事でした。ありがとうございました！！)

ameblo.jp

なお、公式ドキュメントにある通り、実際にこのツールを利用される際は、CyberAgentさんの記事やこの記事だけでなく、しっかりと公式のドキュメントを読んで検証を重ねた上で、導入を進めるようにしてください。

www.percona.com

pt-online-schema-change alters a table’s structure without blocking reads or writes. Specify the database and table in the DSN. Do not use this tool before reading its documentation and checking your backups carefully.

このツールにたどり着いた理由

今回のALTER TABLEは、スナップショットから起動したDBで試した結果、25時間程度かかることが分かっていました。 Chatworkでは、これまで長時間TABLE LOCKをしてしまようなALTER TABLEを実行するときは、

  • スナップショットから、もう１つclusterを構築
  • 新しく起動したcluster上でALTER TABLEを実行
  • 本番稼働中のclusterから、ALTERを実行した新clusterに向けてbinlogによるレプリケーションで既存clusterと同期した状態にもっていく
  • 停止メンテナンスを入れて、clusterをがっちゃんこする
  • (プラスで、万が一の切り戻し用に、逆向きのレプリケーションを貼る)

という手順で行なっていました。

以下の公式ドキュメントで紹介されているものをベースにした手順となります。 https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.MySQL.html

特に大きな問題に遭遇することなく、運用できてはいたのですが、以下の２点の理由で違う方式を検討し始めました。

理由その１：Auroraをbinlog_format =off で運用するようになり、計３回の停止メンテナンスが必要

先に紹介したオペレーションを行なっていた頃は、binlog_format=ROW でAuroraを稼働させていたのですが、昨年、AuroraのSAの方と集中してお話させていただく時間があり、その時に、Auroraのfailover時のcrush recoveryが速くなるので、普段binlogを使っていないのであれば、binlog_format=OFFにすることが推奨ですと教えてもらいました。

以下のドキュメントにも記載があります

If you don't need the binary log for external replication (or an external binary log stream), we recommend that you set the binlog_format parameter to OFF to disable binary logging. Doing so reduces recovery time.

Amazon Aurora storage and reliability - Amazon Aurora

この方式のALTER TABLEを行う頻度はかなり少ないですし、それよりはfailover時のrecoveryが最速になって、サービス影響少ない方がいいよねってことで、推奨通り、binlog_format=OFFにして運用をしているのですが、結果、ALTER TABLEを行うにあたっては、以下のように３回の停止メンテナンスを実施する必要があります。

  • (1回目) binglog_format=ROWにする
  • (2回目) ALTER TABLEを実行したclusterへの切り替え実施
  • (3回目) binglog_format=OFFに戻す (切り戻し用に逆向きのレプリケーションを貼るということをやっているので、２回目のタイミングでは戻せない)

現状、予定停止メンテナンスを入れる場合は、日曜のAM4時〜6時の間で行うというルールになっているので、最短で行おうとすると、３週連続で日曜日の4時前に起きる必要があり、生活リズム的にこれは中々しんどいです。

理由その２：binlogでのレプリケーションのAuroraのバグを踏んだ

とはいえ、他の手段を検証するのもそれなりにパワーのいることですし、データベースのオペレーションに関しては、実績のある方法を採用したくなる、、ってのは本番データベースのオペレーションやったことある人であれば、分かってもらえる気持ちではないかとも思います。
他にもやりたいこともたくさんある中で、新しいやり方にの検証に多くの時間を割いて、さらに、それをドキドキしながら本番に実行するよりは、、というところで、従来の方法でリハーサルを始めたのですが、なぜかbinlog経由でのレプリケーションがすぐにエラーで止まってしまうという事象に遭遇。。
手順を何か間違えてるのかな〜、、と、何度も手順書を見直しては、やり直してみたのですが、同じエラーで停止してしまいます。 これはデータベース側がおかしいのでは、、とあれこれと調べていたら、自分が苦しんでいるのと、とても似た事象がMySQLでbugとして報告されていました。

MySQL Bugs: #98799: Replication failing on valid scenario

すぐにサポートに問い合わせて、３日ほどで、MySQLのバグに起因したバグである旨の連絡はきたのですが、修正スケジュール等は未定であるとの回答。 今回のALTER TABLEはint型のidの枯渇が見えてきたために必要に迫られて計画しているもので、Auroraの修正スケジュールが未定と言われてしまうと、idが枯渇する前に修正が間に合いますように🙏 と祈るだけというわけにもいかず、また、理由その１でも挙げた運用の辛さもあったので、代替手段の検討に入ったのでした。

なお、結果的には、Auroraの修正自体は報告してから２週間程度で修正バージョンが出て、修正された旨の報告をいただきました。（AWSスピーディー！）

Bug#15831300 SLAVE_TYPE_CONVERSIONS=ALL_NON_LOSSY NOT WORKING AS EXPECTED

Aurora MySQL database engine updates 2020-09-02 (version 1.23.0) - Amazon Aurora

が、その時点で、結構ツールの検証は進んでいて、だいぶいい感じだったし、今後の運用を考えても、新しい方式を挑戦する価値はあるだろう！？ということで、今回のALTER TABLEはpt-online-schema-changeを使った方式で行うことにしました。

gh-ostとの比較

似たようなことを行なってくれるツールの有名どころとして、github社が公開しているgh-ostというツールを思い浮かべる方もいるのではないかと思います。 https://github.com/github/gh-ost

今回、私たちはこのツールはドキュメントに目を通しただけで、実際に検証をするには至りませんでした。
理由としては、pt-online-schema-changeがtriggerベースであるのに対し、このツールはbinlogをベースとしたツールなので、

  • 理由その２にあげたバグが悪さしてしまう可能性が結構あるのでは？
  • Chatworkがbinlog=offにして運用していることを先に書きましたが、そもそも、Aurora的にはbinlogは使わないに越したことはない
  • MySQL用に開発されたツールで、SQLやtriggerベースのものと比べると、binlogの方がMySQL特有の何かに依存している可能性が高くハマりやすそう、、？

という所から、まず、pt-online-schema-changeを第一候補として検証し、要件を満たすツールであることがわかったため、gh-ostについては深入りしませんでした。

もちろん、これは今回の私たちのユースケースに対しての要件にpt-online-schema-changeがマッチしたというだけで、要件によってはこちらのツールが当てはまるパティーンもたくさんあると思います。特に、gh-ostの公式ドキュメントにある通り、triggerlessであることで、パフォーマンス劣化なく移行作業ができるというのは、gh-ostの大きなメリットだな〜と感じています。

gh-ost/why-triggerless.md at master · github/gh-ost · GitHub

pt-online-schema-changeは、既存データの新しいテーブルへのコピー + triggerによるデータ同期が発生するため、今回のオペレーションでも目に見えるCPUの利用率増がありましたし、更新の激しいテーブルが対象であれば、その恩恵はさらに大きなものになりそうです。

Chatworkでの実行例

本来的には、オンラインでALTER TABLEを実行するためのツールではあるんですが、これまで停止メンテナンスを入れて切り戻し可能な形でやってきたという歴史があるというのと、私が入社する前のことではありますが、初期のAuroraの不具合でALTER TABLEを実行したらデータベースがおかしくなった、、みたいな経験をしたというトラウマが会社にあるので、初回は、だいぶ安全よりに倒そうと考えて、

  • テーブルの切り替えは停止メンテナンス中に行う
  • 逆向きのtriggerを貼り、ALTER TABLEによって、予期せぬパフォーマンス劣化やエラーが発生し始めた場合、最短時間で、切り戻しが行えるようにする

という方法を採用することにしました。
文字だけだと少し分かりづらいかと思いますので、最初に図で全体の流れを表してみます。



step1. データ同期の開始 (オンライン)

既存データのコピー完了後、step2までの間

step2. テーブルがっちゃんこ 〜 逆向きトリガーの作成 (停止メンテナンス中)

step3. 逆向きtriggerの削除 & 旧テーブルの削除 (オンライン)


以下、具体的なコマンドを交えて手順を紹介していきたいと思います。 （繰り返しになりますが、実際にこのツールを利用される際は、しっかりと公式のドキュメントを読んで、検証を重ねた上で、導入を進めてください）


step1. データ同期の開始 (オンライン)

## tmux等、セッションがきれても大丈夫な方法で実行すること
$ pt-online-schema-change --alter "MODIFY id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT" D=test_db,t=table1 \
 --host DB_HOST --user ROOT_USER --password ROOT_PASSWORD \
 --chunk-index id \
 --no-drop-triggers \
 --no-swap-tables \
 --critical-load Threads_running=500 \
 --print \
 --execute > pt-online-schema-change.txt

特徴的なパラメータに関しての少し補足しておきます。

  • --chunk-index id -> データのコピーの進捗状況が追跡しやすいようにauto incrementで採番されるidを指定
  • --no-drop-triggers -> データのコピーが終わっても、既存テーブル to 新テーブルへのデータ同期を続ける必要があるため、triggerは削除しない
  • --no-swap-tables -> テーブルがっちゃんこは停止メンテナンス中に手動で行うため、同期完了後にswapしない

既存データのコピー完了後、step2.の作業を行うまでの間、triggerによって２つのテーブルは同期された状態が保たれます。

step2. テーブルがっちゃんこ 〜 逆向きトリガーの作成 (停止メンテナンス中)

この作業を行う際は、データロスを防ぐために、DBへの接続が０になっている等、対象テーブルへの挿入・更新が発生していないことは確実に担保する必要があります。

## trigger削除
## trigger名は"SHOW TRIGGERS FROM test_database" で確認可。デフォルトだと、こういう風にネーミングされる。
$ mysql -h DB_HOST -u DB_USER -p  -e 'DROP TRIGGER IF EXISTS `test_db `.`pt_osc_test_db_table1_del`'
$ mysql -h DB_HOST -u DB_USER -p  -e 'DROP TRIGGER IF EXISTS `test_db `.`pt_osc_test_db_table1_upd`'
$ mysql -h DB_HOST -u DB_USER -p  -e 'DROP TRIGGER IF EXISTS `test_db `.`pt_osc_test_db_table1_ins`'

## テーブルがっちゃんこ
## "_TABLE_NAME_new" というのが、pt-online-schema-changeで作られるデフォルトのコピー用のテーブル名
$ mysql -h DB_HOST -u DB_USER -p  -e "RENAME TABLE table1 TO _table1_old" test_db
$ mysql -h DB_HOST -u DB_USER -p  -e "RENAME TABLE _table1_new TO table1" test_db


## 逆向きトリガー作成
$ mysql -h DB_HOST -u DB_USER -p test_db < insert_trigger.sql
$ mysql -h DB_HOST -u DB_USER -p test_db < update_trigger.sql
$ mysql -h DB_HOST -u DB_USER -p test_db < delete_trigger.sql

## (任意) INSERT, UPDATE, DELETE クエリを投げて、triggerの動作確認


逆向きトリガーに関してはコマンド内に埋めてしまうと、ちょっと読みづらいかと思い、下に切り出してみました。スクラッチで書く必要はなく、pt-online-schema-changeを実行して作成されるtriggerをSHOW TRIGGERS FROM test_table でもってきて、テーブル名を調整すればOKです。
一点補足で、trigger内にOLDやNEWという単語が出てきますが、これはtrigger内で利用できるkeywordになります。

Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger. OLD and NEW are MySQL extensions to triggers; they are not case-sensitive.

MySQL :: MySQL 5.6 Reference Manual :: 20.3.1 Trigger Syntax and Examples

insert_trigger.sql

DELIMITER $$ 

CREATE TRIGGER table1_ins_to_old 
AFTER INSERT on table1
for each row 
  REPLACE INTO `test_db `.`_table1_old ` (`id`, `field1`, `field2`, `field3`) 
   VALUES (NEW.`id`, NEW.`field1 `, NEW.`field2 `, NEW.`field3 `);

$$

DELIMITER ;

update_trigger.sql

DELIMITER $$ 

CREATE TRIGGER table1_upd_to_old 
AFTER UPDATE on table1
FOR EACH ROW 
  BEGIN 
    DELETE IGNORE FROM `test_db `.`_table1_old ` WHERE !(OLD.`id` <=> NEW.`id`) AND `test_db `.`_test_table_old `.`id` <=> OLD.`id`;
    REPLACE INTO `test_db `.`_table1_old ` (`id`, `field1`, `field2`, `field3`)  VALUES (NEW.`id`, NEW.`field1 `, NEW.`field2 `, NEW.`field3`);
  END;
$$

DELIMITER ;

delete_trigger.sql

DELIMITER $$ 

CREATE TRIGGER table1_del_to_old 
AFTER DELETE on table1
FOR EACH ROW 
 DELETE IGNORE FROM `test_db `.`_table1_old ` WHERE `test_db `.`_table1_old `.`id` <=> OLD.`id`;
$$

DELIMITER ;


step3. 逆向きtriggerの削除 & 旧テーブルの削除 (オンライン)

しばらく並行稼働して、もう安心だ！ってなったタイミングで実施します。

# trigger削除
$ mysql -h DB_HOST -u DB_USER -p -e  'DROP TRIGGER IF EXISTS `test_db `.`test_table_ins_to_old `'
$ mysql -h DB_HOST -u DB_USER -p -e  'DROP TRIGGER IF EXISTS `test_db `.`test_table_upd_to_old `'
$ mysql -h DB_HOST -u DB_USER -p -e 'DROP TRIGGER IF EXISTS `test_db `.`test_table_del_to_old `'

# 旧テーブル削除
$ mysql -h DB_HOST -u DB_USER -p -e 'DROP TABLE IF EXISTS `test_db `.`_test_table_old `'


おまけ. 本番実行時に発生した問題

何度も検証を重ね、本番実行しましたが、検証では拾いきれない問題が発生するのが本番オペレーションの難しさ。。
２つほど、問題に遭遇しました。２つ目の方はちゃんとした原因を特定できていないので、こんな理由では？みたいなのが分かる方がいたら、コメント等いただけると嬉しいです🙏

step1のコマンド実行後、--critical-load に引っかかってデータ同期が停止

pt-online-schema-changeには、--critical-load というパラメータがあるのですが、

type: Array; default: Threads_running=50 Examine SHOW GLOBAL STATUS after every chunk, and abort if the load is too high.

https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html#cmdoption-pt-online-schema-change-critical-load

db insightを見た感じは全然余裕そうだし、万一このプログラムが暴走した時の安全弁ともなるしなという所で、最初はデフォルトの設定で実行したのですが、結構時間が経ってから、しかも、負荷がめっちゃ低い深夜に、閾値をした旨のログとともに、データ同期が停止されてしまいました。 既にアナウンス済みの計画停止メンテナンスを絡めたオペレーションなのに、データ同期を最初からやり直さないといけなくなり、だいぶ焦りましたが、幸いリカバー可能なタイミングだったので事なきを得ました。。
特に大きなテーブルに対してのALTERを実行する時、このパラメータの取り扱いには注意した方が良さそうです。今回はめっちゃ大きな値をしていしましたが、これだと安全弁として機能しなくなってしまうので、どこを落とし所にするといいのかは、各運用者の見極めが求められるポイントになりそうです。

triggerの作成タイミングで処理が詰まった

step1の問題があったため、本番DBに対して2回コマンド実行したのですが、2回目の時に、既存テーブルから新テーブルへのデータ同期用のtrigger作成に60秒程度かかってしまい、その間、テーブルがロックされ、サービスが一瞬詰まるという問題も発生してしまいました。
テーブルロックを取ること自体に時間がかかることはあっても、ロックを取ってからtrigger作る時間はほぼ一定に収まるはず、、と思っていましたが、そうではなかったようで、ここは根本原因を調べきれなかったので、次にこのオペレーションやるときには、事前検証で原因を突き止めきりたい所です。

まとめ

以上、Aurora推奨のbinlogを有効にしない状態で、数時間、数十時間とかかるALTER TABLEを実行できる、pt-online-schema-changeの使い方を紹介してみました。
今回紹介したパティーンは停止メンテナンスを挟んでのALTER TABLEでしたが、元はオンラインでスキーマ変更を実行するためのツールであり、後日、オンラインでのALTER TABLEをこのツールでやる機会もありましたが、特に問題なく完了することができました。 とはいえ、一抹の不安があるからやっぱり切り戻せるようにしたい、、ということもあるかと思うので、両方のパターンを使い分けつつ、進化させつつ、大小問わずALTER TABLEに対する工数的・心理的な負荷を減らして、ALTERが大変だからと歪なテーブル構成にすることなく、適切なデータ構造を維持し、サービスの健全な成長に貢献したい所存です。
また、そもそもの話になってしまいますが、この一大イベント、とても良い経験にはなりましたが、そもそもテーブルのidの型がintでなくてbigintで定義されていれば、この時間を他の作業にあてれたんだな〜、、みたいな気持ちもなくもないというか、すごくあります。。テーブルレビューを行う時、サービスの伸びに伴ってデータがガンガン増えていきそうなテーブルなのに、int型のidフィールドがあったら、「int型で賄えるくらいにしかこのサービス/機能は伸びない前提なの？」みたいなツッコミを適切に入れてくようにして、将来的な工数最適化を図らないという話をチームでしたりもしたので、皆様もテーブル設計をレビューする時にはお気をつけください！w

Chatworkではデータベースオペレーションをどんどん進化させていきたいエンジニアを空前絶後に絶賛大募集中です！

hrmos.co