慢查询问题

来自 PostgreSQL 维基
(重定向自 SlowQueryQuestions)
跳转至导航跳转至搜索


询问慢查询问题的指南

在任何给定的周中，#postgresql IRC 上约 50% 的问题以及 pgsql-performance 上 75% 的问题都要求帮助解决慢查询问题。 但是，请求者很少会提供有关其慢查询的完整信息，这让他们自己以及试图帮助他们的人感到沮丧。

请将与性能相关的问题发布到 pgsql-performance 邮件列表或 IRC 频道。

注意：如果在 IRC 上寻求帮助，请将相关信息发布到像 https://paste.depesz.com/ 这样的粘贴网站上，而不是直接发布到 IRC 上。

另请参阅：报告问题的指南

在发布之前尝试的事项

如果你在发布问题之前尝试以下事项，可以节省很多时间。

  • 如果你还没有阅读过，请阅读 使用 EXPLAIN。
  • ANALYZE 相关表以确保统计数据是最新的；
    • 收集有关表大小（ntuples、npages）和列值（nullfrac、ndistinct、MCVs 和直方图）的更新统计数据。
    • 请注意，由于其子表的变化，autovacuum 目前无法处理父表的分析。 在其子表内容发生重大变化后，应手动 ANALYZE 父表，例如在 DROP 或 ALTER..DETACH/ATTACH/UN/INHERIT 之后，或批量 DELETE 或 UPDATE 或许多 INSERT 之后。
  • VACUUM 相关表以避免膨胀并设置 relallvisible；
  • REINDEX 相关索引 - 解决死索引元组、膨胀，新索引将按堆 TID 的顺序包含条目；请注意，这将阻塞查询（除非你在 v12+ 中使用 REINDEX CONCURRENTLY；另请参阅 pg_repack）
  • 检查你的主要 GUC 设置，确保它们被设置为合理的值（有关其他提示，请参阅 调整你的 PostgreSQL 服务器）
    • shared_buffers 应为可用 RAM 的 10% 到 25%
    • effective_cache_size 应为可用 RAM 的 75%
  • 测试是否可以用更小的查询或不同的查询参数重现问题；
  • 测试更改 work_mem：将其增加到 8MB、32MB、256MB、1GB。这有影响吗？
  • 对于 Insert/Update/Delete 查询，你还应该尝试配置你的 WAL
    • 如果可能，将 pg_wal 移到单独的存储设备上
    • 增加 max_wal_size 到 2GB 或更大（假设你有磁盘空间）

需要包含的信息

请计划花费约 30 分钟时间收集所需的信息并准备你的问题。成千上万的人会看到它，但如果你没有让别人很容易帮助你，他们可能不会回复。如果写得好，并提供所需的信息，大多数请求都会收到有帮助的回复。甚至有些会导致错误修复或其他补丁。但如果你没有付出很大的努力来提供尽可能多的相关信息，你就很难从社区获得很多免费帮助。

Postgres 版本

请提供你使用的确切服务器版本（SELECT version(); 是一个获取它的简单方法）。规划器的行为在每个版本中都会发生变化，所以这一点很重要。

操作系统+版本

