数据库运行缓慢

该问题的典型症状如下：

• 磁盘输入/输出（I/O）或内存使用率过高
• 慢速查询

最常见的原因是正在检查慢查询。 请完成以下步骤以确定是否如此：

1. 列出长期运行的查询：

   select age(now(),query_start), pid, client_addr, state, substring(query,0,80) from pg_stat_activity where state != 'idle' order by 1 desc;
   

2. 验证查询阻塞：

   select pg_blocking_pids(pid), pid AS blocked_pid, state, query, age(now(),query_start) AS duration from pg_stat_activity where array_length(pg_blocking_pids(pid),1) > 0 order by 1,2;
   

3. 检查查询执行计划：

   Explain <query>;
   

4. 检查数据库统计信息是否缺失。 使用以下查询进行验证：操作 > 运行分析 或者，运行以下命令：

   Analyze <tablename>  select relname, n_live_tup, n_dead_tup, last_vacuum,last_autovacuum,last_analyze,last_autoanalyze, analyze_count,autoanalyze_count from pg_stat_all_tables where schemaname='public';
   

5. 检查表中是否缺少索引。 PostgreSQL 提供索引方法，例如B树、哈希、SP-GiST,GiST, GIN和BRIN。 使用其中一种方法创建索引。

6. 检查表是否膨胀。 运行以下查询进行验证。 操作 > 运行真空

   或者，使用以下语句：

   SELECT
   relname AS table_name,
   pg_size_pretty(pg_relation_size(c.oid)) AS actual_size,
   pg_size_pretty(
   CASE
   WHEN c.relpages = 0 THEN 0
   ELSE (pg_relation_size(c.oid) - (c.relpages *
   (current_setting('block_size')::numeric - 24))) * 100 / pg_relation_size(c.oid)
   END
   ) AS bloat_percentage, pg_size_pretty(
   CASE
   WHEN c.relpages = 0 THEN 0
   ELSE (pg_relation_size(c.oid) - (c.relpages *
   (current_setting('block_size')::numeric - 24)))
   END
   )AS bloat_size
   FROM pg_class c
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
   WHERE relkind = 'r' AND n.nspname = 'public' -- Adjust schema if needed
   ORDER BY bloat_size DESC NULLS LAST;
   

7. 请考虑数据库或对象是否已使用 pg_dump 或 创建 pg_restore。 使用操作 > 更新统计数据进行刷新。

8. 确保分配 work_mem 足够的内存，如果查询正在执行排序操作。

9. 使用 pg_stat 语句来获取以下信息：

   • 查询平均执行时间和调用次数

   • CPU百分比使用率由查询产生

   • 查询占用的内存百分比

pg_stat_activity, pg_stat_bgwriter，和 pg_buffercache 是 PostgreSQL 用于监控和理解数据库性能的重要工具。

部署监控与数据库负载监控

Databases for PostgreSQL 部署提供与 IBM Cloud Monitoring 服务的集成，用于监控您部署中的资源使用情况。 使用此服务监控您的部署（磁盘和内存）以及数据库负载。

使用 Cloud Databases 仪表板设置CPU、内存和磁盘IOPS阈值的警报。 许多可用的指标（如磁盘使用率和IOPS）有助于您为部署配置 自动缩放功能。 自动缩放功能默认未启用，因此您必须手动进行配置。

通过观察使用趋势并配置自动扩展功能以响应这些趋势，可在数据库因资源耗尽而变得不稳定之前缓解性能问题。 例如，磁盘I/O、CPU或内存故障的变化导致性能问题。

磁盘 IOPS

每秒输入/输出操作数（IOPS）受存储卷的类型和大小限制。 部署 Databases for PostgreSQL 的存储卷在 Block Storage 持久卷 上进行配置，采用每GB 10 IOPS的性能层级。

若您的操作负载达到或超过IOPS限制，数据库请求和操作将被延迟，直至存储子系统能够跟上处理进度。 长时间的高负载可能导致部署无法处理查询，从而实质上无法使用。 您可以通过增加磁盘容量来提升部署环境的可用IOPS数量。 例如，对于每GB层级10 IOPS的情况，您可以通过增加卷大小来提升IOPS。

即使磁盘利用率在40-50%的状态下持续较长时间，也会对数据库性能产生显著的负面影响。 为生产环境分配至少100 GB磁盘空间（1,000 IOPS）。 IOPS = 10 × 分配的GB（例如，100 GB = 1,000 IOPS）

内存使用率

