Amazon Web Services ブログ

PostgreSQL パフォーマンス向上 : ロックマネージャー競合の診断と対策

(この記事は、Improve PostgreSQL performance: Diagnose and mitigate lock manager contention を翻訳したものです。)

ワークロードが拡張するにつれて、データベースの読み取り操作が予期せず遅くなっていませんか？PostgreSQL ベースのシステムを運用している多くの組織では、すぐには明らかにならないパフォーマンスのボトルネックに遭遇する事があります。多数のパーティションやインデックスを持つテーブルに対して多くの同時読み取り操作がアクセスすると、PostgreSQL の高速パスロック機能を使い果たし、システムが共有メモリロックを使用せざるを得なくなることがあります。高速パスから共有メモリロックへの切り替えは、ロックマネージャーにおいて軽量ロック (LWLock) の競合を生み出し、読み取り専用操作であってもデータベースのパフォーマンスに影響を与えます。

本投稿では、読み取り集約型のワークロードが、高速パスロックの制限を超えることによって LWLock 競合を引き起こす仕組みを探求します。これは、PostgreSQL エンジンとそのロック機構に基づく任意のシステムで発生する可能性がある問題です。デモンストレーション目的で本投稿では Aurora PostgreSQL 互換エディションを使用します。実際的な実験を通じて、パーティションスキャン、インデックスの使用、複雑な結合がロック動作にどのような影響を与えるかを実演します。また、ワークロードが低速パスロックに移行するタイミングを特定する方法と、より良いパフォーマンスのためにクエリ設計とスキーマ構造を最適化する具体的な技術を実装する方法も示します。

読み取り集約型のワークロードが増加するにつれて、データベース管理者 (DBA) は、データベースを健全に保つために LWLock 競合を監視し、対処する必要があります。同様に、開発者は過度の競合を引き起こすパターンを避けなければなりません。データベースが高速パスから低速パスロックに移行すると、スループットは大幅に低下する可能性があります (本投稿のテストでは最大 34 パーセントのパフォーマンス差を示しています)。このスループット低下は、LWLock:lock_manager などの待機イベントを監視し、pg_locks ビューを確認してワークロードを実行しているバックエンドプロセスの高速パススロットが枯渇しているかどうかを確認することで特定できます。これらのボトルネックに対処するには、効果的なパーティションプルーニング、慎重なインデックス管理、PostgreSQL のバックエンドプロセスあたり 16 スロットの高速パス制限内にワークロードを収めるシンプルな結合パターンなどの戦略が必要です。

LWLock 競合の理解

LWLock は、共有メモリ構造へのアクセスを調整するために PostgreSQL で使用される同期の仕組みです。ヘビーウェイトロック (ユーザー主導のデータベースオブジェクトレベルのロック) とは異なり、LWLock は軽量で高パフォーマンスに最適化されており、共有データへの同時アクセスを管理しながら低いオーバーヘッドを提供します。

LWLock 競合は、複数のプロセスが共有メモリ内のロックデータ構造上の同じ LWLock を取得しようと競合する際に発生し、遅延を引き起こします。この競合は通常、多くのバックエンドプロセスが次のような頻繁に共有されるリソースにアクセスする必要がある場合に発生します。

  1. Buffer Manager – 読み取り/書き込み操作中に共有バッファを保護する
  2. Lock Manager – ロック関連のデータ構造へのアクセスを調整する
  3. WAL management – 先行書き込みログ (WAL) への書き込みを同期する

LWLock 競合は、同時データベース接続数が増加するにつれて増大する可能性があります。

これは特に、Aurora PostgreSQL 互換エディションが、高並列処理、多数のパーティションを持つテーブル、または多数のインデックスを持つテーブルを含むワークロードの高スループット環境で顕著です。

テーブルに対して SQL クエリを実行する際、PostgreSQL はそのテーブルと関連するインデックスに対してロックを取得しようとします。これがパーティションテーブルの場合、SQL クエリによってアクセスされるテーブルパーティションに対してロックが取得されます。ロックをより高速かつ効率的にするため、システムが他のロックとの競合がないことを迅速に確認できる場合、制限の少ない弱いロック (AccessShareLock、RowShareLock、RowExclusiveLock など) に対して高速パスロックが使用されます。

高速パスロック: 動作メカニズム

PostgreSQL では、高速パスロッキング機構により、競合しない操作は共有メモリロックハッシュテーブルとそれに関連する LWLocks をバイパスすることができます。高速パスロッキングは、最も一般的な使用例、即ち、同じリレーションに競合する強力なロックが存在しない限り、弱いロックを取得する頻繁な同時クエリ向けに設計されています。

高速パスロックの動作は以下の通りです。

  1. セッション別キャッシュ – 各バックエンドプロセスは、高速パスロックを格納するために、プライベートPGPROC構造内に最大 16 個のスロット (デフォルトでFP_LOCK_SLOTS_PER_BACKEND = 16) を割り当てます。
  2. 迅速な高速パス適格性チェック –SELECT * FROM my_table;を実行すると、PostgreSQL は小さなバックエンド別の LWLock (MyProc->fpInfoLock) を取得して、現在の SQL クエリに高速パスロック機構が使用できるか、以下の項目をチェックします。
    • ロックモードが適格な弱いモードであること
    • 他のセッションが競合するロックを保持していないこと
    • バックエンドがローカルバックエンドメモリ配列の 16 スロットをすべて使用していないこと
  3. ローカル許可 – 上記の高速パス適格性チェックがパスすると、FastPathGrantRelationLock()がバックエンドのローカルキャッシュにロックを格納します。共有メモリベースのロックハッシュテーブルを保護する共有メモリベースの LWLock は取得されず、関数は成功して即座にリターンします。

実際には、これはトランザクションがアクセスする最初の 16 個の一意のテーブル (またはインデックス) では、ロックマネージャーのオーバーヘッドはほぼゼロになることを意味します。

高速パスキャッシュは小さく、16 個のロックを超えたり、より強いロックモードを要求したりすると、PostgreSQL は低速パスにフォールバックしなければなりません。

  1. 既に取得済みの 16 個の高速パスロックを超える高速パスロックのすべての要求は、FastPathTransferRelationLocks()を使用して共有ロックテーブルに移行されます
  2. ロックタグ（リレーション OID とロックモードを含む）は、共有メモリロックハッシュテーブルの 16 個のロックパーティションのうちの 1 つにハッシュ化されます
  3. PostgreSQL は次に、パーティション LWLock (LWLockAcquire(partitionLock, LW_EXCLUSIVE)) を取得し、共有ハッシュテーブルを更新し、LWLock を解放します

それ以降、テーブルからインデックスまでの追加のロック取得は、ロックマネージャーを通じて行われ、同時実行下でLWLock:LockManager待機イベントを生成します。

高速パス最適化から低速パス競合への移行を理解することで、高速パスの制限内に留まるクエリとスキーマを設計し、ロックマネージャーのボトルネックを完全に回避できます。

