Azure Cosmos DB for PostgreSQL のパフォーマンスのチューニング

適用対象: Azure Cosmos DB for PostgreSQL (PostgreSQL の Citus データベース拡張機能を利用)

分散データベースを潜在能力いっぱいで稼動させると、高いパフォーマンスが得られます。 ただし、そのパフォーマンスに到達させるには、アプリケーション コードとデータ モデリングに多少の調整が必要な場合があります。 この記事では、パフォーマンスを向上させるための最も一般的で効果的な手法について説明します。

クライアント側の接続プール

接続プールでは、再利用のために開いているデータベース接続が保持されます。 アプリケーションでは、必要に応じてプールから接続を要求し、プールでは、(可能な場合は) 既に確立されている接続を返すか、新しい接続を確立します。 完了すると、アプリケーションでは接続を閉じずにプールに戻します。

クライアント側の接続プールを追加することは、最小限のコード変更でアプリケーションのパフォーマンスを向上させる簡単な方法です。 Microsoft の測定では、プールが有効になっているクラスターで単一行の挿入ステートメントを実行すると、約 24 倍高速になります。

アプリケーション コードにプールを追加する、言語固有の例については、 アプリ スタック ガイドを参照してください。

注意

Azure Cosmos DB for PostgreSQL では、pgbouncer を使用したサーバー側接続プールも提供されていますが、これは主にクライアント接続の制限を引き上げる役割を果たします。 個々のアプリケーションのパフォーマンスは、サーバー側のプールよりもクライアント側の方からメリットを受けます。 (もちろん、両方のプール形式を問題なく同時に使用できます)。

分散クエリのスコープ設定

更新プログラム

分散テーブルを更新するとき、ディストリビューション列のクエリをフィルター処理してみてください (少なくとも、それが合理的である、新しいフィルターによってクエリの意味が変化しない場合)。

一部のワークロードでは、これは簡単です。 マルチテナントの SaaS アプリやモノのインターネットなどのトランザクションと運用のワークロードでは、テナントまたはデバイスごとにテーブルを分散させます。 クエリのスコープは、テナントまたはデバイスの ID に設定されます。

たとえば、マルチテナント チュートリアルでは、ads テーブルが company_id 別に配布されます。 ad を更新する単純なやり方は、次のように選び出すことです。

-- slow

UPDATE ads
   SET impressions_count = impressions_count+1
 WHERE id = 42; -- missing filter on distribution column

クエリでは行が一意に識別されて更新されますが、Azure Cosmos DB for PostgreSQL では計画時に、クエリによって更新されるシャードが認識されません。 Citus 拡張機能は念のためにすべてのシャードで ShareUpdateExclusiveLock を行うため、これによってテーブルを更新しようとしている他のクエリがブロックされます。

行を識別するのに id で十分であったとしても、クエリをより高速にするために追加のフィルターを含めることができます。

-- fast

UPDATE ads
   SET impressions_count = impressions_count+1
 WHERE id = 42
   AND company_id = 1; -- the distribution column

Azure Cosmos DB for PostgreSQL クエリ プランナーはディストリビューション列の直接フィルターを確認し、ロックする単一のシャードを正確に認識します。 Microsoft のテストでは、ディストリビューション列のフィルターを追加すると、並列更新のパフォーマンスが 100 倍向上しました。

結合と CTE

UPDATE ステートメントをディストリビューション列でスコープ設定して、不要なシャード ロックを回避する方法について確認しました。 他のクエリもスコープ設定の恩恵を受けます。これは通常、ワーカー ノード間でのデータの不要なシャッフルによるネットワーク オーバーヘッドが回避されるためです。

-- logically correct, but slow

WITH single_ad AS (
  SELECT *
    FROM ads
   WHERE id=1
)
SELECT *
  FROM single_ad s
  JOIN campaigns c ON (s.campaign_id=c.id);

CTE とメインの SELECT ステートメントでディストリビューション列 company_id をフィルター処理することで、クエリを高速化できます。

-- faster, joining on distribution column

WITH single_ad AS (
  SELECT *
    FROM ads
   WHERE id=1 and company_id=1
)
SELECT *
  FROM single_ad s
  JOIN campaigns c ON (s.campaign_id=c.id)
 WHERE s.company_id=1 AND c.company_id = 1;

