はじめまして。 Webアプリケーションエンジニアをしている黒澤です。
現在は新規事業の立ち上げをしていますが、この間までは法人向け名刺管理サービス「Sansan」の開発を行っていました。
突然ですがSQLが遅くて困った経験はありますか？
私はよくあります。
一定以上の規模のRDBを使用しているシステムでは、多かれ少なかれSQLのパフォーマンスがシステムのボトルネックになると思うので、困った経験のある方も多いのではないでしょうか。
Sansanでも新規機能の開発中に想定通りのパフォーマンスが出なかったり、本番環境においても、利用者の増加により、クエリ実行速度の低下がユーザ体験を悪化させてしまったりといったケースがあります。
今回はそんなときに行っているチューニングのポイントを紹介したいと思います。
この記事ではアプリケーションレイヤーでのチューニングを扱います。
インフラレイヤーでのパフォーマンスについては、こちらの記事を御覧ください。
Sansanのデータベースに特有の事象だったり、使用しているDBMSの種類やバージョンによっては有効でないこともあるかと思いますが、クエリチューニングで困っている方の助けになれば幸いです。
推測するな、計測せよ
そのSQL本当に遅いですか？
パフォーマンスが劣化している原因はSQLに限りません。
軽く思いつくだけでも
- サーバーサイドのロジックに問題がある場合
- ブラウザ上のレンダリングやJavascriptなど、フロントエンドに起因する問題
- ユーザの通信環境や、ネットワークなどインフラに起因する問題
といったさまざまな可能性があります。
闇雲に遅そうなクエリをチューニングをしても、ボトルネックとなっている原因が異なる可能性もあります。
時間をかけてチューニングを実施しても思ったより効果が出ないこともあるので、何が原因なのかを特定することが重要です。
Sansanではパフォーマンス監視のために、New Relic を導入しています。
New Relic を使用することで、遅いリクエストの特定や、該当のリクエスト内のどの部分に時間がかかっているのかを特定することができます。
New Relic では、特定のトランザクションを分析することもでき、そのトランザクション内での処理に時間がかかっていたのか、またそのトランザクションで発行されていたSQLは何なのか確認することができます。
推測するな、計測せよ その2
なんでSQLが遅いんですか？
原因がSQLにあることがわかれば、早速クエリを修正したいところですが、その前に実行計画を確認しましょう。
実行計画を取得したいSQLの前に EXPLAIN ANALYZE をつけて実行します。
EXPLAIN ANALYSE SELECT * FROM hoge_table WHERE fuga_column = 'piyo';
実行計画の読み方については、 下雅意さんの資料が非常にわかりやすく書かれています。
PostgreSQLクエリ実行の基礎知識 ～Explainを読み解こう～
実行計画に慣れないうちは、pgAdmin などの DBMS クライアントが持つ Visualize 機能を利用するのも効果的だと思います。
チューニングの際に最初に見るべきところは、 actual time です。
「actual time=[最初の１件目が取得するまでにかかった時間]...[結果行全体を取得するまでにかかった時間]」
となっており、actual time が急激に増加した処理がSQLのパフォーマンスが悪化している箇所となります。
また、cost と actual time を比較することにより、
- cost の増加が少ないのにもかかわらず、 actual time が急増している
- 推定行数 と 実際の行数が大きくずれている
- 多数の loop が繰り返されている
といった、Optimizerの統計情報による予測と、実際のデータがずれている箇所を特定することができます。
Sansanでは、特定のDBのデータの偏りや、テナントによるデータ傾向の違いといった要因により、特定の場合でのみパフォーマンスが悪化するといったケースが発生します。
こちらは、実際の実行計画から一部を抜粋して、テーブル名などの書いたら怒られそうなものを消したものです。
-> Nested Loop (cost=1.16..437.95 rows=1 width=2059) (actual time=2.502..221763.561 rows=9000 loops=1) Join Fileter: xxxx Rows Removed by Join Filter: 384058600 -> Nested Loop (cost=0.73..429.44 rows=1 width=1941) (actual time=1.068..1504.427 rows=1133668 loops=1) ～ 中略 ～ -> Index Scan using idx_xxxxxx on table1 (cost=0.43..8.47 rows=1 width=256) (actual time=0.016..0.164 rows=339 loops=1133668)
Plannningでは1行と推定していた行が、 実際には1133668行ヒットしてしまったことにより、Index は使用できているものの、 1133668 回のループが発生してしまい、パフォーマンスが悪化しています。
SQLが遅い理由
実際に遅い箇所が特定できたら、どうして遅くなっているのかを考えてみましょう。
実行計画をみながら、パフォーマンス劣化の原因となっているところを特定し、原因を考察していくことになります。
少しずつクエリを変えながら実行計画の変化を見て調整してくことが多いですが、チューニングが必要な際は以下のようなケースが多いかと思います。
以下はSansanで実際にあった原因です。
SeqScan
テーブルの大部分のレコードが取得対象になる場合など SeqScan (+ PallarelSeqScan) が有効なケースもありますが、ほとんどの場合では、 Index Scanの方が圧倒的に高速です。
大量のテーブルに対して、 SeqScanが発生していた場合は、 適切な Index を作成することで高速化することが多いです。
必要の無いデータまで取得している
類似した処理で使い回すために、不要なデータまで取得してしまっているケースです。
取得する必要が無いデータを取得するために、余計なテーブルとの結合が発生してしまったり、複雑なクエリとなってしまっていました。
これは極端な例ですが、クエリ内で取得するデータが増えてしまうと、プロセス内で使用するメモリが増えてしまい、quick sort が使用できず、 external sort が発生してしまうなど、パフォーマンスに影響を与えることもあります。
複雑なことをしすぎている
1つのクエリですべてのデータを取得しようとすると、Joinが必要なテーブルが増え過ぎたり、クエリが複雑になりすぎて時間がかかることがあります。
そんなときは、 クエリを分割し、C# 上でデータを結合することにより、パフォーマンスが劇的に改善することもあります。
Join対象のレコードが多すぎる
RDBでは多くの場合、正規化したデータを Joinすることになります。
Joinする際に、巨大なテーブル同士をJoinすると、レコードが爆発的に増加します。Join対象が多ければそれだけコストが増加し、actual time も増加することになります。
そのため、 Joinする前に効率的に対象データを絞り込むことが重要になります。
推測するな、計測せよ その3
クエリをチューニングしてもすぐに本番環境にリリースするのはちょっとまってみましょう。
ほとんどのケースでは問題ないがクエリでも、特定条件下で実行計画が崩れ、パフォーマンスが劇的に悪化することがあります。
特にSansanのようなマルチテナントDBでは、テナントごとのデータ傾向に偏りが発生するケースが多く、大規模なテナントに対しては問題がなくても逆に小規模なテナントでパフォーマンスが悪化する事象がよく見られます。
そんなときは、様々な条件でクエリを実行し問題がないか確認しましょう。
Sansanでは、測定対象のSQLを複数のテナントに実行し、実行時間を取得するスクリプトを作成して、特定テナントでのパフォーマンス悪化を検知するようにしています。
まとめ
実践的なテクニックはあまり紹介できなかったのですが、私がパフォーマンスチューニングを実施する際は、このような手順でチューニングをしています。
クエリチューニングについて最も大切なことは推測で判断せずに、計測によって得られた値と継続的に向き合うことだと思います。
最初は分析のポイントがわからずハードルが高く感じるかと思いますが、最近ではVisualizeしてくれるツールも多く、思ったよりもとっつきやすくなっています。
この夏はSQLチューニングにチャレンジしてみませんか？
