こんにちは。DIGGLEエンジニアのzakkyです。最近、長男が1歳になり、長女が中学卒業、二女＆三女が小学校卒業とイベントが盛りだくさんでした。毎日家の中がにぎやかです。
さて、今回も前回から始まったレポート（多軸分析）の集計処理部分のパフォーマンス向上施策の第2弾となります。
前回までの記事の紹介
今回のお題
今回はPostgreSQLのプロシージャ導入時のあれやこれやをご紹介します。
書かないこと
以下の内容は含みませんので予めご了承ください
- プロシージャの書き方などの基礎的な部分
- PostgreSQLのパラメータを使ったチューニング方法
どれくらい速くなったのか
前回も記載しましたが、以下が今回改善した集計処理部分のLatency推移となります。 リリース後には低下していることが見て取れるかと思います。
大体3倍速程度には改善することができました。
実装方針を決める
前回のおさらいとなりますが、前回は「単純にSQLを発行するだけのような部分はPL/pgSQLで行い、計算式の値の算出のような複雑な処理はplv8を使うハイブリッド方式を採用する」所まで決定しました。
今回は、具体的にどの部分をPL/pgSQLで書いて、どの部分をplv8にするかを決めて実装していきます。
PL/pgSQLとplv8に振り分ける
実際に行う処理をいくつかに分解し、複雑度によってPL/pgSQLとplv8に振り分けていきます。
計算処理の流れを確認する
こちらも前回のおさらいとなりますが、改善箇所は「登録されているデータをレポート用に加工する」箇所の高速化となります。弊社のサービス上では大きく以下のような流れで処理が構成されています。
- 計算式のパース
- データの集計＆集約
- 計算式部分の計算
それぞれの処理の複雑度などを考えて振り分けを行っていきます。
計算式のパース
jsのライブラリであるpegjsで生成したjsを使ってパース処理を行っていますので、複雑度云々ではなく、jsが実行可能な環境が必要となります。
plv8がnodeベースの言語となりますので、plv8で実装します。
データの集計＆集約
単純にSQLによる集約ができれば良い部分となります。
シンプルにSQLを書けば良いので、PL/pgSQLで実装します。
計算式部分の計算
一番複雑な部分になります。それぞれの値の相互関係や四則演算などを表現できないといけません。また、今後計算式機能の拡充をしようとした際のメンテナンス性も重要になります。
PL/pgSQLでは荷が重いので、自由度が高いplv8で実装します。
最終的な振り分け結果
上述までの検討の結果、以下の様に振り分けを行いました。
- 計算式のパース ⇒ plv8
- データの集計＆集約 ⇒ PL/pgSQL
- 計算式部分の計算 ⇒ plv8
プロシージャへの置き換え
方針も決まりましたので、それぞれの処理をプロシージャに置き換えて高速化を行っていきます。
計算式のパース
前述の通り、jsのライブラリであるpegjsで生成したjsを使っていますが、元々はrails上でjsを実行していました。そのため、お世辞にも処理が速いとは言えず、都度パースするのではなく、DB上にパース後の内容を保存しておくなど、極力パース処理が走らないような工夫を行っていました。
それが今回、pegjsで生成したjsをnodeベースであるplv8上で動かすことでかなり速度が向上しました。そのため、「パース処理の仕様を変えたから、全ての計算式をパースし直したい」のような時にも以下の様にSQL上からも呼び出すことで、今までは数分単位で掛かっていたものが1～2秒で終わるようになりました。
-- formula_tableの全ての計算式（formula）を再度パースしてparsed_jsonに格納する UPDATE formula_table SET parsed_json = parse_formula(formula);
※実際には計算式変更後の初回時のみパース⇒DBへ保存する仕様のため、最初の画像のLatencyに対する恩恵はほぼほぼ得られていません。
データの集計＆集約の高速化
以前は同様の内容をRails側で取得⇒メモリ上に展開していたので、RDBとのI/Oや取得したデータの展開などのオーバーヘッドが掛かっていました。 今回はPL/pgSQLでSELECT-INSERTを用いてTEMPORARY TABLEへそのまま集計＆集約しつつ投入する仕組みにしたため、オーバーヘッドを大幅にカットすることができ、大体1秒程度あればデータの集計ができるようになりました。
計算式部分の計算
今回の目玉です。
元々Railsで記載されていたものをplv8(node)へ置き換える作業となるのですが、試行錯誤の連続でした。
TEMPテーブルを活用する（失敗）
最初に実装した際には、計算式で参照する値がある度に、TEMPテーブルから都度参照する仕組みとしました。
理由としては、TEMPテーブルはオンメモリに展開されている筈で、TEMPテーブルへのSELECTは実体としてはRails上のMapなどに対する参照とコストが大差なく、そこまで大きくオーバーヘッドは掛からないだろう。という推測からでした。
しかし残念ながら結果としては、Rails時より速度低下する事態となってしまいました。
後で調べてみて分かったのですが、以下の記述があるように、どうやらTEMPテーブルもファイルへ保存しているようです。TEMPテーブルから取得したからいくらSELECTしても大丈夫！という訳ではなく、どこかしらでHDDへのI/Oが走ってしまい性能劣化へ繋がったのではないかと推測しています。
しかし一時的なリレーションでは、ファイル名はtBBB_FFFという形になります。
PostgreSQLのパラメータをチューニングする（失敗）
私よりDBに詳しい方が色々なサイトで纏めてくださっているので詳細は割愛しますが、以下のようなパラメータを調整してみました。
- shared_buffer
- temp_buffers
- work_mem
色々な値を入れて試してみたのですが改善せず、今回は時間の都合で断念となりました😢
考察：plv8でSQL発行するのは良くないのではないか
上記の調査をしている中で気づいたことなのですが、体感でplv8では各種SQLの発行が遅いように感じました。 特に更新系の処理が顕著で、明らかに遅いと感じられる状況となりました。
後になって思い返してみると、計算式の計算が終わる度にTEMPテーブルへ計算結果をUPDATEする仕組みがあったため、UPDATEのコストが大きかった可能性もあります。（他の計算式から参照される可能性があるため、集計結果は保存しておく必要があるのです）
また、今回plv8で記述した内容を、頑張ってPL/pgSQLで書くようにすれば違った結果になった可能性があります。（ただし、メンテナンス性がかなり落ちるため、今回は採用しませんでした）
最終案：オンメモリに全展開する
最終的には、Railsでやっていたことと同じく、オンメモリに必要な情報を全て展開してから処理を行う。という形となりました。当たり前ですが、RailsでやっていたことをRDB上で実現しましたので速度の向上が見られました。
大まかな処理の流れとしては以下のようになります。
- TEMPテーブルに対してSELECTを行い、集計結果をメモリ（Map）に展開する
- 計算式で参照する値がある場合は、メモリ（Map）から参照する
- 計算式の計算が終わったら、メモリ（Map）に結果を格納する
上記によって懸念されるのが、RDBのメモリが枯渇しないか。という点かと思います。
この問題に関しては、弊社では本番同等の環境＆データを用いたパフォーマンス試験を実施する仕組みがありますので、リリース前にしっかりと本番想定の性能試験をすることが可能となっています。
そして、性能試験の結果としてはメモリ枯渇は発生せず問題なし。結果的にこちらが採用となりました。
まとめ
最終的に時間の都合で納得のいく形までは持っていけませんでしたが、3倍速程度には速くできたので、まぁ及第点かな。と。
ただ、計算式の計算部分のパフォーマンス改善や、PostgreSQLのパラメータチューニングなど、まだまだ改善の余地が残っていることは事実ですので、日を改めて再チャレンジをする予定です。
次回予告
「Rails+ApartmentでどうやってRails Wayに乗っかりつつプロシージャを管理するのか」という所を書きたいと思います。そちら側の方が個人的には大変だったので、ご期待いただければと思います。
本当は今回書きたかったのですが、長くなってしまった結果、泣く泣く分割となりましたので、来月までお待ちいただけますと幸いです。
We're hiring!
今あるものを更により良くするための方法を、我々と一緒に模索してくれる開発メンバーを募集しています！少しでも興味があれば、ぜひ下記採用サイトからエントリーください。
Meetyによるカジュアル面談も行っていますので、この記事の話をもっと聞きたい！という方がいらっしゃいましたら、お気軽にお声がけください。