実験概要

以下のセクションでは、3 つの実験を通じて LWLock 競合の詳細を説明します。

  1. パーティション化されたテーブルでのロックを観察する
  2. 使用されていない、または不要な複数のインデックスを持つパーティション化されていないテーブルでのロックを観察する
  3. マルチ結合クエリでのロック動作を観察する

それぞれの実験では、スキーマのセットアップ、ワークロードの実行、PostgreSQL システムビューによるロック監視、および pgbench を使用した同時実行の影響分析を実演します。すべての実験は、db.r7g.4xlarge インスタンスを使用して Aurora PostgreSQL 互換エディション (PostgreSQL 16.6 と互換性あり) で実施しました。

前提条件

実験を開始する前に、以下が必要となります。

  • Aurora PostgreSQL データベースへのアクセス権を持つ AWS アカウント
  • AWS Management Console へのアクセス権
  • Aurora PostgreSQL インスタンスへの接続性を持つ Amazon Elastic Compute Cloud (Amazon EC2) インスタンス
  • Amazon EC2 インスタンス上にインストールされた PostgreSQL クライアント (psql など)
  • Amazon EC2 インスタンス上にインストールされた pgbench
  • Aurora PostgreSQL クラスターを作成・管理するための AWS Identity and Access Management (IAM) 権限

実験 1 : パーティション化されたテーブルでのロックを観察する

この実験では、3 つの主要なテストシナリオを実行し、パーティション化されたテーブルを扱う際の PostgreSQL のロック動作を調査します。

  1. ordersテーブルのすべてのパーティションをクエリして、PostgreSQL がパーティション全体にわたってロックをどのように処理するかを観察します
  2. 特定のパーティションにアクセスするためにパーティションプルーニングを使用して、より的を絞ったアプローチを検証します
  3. 実世界のワークロードをシミュレートするため に pgbench を使用して、高い同時実行下で両方のアプローチをストレステストします

これらのクエリを通じて、PostgreSQL の高速パスロック最適化がどのように動作するか、高速パススロットが枯渇したときに何が起こるか、および同時実行ワークロード下でパーティションプルーニングがいかにパフォーマンスを改善できるかを実演します。order_tsタイムスタンプ列を使用して月単位でデータがパーティション化されたordersテーブルを使用します。

この実験では、以下の点について重要な洞察が明らかになります。

  • PostgreSQL が読み取り専用操作中にロックをどのように管理するか
  • 高速パスと低速パスロックの影響
  • パーティションプルーニングがどのようにロック競合を軽減できるか
  • 高並行性環境におけるパフォーマンスへの影響

スキーマ準備

好みの PostgreSQL クライアント (psql など) を使用して、EC2 インスタンスから Aurora PostgreSQL インスタンスに接続し、12 の月次子パーティションを持つordersテーブルを作成します。以下の SQL コードを実行してください。

-- Create the schema
CREATE SCHEMA experiment_1;
SET search_path TO experiment_1;

-- Create the partitioned parent table
CREATE TABLE orders (
  id          int NOT NULL,
  order_ts    timestamp NOT NULL,
  customer_id int,
  amount      numeric,
  PRIMARY KEY (id, order_ts)
) PARTITION BY RANGE (order_ts);

-- Create child partitions (example: Jan 2025 - Jun 2025)
DO $$
DECLARE
  month int := 1;
  partition_name text;
  from_date date;
  to_date date;
BEGIN
  WHILE month <= 12 LOOP
    partition_name := 'orders_2025_' || to_char(month, 'FM00');
    from_date := make_date(2025, month, 1);
    to_date := from_date + interval '1 month';
    EXECUTE format(
      'CREATE TABLE %I.%I PARTITION OF %I.%I FOR VALUES FROM (%L) TO (%L)', 
      'experiment_1', partition_name, 
      'experiment_1', 'orders',
      from_date, to_date
    );
    month := month + 1;
  END LOOP;
END $$;

テスト 1 : Orders テーブルの全パーティションをクエリして、ロック動作を観察する

それでは、トランザクションを開始し、パーティション化されたordersテーブルのすべてのパーティションに対してクエリを実行します。パーティションプルーニング無でクエリを実行すると、PostgreSQL はすべてのパーティションにアクセスする必要があり、ロックのオーバーヘッドが大幅に増加します。このテストを開始するには、Aurora PostgreSQL データベースへの新しい接続を開き、以下のコマンドを実行します (これをセッション 1 と呼びます) 。

postgres=> set search_path to experiment_1;
SET
postgres=> begin;
BEGIN
postgres=*> select count(*) from orders;
 count 
-------
     0
(1 row)

上記のSQL ステートメントは、12 のパーティションすべてをスキャンするトランザクションを開始します。COMMIT、ROLLBACKまたはEndコマンドを実行せず、トランザクションを開いたままにしてロックを維持してください。

セッション 1 のトランザクションが開いたままの状態で、2 番目のセッション (これをセッション 2 と呼びます) を開き、データベース内のロックの状態を確認するために次の SQL クエリを実行してください。

postgres=> SELECT                                                                 
    n.nspname AS schema,
    c.relname AS table,
    l.locktype,
    l.mode,
    l.fastpath
FROM pg_locks l
JOIN pg_class c ON l.relation = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid 
    AND n.nspname <> 'pg_catalog';
    schema    |        table        | locktype |      mode       | fastpath 
--------------+---------------------+----------+-----------------+----------
 experiment_1 | orders_2025_07_pkey | relation | AccessShareLock | t
 experiment_1 | orders_2025_07      | relation | AccessShareLock | t
 experiment_1 | orders_2025_06_pkey | relation | AccessShareLock | t
 experiment_1 | orders_2025_06      | relation | AccessShareLock | t
 experiment_1 | orders_2025_05_pkey | relation | AccessShareLock | t
 experiment_1 | orders_2025_05      | relation | AccessShareLock | t
 experiment_1 | orders_2025_04_pkey | relation | AccessShareLock | t
 experiment_1 | orders_2025_04      | relation | AccessShareLock | t
 experiment_1 | orders_2025_03_pkey | relation | AccessShareLock | t
 experiment_1 | orders_2025_03      | relation | AccessShareLock | t
 experiment_1 | orders_2025_02_pkey | relation | AccessShareLock | t
 experiment_1 | orders_2025_02      | relation | AccessShareLock | t
 experiment_1 | orders_2025_01_pkey | relation | AccessShareLock | t
 experiment_1 | orders_2025_01      | relation | AccessShareLock | t
 experiment_1 | orders_pkey         | relation | AccessShareLock | t
 experiment_1 | orders              | relation | AccessShareLock | t
 experiment_1 | orders_2025_11      | relation | AccessShareLock | f
 experiment_1 | orders_2025_09      | relation | AccessShareLock | f
 experiment_1 | orders_2025_09_pkey | relation | AccessShareLock | f
 experiment_1 | orders_2025_11_pkey | relation | AccessShareLock | f
 experiment_1 | orders_2025_08_pkey | relation | AccessShareLock | f
 experiment_1 | orders_2025_10      | relation | AccessShareLock | f
 experiment_1 | orders_2025_08      | relation | AccessShareLock | f
 experiment_1 | orders_2025_10_pkey | relation | AccessShareLock | f
 experiment_1 | orders_2025_12_pkey | relation | AccessShareLock | f
 experiment_1 | orders_2025_12      | relation | AccessShareLock | f
