DBRE(Database Reliability Engineering) の活動〜テーブルリファクタリング編〜

こんにちは、BizReachでDBREチームをやらせてもらっているあわっちです。

今回はDBREとしてデータマイグレーションをサポートした際のサービスとの関わり方と手法について、令和最初のTech Blogとして紹介します。

DBREというよりも、 DBAという要素が強いかもしれませんが、サービスサポートも私たちの重要な役割の１つです。前回のDBRE(Database Reliability Engineering) の活動〜 Backup Platform編〜とは少し違って泥臭いオペレーションのお話になりますが、お付き合いいただければと思います。

ビズリーチサービスRestart Project

Slow Query撲滅に向けて

ビズリーチサービスはサービス開始から10年が経過しましたが、データの肥大化によるSlow Queryの増加が問題になったり、１つのカラムで複数の意味を持ってしまっているなど、データそのものが煩雑な状態になっており、サービスの継続に影響を与える状況が発生していました。

例えるならば 重りを乗せてマラソンさせている 様なイメージでしょうか。

この状況に危機感を持ち、アラートを上げたところ、5ヶ月以上に渡って、 新規開発を止め Slow Queryの解消を含めたテーブルリファクタリング、エラーログの解消を含めたアプリケーションモジュールのリファクタリングなど、非機能要件に全力を注ぐ、という決断が下されました。

今回はその中でキーポイントになった テーブルリファクタリング時の初期データ投入方法 についてお話しさせていただきます。

テーブルリファクタリング時の初期データ投入方法

方法例

テーブルリファクタリングに関しては様々な資料で紹介されています。サービス停止の時間を最小化するための、一般的な方法を雑に記載すると下記の様な形になると思います。

新規のDDLを本番環境に適用
本番データからデータセットを抜き出して新スキーマにimport
トリガーなどをはじめとしたDBの機能でimport、もしくは更新処理をしているアプリケーションの箇所に変更を加え、２つのテーブルに同時に書き込みを行う (ダブルライト)
Read処理をしているアプリケーションモジュールを１つづつ修正して全てのReadモジュールが切り替わったら変更前のテーブルを削除する

上記はほんの一例だと思いますし、また、その時のDatabaseの状況によってアプローチは大きく変わると思っています。

(そもそも私自身はサービスを止めてでも完全に定点を取得し、その上でデータマイグレーションを行いたいタイプの人間でした。)

この中で、具体的に初期データの投入に関して言及しているものはあまり多くないと思ったので、初期データ投入にフォーカスしてお話ししたいと思います。

今回多かった例として下の図の様に２つ以上のテーブルの情報を取得し、１つのテーブルを作るという要件があります。 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でライトに組みました。

(ライトと言いつつ2,000行超えてしまいましたが。。)

続いて、「なぜ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上で下記を実行するだけです。

1

mysql > CALL mysql.rds_stop_replication;

もちろんこのタイミングで一緒にSlave Statusをチェックすることも忘れません(笑) Slave_IO_Running, Slave_SQL_Running のどちらも止まっていることを確認します。

1

mysql > SHOW SLAVE STATUS;

4. Replicaに対して `新規スキーマと構成がほぼ同じ` tmp tableを作成

Replicaに対して 書き込み 処理を行うことになるので、 Parameter Groupのうちのread_onlyをOffにします。

1

$ aws rds modify-db-parameter-group --db-parameter-group-name ${db_parameter_group_name} --parameters ParameterName=read_only,ParameterValue=0,ApplyMethod=immediate

反映までにタイムラグがあるので

