ビルダー向け B2B サービスの例を使用したデータベース クエリの最適化

生産性の高いサーバーに移行せずにデータベースへのクエリ数を 10 倍に増やし、システム機能を維持するにはどうすればよいでしょうか? データベースのパフォーマンスの低下にどのように対処したか、できるだけ多くのユーザーにサービスを提供し、コンピューティング リソースのコストを増加させないように SQL クエリをどのように最適化したかについて説明します。

建設会社の業務プロセスを管理するサービスを作っています。 約3社が当社と提携しています。 10 万人以上が当社のシステムを毎日 4 ～ 10 時間使用しています。 計画、通知、警告、検証などのさまざまな問題を解決します。PostgreSQL 9.6 を使用します。 データベースには約 300 のテーブルがあり、毎日最大 200 億のクエリ (10 万の異なるクエリ) が受信されます。 平均して 3 秒あたり 4 ～ 10 件のリクエストがあり、最もアクティブな時点では XNUMX 秒あたり XNUMX 件以上のリクエストがあります。 ほとんどのクエリは OLAP です。 追加、変更、削除がはるかに少なく、OLTP の負荷が比較的軽いことを意味します。 これらすべての数字を提供したのは、皆さんが私たちのプロジェクトの規模を評価し、私たちの経験がどれだけ役立つかを理解していただくためです。

写真XNUMX。 叙情的

開発を開始したとき、データベースにどのような負荷がかかるのか、サーバーがプルを停止した場合にどうするのかについてはあまり考えていませんでした。 データベースを設計するとき、私たちは一般的な推奨事項に従い、自分自身を傷つけないよう努めましたが、「パターンを使用しないでください」などの一般的なアドバイスを超えました。 エンティティの属性値 私たちは入りませんでした。 私たちは正規化の原則に基づいて設計し、データの冗長性を回避し、特定のクエリの高速化には気を配りませんでした。 最初のユーザーが到着するとすぐに、パフォーマンスの問題が発生しました。 いつものように、私たちはこれに対してまったく準備ができていませんでした。 最初の問題は単純であることが判明しました。 原則として、新しいインデックスを追加することですべてが解決されました。 しかし、単純なパッチが機能しなくなる時期が来ました。 私たちには経験が不足しており、問題の原因を理解することがますます困難になっていることを認識し、専門家を雇い、サーバーを正しく設定し、監視を接続し、どこにアクセスすればよいかを示してくれました。 統計.

写真XNUMX。 統計的

したがって、データベースでは 10 日に約 10 個の異なるクエリが実行されます。 この 2 万件の中には、平均実行時間が 3 ～ 0.1 ミリ秒で 0.3 ～ 30 万回実行されるモンスターもいます。また、100 日あたり XNUMX 回呼び出される平均実行時間 XNUMX 秒のクエリもあります。

10 件のクエリすべてを最適化することは不可能だったので、データベースのパフォーマンスを正しく向上させるためにどこに力を注ぐべきかを判断することにしました。 数回の繰り返しの後、リクエストをタイプに分割し始めました。

トップリクエスト

これらは、最も時間 (合計時間) がかかる最も重いクエリです。 これらは、非常に頻繁に呼び出されるクエリ、または実行に非常に長い時間がかかるクエリです (長く頻繁に呼び出されるクエリは、速度を追求する最初の反復で最適化されました)。 その結果、サーバーはその実行に最も多くの時間を費やします。 さらに、上位のリクエストを合計実行時間ごとに分けたり、IO 時間ごとに分けたりすることが重要です。 このようなクエリを最適化する方法は若干異なります。