(26 rows)

前述の出力の最後の 10 行で fastpath列の値がt(True) とf(False) のうち、f になっていることに注目してください。また、返された行の総数は 26 です。値 t は、16 の高速パススロットが使い果たされ、残りのパーティション/インデックスの AccessShareLockが共有メモリロックハッシュテーブル (低速パス) に移行されたことを意味します。トランザクションが完了すると、これらのロックは解放されます。

テスト 2 : 注文テーブルの特定のパーティションをクエリし、ロック動作の変化を観察する

セッション 1 として、以下に示すように、新しいトランザクション内でパーティションプルーニングアプローチを使用するクエリを実行します。より多くのパーティションにアクセスし続けると、追加の高速パスロックが獲得されます。

postgres=> set search_path to experiment_1;
SET
postgres=> begin;
BEGIN
postgres=*> SELECT * FROM orders WHERE order_ts <= '2025-01-01';
 id | order_ts | customer_id | amount 
----+----------+-------------+--------
(0 rows)

postgres=*> SELECT * FROM orders WHERE order_ts <= '2025-02-01';
 id | order_ts | customer_id | amount 
----+----------+-------------+--------
(0 rows)

postgres=*> SELECT * FROM orders WHERE order_ts <= '2025-03-01';
 id | order_ts | customer_id | amount 
----+----------+-------------+--------
(0 rows)

セッション 1 のトランザクションが開いたままの状態で、前回のテストで利用したセッション 2 で以下の SQL 文を実行して、データベース内のロックの状態を確認してください。

postgres=> SELECT 
    n.nspname AS schema,
    c.relname AS table,
    l.locktype,
    l.mode,
    l.fastpath
FROM pg_locks l
JOIN pg_class c ON l.relation = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid 
    AND n.nspname <> 'pg_catalog';
    schema    |        table        | locktype |      mode       | fastpath 
--------------+---------------------+----------+-----------------+----------
 experiment_1 | orders_2025_03_pkey | relation | AccessShareLock | t
 experiment_1 | orders_2025_03      | relation | AccessShareLock | t
 experiment_1 | orders_2025_02_pkey | relation | AccessShareLock | t
 experiment_1 | orders_2025_02      | relation | AccessShareLock | t
 experiment_1 | orders_2025_01_pkey | relation | AccessShareLock | t
 experiment_1 | orders_2025_01      | relation | AccessShareLock | t
 experiment_1 | orders_pkey         | relation | AccessShareLock | t
 experiment_1 | orders              | relation | AccessShareLock | t
(8 rows)

この出力は、すべてのロックが高速パスロックであることを示しており、fastpath列の値がt(True) になっています。これにより、低速パスロックを取得する必要がなくなります。

これは高速パスの最適化を示していますが、同時実行を伴うシナリオについては説明していません。同時実行を伴うシナリオでは、各バックエンドが高速パススロットを使い切るか、16 スロットの制限内に留まるかのいずれかになります。この特定のシナリオを詳しく見ていきましょう。pgbench を使用してマルチユーザーワークロードをシミュレートします。

テスト 3.1 : Orders テーブルの全パーティションに複数クエリを同時実行し、ロック動作の変化を観察する

パーティションプルーニングせずにパーティションにアクセスする複数の読み取りワークロードをシミュレートするには、以下の pgbench コマンドを使用します。このコマンドは複数のスレッドにわたってSELECT count(*) FROM ordersクエリを継続的に発行します。このテストでは、トランザクションが高速パススロットを使い果たし、メインロックマネージャーを通じてロックの獲得を強制する (LWLock:LockManager の待機をトリガーする) 高い並列度の下で、PostgreSQL の高速パスロック最適化がどのように動作するかを評価します。

pgbench -c 100 -j 10 -n -f transaction.sql -T 900

pgbenchでは、-cと-jオプションを使用してベンチマークワークロードの同時実行性と並列性を制御します。-c オプションは同時クライアント数を指定し、同時にアクティブになるシミュレートされたユーザーセッションまたはデータベース接続の数を意味します。この数値によって、PostgreSQL データベースに適用される負荷のレベルが決まります。-jオプションはpgbenchがこれらのクライアント接続を管理するために使用するワーカースレッドの数を定義します。各スレッドは全クライアントの一部を処理し、ワークロードはスレッド間で均等に分散されます。これにより pgbenchはマルチコアシステムをより効率的に使用し、クライアント側のボトルネックを回避できます。

実行時に認証情報を入力せずに pgbenchコマンドを実行するには、次の環境変数を設定します：PGHOST(Auroraクラスターのエンドポイント)、PGPORT(ポート番号、例えば 5432)、PGDATABASE(データベース名、例えば postgres)、PGUSER(データベースユーザー)、およびPGPASSWORD(データベースユーザーのパスワード)。

上記の pgbench コマンドは、transaction.sql で定義されたトランザクションを実行する 100 の同時クライアント (-c 100) を 15 分間または 900 秒 (-T 900)シミュレートします。

transaction.sqlファイルには、experiment_1スキーマへの検索パスの設定とともに、次の SQL が含まれています。

set search_path to experiment_1;
DO $$
DECLARE
	random_date date;
	last_day date;
	sql text;
BEGIN
	random_date := date '2025-01-01' + floor(random() * 365)::int;
	last_day := (date_trunc('month', random_date) + interval '1 month - 1 day')::date;
	sql := format('SELECT COUNT(*) FROM orders');
	EXECUTE sql;
END $$;

前回のテストのセッション 1 ターミナルから、次の pgbench コマンドを実行してください。このコマンドは 15 分で完了します。このコマンドの実行中、Amazon CloudWatch Database Insights のデータベースの待機イベントを監視します。

sh-5.1$ pgbench -c 100 -j 10 -n -f transaction.sql -T 900
transaction type: transaction.sql
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 10
duration: 900 s
number of transactions actually processed: 42005251
latency average = 2.143 ms
tps = 46672.159856 (including connections establishing)
tps = 46672.894164 (excluding connections establishing)

このワークロードでは、1 秒あたりの平均トランザクション数 (tps) は 46,672 に達し、テスト時間の 15 分間で 4,200 万のトランザクションが処理されました。

CloudWatch Database Insights の以下のスクリーンショットは、アクティブなセッションがインスタンスの CPU 容量を超え、著しいロック競合を伴う高負荷を経験しているデータベースを示しています。

