2017 12.27

MySQLのbinlogを使ってSSPの配信設定反映を40倍速くした話

: ジーニー

技術トピック

Share

はじめに

こんにちは、R&D本部アドプラットフォーム開発部の村岡です。

九州工業大学の先端情報工学専攻を予定通り修了してジーニーに17卒入社し、現在は主にGenieeSSPの開発を行っています。

以前こちらの記事を書きましたが、今回もMySQL関連の記事となります。

GenieeSSPについて

GenieeSSPは、広告配信のレスポンスタイムを短くするために、数十万の広告枠の配信設定をすべてインメモリで保持しています。

全広告枠の配信設定はMySQLに保存されています。配信設定を変更する、つまりDBのデータを変更する方法は、現在の運用では4つあります。

営業担当や、広告運用チームなどが操作画面を使って更新する。
操作画面では対応できない場合などに、エンジニアの運用チームが手作業で更新する。
配信パラメータ最適化のためのバッチが更新する。
リリース時などにエンジニアが権限をもらって更新する（平時は更新する権限がない）。

また、GenieeSSPは広告枠単位で配信設定をキャッシュしており、広告枠に紐づく何かしらの情報が変更となった場合は、その広告枠の情報をすべて再取得しキャッシュを更新します。

以上の事情から、GenieeSSP配信サーバが使用するMySQLのテーブルにはトリガーが設定されており、あるテーブルのレコードが変更された場合に、どの広告枠が変更となったか、わかるようになっています。

数十台あるGenieeSSP配信サーバの全てが、全広告枠の配信情報をインメモリキャッシュとして保持しており、各サーバが定期的にrefresh_counterテーブルのカウンターが更新されているか調べるクエリを投げ、どの広告枠を更新すべきか調べています。

GenieeSSPの課題

このようなキャッシュ更新の仕組みでは、ある広告枠の配信設定がひとつだけ変更となった際に、紐づく配信設定全てを再取得しなおさなければならない、という問題があります。配信サーバが使用するテーブルは60程度あり、一つの広告枠キャッシュを作成するために数百のクエリを投げなければなりません。

このキャッシュ更新の処理が重く、バッチによる大量更新が行われた際には、最悪2時間程度の間、配信設定が配信サーバに反映されないままになってしまう、という深刻な問題がありました。

今回、DBに配信設定が保存されてから、配信サーバに反映されるまで5分以内にする、という目標のもと、GenieeSSPの長年の課題であったこの配信設定反映遅すぎ問題を解決しました。

実装案

設計段階で出た実装案について、各案についてどのように実装しようとしていたか、なぜその案が採用されなかったか、最終的に選ばれた実装案はなぜ採用されたかについて説明します。

実装案を考えるときに重要だったポイントは、

テストが行えるような方法であること
既存のテーブルのトリガーを使用しないような方法であること
既存の運用方法で配信設定の反映が行われること

でした。

テストには、GenieeSSPが定期的にJSON形式でファイルに書き出す、全広告枠の配信情報が使えます。これは、GenieeSSPがクラッシュしたとき、JSONファイルがないと起動に二時間かかってしまうため、JSONから配信情報をロードすることで起動時間を短縮しています。高速化後の実装と古い実装で、このJSONファイルを出力させ比較すれば容易にテストが行えます。

案1：updated時間を見る

この実装案は、一つの広告枠の情報を取るのに数百のクエリを投げたくないなら、テーブルデータまるごと落としてしまえ、というかなり力技な案です。

まず、前準備として、DBの各テーブルに更新時間を記録するカラムを追加します。カラムの更新時間は自動で更新されるように、以下のように定義します。

---------------------
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
---------------------

GenieeSSPが起動するとき、配信に必要な約60のテーブルデータを全て取得します。テーブルデータを全てメモリに載せても10GBも使用しないため、今後、データが増えることを考えてもメモリ使用量としては全く問題になりません。

そして、キャッシュした各テーブルデータをもとに広告枠情報を構築するようにします。

キャッシュ更新は、以下のようなクエリを定期的に投げて、各テーブルに追加したupdated_atカラムを見るようにします。

