Azure Cosmos DB for PostgreSQL の有用な診断クエリ

[アーティクル]
08/15/2024

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

特定のテナントのデータが含まれているノードの検索

マルチテナントのユースケースでは、特定のテナントの行を含むワーカーノードを特定できます。 Azure Cosmos DB for PostgreSQL は、分散テーブルの行をシャードにグループ化し、各シャードをクラスター内のワーカーノードに配置します。

アプリケーションのテナントがストアのときに、ストア ID が 4 のデータを保持しているワーカーノードを検索するとします。つまり、ディストリビューション列の値が 4 である行を含むシャードの配置を見つける必要があります。

SELECT shardid, shardstate, shardlength, nodename, nodeport, placementid
  FROM pg_dist_placement AS placement,
       pg_dist_node AS node
 WHERE placement.groupid = node.groupid
   AND node.noderole = 'primary'
   AND shardid = (
     SELECT get_shard_id_for_distribution_column('stores', 4)
   );

出力には、ワーカーデータベースのホストとポートが含まれます。

┌─────────┬────────────┬─────────────┬───────────┬──────────┬─────────────┐
│ shardid │ shardstate │ shardlength │ nodename  │ nodeport │ placementid │
├─────────┼────────────┼─────────────┼───────────┼──────────┼─────────────┤
│  102009 │          1 │           0 │ 10.0.0.16 │     5432 │           2 │
└─────────┴────────────┴─────────────┴───────────┴──────────┴─────────────┘

分散スキーマをホストするノードの検索

分散スキーマは、これらのスキーマで作成されたテーブルがシャードキーなしで併置された分散テーブルに変換されるように、個々のコロケーショングループに自動的に関連付けられます。分散スキーマが存在する場所は、citus_shards を citus_schemas と結合することで確認できます。

select schema_name, nodename, nodeport
  from citus_shards
  join citus_schemas cs
    on cs.colocation_id = citus_shards.colocation_id
 group by 1,2,3;

 schema_name | nodename  | nodeport
-------------+-----------+----------
 a           | localhost |     9701
 b           | localhost |     9702
 with_data   | localhost |     9702

直接 citus_shards のクエリを実行してスキーマテーブルの種類に絞り込んで、すべてのテーブルの詳細な一覧を表示することもできます。

select * from citus_shards where citus_table_type = 'schema';

   table_name   | shardid |      shard_name       | citus_table_type | colocation_id | nodename  | nodeport | shard_size | schema_name | colocation_id | schema_size | schema_owner
----------------+---------+-----------------------+------------------+---------------+-----------+----------+------------+-------------+---------------+-------------+--------------
 a.cities       |  102080 | a.cities_102080       | schema           |             4 | localhost |     9701 |       8192 | a           |             4 | 128 kB      | citus
 a.map_tags     |  102145 | a.map_tags_102145     | schema           |             4 | localhost |     9701 |      32768 | a           |             4 | 128 kB      | citus
 a.measurement  |  102047 | a.measurement_102047  | schema           |             4 | localhost |     9701 |          0 | a           |             4 | 128 kB      | citus
 a.my_table     |  102179 | a.my_table_102179     | schema           |             4 | localhost |     9701 |      16384 | a           |             4 | 128 kB      | citus
 a.people       |  102013 | a.people_102013       | schema           |             4 | localhost |     9701 |      32768 | a           |             4 | 128 kB      | citus
 a.test         |  102008 | a.test_102008         | schema           |             4 | localhost |     9701 |       8192 | a           |             4 | 128 kB      | citus
 a.widgets      |  102146 | a.widgets_102146      | schema           |             4 | localhost |     9701 |      32768 | a           |             4 | 128 kB      | citus
 b.test         |  102009 | b.test_102009         | schema           |             5 | localhost |     9702 |       8192 | b           |             5 | 32 kB       | citus
 b.test_col     |  102012 | b.test_col_102012     | schema           |             5 | localhost |     9702 |      24576 | b           |             5 | 32 kB       | citus
 with_data.test |  102180 | with_data.test_102180 | schema           |            11 | localhost |     9702 |     647168 | with_data   |            11 | 632 kB      | citus