上のスクリーンショットは、db.r7g.4xlarge インスタンスで実行されている Aurora PostgreSQL 16.6 クラスターが、CPU 使用率とロック競合の両方による高いデータベース負荷を示しています。平均アクティブセッション (AAS) は約 21 で維持されており、これはインスタンスの 16 vCPU 容量よりも高い値です。負荷の 66 パーセントは CPU 使用率に起因していますが、34 パーセントは LWLock:LockManagerの待機に費やされており、PostgreSQL 内部ロック構造の競合を示しています。

次に、パーティションプルーニングを使用してパフォーマンスを評価します。

テスト 3.2 : パーティションプルーニングを使用して、Orders テーブルの特定のパーティションに複数クエリを同時実行する

パーティションプルーニングを使用していない前回のテストと対比するために、ここではパーティションプルーニングがどのようにパフォーマンスを向上させるかを実証します。このワークロードでは、この実験に使用されるtransaction.sqlファイルには PL/pgSQL が含まれています。これは単純な SQL クエリの代わりに使用され、パーティション分割されたテーブルと実行時に生成される値を処理する際に、PostgreSQL で効率的なパーティションプルーニングを提供します。以下のクエリのように SQL を使用することもできますが、order_tsに対するフィルターは共通テーブル式 (CTE) 内のランダムに生成された日付から派生しており、最適な実行計画を作成するクエリプランナーは、クエリ計画時に order_ts値を決定できません。その結果、PostgreSQL はすべてのパーティションを考慮する必要があり、すべてのパーティションの不必要なロックとスキャンにつながります。しかし、ランダムな日付を計算し EXECUTEを使用してクエリを動的に構築する PL/pgSQL ブロックに切り替えることで、実際の日付値が SQL 文字列に直接注入されます。これにより、クエリプランナーの観点からフィルターが定数に変換され、効果的なパーティションプルーニングが可能になり、関連するパーティションのみがアクセスされロックされることが保証されます。

以下は、上記で説明した CTE ベースの SQL クエリで、すべてのパーティションをロックする可能性があります。

WITH rand_date AS (
    SELECT (date '2025-01-01' + (floor(random() * 365))::int) AS dt
),
last_day AS (
    SELECT 
        dt,
        (date_trunc('month', dt) + interval '1 month - 1 day')::date AS last_day
    FROM rand_date
)
SELECT 
    dt AS random_date,
    last_day,
    (
        SELECT COUNT(*)
        FROM orders
        WHERE order_ts >= last_day.last_day
        AND order_ts < last_day.last_day + interval '1 day'
    ) AS order_count
FROM last_day;

上記で説明した効果的なパーティションプルーニングを実現するために、以下の PL/pgSQL アプローチを使用した SQL を使用してください。

set search_path to experiment_1;
DO $$
DECLARE
    random_date date;
    last_day date;
    sql text;
BEGIN
    random_date := date '2025-01-01' + (floor(random() * 365))::int;
    last_day := (date_trunc('month', random_date) + interval '1 month - 1 day')::date;
    
    sql := format('SELECT count(*) FROM orders WHERE order_ts = %L', last_day);
    EXECUTE sql;

前回のテストと同じ手順に従い、セッション 1 のターミナルから pgbench コマンドを実行してください。このコマンドは 15 分でテストを完了し、コマンドの実行中、CloudWatch Database Insights のデータベースの待機イベントを監視します。

sh-5.15 pgbench -c 100 -j 10 -n -f transaction.sql -T 900
transaction type: transaction.sql
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 10
duration: 900 s
number of transactions actually processed: 53240775
latency average = 1.690 ms
tps = 59255.673468 (including connections establishing)
tps = 59156.616479 (excluding connections establishing)

上記の pgbench 出力に示されているように、このワークロードは 1 秒あたり平均 59,255 トランザクションを達成し、15 分間のテスト期間中に約 5,330 万トランザクションが処理されました。ロック競合がない状態では、システムは 1,100 万トランザクションを追加で処理出来た事になります。

CloudWatch Database Insights の以下のスクリーンショットは、パーティションプルーニングを実装した後の改善されたデータベースパフォーマンスを示しており、安定した負荷パターンとロック競合がないことがわかります。

前述のワークロードにパーティションプルーニングを導入したことで、Aurora PostgreSQL 16.6 クラスターのパフォーマンスは大幅に向上しました。以前は、ワークロードは CPU 使用率と並んでデータベース負荷の約 34 パーセントを消費するLWLock:LockManagerの待機イベントによって特徴付けられていました。

対照的に、現在のワークロードパフォーマンスはバランスの取れたワークロードを示しています。平均アクティブセッション (AAS) は最大 vCPU 閾値を下回っており、待機は Timeout:SpinDelayのごく一部が観測されているのみです。CPU は最適化された OLTP システムでは予想される通り、負荷の主要な要因となっています。そしてロック競合は大幅に減少しました。これは、パーティションプルーニングによって取得されるロックの数を削減し、各セッションが関連するパーティションとセッションごとの高速パスロックのみにアクセスするようになり、同時実行性が大幅に向上したことを意味します。パーティションプルーニングにより、AAS は最大 vCPU 閾値を下回ったままでした。

実験 2 : 複数の未使用または不要なインデックスを持つ非パーティションテーブルのロックを観察する

この実験では、PostgreSQL が複数の B-tree インデックスを持つ非パーティション化テーブルでのロック動作をどのように処理するかを調査します。ここでは、使用されていないまたは不要なインデックスの影響に焦点を当てるためにパーティション化されていないテーブルを使用しています。E コマースまたは在庫システムを表すitemsテーブルを使用して、2 つの重要なテストシナリオを探ります。

  1. まず、インデックスのみのスキャンを使用して単純なクエリを実行し、以下を観察します
    • PostgreSQL が複数の未使用または不要なインデックス全体でロックをどのように管理するか
    • 高速パススロットが使い果たされた場合に何が起こるか
    • 20 個の B-tree インデックスを持つことがロック取得に与える影響
  2. 次に、高い同時実行性の下でシステムのストレステストを行い、以下を実証します
    • 過剰なインデックスがロックマネージャーの動作にどのように影響するか
    • 複数のインデックスによるロック競合のパフォーマンスへの影響
    • インデックス数とLWLock:LockManagerの待機の関係

これらのテストを通じて、インデックス関連のロックオーバーヘッドに関する重要な洞察を明らかにし、高い同時実行性の環境におけるインデックス管理のための実践的なガイダンスを提供します。

スキーマ準備

以下の SQL コードを使用して、Aurora PostgreSQL データベースに items テーブルスキーマとそれに関連するインデックスを作成してください。

-- Create Schema
CREATE SCHEMA IF NOT EXISTS experiment_2;
SET search_path TO experiment_2;

