慢查询问题

来自 PostgreSQL Wiki
跳转到导航跳转到搜索


提问慢查询问题的指南

在任何一周，#postgresql IRC 上大约 50% 的问题，以及 pgsql-performance 上 75% 的问题，都是关于慢查询的帮助请求。但是，请求者很少提供关于其慢查询的完整信息，这让他们以及试图提供帮助的人都很沮丧。

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

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

另请参见：报告问题指南

在您发布之前要尝试的事情

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

  • 如果您还没有阅读过，请阅读 使用 EXPLAIN。
  • ANALYZE 相关表以确保统计数据是最新的；
    • 收集关于表大小（ntuples，npages）和列值（nullfrac，ndistinct，MCV 和直方图）的更新统计信息。
    • 请注意，由于其子表的变化，autovacuum 目前不会处理对父表的分析。在子表的內容发生重大变化后，例如在 DROP 或 ALTER..DETACH/ATTACH/UN/INHERIT 之后，或者在批量 DELETE 或 UPDATE 或者许多 INSERT 之后，应该手动 ANALYZE 父表。
  • 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，如果您的 RAM 超过约 64GB，则需要按比例增加。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
检索自 "https://wiki.postgresql.ac.cn/index.php?title=Slow_Query_Questions&oldid=37712"