すべての企業の通常の慣行は、TOP リクエストに対処することです。 それらはほとんどありません。クエリを 5 つ最適化するだけでも、リソースの 10 ～ 3% を解放できます。 ただし、プロジェクトが成熟するにつれて、TOP クエリの最適化はますます簡単なタスクではなくなります。 簡単な方法はすべてすでに解決されており、最も「重い」リクエストでもリソースの「わずか」 5 ～ 30% しかかかりません。 TOP クエリにかかる時間が合計で 40 ～ XNUMX% 未満である場合は、それらを迅速に動作させるための努力をすでに行っている可能性が高く、次のグループのクエリの最適化に進む時期が来ています。
このグループに上位クエリをいくつ含めるべきかという疑問に答える必要があります。 私は通常、少なくとも 10 回、最大 20 回を記録します。TOP グループの最初と最後のタイムの差が 10 倍以内になるように努めます。 つまり、クエリ実行時間が 1 位から 10 位まで急激に低下した場合は TOP-10 を採用し、低下がより緩やかな場合はグループ サイズを 15 または 20 に増やします。

中農民

最後の 5 ～ 10% を除いて、これらはすべて TOP の直後に来るリクエストです。 通常、これらのクエリを最適化すると、サーバーのパフォーマンスを大幅に向上させることができます。 これらのリクエストの重みは最大 80% に達する場合があります。 しかし、たとえそのシェアが 50% を超えていたとしても、より慎重に検討する時期が来ています。

しっぽ

前述したように、これらのクエリは最後に発生し、時間の 5 ～ 10% かかります。 自動クエリ分析ツールを使用しない場合に限り、これらを忘れても問題ありません。その場合は、最適化のコストも低くなる可能性があります。

各グループをどのように評価するか?

私は、PostgreSQL に対してこのような評価を行うのに役立つ SQL クエリを使用しています (他の多くの DBMS に対しても同様のクエリを作成できると思います)。

TOP-MEDIUM-TAIL グループのサイズを見積もるための SQL クエリ

SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail
FROM
(
  SELECT CASE WHEN rn <= 20              THEN tt_percent ELSE 0 END AS time_top,
         CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium,
         CASE WHEN rn > 800              THEN tt_percent ELSE 0 END AS time_tail
  FROM (
    SELECT total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,
    ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn
    FROM pg_stat_statements
    ORDER BY total_time DESC
  ) AS t
)
AS ts

クエリの結果は 20 つの列で、各列にはこのグループからのクエリの処理にかかる時間の割合が含まれます。 リクエスト内には、あるグループからのリクエストを別のグループから区別する 800 つの番号 (私の場合は XNUMX と XNUMX) があります。

これは、最適化作業が開始された時点と現在のリクエストのシェアを大まかに比較したものです。

この図は、TOP リクエストのシェアが急激に減少している一方で、「中産階級」が増加していることを示しています。
当初、TOP の要求には明らかな失敗が含まれていました。 時間が経つにつれて、小児疾患はなくなり、TOP リクエストの割合は減少し、困難なリクエストをスピードアップするためにますます努力する必要がありました。

リクエストのテキストを取得するには、次のリクエストを使用します

SELECT * FROM (
  SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query
  FROM pg_stat_statements
  ORDER BY total_time DESC
) AS T
WHERE
rn <= 20 -- TOP
-- rn > 20 AND rn <= 800 -- MEDIUM
-- rn > 800  -- TAIL

以下は、TOP クエリの高速化に役立つ最も一般的に使用されるテクニックのリストです。

• システムの再設計。たとえば、データベースへの定期的なクエリの代わりにメッセージ ブローカーを使用して通知ロジックを再作成します。
• インデックスの追加または変更
• ORM クエリを純粋な SQL に書き直す
• 遅延データ読み込みロジックの書き換え
• データの非正規化によるキャッシュ。 たとえば、納品 -> 請求書 -> 要求 -> 申請というテーブル接続があります。 つまり、各配信は他のテーブルを通じてアプリケーションに関連付けられます。 各リクエストのすべてのテーブルをリンクしないようにするために、配信テーブル内のリクエストへのリンクを複製しました。
• 参考書籍を使用して静的テーブルをキャッシュし、プログラム メモリ内のテーブルをほとんど変更しない。

場合によっては、変更によって印象的な再設計が行われることもありましたが、システム負荷の 5 ～ 10% が発生し、正当化されました。 時間が経つにつれて、排気装置はますます小さくなり、ますます本格的な再設計が必要になりました。