CREATE TABLE items (
    item_id SERIAL PRIMARY KEY, sku TEXT NOT NULL, barcode TEXT, name TEXT NOT NULL, 
    description TEXT,category TEXT, subcategory TEXT, vendor_id INT, 
    vendor_region TEXT, brand TEXT, model TEXT,price NUMERIC(10,2), 
    cost NUMERIC(10,2), discount NUMERIC(5,2), margin NUMERIC(5,2),
    quantity_in_stock INT, reorder_level INT, lead_time_days INT, rating NUMERIC(2,1)
    ,num_reviews INT, available BOOLEAN DEFAULT true, warehouse_location TEXT, 
    is_active BOOLEAN DEFAULT true,last_restocked TIMESTAMP, 
    created_at TIMESTAMP DEFAULT now(), updated_at TIMESTAMP DEFAULT now()
);

CREATE INDEX idx_items_sku ON items(sku);
CREATE INDEX idx_items_barcode ON items(barcode);
CREATE INDEX idx_items_name ON items(name);
CREATE INDEX idx_items_category ON items(category);
CREATE INDEX idx_items_vendor_id ON items(vendor_id);
CREATE INDEX idx_items_brand ON items(brand);
CREATE INDEX idx_items_model ON items(model);
CREATE INDEX idx_items_price ON items(price);
CREATE INDEX idx_items_cost ON items(cost);
CREATE INDEX idx_items_quantity ON items(quantity_in_stock);
CREATE INDEX idx_items_rating ON items(rating);
CREATE INDEX idx_items_num_reviews ON items(num_reviews);
CREATE INDEX idx_items_created_at ON items(created_at);
CREATE INDEX idx_items_updated_at ON items(updated_at);
CREATE INDEX idx_items_vendor_category ON items(vendor_id, category);
CREATE INDEX idx_items_sku_available ON items(sku, available);
CREATE INDEX idx_items_barcode_active ON items(barcode, is_active);
CREATE INDEX idx_items_category_subcategory_price ON items(category, subcategory, 
    price);
CREATE INDEX idx_items_vendor_region_brand ON items(vendor_region, brand);
CREATE INDEX idx_items_brand_model ON items(brand, model);

上記の SQL コードは、商品の詳細 (SKU、price、inventoryなど) 用の 26 カラムを持つ E コマースのitemsテーブルと、よく検索されるカラムやカラムの組み合わせに対する 20 個の B-tree インデックスを作成します。

テスト 1 : 複数のインデックスを持つ非パーティションテーブルをクエリする

過剰なインデックスが PostgreSQL のロック動作にどのように影響するかの調査を始めるために、最初のテストとして itemsテーブルに対してクエリを実行しましょう。単純な名前検索クエリを実行して、ほとんどのインデックスがこのクエリには不要であるにもかかわらず、PostgreSQL が 20 個すべてのインデックスにわたってロック管理をどのように処理するかを観察します。これにより、過剰なインデックスを維持することで生じる基本的なロックオーバーヘッドを理解するのに役立ちます。トランザクションを開始し、インデックスアクセスパスを使用して itemsテーブルの特定のカラムにクエリを実行します。

postgres=> set search_path to experiment_2;
SET

postgres=> EXPLAIN SELECT name FROM items WHERE name = 'test';
                           QUERY PLAN                           
----------------------------------------------------------------
 Index Only Scan using idx_items_name on items  (cost=0.14..8.16 rows=1 width=32)
   Index Cond: (name = 'test'::text)
(2 rows)
postgres=> BEGIN;
BEGIN
postgres=> SELECT name FROM items WHERE name = 'test';
 name 
------
(0 rows)

前述の SQL クエリに対して、クエリプランナーはインデックスのみのスキャンパスを選択しました。この SQL ではitemsテーブルからnameカラムのみを選択しています。別のセッションで、ロック動作を観察します。

以下の出力で、itemsテーブル、主キー、およびクエリ実行のためにプランナーが使用したインデックス (idx_items_name) とは別に AccessShareLockを獲得したインデックスに注目して下さい。返された行の総数は 22 で、高速パススロットは使い果たされ、6 つのロックが共有メモリロックテーブルに移動しました。このテーブルにさらにインデックスがあった場合、それらのインデックスもAccessShareLockを必要とし、高速パススロットが使い果たされているため共有メモリロックテーブルに配置されるでしょう。このテーブルに対して高い並行ワークロードが発生すると、共有メモリロックテーブルで競合が発生するため、パフォーマンスは低下する事が想定されます。

postgres=> SELECT
    n.nspname AS schema,
    c.relname AS table,
    l.locktype,
    l.mode,
    l.fastpath
FROM pg_locks l
JOIN pg_class c ON l.relation = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
    AND n.nspname <> 'pg_catalog';
    schema    |                table                 | locktype |      mode       | fastpath
--------------+--------------------------------------+----------+-----------------+----------
 experiment_2 | idx_items_updated_at                 | relation | AccessShareLock | t
 experiment_2 | idx_items_created_at                 | relation | AccessShareLock | t
 experiment_2 | idx_items_num_reviews                | relation | AccessShareLock | t
 experiment_2 | idx_items_rating                     | relation | AccessShareLock | t
 experiment_2 | idx_items_quantity                   | relation | AccessShareLock | t
 experiment_2 | idx_items_cost                       | relation | AccessShareLock | t
 experiment_2 | idx_items_price                      | relation | AccessShareLock | t
 experiment_2 | idx_items_model                      | relation | AccessShareLock | t
 experiment_2 | idx_items_brand                      | relation | AccessShareLock | t
 experiment_2 | idx_items_vendor_id                  | relation | AccessShareLock | t
 experiment_2 | idx_items_category                   | relation | AccessShareLock | t
 experiment_2 | idx_items_name                       | relation | AccessShareLock | t
 experiment_2 | idx_items_barcode                    | relation | AccessShareLock | t
 experiment_2 | idx_items_sku                        | relation | AccessShareLock | t
 experiment_2 | items_pkey                           | relation | AccessShareLock | t
 experiment_2 | items                                | relation | AccessShareLock | t
 experiment_2 | idx_items_vendor_region_brand        | relation | AccessShareLock | f
 experiment_2 | idx_items_brand_model                | relation | AccessShareLock | f
 experiment_2 | idx_items_vendor_category            | relation | AccessShareLock | f
 experiment_2 | idx_items_barcode_active             | relation | AccessShareLock | f
 experiment_2 | idx_items_sku_available              | relation | AccessShareLock | f
 experiment_2 | idx_items_category_subcategory_price | relation | AccessShareLock | f
(22 rows)

テスト 2 : 高い同時実行性のもとで、複数のインデックスを持つ非パーティションテーブルをクエリする

これらの不要なインデックスがパフォーマンスにどのように影響するかを理解するために、高い並行性の下でパーティション化されていないitemsテーブルのストレステストを行いましょう。パーティション化されたテーブル (実験 1) での実験と同様に、pgbench を使用して複数の同時ユーザーが同時にテーブルにアクセスすることをシミュレートします。