内存是访问和处理数据最快捷、最高效的方式。 因此，数据库使用内存处理数据时，其运行速度几乎总是快于从磁盘读取数据。 还有其他指标需要关注和考虑，例如缓存命中率、读取的块数以及命中的块数。 仅看到100%的内存使用率本身并不值得担忧。 若内存耗尽并启用交换页，性能可能显著下降。

您可以在配置文件 Databases for PostgreSQL 中调整 shared_buffers 参数，来设定分配给数据库共享缓冲池的内存大小。 建议的最大值为部署总内存的25%。 为共享缓冲池分配过多内存会导致系统其他用途的内存不足，可能影响性能，甚至可能导致数据库无法运行。

数据库负载监控

检查数据库负载有两种方式：

{

source logs|filter message.attr.durationMillis>=1000

}

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT username, database, queryid, query_preview, calls, total_exec_time, pct_exec_time, ROUND(SUM(pct_exec_time) OVER (ORDER BY total_exec_time DESC)) AS cum_pct_exec_time, avg_exec_time FROM ( SELECT pu.usename AS username, pd.datname AS database, pss.queryid, LEFT(pss.query, 50) AS query_preview, pss.calls, ROUND(pss.total_exec_time::numeric, 3) AS total_exec_time, ROUND((100.0 * pss.total_exec_time / SUM(pss.total_exec_time) OVER ())::numeric, 2) AS pct_exec_time, ROUND(pss.mean_exec_time::numeric, 3) AS avg_exec_time FROM pg_stat_statements pss JOIN pg_user pu ON pss.userid = pu.usesysid JOIN pg_database pd ON pss.dbid = pd.oid ) AS subquery ORDER BY total_exec_time DESC LIMIT 25;

SELECT
   username,
   database,
   queryid,
   query_preview,
   calls,
   pct_calls,
   ROUND(SUM(pct_calls) OVER (ORDER BY calls DESC)) AS cum_pct_calls,
   total_exec_time,
   avg_exec_time
FROM (
   SELECT
      pu.usename AS username,
      pd.datname AS database,
      pss.queryid,
      LEFT(pss.query, 50) AS query_preview,
      pss.calls,
      ROUND(100.0 * pss.calls / SUM(pss.calls) OVER (), 2) AS pct_calls,
      ROUND(pss.total_exec_time::numeric, 3) AS total_exec_time,
      ROUND(pss.mean_exec_time::numeric, 3) AS avg_exec_time
   FROM
      pg_stat_statements pss
      JOIN pg_user pu ON pss.userid = pu.usesysid
      JOIN pg_database pd ON pss.dbid = pd.oid
) AS subquery
ORDER BY
   calls DESC
LIMIT 25;

SELECT query, calls, total_exec_time/calls as avg_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

您可采取的进一步措施以提升性能

您还可以考虑采取以下措施来排查性能问题：

• 优化慢查询

  • 对运行缓慢的查询执行EXPLAIN (ANALYZE, BUFFERS)。 这显示了数据库是如何运行查询的，以及在哪些地方运行缓慢。 例如：

  EXPLAIN (ANALYZE, BUFFERS)
  SELECT * FROM student WHERE student_id = 12345;
  

  • 寻找：

    • 占用大量内存或磁盘空间的查询

    • 根据需要添加 IBM Cloud 资源

    • 扩展磁盘/内存以获得更高的IOPS和内存容量

• 缺失或低效的索引是导致查询缓慢的常见原因。 使用EXPLAIN来识别顺序扫描，并考虑添加索引。

• 运行 VACUUM 命令以协助数据库健康分析。

• 考虑使用连接池来处理更多连接。Databases for PostgreSQL 将数据库 Databases for PostgreSQL 的最大连接数设置为 115。 其中15个连接预留给超级用户用于维护数据库状态和完整性，其余100个连接可供您和您的应用程序使用。 达到连接限制后，任何尝试建立新连接的行为都会导致错误。 为避免部署因连接过载而崩溃，请使用连接池技术，或扩展部署规模并提高其连接限制。 有关更多信息，请参阅 管理 PostgreSQL 连接池。

• 检查是否有任何程序正在执行备份和批量上传数据，例如： 自动备份每日完成，并采用30天的简单保留周期进行存储。 如果备份卡住，您可以在数据库实例的云管理界面中查看“可用备份”部分，从而识别出卡住的备份。

• 请查看您的 IBM Cloud 通知以了解任何维护信息。 例如，数据库修补。

• 若您认为这是平台问题（例如维护），请联系 IBM 支持团队并提供数据库CRN。