---------------------
SELECT * FROM table WHERE updated_at > "最後のポーリング時刻";
---------------------

updated_atカラムの時刻が、以前更新したときの時刻から更新されていれば、データが変更されたとわかります。更新されたレコードから、キャッシュの再構築が必要な広告枠だけ、再構築を行うようにします。

この実装案は仕組み自体はとてもシンプルですが、採用されずお蔵入りとなりました。理由は、テーブルのレコード削除がわからない、という致命的な問題があったためです。

案2：操作画面変更時に配信サーバに通知する

この実装案は、操作画面で配信設定が変更されたら、配信画面側でJSONファイルを生成し、RedisかAerospikeに保存する、という案です。

GenieeSSPは、定期的に保存場所をポーリングし、更新された広告枠についてJSONをダウンロードしデシリアライズしてキャッシュを更新します。

GenieeSSP側の変更はほぼ必要なく、操作画面側でJSONを作る処理を追加する必要があります。

この実装案が没となった理由は、操作画面以外からDBを更新された場合に対応できない、操作画面のコードの保守が大変になってしまうためです。

案3：MySQLのBinlogを使う

この実装案は、案1をベースに、DB更新の検知をMySQLが出力するbinary log（binlog）を見るようにする、というものです。

まず、起動時に約60のテーブルデータ全てを、通常のSELECTクエリで取得します。その後、MySQLサーバにスレーブとして接続し送られてくるbinlogを処理し、インメモリのテーブルデータを更新していきます。 MySQLにクエリを投げるのは起動時のみ、あとは送られてくるbinlogを処理するだけです。

binlogをハンドリングするためのライブラリとして、MySQL Labsで公開されているlibbinlogeventsを使用します。

案1と同様に、更新されたレコードから、キャッシュの再構築が必要な広告枠を計算し、キャッシュの再構築を行うようにします。

この実装案は、experimentalなライブラリであり、社内社外ともに実績があまりないlibbinlogeventsを使用するため、本当にうまくいくのか疑問ではありましたが、以下の点でメリットがあり採用に至りました。

他の案と異なり既存の運用にも対応できること
配信設定の反映時間は最速が狙えること
変更がpushされるため、ポーリングするより効率的
何より純粋にbinlogを使う実装が面白そう

実装

GenieeSSP配信サーバが、experimentalなライブラリを使用してMySQLサーバにスレーブとして接続するのはあまりにもリスキーです。何かあったときに、配信サーバが全滅しかねません。 GenieeSSPは幸いにも、JSONファイルから配信設定をロードする機能があるため、これを利用することにします。

具体的には、MySQLサーバにスレーブとして接続しbinlogを処理、JSONファイルを生成する配信キャッシュ作成サーバを開発します。

DBのレコードが更新されると、以下のような流れで配信設定が反映されるように実装を行います。

現状では、更新されていない広告枠も含む、全ての広告枠情報をJSONに出力しています。これにより、テストが容易になる、実装が容易になる利点があります。ただし、配信キャッシュ作成サーバから、JSONを各GenieeSSP配信サーバに転送するときはサイズが大きいため時間がかかってしまいます。

Zstandardで圧縮することでファイルサイズを98%削減できましたが、プロダクション環境ではZstandardが新しすぎるためにパッケージがない・バージョンが古く、自前でビルドしなければならなかったため、一旦LZ4で圧縮しています。 LZ4の方が少し処理時間は長く、ファイルサイズを94%削減できています。

配信キャッシュ作成サーバ

クラスをJSONでシリアライズする部分など、流用できる/流用したいコードが多くあること、libbinlogeventsがC++だったことなどから、配信キャッシュ作成サーバもC++17で開発しました。

LL言語のほうが良いんじゃないか、とも言われましたが、

libbinlogeventsのバインディングを書くことがめんどくさい
大量にメモリを使うのでマークスイープ系のGC走ると止まる時間長そう
シリアライズ部分とか並列化したいけどプロセス並列やりたくない
そもそも速度が最重要なのでC++でいいじゃん