最初の実験と同じ pgbench コマンドを使用して、セッション 1 のターミナルから以下を実行します。このテストは 5 分間実行されます。テスト実行中、CloudWatch Database Insights のデータベースの待機イベントを監視します。

pgbench -c 100 -j 10 -n -f transaction.sql -T 300

transaction.sqlファイルには、前回のテストと同じitemsテーブルに対する名前検索 SQL クエリが含まれています。

CloudWatch Database Insights の以下のスクリーンショットは、itemsテーブルの過剰なインデックスによって、LWLock:LockManagerの待機時間が増加し、データベース負荷と CPU 使用率が増加する様子を示しています。

ここで観察されたLWLock:LockManager待ちは、主に itemsテーブルの過剰なインデックスによって引き起こされています。データが無い場合でも、PostgreSQL はクエリの計画と実行中に 20 個すべてのインデックスを調べ、関連するロックを取得し、カタログメタデータにアクセスする必要があるため、オーバーヘッドが発生します。高い並行性のため、多数のロックが関与し、データベースセッションは高速パスロックを使い果たし、バックエンドプロセスがメインロックマネージャーに頼らざるを得なくなり、追加の競合が発生しました。これにより、繰り返されるカタログスキャンによる CPU 使用率の増加と、ロック取得のオーバーヘッドによるデータベース負荷の上昇が生じました。不要なインデックスの数を減らすことは、クエリプランニングの複雑さを減少させるだけでなく、高速パスロッキングを維持するのに役立ち、高い並行性のワークロードの下でシステム効率を向上させるでしょう。

実験 3 : 複数結合クエリでのロック動作を観察する

この実験では、PostgreSQL が複数の関連テーブルにまたがる複雑なクエリを実行する際にどのようにロックを管理するかを調査します。実際的な E コマースデータベーススキーマを使用して、2 つの重要なテストシナリオを探ります。

  1. まず、単一のマルチ結合クエリを検証します
    • ユーザーのカート内容、アイテム価格、注文状況、および支払い詳細を単一の読み取り専用操作で取得します
    • 6 つの相互関連テーブル (users、carts、cart_items、items、orders、payments) を接続します
    • PostgreSQL が複数のテーブルとそのインデックスにまたがるロックをどのように処理するかを実証します

このクエリは、複数のテーブル結合が累積的なロックのフットプリントにどのように影響するかを観察する機会を提供します。各テーブルには独自の主キーと外部キーのインデックスがあるため、PostgreSQL はこれらの単純な読み取りに高速パスロックを使用できる可能性があり、共有メモリロックテーブルにエントリを取得するオーバーヘッドを回避できます。

  1. 次に、高い並行性の下でシステムのストレステストを行います
    • 複雑な結合を実行する複数のセッションがロック管理にどのように影響するかを観察します
    • 複数のインデックス付きテーブルにまたがるロック取得のパフォーマンスへの影響を測定します
    • 結合の複雑さが CPU 使用率とロック競合の両方にどのように影響するかを実証します

これらのテストを通じて、以下に関する重要な洞察を明らかにします。

  • 複雑な相互接続されたテーブル構造におけるロック管理
  • テーブル結合、インデックス、およびロックオーバーヘッドの関係
  • 高い並行性環境におけるマルチ結合クエリのパフォーマンスに関する考慮事項

スキーマ準備

以下の SQL コードを使用して、6 つの相互接続されたテーブル (users、carts、cart_items、items、orders、および payments) とそれらに関連するインデックスおよび外部キー制約を含む E コマーススキーマを作成してください。このスキーマは、実際の E コマースデータベースをシミュレートするために、テーブルごとに複数のインデックスと適切な参照整合性制約を含む包括的な設計になっています。

-- Create Schema
CREATE SCHEMA IF NOT EXISTS experiment_3;
SET search_path TO experiment_3;

-- USERS table
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    phone TEXT,
    role TEXT DEFAULT 'customer',
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted BOOLEAN DEFAULT FALSE,
    created_by INT,
    updated_by INT,
    created_at TIMESTAMP DEFAULT now(),
    updated_at TIMESTAMP DEFAULT now(),
    deleted_at TIMESTAMP
);
-- Indexes for USERS
CREATE INDEX idx_users_name_created_at ON users(name, created_at);
CREATE INDEX idx_users_email_active ON users(email, is_active);
CREATE INDEX idx_users_role_active ON users(role, is_active);
CREATE INDEX idx_users_created_at ON users(created_at);

-- ITEMS table
CREATE TABLE items (
    item_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    sku TEXT UNIQUE,
    category TEXT,
    price NUMERIC(10,2) NOT NULL,
    discount NUMERIC(5,2) DEFAULT 0.00,
    stock_quantity INT NOT NULL,
    restock_threshold INT DEFAULT 10,
    is_active BOOLEAN DEFAULT TRUE,
    is_deleted BOOLEAN DEFAULT FALSE,
    created_by INT,
    updated_by INT,
    created_at TIMESTAMP DEFAULT now(),
    updated_at TIMESTAMP DEFAULT now(),
    deleted_at TIMESTAMP
);
-- Indexes for ITEMS
CREATE INDEX idx_items_price_stock ON items(price, stock_quantity);
CREATE INDEX idx_items_category_active ON items(category, is_active);
CREATE INDEX idx_items_name_price ON items(name, price);
CREATE INDEX idx_items_created_at ON items(created_at);

-- CARTS table
CREATE TABLE carts (
    cart_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(user_id) ON DELETE CASCADE,
    status TEXT DEFAULT 'open',
    is_deleted BOOLEAN DEFAULT FALSE,
    expires_at TIMESTAMP,
    created_by INT,
    updated_by INT,
    created_at TIMESTAMP DEFAULT now(),
    updated_at TIMESTAMP DEFAULT now(),
    deleted_at TIMESTAMP
);
-- Indexes for CARTS
CREATE INDEX idx_carts_user_created ON carts(user_id, created_at);
CREATE INDEX idx_carts_user_status ON carts(user_id, status);
CREATE INDEX idx_carts_status_expires ON carts(status, expires_at);
CREATE INDEX idx_carts_created_at ON carts(created_at);

-- CART_ITEMS table
CREATE TABLE cart_items (
    cart_item_id SERIAL PRIMARY KEY,
    cart_id INT REFERENCES carts(cart_id) ON DELETE CASCADE,
    item_id INT REFERENCES items(item_id) ON DELETE RESTRICT,
    quantity INT NOT NULL CHECK (quantity > 0),
    price_at_addition NUMERIC(10,2),
    is_deleted BOOLEAN DEFAULT FALSE,
    added_at TIMESTAMP DEFAULT now()
);
-- Indexes for CART_ITEMS
CREATE INDEX idx_cart_items_cart_item ON cart_items(cart_id, item_id);
CREATE INDEX idx_cart_items_item_deleted ON cart_items(item_id, is_deleted);
CREATE INDEX idx_cart_items_cart_added ON cart_items(cart_id, added_at);
CREATE INDEX idx_cart_items_added_at ON cart_items(added_at);