テーブルのディストリビューション列の検索

すべての分散テーブルに、"ディストリビューション列" があります (詳細については、「分散データモデリング」を参照してください)。これがどの列であるかを把握しておくことが重要な場合があります。たとえば、テーブルを結合またはフィルター処理するときに、"ディストリビューション列にフィルターを追加してください" などのヒントを含むエラーメッセージが表示される場合があります。

コーディネーターノードの pg_dist_* テーブルには、分散データベースに関するさまざまなメタデータが含まれています。特に、pg_dist_partition には、各テーブルのディストリビューション列に関する情報が保持されています。便利なユーティリティ関数を使用すると、メタデータの下位レベルの詳細からディストリビューション列名を参照できます。例とその出力を次に示します。

-- create example table

CREATE TABLE products (
  store_id bigint,
  product_id bigint,
  name text,
  price money,

  CONSTRAINT products_pkey PRIMARY KEY (store_id, product_id)
);

-- pick store_id as distribution column

SELECT create_distributed_table('products', 'store_id');

-- get distribution column name for products table

SELECT column_to_column_name(logicalrelid, partkey) AS dist_col_name
  FROM pg_dist_partition
 WHERE logicalrelid='products'::regclass;

出力例:

┌───────────────┐
│ dist_col_name │
├───────────────┤
│ store_id      │
└───────────────┘

ロックの検出

このクエリはすべてのワーカーノードで実行され、ロックおよびそれらが開いている期間、および問題のあるクエリを識別します。

SELECT run_command_on_workers($cmd$
  SELECT array_agg(
    blocked_statement || ' $ ' || cur_stmt_blocking_proc
    || ' $ ' || cnt::text || ' $ ' || age
  )
  FROM (
    SELECT blocked_activity.query    AS blocked_statement,
           blocking_activity.query   AS cur_stmt_blocking_proc,
           count(*)                  AS cnt,
           age(now(), min(blocked_activity.query_start)) AS "age"
    FROM pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity
      ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks
      ON blocking_locks.locktype = blocked_locks.locktype
     AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
     AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
     AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
     AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
     AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
     AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
     AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
     AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
     AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
     AND blocking_locks.pid != blocked_locks.pid
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
    WHERE NOT blocked_locks.GRANTED
     AND blocking_locks.GRANTED
    GROUP BY blocked_activity.query,
             blocking_activity.query
    ORDER BY 4
  ) a
$cmd$);

出力例:

┌───────────────────────────────────────────────────────────────────────────────────┐
│                               run_command_on_workers                              │
├───────────────────────────────────────────────────────────────────────────────────┤
│ (10.0.0.16,5432,t,"")                                                             │
│ (10.0.0.20,5432,t,"{""update ads_102277 set name = 'new name' where id = 1; $ sel…│
│…ect * from ads_102277 where id = 1 for update; $ 1 $ 00:00:03.729519""}")         │
└───────────────────────────────────────────────────────────────────────────────────┘

シャードのサイズについてのクエリの実行

このクエリでは、my_distributed_table と呼ばれる、特定の分散テーブルのすべてのシャードのサイズが提供されます。

SELECT *
FROM run_command_on_shards('my_distributed_table', $cmd$
  SELECT json_build_object(
    'shard_name', '%1$s',
    'size',       pg_size_pretty(pg_table_size('%1$s'))
  );
$cmd$);

出力例:

┌─────────┬─────────┬───────────────────────────────────────────────────────────────────────┐
│ shardid │ success │                                result                                 │
├─────────┼─────────┼───────────────────────────────────────────────────────────────────────┤
│  102008 │ t       │ {"shard_name" : "my_distributed_table_102008", "size" : "2416 kB"}    │
│  102009 │ t       │ {"shard_name" : "my_distributed_table_102009", "size" : "3960 kB"}    │
│  102010 │ t       │ {"shard_name" : "my_distributed_table_102010", "size" : "1624 kB"}    │
│  102011 │ t       │ {"shard_name" : "my_distributed_table_102011", "size" : "4792 kB"}    │
└─────────┴─────────┴───────────────────────────────────────────────────────────────────────┘

すべての分散テーブルのサイズについてのクエリの実行

このクエリは、各分散テーブルのサイズとそれらのインデックスのサイズの一覧を取得します。

SELECT
  tablename,
  pg_size_pretty(
    citus_total_relation_size(tablename::text)
  ) AS total_size
FROM pg_tables pt
JOIN pg_dist_partition pp
  ON pt.tablename = pp.logicalrelid::text
WHERE schemaname = 'public';

出力例:

┌───────────────┬────────────┐
│   tablename   │ total_size │
├───────────────┼────────────┤
│ github_users  │ 39 MB      │
│ github_events │ 98 MB      │
└───────────────┴────────────┘

分散テーブルのサイズを問い合わせるための Azure Cosmos DB for PostgreSQL 関数が他にもあることに注意してください。「テーブルサイズの確認」を参照してください。

未使用のインデックスの識別

次のクエリでは、特定の分散テーブル (my_distributed_table) のワーカーノードでの未使用のインデックスを識別します。

SELECT *
FROM run_command_on_shards('my_distributed_table', $cmd$
  SELECT array_agg(a) as infos
  FROM (
    SELECT (
      schemaname || '.' || relname || '##' || indexrelname || '##'
                 || pg_size_pretty(pg_relation_size(i.indexrelid))::text
                 || '##' || idx_scan::text
    ) AS a
    FROM  pg_stat_user_indexes ui
    JOIN  pg_index i
    ON    ui.indexrelid = i.indexrelid
    WHERE NOT indisunique
    AND   idx_scan < 50
    AND   pg_relation_size(relid) > 5 * 8192
    AND   (schemaname || '.' || relname)::regclass = '%s'::regclass
    ORDER BY
      pg_relation_size(i.indexrelid) / NULLIF(idx_scan, 0) DESC nulls first,
      pg_relation_size(i.indexrelid) DESC
  ) sub
$cmd$);

出力例:

┌─────────┬─────────┬───────────────────────────────────────────────────────────────────────┐
│ shardid │ success │                            result                                     │
├─────────┼─────────┼───────────────────────────────────────────────────────────────────────┤
│  102008 │ t       │                                                                       │
│  102009 │ t       │ {"public.my_distributed_table_102009##some_index_102009##28 MB##0"}   │
│  102010 │ t       │                                                                       │
│  102011 │ t       │                                                                       │
└─────────┴─────────┴───────────────────────────────────────────────────────────────────────┘

クライアント接続数の監視

次のクエリでは、コーディネーターで開かれている接続をカウントし、種類別にグループ化します。

SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

出力例:

┌────────┬───────┐
│ state  │ count │
├────────┼───────┤
│ active │     3 │
│ idle   │     3 │
│ ∅      │     6 │
└────────┴───────┘

システムクエリの表示

アクティブなクエリ

pg_stat_activity ビューに、現在実行中のクエリが表示されます。フィルター処理を行って、アクティブに実行されているものを、バックエンドのプロセス ID と一緒に検索できます。

SELECT pid, query, state
  FROM pg_stat_activity
 WHERE state != 'idle';

クエリが待機している理由

また、クエリを実行して、待機している非アイドルクエリの最も一般的な理由を確認することもできます。これらの理由の説明については、PostgreSQL のドキュメントを参照してください。

