１０倍に伸びた登録者数によるSQLリクエストの負荷対策について

皆さんこんにちは、べリョ・トムと申します。

去年コロナ禍の最初の数ヶ月で、貴社では負荷の増加という影響を受けましたのでしょうか?
弊社では、２０１８年にリリースされ、農業者向けに求人掲示板サイトを運営していて、２０２１年４月で農業界において人手不足という状況が発生して、そのプロジェクトがテレビに流されました。

それによって、１日で合計３万人の登録者数が３０万人になって、その負荷に準備していなかったインフラやコードとしては、とんでもない負荷を背負うことができました。チームと負荷を複数のサーバーに分散してから、メールの送信率も調整し始めて、データベースのサーバを含めて、全てのサーバーのスペックを上げたことで、中止なし可用性を守ることができました。
その後の数週はプロダクトのあらゆる非効率性の部分を改善していくことにしました。

そこに、もっと大きな改善はマッチングアルゴリズムの性能でした。
マッチングアルゴリズムとは、仕事の案件に添う人を提案するアルゴリズムで、機能としてはプラダクトの中心でした。開発の初めから作られたもので、簡単な形で作られて、機能に積められてきたものだから、技術的負債を作っていったのですね。

その機能の性能を探ることで、下記の問題を分かりました：

• リクエストから直接、必要だけの結果を返さず、大量の結果を返して、PHPで色んなスコアーを計算して整理されていました
• その整理から、二十位以内だけにある結果をページに表示
• 大量のデータをPHPに送って、計算、整理するのが時間がかかる
• リクエストが大きすぎて、ロジックが複雑になって、コードが更新しづらかった
• JOINでの絞り込みが増えていたため、データベースの設計には不適切になっていた

そして下記のようにリファクトリングしました：

• Materialized Viewでデータを簡単にまとめて
• 一件に関するリレーションのIDを配列に保管して、旧リクエストの複数のJOINを減らすことができる
• インデックスの配列として扱うことで、PHPで計算されていたマッチングスコアーがSQL直接に計算するのができる

その改善のおかげで、数十秒以上の処理時間が数百ミリ秒だけになりました。SQLのリクエストも省略することができ、アルゴリズムを一つの場所だけに統括することで、コードが読みやすくなって、スコアの複数の機能を分かりやすくようになりました。先に来る機能の変化や追加も安くするようになりました。
Materialized ViewによるRAMの消費の増加を観察しなかったので、本番環境に安全にリリースしました。

皆さんも似たような経験を生きたのでしょうか？共感できたらぜひ教えてください。
又、日本語での初めての記事としてどうでしたか？感想があれば幸いです。

今、バックエンドやフルスタックエンジニアとして日本に働くために就職中ですので、興味があればぜひ声をかけて下さい：https://www.linkedin.com/in/tom-berriot-ba689110a/