1

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を行ってから決める必要があります)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


    (
        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なカラムリストを取得

1

mysql > SELECT COLUMN_NAME FROM information_schema.columns WHERE table_schema = '${target_db}' AND TABLE_NAME = '${table_name}' AND IS_NULLABLE = 'YES';

NULLABLEなカラムを入れながらチェッククエリを作って流す

1

mysql > SELECT SUM(CASE WHEN HOGEHOGE IS NULL THEN 1 ELSE 0 END), ... hogehoge FROM TMP_NEW_TABLE \G;

置換した文字列が入ってないかを同じ様に確認する

1

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とリトライを組み合わせて対応しました。

1

$ aws rds modify-db-parameter-group --db-parameter-group-name ${db_parameter_group_name} --parameters ParameterName=read_only,ParameterValue=1,ApplyMethod=immediate

1

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でMySQLを叩くときのコツ

Shell Scriptからクエリを流す場合に下記のように作業をしていた方も多いのではないでしょうか。

1

$ mysql -u ${mysql_user} -p -h ${mysql_host} -P ${mysql_port} ${target_db} -e "${query}";

MySQL 5.6以降ではこれをこのまま叩くと

1
2
3
4
5
6
7


$ mysql -u ${mysql_user} -p${mysql_password} -h ${mysql_host} -P ${mysql_port} ${target_db} -e "SELECT 1";
mysql: [Warning] Using a password on the command line interface can be insecure.
+---+
| 1 |
+---+
| 1 |
+---+

のようにWarningが標準出力されてしまい鬱陶しいですよね。

Shell Scriptの場合、これを下記のように対応してあげるとWarningが出力されずスッキリします。

1
2
3
4
5
6


$ MYSQL_PWD=${mysql_password} mysql -u ${mysql_user} -h ${mysql_host} -P ${mysql_port} ${target_db} -e "SELECT 1";
+---+
| 1 |
+---+
| 1 |
+---+

mysqldumpコマンドでも同様ですね。

私の場合、手が勝手に -pを入れてしまうのと MYSQL_PWD が大文字なので、意識しないでできるようになるまでは苦労しましたが(笑)、慣れてしまうととても使い勝手がいいです。使ってない方がいらっしゃいましたら (きっとMySQL歴が長い人ですね) 試してみてください。

まとめ

またまた長くなってしまいましたが、今回はテーブルリファクタリングの中で特に頭を悩ませる初期データ投入の部分に対する私たちのアプローチについてお話しさせていただきました。

もちろんこのやり方以外にもたくさんの方法がありますよね。

データマイグレーションは特に仕様だったり、テーブルの特性で様々な手法を取ることになるので、このタイミングではShell Scriptで組むというPlatform-nizeしづらい方法を選択してしまいましたが、今後需要があればStep Functionsなどを利用する、ということも検討したいと思います。

余談: ビズリーチサービスRestart Projectの現在

まだ完全にリファクタリングも終わっていませんが、現在のビズリーチサービスは徐々に健康的な状態を取り戻しています。

INDEX戦略も同時に行うことでSlow Queryも順調に減っています。

Slow Query撲滅という名目で何ヶ月も新規機能のリリース止めることは、普通の会社では難しく、それを理解して推進してくれた執行役員、MGRs、そしてサービスのアプリケーションエンジニアの皆に理解してもらえたことに大変感謝しています。

アプリケーションエンジニアにとっては面白くない期間を過ごさせてしまったかもしれないことは申し訳なく思っていますが、この取り組みによってDBAだけでなくアプリケーションエンジニアがDatabaseを意識してコーディングをしていく。といった文化形成がなされ、アプリケーションエンジニアにとってもDBAにとっても過ごしやすい環境になりました。

レッツジョイン(お約束)

DBREチームを発足はしたのですが、とはいえやりたいことに対して仲間はまだまだ足りません、実はまだDBREチームの専任は２名です！

DBREに興味のある方、是非ご応募ください。いまDBAやってるよって方からソフトウエアエンジニアやSREだけどDBREに興味あるよって方もご応募お待ちしております。

採用情報のページはこちら DBRE

Data

awache

MySQL と特攻の拓が好きな DBRE、トラブル時には心の中で不運(ハードラック)と踊(ダンス)っちまったと唱えてます

DBRE(Database Reliability Engineering) の活動 〜 テーブルリファクタリング編 〜