-- ORDERS table
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(user_id),
    cart_id INT REFERENCES carts(cart_id),
    status TEXT NOT NULL CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled', 'refunded')),
    shipping_address TEXT,
    billing_address TEXT,
    tracking_number TEXT,
    payment_due_date TIMESTAMP,
    is_deleted BOOLEAN DEFAULT FALSE,
    total NUMERIC(10,2),
    created_by INT,
    updated_by INT,
    created_at TIMESTAMP DEFAULT now(),
    updated_at TIMESTAMP DEFAULT now(),
    deleted_at TIMESTAMP
);
-- Indexes for ORDERS
CREATE INDEX idx_orders_status_created ON orders(status, created_at);
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);
CREATE INDEX idx_orders_tracking ON orders(tracking_number);

-- PAYMENTS table
CREATE TABLE payments (
    payment_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id) ON DELETE CASCADE,
    payment_method TEXT NOT NULL,
    payment_status TEXT DEFAULT 'initiated',
    transaction_id TEXT UNIQUE,
    currency TEXT DEFAULT 'USD',
    amount NUMERIC(10,2) NOT NULL,
    is_refundable BOOLEAN DEFAULT FALSE,
    paid_at TIMESTAMP DEFAULT now(),
    refunded_at TIMESTAMP
);
-- Indexes for PAYMENTS
CREATE INDEX idx_payments_method_paid_at ON payments(payment_method, paid_at);
CREATE INDEX idx_payments_method_status ON payments(payment_method, payment_status);
CREATE INDEX idx_payments_order_status ON payments(order_id, payment_status);
CREATE INDEX idx_payments_currency_paid ON payments(currency, paid_at);

テスト 1 : 複数のインデックステーブルにわたるマルチ結合クエリを実行する

PostgreSQL が複雑な結合操作中にロックをどのように処理するかの調査を開始するために、ユーザーのショッピングカート情報を取得するマルチ結合クエリを使用して最初のテストを実行しましょう。このクエリは、PostgreSQL が複数のテーブルとそれらに関連するインデックスにわたってロックをどのように管理するかを示します。セッション 1 で、トランザクションを開始して次のクエリを実行します。

postgres=> SET search_path TO experiment_3;
BEGIN;
SELECT
    u.user_id,
    u.name AS user_name,
    c.cart_id,
    i.name AS item_name,
    ci.quantity,
    i.price,
    (i.price * ci.quantity) AS line_total,
    o.order_id,
    o.status,
    p.payment_method,
    p.amount
FROM users u
    JOIN carts c ON c.user_id = u.user_id
    JOIN cart_items ci ON ci.cart_id = c.cart_id
    JOIN items i ON i.item_id = ci.item_id
    LEFT JOIN orders o ON o.cart_id = c.cart_id
    LEFT JOIN payments p ON p.order_id = o.order_id
WHERE u.user_id = (1 + floor(random() * 10))::int;
SET
BEGIN
 user_id | user_name | cart_id | item_name | quantity | price | line_total | order_id | status | payment_method | amount
---------+-----------+---------+-----------+----------+-------+------------+----------+--------+----------------+--------
(0 rows)

セッション 1 で上記のトランザクションを開いたままにして、セッション 2 で以下のクエリを実行し、PostgreSQL がマルチ結合クエリに関わるすべてのテーブルとインデックスにわたってロックをどのように管理しているかを調べてください。

postgres=> SELECT
    n.nspname AS schema,
    c.relname AS table,
    l.locktype,
    l.mode,
    l.fastpath
FROM pg_locks l
JOIN pg_class c ON l.relation = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
AND n.nspname <> 'pg_catalog';
schema         |            table             | locktype |      mode      | fastpath
---------------+------------------------------+----------+----------------+----------
experiment_3   | idx_carts_status_expires     | relation | AccessShareLock | t
experiment_3   | idx_carts_user_status        | relation | AccessShareLock | t
experiment_3   | idx_carts_user_created       | relation | AccessShareLock | t
experiment_3   | carts_pkey                   | relation | AccessShareLock | t
experiment_3   | idx_users_created_at         | relation | AccessShareLock | t
experiment_3   | idx_users_role_active        | relation | AccessShareLock | t
experiment_3   | idx_users_email_active       | relation | AccessShareLock | t
experiment_3   | idx_users_name_created_at    | relation | AccessShareLock | t
experiment_3   | users_email_key              | relation | AccessShareLock | t
experiment_3   | users_pkey                   | relation | AccessShareLock | t
experiment_3   | payments                     | relation | AccessShareLock | t
experiment_3   | orders                       | relation | AccessShareLock | t
experiment_3   | items                        | relation | AccessShareLock | t
experiment_3   | cart_items                   | relation | AccessShareLock | t
experiment_3   | carts                        | relation | AccessShareLock | t
experiment_3   | users                        | relation | AccessShareLock | t
experiment_3   | idx_cart_items_item_deleted  | relation | AccessShareLock | f
experiment_3   | idx_orders_user_status       | relation | AccessShareLock | f
experiment_3   | idx_cart_items_cart_item     | relation | AccessShareLock | f
experiment_3   | idx_payments_order_status    | relation | AccessShareLock | f
experiment_3   | idx_carts_created_at         | relation | AccessShareLock | f
experiment_3   | idx_cart_items_cart_added    | relation | AccessShareLock | f
experiment_3   | idx_items_created_at         | relation | AccessShareLock | f
experiment_3   | orders_pkey                  | relation | AccessShareLock | f
experiment_3   | payments_pkey                | relation | AccessShareLock | f
experiment_3   | idx_items_name_price         | relation | AccessShareLock | f
experiment_3   | idx_payments_method_status   | relation | AccessShareLock | f
experiment_3   | payments_transaction_id_key  | relation | AccessShareLock | f
experiment_3   | idx_items_category_active    | relation | AccessShareLock | f
experiment_3   | items_pkey                   | relation | AccessShareLock | f
experiment_3   | idx_orders_status_created    | relation | AccessShareLock | f
experiment_3   | idx_items_price_stock        | relation | AccessShareLock | f
experiment_3   | idx_cart_items_added_at      | relation | AccessShareLock | f
experiment_3   | idx_orders_tracking          | relation | AccessShareLock | f
experiment_3   | idx_payments_currency_paid   | relation | AccessShareLock | f
experiment_3   | cart_items_pkey              | relation | AccessShareLock | f
experiment_3   | items_sku_key                | relation | AccessShareLock | f
experiment_3   | idx_orders_user_created      | relation | AccessShareLock | f
experiment_3   | idx_payments_method_paid_at  | relation | AccessShareLock | f
(39 rows)

