ビズリーチサービスRestart Project
ここからはDBREとしてデータマイグレーションをサポートした際のサービスとの関わり方と手法について紹介します。DBREというよりも、DBAという要素が強いかもしれませんが、サービスサポートも私たちの重要な役割の一つです。
Slow Query撲滅に向けて
ビズリーチサービスはサービス開始から10年が経過しましたが、データの肥大化によるSlow Queryの増加が課題になったり、1つのカラムで複数の意味を持ってしまっていたりなど、データそのものが煩雑な状態になっていました。
この状況を打破するため、Slow Queryの削減やテーブルリファクタリング、エラーログの解消を含めたアプリケーションモジュールのリファクタリングなど、非機能要件に全力を注ぐ、という決断がされました。
今回はその中でキーポイントになったテーブルリファクタリング時の初期データ投入方法についてお話しさせていただきます。
テーブルリファクタリング時の初期データ投入方法
方法例
テーブルリファクタリングに関してはさまざまな資料で紹介されています。サービス停止の時間を最小化するための、一般的な方法を雑に記載すると下記のような形になると思います。
- 新規のDDLを本番環境に適用
- 本番データからデータセットを抜き出して新スキーマにimport
- トリガーなどをはじめとしたDBの機能でimport、もしくは更新処理をしているアプリケーションの箇所に変更を加え、2つのテーブルに同時に書き込みを行う（ダブルライト）
- Read処理をしているアプリケーションモジュールを1つずつ修正してすべてのReadモジュールが切り替わったら変更前のテーブルを削除する
上記は数ある手法のうちの一例ですし、サービスを停止して、完全に定点を取得してからデータマイグレーションを行う方法を選択するなど、その時のサービスやDatabaseの状況によってアプローチは大きく変わると思います。そういった手法を紹介している書籍やブログなどはありますが、具体的に初期データの投入に関して言及しているものはあまり多くないと感じていたため、ここでは初期データ投入にフォーカスしてお話ししたいと思います。
今回多かった例として下の図のように2つ以上のテーブルの情報を取得し、1つのテーブルを作るという要件があります。A Tableの中からB Tableのある条件にマッチするものだけをNew Tableに入れてアプリケーションの実情に沿ったスキーマ設計に直すことが狙いです。
テーブルリファクタリング方法に関して
前提となりますがビズリーチサービスでは主にRDS for MySQLを使用しています。その上で私たちが取ったアプローチを紹介させていただきます。
- Masterに新規スキーマを反映する
- Masterから新規でReplicaを作る
- ReplicaをStop Slaveし定点を取得
- Replicaに対して新規スキーマと構成がほぼ同じTmp Tableを作成
- 初期データセットをtsvで出力
- Tmp Tableにload data
- Tmp TableからmysqldumpでDMLを取得
- ReplicaをStart Slave
- Masterに1行ずつInsert
ざっくりとした流れですが、上記のような方法をとりました。仕様によってさまざまな方法が取れるので、それほど使い回しが効くものでもなかったため、割り切ってShell Scriptでライトに組みました。
続いて、「なぜTmp Tableを作ったのか」「なぜdumpを取得して一行ずつInsertする方法を取ったのか」など合わせて、このスクリプトの仕様を簡単に紹介させていただきます。
1. Masterに新規スキーマを反映する
事前にリファクタリング後のテーブルをMasterに反映しておくことで後々の作業がしやすくなるので、あらかじめスキーマリリースをできるタイミングで行っておきます。
2. Masterから新規でReplicaを作る
1で行われた新規のスキーマ定義が反映された状態でOperation用のReplicaを作りました。ここはMigrationしか行われないため、多少負荷が上がったり、Replication遅延が発生してもほとんど気にせずに作業できるメリットがあります。
後述しますが、このReplicaにはTmp Tableを作成するなど、本番とは別でWRITE処理をさせるため、Master－Slave間でスキーマの違いが発生することを前提としています。これらを実現させるために、途中でread_onlyを切り替える必要があるので、他のReplicaとは別のParameter Groupを用意しました。
3. ReplicaをStop Slaveし定点を取得
新しく作ったReplicaはアプリケーションからのリクエストを一切受け付けていないので、Stop Slaveした時点でStart Slaveをするまでデータが更新されることはありません。逆にStart Slaveがされればその間にされた更新情報は反映されます。
コマンド的には該当のRDS上で下記を実行するだけです。
mysql > CALL mysql.rds_stop_replication;
もちろんこのタイミングで一緒にSlave Statusをチェックすることも忘れません（笑）。Slave_IO_Running、Slave_SQL_Runningのどちらも止まっていることを確認します。
mysql > SHOW SLAVE STATUS;
4. Replicaに対して新規スキーマと構成がほぼ同じtmp tableを作成
Replicaに対して書き込み処理を行うことになるので、Parameter Groupのうちのread_onlyをOffにします。
$ aws rds modify-db-parameter-group --db-parameter-group-name ${db_parameter_group_name} --parameters ParameterName=read_only,ParameterValue=0,ApplyMethod=immediate
反映までにタイムラグがあるので、
mysql > SHOW VARIABLES LIKE 'read_only';
を実行して確実にOFFになったことを確認します。
この上で該当のReplica上にCreate Tableします。ここで新規スキーマと構成がほぼ同じと言っていたのは単にデータロードの時間を短縮するために、不要なINDEXやForeign Keyはすべて削除しているものを作成した、という意味です。
ただし、Unique制約は外すと事故を起こす可能性高いのでUnique Keyは外しませんでした。
5. 初期データセットをtsvで出力
サービス側に提供してもらったクエリを流してファイルにリダイレクトします。この際の注意する必要があるのは、
- データマイグレーションは大量データを扱うため、--quickオプションをつけないとクライアントサーバ側が処理できなくなってしまう
- オリジナルのデータにNULLがあった場合にtsvファイルで出力したものをそのまま入れるとNULLという文字列になってしまう
という点です。
特にNULLに関しては取り扱いが面倒でした。例えば文字列型であれば、使われないであろう文字列NNNNUUUULLLLLLLLという文字列に置換したり、日付であれば0000-01-01 00:00:00、数値であれば-1（該当のテーブルでマイナスを扱う必要が無かったため）など、カラムの型やその用途に合わせて置換を行いました（確実な正解があるわけではなく、これらの値が入っている可能性も捨てきれないのでSELECTを行ってから決める必要があります）。
(
CASE
WHEN A_TABLE.HOGE_TEXT IS NULL THEN 'NNNNUUUULLLLLLLL'
ELSE A_TABLE.HOGE_TEXT
END
) AS HOGE_TEXT,
(
CASE
WHEN A_TABLE.HOGE_DATE IS NULL THEN '0001-01-01 00:00:00'
ELSE A_TABLE.HOGE_DATE
END
) HOGE_DATE,
基本的に私たちはクエリのシンタックスを気にする程度で、UNLOADするためのクエリ自体はサービスの仕様に詳しいエンジニアに作ってもらいました。クエリそのものを私たちが作らない、というのはサービス直属のDBAではないため、サービスの仕様を理解できておらず、スキーマ情報だけでは判断できないマジカルな仕様を知らなかったということもあります。
もちろんやりたいことを聞いて、その上で一緒にクエリを作ったり、作ってもらったクエリを確認してOperation用のReplicaのみにINDEXをつける、などDatabaseに限られたことは行います。
6. tmpテーブルにload data
出力したファイルをLOAD DATA INFILE構文を使ってデータをimportしていきます。
とはいえ、あまりに大量データを一気に流すと処理が途中で落ちてしまうため、今回は10万行ずつsplitして実行しました。また--show-warningオプションを付けて、すべて正常終了することを確認します。
全データが入った後、Beforeで取得した件数とTmp Tableに入ったデータの件数が合っていること、また置換した文字列が正しくNULLで入力されているかをチェックする。ということも合わせて行いました。
このNULLチェックのやり方を簡単に説明すると、次の通りです。
- 該当のテーブルからNULLABLEなカラムリストを取得
mysql > SELECT COLUMN_NAME FROM information_schema.columns WHERE table_schema = '${target_db}' AND TABLE_NAME = '${table_name}' AND IS_NULLABLE = 'YES';
- NULLABLEなカラムを入れながらチェッククエリを作って流す
mysql > SELECT SUM(CASE WHEN HOGEHOGE IS NULL THEN 1 ELSE 0 END), ... hogehoge FROM TMP_NEW_TABLE \G;
- 置換した文字列が入ってないかを同じように確認する
mysql > SELECT SUM(CASE WHEN HOGEHOGE IS 'NNNNUUUULLLLLLLL' THEN 1 ELSE 0 END), ... hogehoge FROM TMP_NEW_TABLE \G;
これらを組み合わせてチェックしていきました。
7. Tmp TableからmysqldumpでDMLを取得
今回mysqldumpを選んだのはnet_buffer_lengthの範囲内で、バルクインサートをよしなに分割してくれるためです。
Masterに対して一気にデータをimportするとFKのLockやReplica Lag（レプリケーション遅延）などが発生し、結果としてサービスに負の影響を与えてしまうことが想定されます。この後記載しますが、どうやってMasterに対して負荷を最小限にしつつ、確実に流し込むか、そして一番楽にできるかを考えた結果、mysqldumpという選択に至りました。
8. ReplicaをStart Slave
Start Slaveの前にread_onlyをOFFに戻す必要があるため、awsコマンドから実行します。この時read_onlyをONにしたタイミングからOFFに戻すまでの時間が短いと、正常に反映されなかったため、Sleepとリトライを組み合わせて対応しました。
$ aws rds modify-db-parameter-group --db-parameter-group-name ${db_parameter_group_name} --parameters ParameterName=read_only,ParameterValue=1,ApplyMethod=immediate
mysql > CALL mysql.rds_start_replication;
9. Masterに1行ずつInsert
出力されたステートメントを1行ずつ流しSleepを入れることで、サービスから流れてくる更新ステートメントをできる限り邪魔せずにReplicationに渡すようにしました。また、万が一何か問題が発生した場合に処理を停止した場合でもRollbackが走った際の影響を少なくできます。
そうすることにより、サービスに対する影響を最小限にして、データを確実に入れられるようにしました。これが少し遠回りをしてでも最終的にmysqldumpを使って初期データを入れることを選択した1番のメリットです。
データ投入後～アプリケーションモジュール（ダブルライト）～Readモジュールリリースまで
上記手法でアプリケーションを稼働させつつ、ある定点下での初期データの投入は無事にできました。とはいえ、やはりサービス稼働状態にある、ということを考慮すると初期データ投入からアプリケーションモジュールリリースまでの間に、データ更新がなされることを前提に動く必要があります。ビズリーチサービスは、アプリケーションモジュールリリースのおよそ2時間前、というタイミングが多かったです。
そのため、初期データ投入からアプリケーションモジュールリリースまでの間に発生した差分を改めて取り込む必要があります。
専用のReplicaをStop Slaveした上で上記5で使用したクエリと同じクエリを流す
この時点で入っているべき全データは、初期データ投入で使用したクエリとまったく同じであるはずなので、これをまずは流します。
New Tableを全件取得
この全件と、上記で取得した全データの差分がその2時間の間に更新されたデータである、ということで、その差分を新テーブルにINSERTします。
ここでの考え方として重要なポイントは割り切りです。
NEW Table上のデータはまだアプリケーションから見られていない、という前提で、DELETE－INSERTという選択をしました。対象のデータが入っていればDELETEしてからINSERT、入っていなければINSERTを行うことで帳尻を合わせます。
これを何度も繰り返すことで差分が少しずつ解消され、最終的に0になったらReadモジュールのリリースができる、といった判断ができます。もちろんダブルライト自体にバグがあることによって差分が発生することも考えられるので、出力された差分は一本一本見ていく必要があります（特に月末月初のタイミングで走るバッチなどの考慮は欠かせないですね）。
こうして無事に初期データ投入が完了しました。
もちろんこのやり方以外にもたくさんの方法があります。
データマイグレーションは特に仕様だったり、テーブルの特性でさまざまな手法を取ることになるので、このタイミングではShell Scriptで組むというPlatform-nizeしづらい方法を選択してしまいましたが、今後需要があればStep Functionsなどを利用する、ということも検討したいと思います。
余談：ビズリーチサービスRestart Projectの現在
まだ完全にリファクタリングも終わっていませんが、現在のビズリーチサービスは徐々に健康的な状態を取り戻しています。INDEX戦略も同時に行うことでSlow Queryも順調に減っています。
Slow Query撲滅という名目で新規機能のリリースを止めて推進することは、一般的には難しいことも多いと思います。しかし、それを理解して推進してくれた役員やマネージャー陣、そしてサービスのアプリケーションエンジニアの皆に理解してもらえたことに大変感謝しています。
この取り組みによってDBAだけでなくアプリケーションエンジニアがDatabaseを意識してコーディングをしていく。といった文化形成がなされ、アプリケーションエンジニアにとってもDBAにとっても過ごしやすい環境になりました。
まとめ
私たちの考えるDBREのアクティビティについて少しイメージいただけましたでしょうか？
私たちの場合はまだこのチームが出来たばかりということあり、 理想だけを求めてDBREを実践していくにはまだまだ時間もリソースも経験も知識も足りないものばかりです。
だからこそ、こうあるべき、ということに固執しすぎず、これから柔軟にビズリーチ流のDBREを進化させていきたいと思います。
