こんにちは。DIGGLEエンジニアのzakkyです。最近、長女の高校受験が終わって少し落ち着きました。
今回から何回かに分けてDIGGLEの肝となるレポート（多軸分析）の集計処理部分のパフォーマンス向上施策についてお話しします。
今回のお題
今回はPostgreSQLのプロシージャ導入に至るまでの技術選定部分をご紹介します。
レポート機能では何ができるのか
改善施策として何を行ったのかを語る前に、まずは背景として弊社のレポート機能を簡単に紹介させてください。
弊社のレポートは自由度が高く、大きく以下の様な機能があります。
- 任意の計算式を作ることができる（Excelで計算式を作るようなイメージ）
- 任意の軸を指定した分析ができる
- 分析結果からドリルダウンができる
それぞれの機能の細かい内容までは掘り下げませんが、「サーバーへの負荷が凄いことになりそう」と感じていただければ幸いです。
課題となっていたこと
ユーザーの状況
昨年あたりから、弊社営業・マーケ・CSの尽力で様々なお客様と契約いただき、それに伴い事業規模の大きなお客様にご利用いただく機会が増えてきました。
それ自体はとても嬉しいことなのですが、事業規模が大きいため、投入される分析用のデータ量も多くなり、取り扱うべきデータ量が以前と比べて飛躍的に増大する結果となりました。
結果として、よくある話ではあるのですが、パフォーマンス劣化が発生する事態となり、特に大量のデータを使って分析を行うレポート（多軸分析）では、処理時間が長くなってしまうという問題が発生していました。
ボトルネックとなっている箇所の洗い出し
弊社のサービスでは、レポート（多軸分析）を表示するまでの間に行う処理として大きく以下の3つがあります。
- 登録されているデータをレポート用に加工する
- レポート用に加工したデータをユーザーが指定した任意の軸・条件で取得する
- フロントエンド側で描画する
この中で、今回一番パフォーマンス低下が顕著だったのが「登録されているデータをレポート用に加工する」箇所となります。
他の2点に関しては、以前に改善施策を打っていたこともあり、パフォーマンス低下もそこまで顕著では無かったため、今回の改善対象とはしませんでした。
調査の際には、DatadogのAPMなどを使うことで、どこがボトルネックになるのかを視覚的に把握することができます。ご参考までにリンクを貼っておきます。
具体的にどこに問題があったのか
前述のDatadogのAPMや、サーバーのCPU/メモリなどを調査した結果、以下のような問題があることが分かりました。
- RDBから取得したデータをRailsに取得するまでに以下のオーバーヘッドが掛かる
- SQLの実行時間
- ネットワークI/Oのオーバーヘッド（イントラネット内だとしてもデータ量が多いと問題になり得ます）
- 取得した結果をRails上に展開するのにもオーバーヘッド
- 扱うデータ量が増えるとメモリを圧迫し、CPU使用量が増える（スワップが起きていると推測）
なぜ問題が起こっているのか
弊社のサービスの特徴にも関係してくるのですが、前述の問題が起こる理由として以下のようなことがあります。
最初にも軽く触れましたが、弊社のサービスでは「任意の計算式を作ることができる」という機能があり、「登録されているデータをレポート用に加工する」際には、同時に計算式部分の値まで計算しておく必要があります。
そして、「任意の計算式を作ることができる」機能では、集計した結果を別の場所で参照し、更に別の場所で参照して・・・。といった表現が可能となります。
ここまで聞いただけで頭が痛くなってくるかと思いますが、つまり、集計する際は単純にSQLでgroup byするのではなく、それぞれの依存関係を見ながら計算していく必要があり、どうしても一度データをどこかに展開する必要が出てきます。
「だったら集計した結果だけ良い感じに残しておくようにすれば扱うデータ量自体は減るじゃないか！」と、思われるかもしれませんが、そちらも最初に触れた「分析結果からドリルダウンができる」ためには、一番深くまでドリルダウンした時の値まで保持しておく必要があり、簡単には取り扱うデータ量を減らすことができません。
今回行うべき事項は何なのか
ここまでで洗い出した問題点から、満たすべき要件と、改善すべき事項を導き出します。
満たすべき要件
- 計算式の値を算出できる
- ドリルダウンができる
改善すべき事項
- 現在のI/Oと比べて同等か、もしくはそれ以上高速にRDBからデータを取得する
- 大量のデータをメモリなどのI/Oが高速な場所へスワップが起こらないように負荷なく展開する
何を改善したのか
先ほどまでで課題の検討を行って何を行うべきかを導き出したので、ここからはどうやって実現するかを考えていきます。
今までの構成の延長で何とかならないか
今回の改善を行う前までは、「登録されているデータをレポート用に加工する」機能はRailsサーバー上で実行していました。
計算式の値を算出するためには、かなり複雑なロジックを記述する必要があり、バックエンドサーバーとして使っているRails上で実装するのが現実的である。という判断でした。
そのため、検討当初はRailsサーバー上で何とか改善できないかと検討もしたのですが、実は、高速化については私が入社した2019年当初より、継続的に検討＆改善を行ってきた関係で、Rails上で行える改善はやり尽くしている感があり、これ以上の劇的な改善は難しいとの結論を出さざるをえませんでした。
そして結果として、Rails以外で解決する方法が無いかを検討することとなりました。
具体的にどうやって高速化するか
Rails以外にも裾野を広げて検討していく中で、大きく以下のような案が出ました。
- Rails以外の高速な言語でデータ集計用にサーバーを新規で立ち上げる
- メリット 完全に独立した集計特化の構成を組める
- デメリット どの言語を使うのかの選定が必要／劇的に改善するかはやってみないと分からない／導入までに時間が掛かる
- Redshift、BigQuery、Snowflakeなどを導入する
- メリット 弊社が扱うデータ量であれば超高速に処理できる
- デメリット RDBからデータを流すオーバーヘッドが掛かる
- RDB（PostgreSQL）上でRailsでやっていた内容を実行する
- メリット 新しく何かを構築する必要が無い／同一サーバー上で処理が完結するのでネットワークI/Oをゼロにできる
- デメリット PL/pgSQLを使って計算式の値を算出することは難しい
結果としては、上記のメリット／デメリットに加えて、開発工数、リスク、サービスとしての今後の見通しなど様々な要因を鑑みて、一番下の「RDB（PostgreSQL）上でRailsでやっていた内容を実行する」を選択することとなりました。
使用言語の策定
RDB（PostgreSQL）へ処理を移譲するという事で、真っ先に思いつくのがプロシージャ（PL/pgSQL）です。 ただ、前段でデメリットとして挙げた通り、計算式の値の算出のような複雑な処理をPL/pgSQLで書くのは、現実的ではありません。（プロシージャの開発経験がある方であれば、同意いただけるのではないでしょうか）
ということで、PostgreSQL内で実行可能な他の言語の調査を開始しました。
そんな中、nodeベースで記述できるplv8があることを知り、PL/pgSQLでは表現しづらい難しい部分はplv8で実現することにしました。
ただし、PL/pgSQLで書ける部分は極力書くことで速度は出したい。ということから、単純にSQLを発行するだけのような部分はPL/pgSQLで行い、計算式の値の算出のような複雑な処理はplv8を使うハイブリッド方式を採用しました。
最終的にどれくらい速くなったのか
今回プロシージャ化を行ってパフォーマンス改善を行いましたが、気になる改善結果は、というと・・・
以下が今回改善した集計処理部分のLatency推移となります。 リリース後には全体的に数値が低下していることが見て取れるかと思います。
大体3倍速程度には改善することができました。
次回予告
今回は技術選定部分のみにフォーカスを当てましたが、次回からは実際にプロシージャを使ってどのように改善したのかや、どうやってRails Wayに乗っかりつつプロシージャを管理したのか。といった細かい部分に踏み込んでいきたいと思います。
We're hiring!
今あるものを更により良くするための方法を、我々と一緒に模索してくれる開発メンバーを募集しています！少しでも興味があれば、ぜひ下記採用サイトからエントリーください。
Meetyによるカジュアル面談も行っていますので、この記事の話をもっと聞きたい！という方がいらっしゃいましたら、お気軽にお声がけください。
