高負荷 HTTP サーバのボトルネックは大体 SQL

PHP で json や gRPC をしゃべるサーバーを作っていて、サーバー処理時間のほとんどを占めるのが「SQL の実行と結果取得待ち」の時間。 PHP サーバーはマルチプロセスで動作する事例が多いが、 PHP サーバーの CPU やメモリー使用量がボトルネックになることはほとんどない(たまに画像ファイルの処理が遅かったりはするが、それはキューに詰めなおした方が良い）。大体は一部の遅いクエリに引っ張られる。

Eloquent や Doctrine のような ORM を使って、 SQL を自分で書かずにオブジェクト指向に Relational Database を操作することが現状多いが、そうするとどのようなクエリが発行されるのかを制御しづらくなるため、この「遅いクエリ」の発見が遅れる。ローカル環境の 100 件しかレコードがない場所でテストして問題なかった API が、本番にリリースされて 100 万件のレコードに対して極端に遅くなり、エラー率の上昇やレスポンス速度の低下につながる恐れがある。つまり、 SQL の抽象化が問題となる場合もある、ということ。

本番でもレコードが 100 万件いく可能性がない、秒間 1 万クエリも発行されない、といった小～中規模のサーバーではこの問題が発生しないこともある。その場合は ORM の抽象化によるコーディング高速化の恩恵を最大限に受けることが出来る。

しかし、レコード数をコーディング時に予測することはほぼ不可能であり、どんなサービスにも N + 1 や全行探索が問題になる可能性が存在する。 Eager Loading など手動で対応することも出来るが、問題が発覚するのは恐らくリリース後、数年経って「実は遅くない？」とか、昨日まで大丈夫だったのに今日バズって急にアクセス数が増えた結果エラーになるとかだ。

それを考えると、常に発行される SQL に注意し、 INDEX が適切に利用されているかを確認しながらコーディングした方が安全ではないだろうか。であれば、 ORM で抽象化されると逆に困ってしまう。 SQL をロギングする仕組みはあれど、 ORM の流儀に従いつつクエリも最適化するのが難しい場合もある（手書きの SQL と ORM が生成した SQL を組み合わせたりしてカオスになる）。

であれば、コーディング時に SQL(INDEX) を注意深く監視出来る仕組みがフレームワークなり言語なりに搭載されていても良いのではないだろうか。

INDEX の利用状況は RDB エンジンやそのバージョン、実装によって異なるため、本番環境と開発環境で全く同じクエリ状態にならないのもこの問題の難しさを高めている。

例えば、ステージング環境に本番環境から個人情報をマスクしたデータをコピーしてきて、そこでクエリを流して問題なければリリースする、といったプロジェクトもある。しかし、 https://zenn.dev/yamayuski/articles/a5562ccfe521fe でも言った通りイテレーションは早い方が良い。つまり、クエリの問題はコーディングの時点で分かった方が良いのである。

現状 ORM はシンプルな Primary Key によるクエリに関しては最適なものを発行してくれるが、その道を外れると急に複雑になる。そのあたりどのように現実的に解決していくのか難しい所である。