上記の出力では、PostgreSQL が合計 39 個のロックを取得したことがわかります。fastpath列を見ると、23 行が f (false) を示しており、これらのロックは高速パスの代わりに共有メモリロックテーブルを通じた、低速パスを使用する必要があったことを示しています。これは、一見単純なネスト結合クエリでも、高速パススロットが使い果たされると著しいロック競合が発生する可能性があることを示しています。

テスト 2 : 高い同時実行性でマルチ結合クエリを実行する

上記の SQL クエリにおける複雑な結合が大規模環境でどのように実行されるかを理解するために、高い並行性の下で E コマーススキーマのストレステストを実施しましょう。前回の実験と同様に、pgbench を使用して複数の同時ユーザーが同時にマルチ結合クエリを実行することをシミュレートします。

前回の実験 (実験 1 と 2) と同じ pgbench コマンドを使用して、セッション 1 のターミナルから以下を実行します。このテストは 5 分間実行されます。

pgbench -c 100 -j 10 -n -f transaction.sql -T 300

transaction.sqlファイルには、前のテストと同じマルチ結合 SQL クエリが含まれています。テスト実行中、CloudWatch Database Insights でデータベース待機イベントを監視します。

CloudWatch Database Insights の以下のスクリーンショットは、多数のインデックスが付いたテーブル間のマルチ結合クエリが、アクティブセッションの 20 パーセントを消費するLWLock:LockManager競合と、80 パーセントで飽和に近づく CPU 使用率という二重のパフォーマンス影響をもたらすことを示しています。

データベース負荷グラフは、複数のインデックスを持つテーブル間でのマルチ結合クエリによる著しいLWLock:LockManager競合 (AAS の 20 パーセント) と高い CPU 使用率 (80 パーセント) を示しています。各結合は、PostgreSQL がインデックスに対してAccessShareLockロックを取得することを強制し、高速パスロックを使い果たし、より遅いメインロックマネージャーに頼ることになります。クエリプランニング中の繰り返しのカタログスキャンにより、CPU 使用率は飽和に近づいています。このロックマネージャーのボトルネックは、結合プランニング中に複数のインデックス付きテーブル全体でロックを維持することの複合的なオーバーヘッドに起因しています。冗長なインデックスを減らし、結合パターンを簡素化することで、ワークロードで見られるロック競合と CPU 圧力の両方が軽減されるでしょう。

PostgreSQL のロック競合を管理するための主な緩和戦略

PostgreSQL のロック競合を軽減するための以下の主要な対策を検討してください。

  • パーティション化されたテーブルへの最適化されたアクセス
    • パーティションプルーニングを有効にする – フルテーブルスキャンの代わりに明示的な日付範囲 (WHERE order_ts BETWEEN X AND Y) を使用します。PostgreSQL のドキュメントからパーティションプルーニングについて詳細を学んでください。
    • 定数のない動的 SQL を避ける – プルーニングを強制するため、本投稿の最初の実験のように CTE を PL/pgSQL ブロックに置き換えます。
    • パーティション数を制限する – 可能な場合はパーティションを減らし (例えば、月次パーティションの代わりに四半期パーティションを検討)、高速パスの制限内に収めます。
  • インデックスの合理化
    • 未使用のインデックスを監査し削除する – pg_stat_user_indexesを使用して使用頻度の低いインデックスを特定します。
    • 冗長なインデックスを統合する – 単一カラムのインデックスを複合インデックスに置き換えます (例えば、3 つの個別インデックスの代わりに(category, subcategory, price))。
    • 過剰なインデックス付けを避ける – OLTP にとって重要でない限り、テーブルあたりのインデックス数を制限します (例えば、10 以下)。
  • スキーマ設計の調整
    • 不必要なパーティション化を避ける – 100GB を超えるテーブルまたは明確なアクセスパターンがあるテーブルのみをパーティション化します。テーブルパーティション化をいつどのように実装するかについての詳細なガイダンスは、「Improve performance and manageability of large PostgreSQL tables by migrating to partitioned tables on Amazon Aurora and Amazon RDS」を参照してください。
    • カバリングインデックスを使用する – テーブルアクセスを避けるためにINCLUDE列を追加します (リレーションロックを減らします)。カバリングインデックスとインデックスのみのスキャンの実装についての詳細は、PostgreSQL のドキュメントを参照してください。
    • 高並行性テーブルを正規化する – 幅広いテーブル (例えば、items) を分割してインデックスの拡散を減らします。
  • クエリの最適化
    • 結合を簡素化する – マルチ結合クエリをマテリアライズドビューまたはステージドクエリに分割します。マテリアライズドビューの実装については、PostgreSQL のドキュメントを参照してください。
    • 小さな読み取りをバッチ処理にする – 小さな検索 (例えば、IN (...)句) を組み合わせてロック頻度を減らします。
  • PostgreSQL のチューニング
    • max_locks_per_transaction を調整する – パーティション化が避けられない場合 (メモリを監視)、および余分なロックが共有メモリロックハッシュテーブルに移動される場合は増やします (例えば、256 から 512 へ)。
    • 高速パスの使用状況を監視する – pg_locksを追跡してスロットの使い果たしを特定します。

クリーンアップ

本投稿のソリューションを実装することに関連した将来の料金が発生しないようにするには、作成したリソースを削除してください。

  • 実験で作成したテストスキーマとテーブルを削除します
  • テスト用に専用の Aurora PostgreSQL クラスターを作成した場合は、それを削除します
  • 不要になった関連スナップショットを削除します

結論

PostgreSQL の読み取り負荷の多いワークロードにおける LWLock の競合は、高速パスロッキングの制限を超えることから生じます。これは、パーティションプルーニングの未実装、冗長なインデックス、および複雑な結合によって引き起こされます。本投稿の実験では、以下のことが実証されました。

  • パーティションプルーニングはロックのオーバーヘッドを削減し、ロックを高速パススロットに限定することで、34 パーセントのパフォーマンス向上 (46,000 tps から 59,000 tps へ) をもたらしました
  • 使用されていない各インデックスはロック負荷を増加させ、空のテーブルでも低速パスへのフォールバックを強制します
  • マルチ結合クエリは競合を増幅し、テストされたシナリオでは 60 パーセントのロックが低速パスに溢れました

パーティションを意識したクエリの優先、厳格なインデックスの管理、および結合の簡素化によって、高速パスの効率を維持し、Amazon Aurora PostgreSQL とAmazon RDS for PostgreSQL で線形な読み取りスケーラビリティを提供できます。データベースが成長するにつれ、これらの最適化はロックのボトルネックなしで AWS の弾力性を活用するための基礎となります。

Aurora でのパフォーマンスチューニングとスケーラブルな PostgreSQL ワークロードの設計についての詳細は、「Aurora PostgreSQL チューニングの基本概念」を参照してください。

翻訳はテクニカルアカウントマネージャーの西原が担当しました。原文はこちらをご覧下さい。