Mirrativのバックエンド開発におけるMySQLとの向き合い方

こんにちは、バックエンドエンジニアのmakinoです。先日、LINE LIVEさんとの共催イベントにて「Mirrativを支えるバックエンド開発 ~MySQLとの向き合い方~」というテーマでLTをしました。

connpass.com

speakerdeck.com

今回はLTの内容から一部抜粋して、Mirrativのバックエンド開発において遭遇したMySQLに関する問題と、その対策について紹介します。

問題 その1

データ量/QPSの増加に伴って、非効率なクエリが顕在化した

サービス初期の段階ではデータ量が少なかったり、ユーザーのアクティビティが少ないために問題がなかったクエリも、サービスの成長に伴ってデータ量・QPSが増加したことによって、MySQLに負荷をかけてしまうことがありました。

具体例を以下にいくつか示します。

• 数千件レコードのfilesort
  • 適切なindexが利用できればMySQLはソートを行わずにORDER BY 句を満たすことができますが、indexが利用できないとfilesortと呼ばれるソートを行うことがあり、データ量によっては重いクエリとなってしまいます。
  • MySQL :: MySQL 8.0 リファレンスマニュアル :: 8.2.1.16 ORDER BY の最適化
• limit offsetを利用したページングを実装し、offsetが大きい値になったときに劣化する
  • limit 10 offset 10000のようなクエリは10,000件のレコードをスキャンした後に10件のレコードを返すので、offsetに大きい値が指定されると非効率なクエリになってしまいます。Mirrativの開発では、以下の記事に紹介されているようなカーソルベースのページングを実装することを推奨しています。
  • Evolving API Pagination at Slack - Slack Engineering
• パーティションプルーニングが効かないクエリを発行してしまう
  • パーティション化されたテーブルに対してクエリを投げるときにパーティションのキーとなるカラムが含まれていないと、全てのパーティションをスキャンすることになり、重いクエリになってしまいます。
  • MySQL :: MySQL 8.0 リファレンスマニュアル :: 24.4 パーティションプルーニング
• ユニークなキーを指定せずに排他ロックをかけてしまい、広範囲にロックをかけてしまう
  • InnoDB のデフォルトのトランザクション分離レベルである REPEATABLE READ では、ファントムリードを防ぐためにネクストキーロックという行ロックとギャップロックを組み合わせたロックを取得することがあります。これによって意図せず広範囲にロックをかけてしまうことがあるため、PRIMARY KEYもしくはUNIQUE KEYを指定し、行ロックのみで排他ロックを取ることが望ましいです。
  • MySQL :: MySQL 8.0 リファレンスマニュアル :: 15.7.3 InnoDB のさまざまな SQL ステートメントで設定されたロック

こういったクエリが混入してしまわないように、非効率なクエリを機械的に検知できるような対策を導入しました。

対策

①クエリ発行時にEXPLAINを実行し、非効率なクエリになっていないかチェックする

素朴なアプローチです。CIでEXPLAINを実行し、EXPLAIN結果にfilesortといった文言が含まれていないかチェックしています。
ただし、CI環境では本番環境と同等のデータ量を用意することができていないのもあり、本番環境とは実行計画が異なる可能性があります。
そのため、本番環境でもEXPLAINの実行を行っています。また、EXPLAINの実行によるオーバーヘッドがあるので、ユーザー影響のない頻度に絞って行うようにしています。

②クエリを宣言し、lintによってチェックする

MirrativではYAMLファイルにテーブル定義と利用するクエリを書き、DDLやクエリを発行するコードの生成を行っています。YAMLファイルのフォーマットは以下のようなイメージです。

user:
  columns:
    - name: user_id
      type: uint64
    - name: name
      type: string
    - name: locale
      type: string
  sql:
    - select * from user where user_id = :user_id

このYAMLに書かれたクエリに対してlintをかけて、ルールを守ったクエリになっているかチェックを行っています。

問題 その2

クエリがどこから発行されているのか分からない

スロークエリログなどから問題となるクエリが判明したとしても、そのクエリがアプリケーションのどこから発行されているのか調査に時間がかかるという問題がありました。

MirrativのバックエンドシステムはMVCのアーキテクチャを採用していたのですが、あらゆるレイヤーからSQLが投げられていました。また、ORMによってコード上で柔軟にSQLを組み立てることができていたのも、発見までに時間がかかる要因となっていました。

対策

①Clean Architectureへの移行

Clean Architectureを参考にシステムのアーキテクチャを再設計し、クエリを発行する責務を持ったレイヤーを明確に分離することで、見通しを良くしました。 詳細については以下の記事をご覧ください。 tech.mirrativ.stream

②クエリからコード生成を行う

前述したようにアプリケーションで利用するクエリをYAMLに書いておき、そこからコード生成を行っています。(https://github.com/kyleconroy/sqlcを参考に実装しています)
クエリとコードが1対1になり、問題となっているコードを特定することが容易になりました。

③クエリにコメントを仕込む

これもよくある素朴なアプローチですが、以下のようなコメントをクエリに仕込むことで、問題となるログを発見した際にどのようなコンテキストで発行されているかが分かるようになりました。

/* [endpoint]([trace_id]) */ select * from user where user_id = 1;

Mirrativでは、エンドポイント名やリクエストを識別するIDなどをコメントに仕込んでいます。

番外編

先日のイベントでは時間の都合上発表できなかったtipsを紹介します。

開発環境での強制的なレプリ遅延

MySQLにおいてレプリケーションを利用すると、数秒程度のレプリケーション遅延(スレーブへの反映の遅れ)が発生することがあるので、アプリケーション側ではレプリケーション遅延を考慮した実装を行う必要があります。 ですが、レプリケーション遅延はスレーブに負荷がかかったときに発生しやすく、開発環境では問題が発生せずに本番環境で初めて問題が発生するといったことが起こりがちです。

Mirrativでは遅延レプリケーション(MASTER_DELAYオプション)の設定を有効にすることで、開発環境では意図的に遅延を発生させて、レプリケーション遅延による問題を早期発見できるようにしています。 dev.mysql.com

まとめ

Mirrativのバックエンド開発において遭遇したMySQLに関する問題と、その対策を紹介しました。
今後の展望としては、本番同等のデータ量がある検証環境でCIを行ったり、人力によるレビューに頼ってlint化できていないルールがあるので、このあたりの改善を行うことでより堅牢なシステムを作っていければと思います。

また、今回はバックエンドチームの取り組みについて紹介しましたが、インフラチームによるMySQL運用ノウハウに関する記事もぜひご覧ください。 tech.mirrativ.stream

We are hiring!

高トラフィック環境で発生する問題に愚直に向き合うことが好きなバックエンドエンジニアを募集中です！

www.mirrativ.co.jp

speakerdeck.com