次に、私たちは 3 番目のグループの要求、つまり中農民のグループに注目しました。 さらに多くのクエリが含まれており、グループ全体を分析するにはかなりの時間がかかるように見えました。 ただし、ほとんどのクエリは最適化が非常に簡単であることが判明し、多くの問題がさまざまなバリエーションで何十回も繰​​り返されました。 ここでは、数十の同様のクエリに適用した典型的な最適化の例を示します。最適化されたクエリの各グループは、データベースを 5 ～ XNUMX% アンロードしました。

• COUNT と全テーブル スキャンを使用してレコードの存在をチェックする代わりに、EXISTS が使用され始めました。
• DISTINCT を削除しました (一般的なレシピはありませんが、リクエストを 10 ～ 100 倍高速化することで簡単に削除できる場合があります)。

  たとえば、配送に関する大きなテーブル (DELIVERY) からすべてのドライバーを選択するクエリの代わりに

  SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.ID
  

  比較的小さなテーブル PERSON に対してクエリを実行しました

  SELECT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM PERSON
  WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)
  

  相関サブクエリを使用したように見えますが、10 倍以上の高速化が得られます。

• 多くの場合、COUNT は完全に放棄され、
  近似値の計算に置き換える
• 代わりに

  UPPER(s) LIKE JOHN%’ 
  

  使用

  s ILIKE “John%”
  

それぞれの特定のリクエストが 3 ～ 1000 倍高速化されることがありました。 素晴らしいパフォーマンスにもかかわらず、最初は、完了までに 10 ミリ秒かかり、3 番目に重いクエリの XNUMX つであり、データベース全体の読み込み時間の XNUMX 分の XNUMX を占めるクエリを最適化することに意味がないと思われました。 しかし、同じレシピを同じタイプのクエリのグループに適用することで、数パーセントの利益を得ることができました。 何百ものクエリをすべて手動で確認する時間を無駄にしないために、正規表現を使用して同じタイプのクエリを検索する簡単なスクリプトをいくつか作成しました。 その結果、クエリのグループを自動的に検索することで、適度な労力でパフォーマンスをさらに向上させることができました。

その結果、私たちは 30 年間同じハードウェアに取り組んできました。 70 日の平均負荷は約 10%、ピーク時には XNUMX% に達します。 リクエスト数もユーザー数も約XNUMX倍に増加しました。 これはすべて、TOP-MEDIUM リクエストの同じグループを継続的に監視しているおかげです。 TOPグループに新しいリクエストが現れるとすぐにそれを分析し、高速化を試みます。 私たちはクエリ分析スクリプトを使用して週に XNUMX 回 MEDIUM グループをレビューします。 最適化方法がすでにわかっている新しいクエリに遭遇した場合は、すぐに変更します。 場合によっては、一度に複数のクエリに適用できる新しい最適化方法が見つかることがあります。

当社の予測によれば、現在のサーバーはユーザー数がさらに 3 ～ 5 倍増加しても耐えられると考えられます。 確かに、私たちにはもう XNUMX つ切り札があります。推奨されているように、まだ SELECT クエリをミラーに転送していません。 しかし、私たちはこれを意識的に行っているわけではありません。なぜなら、「重砲」をオンにする前に、まず「スマート」な最適化の可能性を完全に使い尽くしたいからです。
行われた作業を批判的に見ると、垂直スケーリングの使用が提案される場合があります。 専門家の時間を無駄にするのではなく、より強力なサーバーを購入してください。 特に垂直方向のスケーリングの限界をまだ使い果たしていないため、サーバーにはそれほどコストがかからない可能性があります。 ただし、リクエストの数だけが 10 倍に増加しました。 数年の間にシステムの機能が増加し、リクエストの種類も増えました。 キャッシュのおかげで、既存の機能はより少ないリクエストで実行され、より効率的なリクエストが実行されます。 これは、安全にさらに 5 を掛けて実際の加速係数を取得できることを意味します。 したがって、最も控えめに見積もっても、加速は 50 倍以上であると言えます。 サーバーを垂直にスイングすると、50 倍のコストがかかります。 特に、一度最適化を実行すると常に動作し、レンタルサーバーの料金が毎月請求されることを考慮すると、

出所： habr.com