インスタンスの高いメモリ使用量の最適化

このドキュメントでは、Cloud SQL インスタンスの高いメモリ使用量を特定する方法について説明し、メモリ関連の問題を解決する方法についての推奨事項を提供します。

Cloud SQL インスタンスのメモリ使用量を構成する方法については、メモリ使用量を管理するためのベストプラクティスをご覧ください。

高いメモリ使用量を特定する

Metrics Explorer を使用してメモリ使用量を確認する

インスタンスのメモリ使用量は、Metrics Explorer の database/memory/components.usage 指標で確認できます。

Query Insights を使用して、リソースを大量に消費しているクエリの説明プランを分析する

Query Insights では、Cloud SQL データベースに対するクエリパフォーマンスの問題を検出、診断、防止できます。Query Insights には、長時間実行クエリのリストと説明プラン（PostgreSQL ドキュメント）が表示されます。説明プランを確認し、クエリの中でメモリ使用量のスキャン方法が高い部分を特定します。クエリの実行時間にかかわらず、Query Insights では、すべてのクエリの説明プランが提供されます。時間がかかっている複雑なクエリを特定することで、どのクエリが長時間メモリをブロックしているかを把握できます。

多くのメモリを使用する一般的な PostgreSQL スキャン方式には、次のものがあります。

ビットマップヒープスキャン
クイックソート
ハッシュ結合またはハッシュ

Gemini 対応インスタンスの高いメモリ使用量と関連するログ

Gemini を有効にすると、メモリ不足（OOM）エラーが発生してデータベースのダウンタイムが発生する代わりに、メモリ使用量が高いクエリを実行している接続が終了し、データベースのダウンタイムを回避できます。デフォルト値が設定されたクエリを特定するには、データベースログで次のエントリを確認します。

  (...timestamp….) db=postgres, user=customer FATAL: terminating connection due to administrator command

次の Cloud SQL for PostgreSQL データベースログが表示されます。これは、OOM を防ぐために終了したメモリ使用量が高いクエリをキャプチャしたものです。このクエリは、元のクエリの正規化されたバージョンです。

  db=postgres,user=customer LOG:  postgres process with PID 1734 for the query "SELECT COUNT(product) AS item_count FROM test_table WHERE product_type = $1 AND product LIKE $2 AND c6_2 IN ($3,$4,$5,$6,$7)" has been cancelled.

次のイベントが発生した場合、[Cloud SQL インスタンス] ページにも通知が表示されます。

過去 24 時間にわたるインスタンスのメモリ使用率。
過去 24 時間以内にキャンセルされた正規化されたクエリのリスト。
メモリ使用量の最適化に関する Google ドキュメントへのリンク。

高いメモリ使用量 - 推奨事項

メモリ関連の一般的な問題への推奨対処方法は次のとおりです。インスタンスが大量のメモリを使い続けると、最終的に out of memory の問題が発生する可能性が高くなります。PostgreSQL または他のプロセスでのメモリ需要によりシステムのメモリが不足すると、PostgreSQL ログではカーネルメッセージの Out of Memory が確認され、PostgreSQL インスタンスは最終的に停止します。次に例を示します。

Out of Memory: Killed process 12345 (postgres)

OOM の問題が最もよく発生するのは、work_mem の値が高く、アクティブな接続数が多いインスタンスです。したがって、Cloud SQL for PostgreSQL インスタンスで、頻繁に OOM が発生する場合や、OOM を避ける場合は、次の推奨事項を検討してください。

work_mem を設定する

クイックソートを使用するクエリは、外部マージソートを使用するクエリよりも高速です。ただし、前者はメモリの枯渇につながる可能性があります。この問題を解決するには、メモリとディスクで発生するソートオペレーションのバランスが取れるように、work_mem の値を妥当な値に設定します。インスタンス全体ではなく、セッションレベルで work_mem を設定することもできます。
アクティブなセッションをモニタリングする

各接続では、一定量のメモリが使用されます。次のクエリを使用して、アクティブな接続の数を確認します。
```
  SELECT
    state,
    usename,
    count(1)
  FROM
    pg_stat_activity
  WHERE
    pid <> pg_backend_pid()
  GROUP BY
    state,
    usename
  ORDER BY
    1;
```
アクティブなセッションが多数ある場合は、アクティブなセッションの数が多くなっている根本原因（トランザクションのロックなど）を分析します。
shared_buffers を設定する

shared_buffers が大きい値に設定されている場合は、他のオペレーション（work_mem など）や、新しい接続の確立にメモリが使用できるように、shared_buffers の値を減らすことを検討してください。

キャッシュヒット率

通常、PostgreSQL は、アクセス頻度の高いデータをキャッシュに保持しようとします。クライアントがデータをリクエストしたときに、すでに共有バッファにキャッシュ保存されている場合は、それが直接クライアントに渡されます。これを「キャッシュヒット」と呼びます。データが共有バッファにない場合、データはまずディスクから共有バッファに取得されてから、クライアントに渡されます。これは、「キャッシュミス」と呼ばれます。キャッシュヒット率は、受信したリクエストに対するキャッシュが処理したコンテンツリクエストの数で求めます。次のクエリを実行して、PostgreSQL インスタンスのテーブルリクエストのキャッシュヒット率を確認します。
```
SELECT
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit)  as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM
  pg_statio_user_tables;
```
次のクエリを実行して、PostgreSQL インスタンスのインデックスリクエストのキャッシュヒット率を確認します。
```
  SELECT
    sum(idx_blks_read) as idx_read,
    sum(idx_blks_hit)  as idx_hit,
    (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
  FROM
    pg_statio_user_indexes;
```
一般的には、95～99% のキャッシュヒット率が適切な値と見なされます。
大規模なページを有効にする場合、Cloud SQL for PostgreSQL では、メモリ管理の改善を目的として、デフォルトで huge_pages が有効になっています。有効にすることをおすすめします。huge_pages の詳細については、PostreSQL のドキュメントをご覧ください。
max_locks_per_transaction を設定する

max_locks_per_transaction 値は、同時にロックできるデータベースオブジェクトの数を示します。ほとんどの場合、デフォルト値の 64 で十分です。ただし、大規模なデータセットを処理する場合は、最終的に OOM が発生する可能性があります。OOM を回避するには、max_locks_per_transaction の値を大きくすることを検討してください。

注: 進行中のトランザクションでは、オブジェクトレベルのロックがメモリ内で発生します。max_prepared_transactions の値を不必要に大きくすると、データベースインスタンスによって共有メモリがさらにリクエストされる可能性があります。
max_locks_per_transaction 値は、max_locks_per_transaction ×（max_connections + max_prepared_transactions）個のオブジェクトにする必要があります。つまり、300,000 個のオブジェクトがあり、max_connections の値が 200 の場合、max_locks_per_transaction は 1,500 にする必要があります。
max_pred_locks_per_transaction を設定する

1 つのシリアル化可能なトランザクションで、多くの異なるテーブルを扱う複数のクライアントが存在する場合、トランザクションが失敗する可能性があります。そのシナリオでは、max_pred_locks_per_transaction を適度に大きい値に引き上げることを検討してください。max_locks_per_transaction と同様に、max_pred_locks_per_transaction も共有メモリを使用するため、合理的な理由なしに高い値を設定しないでください。
メモリ使用量は依然として大きいが、それらのクエリが妥当なトラフィックであると思われる場合は、データベース障害やダウンタイムを回避するために、インスタンス内のメモリリソースの数を増やすことを検討してください。