プロダクト開発チームの田中と申します。(社内ではたなしゅんと呼ばれております)
先日新機能のリリースを行いまして、リリース時に既存テーブルに対してのカラム追加が必要だったのですが、カラム追加のALTER TABLEが中々終わらないという問題が以前のリリース時に起きていたこともあり、事前に問題なくDBマイグレーション(Railsを利用しているため、この記事ではALTER TABLEなどのDB操作をマイグレーションと呼びます)が実施できるように調査を行いました。
その際に調査した内容や工夫したことなどを共有したいと思います。
過去のリリース時に起きた問題
ALTER TABLEが終わらない
メンテナンスタイム中にデータベースのマイグレーションができるのが理想ですが、hacomonoのサービスの特性上24時間運営の店舗様にもご利用いただいているため、頻繁にメンテナンスタイムを設けることが難しく、なるべく影響の少ない日中の時間で実施する必要がありました。
カラム追加のALTER TABLEが中々終わらなかった原因は、レコード数が多いテーブルに対してカラム追加をした際にコストの高いテーブルコピーが走っていたことが原因でした。
また、テーブルコピー中は該当テーブルへの書き込みが長時間ロックされてしまうという問題も発生していました。
マイグレーションが途中で終わる
マイグレーションの時間が長時間に及ぶことで途中でタイムアウトしてしまい、マイグレーションファイル内の一部のSQLだけが実行された状態になってしまったケースがありました。
hacomonoではRuby on Railsを利用していますが、途中でタイムアウトを引き起こしたことにより、同じマイグレーションファイル内の一部のSQLは実行済みだが、schema_migrationsのレコード上ではDOWNしているという状態になってしまっていました。
この問題が発生した当時は、そのままマイグレーションを再実行すると既にカラムが存在しているなどの理由でコケてしまうため、未完了のSQLをDBに対して直接発行し、schema_migrationsのテーブルを調整して該当のマイグレーションファイルをUP状態にすることにより復旧させました。
過去の教訓を活かして工夫したこと
オンラインDDLを活用する
hacomonoのDBはMySQLを利用していますが、 ALTER TABLEに時間がかかってしまう問題に対してはオンラインDDLという仕組みを利用することで、長時間ロックを発生させることなくDDLできるようにしました。
MySQLのオンラインDDLの利用方法については下記を参考にしました。
dev.mysql.com
オンラインDDLを利用することでALTER TABLEを実行中にINSERTやUPDATEなどの並列DMLを許可できるようになります。
注意点として、下記リファレンスに記載のように並列で実行されるDMLの量が多く、並列DMLを記録しているログのサイズが、innodb_online_alter_log_max_sizeで設定されている値を超えるとDDLは失敗します。
dev.mysql.com
オンラインDDLの場合でも、DDLの開始と終了時にメタデータロックがかかるタイミングがあるのも要注意です。
SELECT FOR UPDATEなどで長時間実行されるトランザクションが存在する場合は当該トランザクションが完了するまでDDLの操作は待機されます。
dev.mysql.com
オンラインDDLは特にSQLで明示をしなくてもMySQL側が自動的に適用してくれる場合もありますが、外部キーを伴うカラム追加の場合は並列DMLが許可されないなどと、一部自動でオンラインにならない制約があります。
下記のようにSQL上で”ALGORITHM”と”LOCK”をSQLで明示することで、オンラインにならないSQLの場合はエラーにしてくれるため、意図せぬDDLを防ぎたい場合は明示的に書いた方が安心です。
ALTER TABLE hoge ADD COLUMN `huga` INT NULL, ALGORITHM=INPLACE, LOCK=NONE, ...
また、今回は外部キーのカラム追加だったため、ALGORITHM=INPLACEとLOCK=NONEを指定する場合はエラーになってしまいますが、MySQLリファレンスの情報を元に “foreign_key_checks” を一時的に無効にすることでオンラインでDDLを実行することができました。
-- foreign_key_checksを無効にする SET FOREIGN_KEY_CHECKS = 0 ALTER TABLE ... -- DDL実行後にもとに戻す SET FOREIGN_KEY_CHECKS = 1
マイグレーションファイルの冪等性担保
オンラインDDLに対応させたことでタイムアウトすることはなくなりましたが、念の為を考慮して、途中までしかSQLが実行できなかったマイグレーションファイルを再実行しても結果が同じになるように、冪等性を担保することにしました。
今回は外部キーのカラム追加のマイグレーションファイルだったため、MySQLのINFORMATION_SCHEMA.STATISTICSを元に、インデックスの存在有無で各SQLを実行するかどうかを判定するようにし、既にカラムが追加済の場合は再追加しないようにしました。
-- ALTER TABLE発行前にインデックスの有無を取得 SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, SEQ_IN_INDEX FROM INFORMATION_SCHEMA.STATISTIC;
Rails側でインデックスの有無を判定できるメソッドが用意されているのを最近知りました… api.rubyonrails.org
また、1つのマイグレーションファイルに多くのSQLを入れないように適切にファイル分割をすることで、同じファイル内で一部だけコケるような事象がなるべく起きないように工夫しました。
まとめ
サービスの拡大化に伴いデータ量が増えてきたため、大量のレコードが存在するテーブルに対してのマイグレーションが段々と難しくなってきましたが、オンラインDDLを活用することでサービスダウンのリスクを低減し、新機能のリリースを行うことができました。
今後はpt-online-schema-changeなどの外部ツールを活用したスキーマ変更についてもご紹介していきたいと思います。