という理由でC++です。個人的にはRustを使いたかった気持ちもありますが、Rustは別の仕事や趣味で使うことにします。

処理フロー

約60あるテーブルデータを保持するクラスを手で書くのは流石に大変なので（一度手で書こうとして止められた）、コードジェネレータを作りました（後述）。

GenieeSSPから流用したコードは”広告枠の情報を再構築、再シリアライズ”する部分となります。当然ですが、GenieeSSPが投げていたSQLクエリは流用できないので、全てC++で書き直して実装しました。

C++で書き直したので何をしているのかわかりづらい…と思うかもしれませんが、元のSQLクエリが既に意味不明な部分も多かったため、全体としてそれほど読みにくくなってはいないように思います…が、SQLクエリのままのほうがやはり簡潔で良いと思います。

コードの生成

キャッシュ生成に必要なテーブルのテーブル名、及び、必要なカラム名などを記述したJSONファイルをもとに、MySQLサーバにSHOW CREATE TABLEクエリを投げてカラムの型情報、カラムインデックスなどを取得し、C++のコードを生成するコードジェネレータが必要となり作りました。

コードジェネレータを作ることで、あとからデバッグのための関数追加、コードの修正などが容易に行えるようになり開発がスムーズに進みます。

また、binlogからカラムデータ取得、MySQLにクエリを投げてデータを取得するコードは、JSONファイルにテーブル名、カラム名を追加しコードジェネレータを叩くだけで全て生成されるため、メンテナンスも容易になります。

今回はJinja2テンプレートエンジンを使いましたが、扱いやすく、テンプレート中で制御構文など使えて簡潔にテンプレートを書くことができました。

binlogのハンドリング

大真面目に実装していけばMySQLが作れます。

今回はその必要はないので、以下のイベントのみハンドリングします。

TABLE_MAP_EVENT
XID_EVENT
UPDATE_ROWS_EVENT
WRITE_ROWS_EVENT
DELETE_ROWS_EVENT

TABLE_MAP_EVENTは、ROWベースのbinlogにおいて、全ての行操作イベントの前に表定義とその後のイベントの値をマップするイベントです。このイベントを見ることで、DB名、テーブル名、カラム定義などがわかります。

XID_EVENTは、テーブルの変更をするトランザクション処理がCOMMITされると発生するイベントです。このイベントを見て、中途半端なところでbinlogを処理しないようにする必要があります。

UPDATE_ROWS_EVENT、WRITE_ROWS_EVENT、DELETE_ROWS_EVENTはそれぞれ、レコードがUPDATE、INSERT、DELETE時に発生するイベントです。

おそらく、QUERY_EVENTをハンドリングすることでテーブルのALTERなどもわかるはずですが、試したことはないです。配信キャッシュ作成サーバでは、テーブルをALTERするときは新しいテーブル定義を使ってコード生成しなおし、再リリースすることにしています。テーブルのALTERはめったに行わないため、この運用で問題ありません。

スレーブとしてMySQLサーバに接続したあと、binlogのポジションを設定する必要があります。 binlogポジションはSHOW MASTER STATUSコマンドを投げることでわかります。 SELECTクエリでテーブルデータを取得したあとは、binlogによる更新となるのでbinlogポジションはちゃんと設定してあげなければデータの整合性がとれなくなります。

今回は、MySQLサーバにSTOP SLAVEして、テーブルデータとbinlogポジションを取得、MySQLサーバにスレーブとして接続してからSTART SLAVEするようにしています。そのため、配信キャッシュ作成サーバ専用のMySQLサーバを動かしています。

libbinlogevents ver1.0.2を使用しましたが、このライブラリには重大な問題があります。それは、

Decimal型のカラムの値を正しく取得できないこと
DateTime型のカラムの値を正しく取得できないことがあること

です。

これらの問題は、libbinlogeventsにそれらのデータを処理するコードがないことが原因です。この問題を修正するには、MySQLのソースコードを漁って、MySQLがbinaryフォーマットからdouble、DateTimeに変換している部分のコードを移植してやる必要があります。