什么操作系统/版本？ 至少对于 linux，你可以通过运行以下命令来获取发行版：tail /etc/*release

完整的表和索引模式

发布查询中引用的所有表和索引的定义。如果查询涉及视图或自定义函数，我们也需要这些定义。 运行 psql 命令 "\d table"，其中 table 是问题查询中引用的表/视图/索引。

表元数据

除了表定义之外，请还发布表中的近似行数。

  • SELECT relname, relpages, reltuples, relallvisible, relkind, relnatts, relhassubclass, reloptions, pg_table_size(oid) FROM pg_class WHERE relname='TABLE_NAME';

该表是否有任何不寻常之处？

  • 包含大对象
  • 在多个列中具有高比例的 NULL 值
  • 经常收到大量 UPDATE 或 DELETE 操作
  • 正在快速增长
  • 拥有许多索引
  • 使用可能正在执行数据库函数的触发器，或直接调用函数

EXPLAIN (ANALYZE, BUFFERS)，不仅仅是 EXPLAIN

EXPLAIN (ANALYZE, BUFFERS, SETTINGS) 告诉我们查询是如何实际执行的，而不仅仅是它是如何被规划的。这是找出规划器是否错误（如果有的话）的重要信息。 例如：EXPLAIN (ANALYZE, BUFFERS) select * from tablename; 如果你无法运行 EXPLAIN (ANALYZE, BUFFERS) 因为查询从未完成，那就说明这一点。

  • 如果你的邮件客户端可能弄乱格式，最好将输出作为单独的文件附加。（否则，人们可能会因为需要在阅读前重新格式化邮件而却步于帮助你）。
  • 或者，将你的 EXPLAIN (ANALYZE, BUFFERS, SETTINGS) 结果粘贴到 explain.depesz.com 并发布到结果页面的链接。我们非常喜欢这样，因为它使计划更容易阅读和检查。
  • 在执行 EXPLAIN 之前打开 track_io_timing。如果你以超级用户身份登录，可以通过执行 SET track_io_timing = on; 为当前会话打开 track_io_timing。
  • 如果使用的是 PostgreSQL v11 或更早版本，SETTINGS 不受支持，你应该还提供所有非默认设置的列表。请参阅 服务器配置。

历史

这个查询一直都很慢，还是最近才变得更慢？如果查询的计划/执行以前有所不同，你有那些查询计划的副本吗？除了数据积累之外，你的数据库是否发生了任何变化？

硬件

请发布有关你的硬件平台的基本信息。如果你的硬件的任何部分都不寻常，请提供有关这些部分的详细信息。请参阅 报告问题的指南，了解哪些硬件信息是有用的。

硬件基准测试

  • 运行 bonnie++ 或其他驱动器速度测试，看看你的性能问题是否仅仅是基于硬件的。例如，无论你如何调整查询，RAID-5 配置都永远不会有快速的插入/更新操作。
    • bonnie++ -f -n0 -x4 -d /var/lib/pgsql
  • 测试存储 postgres 数据文件的设备的顺序读取速度，运行多次。如果你为 WAL 或 tmp 或多个表空间使用单独的驱动器，请为每个驱动器运行多次。128*$RANDOM/32 是 128GB，如果你有超过 ~64GB 的 RAM，则需要相应地增加。32K 是 32GB，如果你的驱动器非常快（或非常慢），则需要增加。如果你的数据库非常小，这可能不是一个好的测试（但在这种情况下，驱动器速度可能并不重要）。
    • time dd if=/dev/sdX2 of=/dev/null bs=1M count=32K skip=$((128*$RANDOM/32))

维护设置

你是否运行 autovacuum？如果是，设置是什么？如果不是，你是否正在进行手动 VACUUM 和/或 ANALYZE？频率如何？ SELECT * FROM pg_stat_user_tables WHERE relname='table_name';

WAL 配置

对于数据写入查询：你是否将 WAL 移到不同的磁盘？更改了设置？

GUC 设置

你更改了哪些数据库配置设置？它们的值是什么？（这些设置包括 "shared_buffers"、"work_mem"、"enable_seq_scan"、"effective_io_concurrency"、"effective_cache_size" 等）。请参阅 服务器配置，了解一个有用的查询，该查询将以比发布你的 postgresql.conf 文件的部分内容更容易阅读的格式显示你所有非默认数据库设置。

统计：n_distinct, MCV, 直方图

对检查导致错误连接计划的统计数据很有用。 SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, tablename, attname, inherited, null_frac, n_distinct, array_length(most_common_vals,1) n_mcv, array_length(histogram_bounds,1) n_hist, correlation FROM pg_stats WHERE attname='...' AND tablename='...' ORDER BY 1 DESC;

启用日志记录

一些建议的初始值

  • log_min_duration_statement = '2222ms'
  • log_autovacuum_min_duration = '9s'
  • log_checkpoints = on
  • log_lock_waits = on
  • log_temp_files = 0
  • log_destination = 'stderr,csvlog'
  • log_rotation_age = '2min'
  • log_rotation_size = '32MB'
  • logging_collector = on
Retrieved from "https://wiki.postgresql.ac.cn/index.php?title=Slow_Query_Questions&oldid=37712"