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

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

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

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

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

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

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

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

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

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

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

メモリ関連の一般的な問題への推奨対処方法は次のとおりです。インスタンスが大量のメモリを使い続けると、最終的に 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_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 も共有メモリを使用するため、合理的な理由なしに高い値を設定しないでください。

  • メモリ使用量は依然として大きいが、それらのクエリが妥当なトラフィックであると思われる場合は、データベース障害やダウンタイムを回避するために、インスタンス内のメモリリソースの数を増やすことを検討してください。