どちらもすぐに処理を行っているコードは見つかります。 MySQL本体とべったり書かれているわけではないので、移植もわりとやりやすいと思います。

特にDecimalについては、内部でどういう風に扱われているのか全く知らなかったため、MySQLのソースコードとコメントを読むのは楽しかったです。

テスト

テスト環境に配信キャッシュ作成サーバ専用のMySQLサーバを置きます。このサーバは本番サーバとレプリケーションするようにしておき、このMySQLサーバに古いGenieeSSPと配信キャッシュ作成サーバを接続し、一定時間ごとにレプリケーションを止めて、生成されたJSONを比較します。

JSONの比較のためのツールをPythonで書きましたが、やたらとメモリ食いで40〜50GB程度使っていました。しかもGCが走ると数十秒平気で止まるので、なかなかテストが進みません…仕方ないですね。

2〜3週間程度、自動でテストが動くようにして、毎日差分をチェック、問題があれば調べて修正を繰り返しました。

最後の一週間は問題のある差は出なかったため、プロダクション環境に一台だけリリースして一週間程度様子を見てから全台リリースを行いました。

リリース後

配信設定反映まで、最悪2時間かかっていましたがリリース後は約3分程度となっています。 3分の内訳は、

Binlog処理・JSONシリアライズ…1秒
全広告枠分のJSONをRamdiskに書き出し…5秒
LZ4で圧縮…30秒
転送…30秒
配信サーバのJSONロード…平時10秒程度
残りは、ファイルの転送はcronで毎分行われているためそのディレイと、配信サーバが新しいJSONファイルを見つけるまでのディレイ

となっています。

binlog周りで問題が発生しても、MySQLに通常のクエリを投げてテーブルデータを全件取得するのにかかる時間は１分程度なので、毎回全件取得で更新できるようにもしています。この場合は更新に少し時間がかかるようになりますが、5分以内更新は達成できます。

上図はリリース後のMySQLサーバの負荷です。データの転送量、CPU負荷が格段に下がっています。

MySQL Enterprise Monitorで見られる、受け付けたクエリの統計です。リリース後は、赤線のSELECTクエリが圧倒的に減っています。

上図は配信キャッシュ作成サーバの負荷です。配信キャッシュ作成サーバのCPU負荷は高くありません。定期的に走るバッチによる大量更新でもそれほど負荷は上がらず、更新できています。

ただし、テーブルの全データに加え、数十万の広告枠のシリアライズ済みのJSONデータを保持しているため、メモリ使用量は多く25GB程度使っています。 25GBに、数GBのJSONファイルをRamdiskに保存するため、場合によってはメモリ64GBのサーバでもカツカツだったりします。

ネットワーク帯域もかなり消費していますが、これは今後、差分更新の仕組みなどを入れることでかなり改善すると思われます。

おわりに

9月頃に設計を開始し、libbinlogeventsが使えるかの検証、コーディング、テスト、ドキュメントなども書いたりしていたら、リリースが12月になってしまいました。

とりあえずリリースしてから、二週間経っていますが、大きな障害は起きておらず快調に動いています。

お客様等からの声はまだほとんど届いていないのでわかりませんが、今後開発を行っていき、広告配信のパラメータを高頻度に更新していける環境ができあがったと思っています。今まで、この性能の問題から更新頻度が低く抑えられていたものなど頻度を上げてパラメータを最適化していければ、メディア様の収益もあげていけるでしょう。

とりあえず、二ヶ月程度様子を見て、テーブルのトリガー削除や、旧GenieeSSPの不要コードの完全削除などを行う予定です。その後は、差分更新の仕組みを入れてより高速に配信設定を反映できるようにしていきたいと思っています。

正直、タイトルの”binlogを使って”は高速化のキモではなく、MySQLにクエリを投げる回数を減らしてC++で処理するようになったら速くなった、という感じではあります。ただ、MySQLのbinlogを使う、という経験はなかなかないと思うので、これはこれで楽しい経験と思っています。

libbinlogeventsを使う上で、以下の2つの記事には大変助けられました。この場を借りてお礼申し上げます。ありがとうございました。