一般に、分散テーブルを結合する場合は、結合条件にディストリビューション列を含めてみてください。 ただし、分散テーブルと参照テーブルの間で結合する場合、必須ではありません。これは、参照テーブルの内容はすべてのワーカー ノードにレプリケートされるためです。

すべてのクエリに追加のフィルターを追加するのが面倒に思われる場合は、いくつかの一般的なアプリケーション フレームワーク用のヘルパー ライブラリを使えば作業が楽になることを覚えておいてください。 手順は以下のとおりです。

• Ruby on Rails:
• Django:
• ASP.NET:
• Java Hibernate:

効率的なデータベースのログ記録

すべての SQL ステートメントを常にログに記録すると、オーバーヘッドが増加します。 Microsoft の測定では、より適切なログ レベルを使用することで、完全なログ記録に比べて 1 秒あたりのトランザクション数が 10 倍改善しました。

毎日の効率的な操作を行うために、エラーや実行時間の異常に長いクエリを除き、ログ記録を無効にすることができます。

設定	価値	理由
log_statement_stats	オフ	プロファイリングのオーバーヘッドを回避する
log_duration	オフ	通常のクエリの実行時間を知る必要がない
log_statement	なし	具体的な理由がない場合はクエリをログに記録しない
log_min_duration_statement	通常のクエリで必要と思われるよりも長い値	異常に長いクエリを表示します

注意

Microsoft のマネージド サービスでのログ関連の設定は、上記の推奨事項が考慮されています。 そのまま残すことができます。 しかしながら、ログ記録を積極的に行うために設定を変更し、それによってパフォーマンスの問題が発生しているお客様をしばしば見かけます。

ロックの競合

データベースではロックを使用して、同時アクセスの下でデータの一貫性を維持しています。 ただし、一部のクエリ パターンでは過剰な量のロックが必要であり、もっと速い代替手段が存在します。

システムの正常性とロック

一般的なロックの非効率性について説明する前に、データベース クラスター全体のロックとアクティビティを表示する方法を見てみましょう。 citus_stat_activity ビューには、詳細ビューが表示されます。

このビューには特に、ロックを含む "待機イベント" によってクエリがどのようにブロックされるかが表示されます。 wait_event_type でグループ化すると、システム正常性の実体が描き出されます。

-- general system health

SELECT wait_event_type, count(*)
  FROM citus_stat_activity
 WHERE state != 'idle'
 GROUP BY 1
 ORDER BY 2 DESC;

NULL wait_event_type は、クエリが何も待機していないことを意味します。

統計アクティビティの出力にロックが表示される場合は、citus_lock_waits を使用して特定のブロックされたクエリを表示できます。

SELECT * FROM citus_lock_waits;

たとえば、あるクエリが、同じ行を更新しようとしている別のクエリでブロックされると、ブロックされたステートメントとブロックされているステートメントが表示されます。

-[ RECORD 1 ]-------------------------+--------------------------------------
waiting_gpid                          | 10000011981
blocking_gpid                         | 10000011979
blocked_statement                     | UPDATE numbers SET j = 3 WHERE i = 1;
current_statement_in_blocking_process | UPDATE numbers SET j = 2 WHERE i = 1;
waiting_nodeid                        | 1
blocking_nodeid                       | 1

現時点で発生しているロックだけでなく、履歴パターンを確認するために、PostgreSQL ログでロックをキャプチャできます。 詳細については、PostgreSQL ドキュメントの log_lock_waits サーバー設定を参照してください。 もう 1 つの優れたリソースは、Citus データ ブログのロックを処理するための 7 つのヒントです。

一般的な問題と解決策

DDL コマンド

truncate、drop、create index などのすべての DDL コマンドは、書き込みロックを受け取り、テーブル全体に対する書き込みをブロックします。 このような操作を最小限に抑えることで、ロックの問題が軽減されます。

ヒント:

• DDL はメンテナンス期間に統合するか、これらをあまり頻繁に使用しないようにしてください。

• PostgreSQL では、テーブルに対する書き込みロックを回避するために、 インデックスの同時構築がサポートされています。

• 重い DDL コマンドを実行する前に、SQL セッションで lock_timeout を設定することを検討してください。 lock_timeout を使用すると、コマンドの書き込みロックの待機が長すぎる場合に PostgreSQL で DDL コマンドが中止されます。 ロックを待機している DDL コマンドによって、後続のクエリがそれ自体の背後のキューに入れられることがあります。

