优化实例中的高内存用量

本文档介绍了如何识别 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 值,并且具有大量活跃连接。因此,如果您频繁遇到 OOM 或者如需避免 Cloud SQL for PostgreSQL 实例中出现 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 设置为较高的值,请考虑减小 shared_buffers 值,以便内存可用于其他操作(例如 work_mem)或是用于建立新连接。

    缓存命中率

    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。请考虑将 max_locks_per_transaction 的值增加得足够高,以避免 OOM。

    max_locks_per_transaction 值应为 max_locks_per_transaction * (max_connections + max_prepared_transactions) 个对象。这意味着,如果您有 30 万个对象,并且如果 max_connections 的值为 200,则 max_locks_per_transaction 应为 1500。

  • 设置 max_pred_locks_per_transaction

    如果您的客户端在单个可序列化事务中处理许多不同的表,则事务可能会失败。在这种情况下,请考虑将 max_pred_locks_per_transaction 增加到合理的较高值。和 max_locks_per_transaction 一样,max_pred_locks_per_transaction 也会使用共享内存,因此请勿设置不合理的较高值。

  • 如果内存用量仍然较高,并且您认为这些查询是合法流量,请考虑增加实例中的内存资源数量,以避免数据库崩溃或停机。