SELECT wait_event || ':' || wait_event_type AS type, count(*) AS number_of_occurences
  FROM pg_stat_activity
 WHERE state != 'idle'
GROUP BY wait_event, wait_event_type
ORDER BY number_of_occurences DESC;

個別のクエリで同時に pg_sleep を実行する場合の出力例:

┌─────────────────┬──────────────────────┐
│      type       │ number_of_occurences │
├─────────────────┼──────────────────────┤
│ ∅               │                    1 │
│ PgSleep:Timeout │                    1 │
└─────────────────┴──────────────────────┘

インデックスのヒット率

このクエリでは、すべてのノードでのインデックスのヒット率が得られます。インデックスのヒット率は、クエリの実行時にインデックスを使用する頻度を決定するのに役立ちます。 95% 以上の値が最適です。

-- on coordinator
SELECT 100 * (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) AS index_hit_rate
  FROM pg_statio_user_indexes;

-- on workers
SELECT nodename, result as index_hit_rate
FROM run_command_on_workers($cmd$
  SELECT 100 * (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) AS index_hit_rate
    FROM pg_statio_user_indexes;
$cmd$);

出力例:

┌───────────┬────────────────┐
│ nodename  │ index_hit_rate │
├───────────┼────────────────┤
│ 10.0.0.16 │ 96.0           │
│ 10.0.0.20 │ 98.0           │
└───────────┴────────────────┘

キャッシュヒット率

通常、ほとんどのアプリケーションで一度にアクセスされるのは、その合計データのごく一部です。 PostgreSQL では、頻繁にアクセスされるデータがメモリに保持され、ディスクからの読み取り速度が低下するのを防ぎます。これに関する統計を pg_statio_user_tables ビューで確認できます。

重要な測定値は、ワークロードにおいてディスクから読み取られるデータに対する、メモリキャッシュから取得される割合です。

-- on coordinator
SELECT
  sum(heap_blks_read) AS heap_read,
  sum(heap_blks_hit)  AS heap_hit,
  100 * sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_rate
FROM
  pg_statio_user_tables;

-- on workers
SELECT nodename, result as cache_hit_rate
FROM run_command_on_workers($cmd$
  SELECT
    100 * sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_rate
  FROM
    pg_statio_user_tables;
$cmd$);

出力例:

┌───────────┬──────────┬─────────────────────┐
│ heap_read │ heap_hit │   cache_hit_rate    │
├───────────┼──────────┼─────────────────────┤
│         1 │      132 │ 99.2481203007518796 │
└───────────┴──────────┴─────────────────────┘

割合が 99% よりはるかに低い場合は、データベースで使用可能なキャッシュを増加させることを検討します。

次のステップ

診断に役立つその他のシステムテーブルについて説明します。

次の方法で共有

Azure Cosmos DB for PostgreSQL の有用な診断クエリ

特定のテナントのデータが含まれているノードの検索

分散スキーマをホストするノードの検索

テーブルのディストリビューション列の検索

ロックの検出

シャードのサイズについてのクエリの実行

すべての分散テーブルのサイズについてのクエリの実行

未使用のインデックスの識別

クライアント接続数の監視

システムクエリの表示

アクティブなクエリ

クエリが待機している理由

インデックスのヒット率

キャッシュヒット率

次のステップ

フィードバック

その他のリソース

次の方法で共有

Azure Cosmos DB for PostgreSQL の有用な診断クエリ

特定のテナントのデータが含まれているノードの検索

分散スキーマをホストするノードの検索

テーブルのディストリビューション列の検索

ロックの検出

シャードのサイズについてのクエリの実行

すべての分散テーブルのサイズについてのクエリの実行

未使用のインデックスの識別

クライアント接続数の監視

システム クエリの表示

アクティブなクエリ

クエリが待機している理由

インデックスのヒット率

キャッシュ ヒット率

次のステップ

フィードバック

その他のリソース

システムクエリの表示

キャッシュヒット率