トランザクション接続でのアイドル状態

アイドルな (コミットされていない) トランザクションでは、他のクエリが不必要にブロックされることがあります。 以下に例を示します。

BEGIN;

UPDATE ... ;

-- Suppose the client waits now and doesn't COMMIT right away.
--
-- Other queries that want to update the same rows will be blocked.

COMMIT; -- finally!

コーディネーター ノードで長時間アイドル状態のクエリを手動でクリーンアップするには、次のようなコマンドを実行します。

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'citus'
 AND pid <> pg_backend_pid()
 AND state in ('idle in transaction')
 AND state_change < current_timestamp - INTERVAL '15' MINUTE;

PostgreSQL には、アイドルなセッションの終了を自動化するための idle_in_transaction_session_timeout 設定も用意されています。

デッドロック

Azure Cosmos DB for PostgreSQL では分散デッドロックが検出され、クエリが取り消されますが、この状況は、最初にデッドロックを回避するよりもパフォーマンスが低くなります。 デッドロックの一般的な原因は、同じ行セットを複数のトランザクションから異なる順序で一度に更新することです。

たとえば、次のトランザクションを並列で実行するとします。

セッション A:

BEGIN;
UPDATE ads SET updated_at = now() WHERE id = 1 AND company_id = 1;
UPDATE ads SET updated_at = now() WHERE id = 2 AND company_id = 1;

セッション B:

BEGIN;
UPDATE ads SET updated_at = now() WHERE id = 2 AND company_id = 1;
UPDATE ads SET updated_at = now() WHERE id = 1 AND company_id = 1;

-- ERROR:  canceling the transaction since it was involved in a distributed deadlock

セッション A では ID が 1 から 2 の順に更新され、セッション B では 2 から 1 の順に更新されました。 行が同じ順序で更新されるように、トランザクションの SQL コードを慎重に記述してください。 (更新順序は "ロック階層" と呼ばれることもあります)。

Microsoft の測定では、多数のトランザクションによる一連の行の一括更新は、デッドロックを回避すると 3 倍速くなりました。

インジェスト中の I/O

I/O ボトルネックは通常、シャーディングのために、単一ノード PostgreSQL よりも Azure Cosmos DB for PostgreSQL の方が問題が少なくなります。 シャードは個別に小さいテーブルで、インデックスとキャッシュのヒット率が高く、パフォーマンスが向上します。

ただし、Azure Cosmos DB for PostgreSQL の場合でも、テーブルとインデックスが大きくなるにつれて、ディスク I/O がデータ インジェストの問題になる可能性があります。 wait_event_type 内で表示される 'IO' citus_stat_activity エントリの数が増えていることに気を付ける必要があります。

SELECT wait_event_type, wait_event count(*)
  FROM citus_stat_activity
 WHERE state='active'
 GROUP BY 1,2;

上記のクエリを繰り返し実行して、待機イベントに関連する情報をキャプチャします。 さまざまな待機イベントの種類の数がどのように変化するかに注意してください。

また、Azure portal のメトリック、特に IOPS メトリックが上限に達していることを確認してください。

ヒント:

• 時系列などデータが自然な順序になっている場合は、PostgreSQL テーブルのパーティション分割を使用します。 分散テーブルをパーティション分割する方法については、このガイドを参照してください。

• 未使用のインデックスを削除します。 インデックスのメンテナンスにより、インジェスト中に I/O 増幅が発生します。 使用されていないインデックスを見つけるには、このクエリを使用します。

• 可能であれば、ランダム化されたデータのインデックス作成を避けてください。 たとえば、一部の UUID 生成アルゴリズムは順序に従いません。 このような値のインデックスを作成すると、多くのオーバーヘッドが発生します。 代わりに bigint シーケンスを試すか、UUID を単調に増やしてください。

変更の概要

INSERT、UPDATE、トランザクション ブロックを使用した単純なインジェストのベンチマークでは、この記事の手法について、次のクエリの高速化を確認しました。

手法	クエリの高速化
スコープ設定クエリ	100x
接続のプール	24x
効率的なログ記録	10x
デッドロックの回避	3x

次の手順

• 高度なクエリ パフォーマンスの調整
• 有用な診断クエリ
• 高速なアプリ スタックを構築する