我有两个来自 Sidekiq 的作业似乎需要很长时间才能完成。看起来是相同的整体流程，但有两个作业显示为 RUNNING 状态。这些作业已经运行了 6 个小时，当我尝试对其中第一个查询运行 EXPLAIN ANALYZE 时，PostgreSQL 在 CLI 上卡住了。
有什么想法可能导致这些查询执行时间如此之长吗？
随意终止这些查询，让系统稍后重试。
5 个赞
我之前其实已经做过这件事了。我杀掉了那个任务，以便让重新索引完成，所以这是它在之后重新启动的情况。
看起来今天早上任务在夜间某个时间完成了，现在又开始运行。当前状态如下：
在我们的实例上，这个子查询返回大约 13,000 行：
SELECT ids.user_id, q.post_id, p3.created_at granted_at
FROM
(
SELECT p1.user_id, MIN(q1.id) id
FROM quoted_posts q1
JOIN badge_posts p1 ON p1.id = q1.post_id
JOIN badge_posts p2 ON p2.id = q1.quoted_post_id
WHERE (TRUE OR ( p1.id IN (-1) ))
GROUP BY p1.user_id
) ids
JOIN quoted_posts q ON q.id = ids.id
JOIN badge_posts p3 ON q.post_id = p3.id
然后它被 LEFT JOIN 到拥有 84,000 行的 user_badges 表。看起来最后一个条件 WHERE ub.badge_id = 15 AND q.user_id IS NULL 中的某个部分导致这个查询性能崩溃。如果我去掉 WHERE 子句，它可以在合理的时间（大约 20 秒）内执行；但如果我加上甚至只是 WHERE ub.badge_id = 15，我甚至无法在合理时间内让 EXPLAIN 对这个查询执行。EXPLAIN 已经挂起了好几分钟，没有任何结果。完整查询的实际执行已经运行了数小时。我们有什么办法可以优化这个查询吗？
2 个赞
从昨晚开始在这里的 Meta 上阅读相关主题，似乎没有任何任务应该运行超过 8 小时，尤其是对于大型数据库而言。
但我不确定我们还能做些什么来改善这一状况。
令人难以置信的是，我们甚至无法通过 EXPLAIN 获取任何信息，因为它会挂起。
1 个赞
查看此文件：discourse/app/services/badge_granter.rb at main · discourse/discourse · GitHub
其中包含以下代码，它执行了一个当前卡住的查询。如果我将第一个连接从 LEFT JOIN 改为 INNER JOIN，查询会立即执行。请问这里必须使用左连接的原因是什么？
sql = <<~SQL
DELETE FROM user_badges
WHERE id IN (
SELECT ub.id
FROM user_badges ub
LEFT JOIN (
#{badge.query}
) q ON q.user_id = ub.user_id
#{post_clause}
WHERE ub.badge_id = :id AND q.user_id IS NULL
)
SQL
1 个赞
有没有办法让这些徽章查询更快一些？
另一个看起来有问题的查询，我想这是来自每周清理任务：
UPDATE posts
SET percent_rank = X.percent_rank
FROM (
SELECT posts.id, Y.percent_rank
FROM posts
JOIN (
SELECT id, percent_rank()
OVER (PARTITION BY topic_id ORDER BY SCORE DESC) as percent_rank
FROM posts
) Y ON Y.id = posts.id
JOIN topics ON posts.topic_id = topics.id
WHERE (posts.percent_rank IS NULL OR Y.percent_rank <> posts.percent_rank)
LIMIT 20000
) AS X
WHERE posts.id = X.id
其执行计划显示，它试图对 posts 表中的所有 2600 万行进行排序。我无法确定该查询将采用何种方法，但鉴于当前等待事件是“DataFileRead”，我认为它正在从磁盘读取某些数据……
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Update on posts (cost=8312704.61..8627308.35 rows=20000 width=825)
-> Nested Loop (cost=8312704.61..8627308.35 rows=20000 width=825)
-> Subquery Scan on x (cost=8312704.18..8464468.35 rows=20000 width=48)
-> Limit (cost=8312704.18..8464268.35 rows=20000 width=12)
-> Hash Join (cost=8312704.18..209445240.14 rows=26540908 width=12)
Hash Cond: (posts_1.topic_id = topics.id)
-> Nested Loop (cost=8277347.60..209340213.36 rows=26540908 width=16)
-> WindowAgg (cost=8277347.16..8809352.84 rows=26600284 width=24)
-> Sort (cost=8277347.16..8343847.87 rows=26600284 width=16)
Sort Key: posts_2.topic_id, posts_2.score DESC
-> Seq Scan on posts posts_2 (cost=0.00..4542277.84 rows=26600284 width=16)
-> Index Scan using posts_pkey on posts posts_1 (cost=0.44..7.52 rows=1 width=16)
Index Cond: (id = posts_2.id)
Filter: ((percent_rank IS NULL) OR ((percent_rank() OVER (?)) <> percent_rank))
-> Hash (cost=23871.05..23871.05 rows=918842 width=4)
-> Index Only Scan using topics_pkey on topics (cost=0.42..23871.05 rows=918842 width=4)
-> Index Scan using posts_pkey on posts (cost=0.44..8.14 rows=1 width=781)
Index Cond: (id = x.id)
JIT:
Functions: 24
Options: Inlining true, Optimization true, Expressions true, Deforming true
(21 rows)
我强烈感觉你在这里是在逆流而上，你正在使用的数据库根本没有足够的资源来运行 Discourse。
- 物理服务器的确切规格是什么（CPU / 硬盘的品牌和型号）？
- 虚拟机的确切规格是什么？
这些查询确实开销很大，但我们托管了许多大型论坛（例如：https://boards.straightdope.com/about，约 2200 万篇帖子），并且我们能够在该实例上顺利运行所有这些查询。
1 个赞
这是一台专用服务器，配置如下：
AMD Ryzen 7 3800X
64 GB ECC RAM @ 2666 Mhz
2 x 1.2 TB Intel P3600 NVMe SSD (ZFS RAID 1)
运行 Discourse 的虚拟机已分配 8 个 CPU 核心和 32 GB 内存。
我相信我已经发现了第一个查询的问题，或者至少找到了一种方法可以指导查询规划器做出正确的决策。以下是那个耗时超过 16 小时都无法完成的查询（这是针对“首次引用”徽章的）：
SELECT ub.id \n FROM user_badges ub \n LEFT JOIN ( \n SELECT ids.user_id, q.post_id, p3.created_at granted_at \n FROM \n ( \n SELECT p1.user_id, MIN(q1.id) id \n FROM quoted_posts q1 \n JOIN badge_posts p1 ON p1.id = q1.post_id \n JOIN badge_posts p2 ON p2.id = q1.quoted_post_id \n WHERE (TRUE OR ( p1.id IN (-1) )) \n GROUP BY p1.user_id \n ) ids \n JOIN quoted_posts q ON q.id = ids.id \n JOIN badge_posts p3 ON q.post_id = p3.id \n \n ) q ON q.user_id = ub.user_id \n AND (q.post_id = ub.post_id OR NOT TRUE) \n WHERE ub.badge_id = 15 AND q.user_id IS NULL
如果我在正确的位置添加一行 ORDER BY，这个查询现在只需几秒钟就能完成：
SELECT ub.id \n FROM user_badges ub \n LEFT JOIN ( \n SELECT ids.user_id, q.post_id, p3.created_at granted_at \n FROM \n ( \n SELECT p1.user_id, MIN(q1.id) id \n FROM quoted_posts q1 \n JOIN badge_posts p1 ON p1.id = q1.post_id \n JOIN badge_posts p2 ON p2.id = q1.quoted_post_id \n WHERE (TRUE OR ( p1.id IN (-1) )) \n GROUP BY p1.user_id \n ) ids \n JOIN quoted_posts q ON q.id = ids.id \n JOIN badge_posts p3 ON q.post_id = p3.id\n ORDER BY ids.user_id \n \n ) q ON q.user_id = ub.user_id \n AND (q.post_id = ub.post_id OR NOT TRUE) \n WHERE ub.badge_id = 15 AND q.user_id IS NULL
我觉得它应该足够智能，能在正确的位置自动执行这种排序，但看来它并没有做到……不过，目前的修复方案似乎相当简单。
我还没有开始深入研究另一个关于 percent_rank 的查询。
2 个赞
有时统计信息不佳会导致计划失效……在极少数情况下，执行完整真空（VACUUM）会有帮助；导入后强烈建议执行最小真空。我认为您已经做了这两步。
请问您是在虚拟机中运行，还是直接在主机上使用 Docker？有什么具体原因吗？
1 个赞
如果方便的话，请问 Straight Dope 投入了哪些资源？它是否有像我们那样需要耗时数小时的工作任务（我们目前处理的是 2700 万篇帖子）？
是的，我已经运行过几次 VACUUM ANALYZE。统计信息应该是正确的，但即使在多次重建、Postgres 调优调整和 VACUUM 操作后，查询计划似乎仍然选择不佳。
这台宿主机上还在运行其他虚拟机，但目前我们有足够的空闲资源，因此我选择在这里搭建系统来测试 Discourse。
从我们的大型实例查看：/sidekiq/scheduler
以及
您执行的是 FULL VACUUM 吗？
我们的数据库服务器硬件性能大致与您的相当（不过由于我们拥有更大的 RAID 阵列，我们的 IO 速度更快）。但我们完全没有采用虚拟化部署。这是一个很大的区别。
2 个赞
还没有。我可以试一下，看看行为是否有变化。
我相信在虚拟机中运行会带来一些性能损失，但并没有任何进程对硬件造成严重占用。当我执行导入操作，将其他软件的所有数据迁移过来时，通过同时运行多个导入进程，我能够利用全部 8 个核心达到 60-70% 的使用率。
而现在这些任务处于空闲和轮询状态时，我通常看到的负载平均值不超过 2-3，说明它们甚至没有充分利用所有可用的 CPU 资源。
2 个赞
完全真空是我在大规模迁移后见过有帮助的操作，很想知道它是否会产生影响。
3 个赞
它目前正在运行中。
2 个赞
关于看起来属于每周任务的 percent_rank 查询，您的大型实例的 EXPLAIN 输出是什么样的？我的实例报告该查询的成本超过 800 万，这看起来有点吓人。
您有什么关于 app.yml 中 PostgreSQL 调优值的建议吗？目前我使用的是：
shared_buffers: 16GB
work_mem: 512MB
VACUUM FULL 已完成，但似乎并未改善查询性能。徽章查询似乎仍需要运行数小时，除非我添加 ORDER BY 子句，而 percent_rank 查询已经运行了两个小时仍未完成。我们可能需要更改“第一引用”徽章的 SQL 定义，然后我需要看看如何解决 percent_rank 查询的问题。
基于这个 EXPLAIN 输出，您对 percent_rank 查询有什么建议吗？
UPDATE posts \n SET percent_rank = X.percent_rank \n FROM ( \n SELECT posts.id, Y.percent_rank \n FROM posts \n JOIN ( \n SELECT id, percent_rank() \n OVER (PARTITION BY topic_id ORDER BY SCORE DESC) as percent_rank \n FROM posts \n ) Y ON Y.id = posts.id \n JOIN topics ON posts.topic_id = topics.id \n WHERE (posts.percent_rank IS NULL OR Y.percent_rank \u003c\u003e posts.percent_rank) \n LIMIT 20000 \n ) AS X \n WHERE posts.id = X.id\n\n QUERY PLAN\n---------------------------------------------------------------------------------------------------------------------------\n Update on posts (cost=6511439.82..6944253.09 rows=20000 width=828)\n -\u003e Nested Loop (cost=6511439.82..6944253.09 rows=20000 width=828)\n -\u003e Subquery Scan on x (cost=6511439.38..6784765.09 rows=20000 width=48)\n -\u003e Limit (cost=6511439.38..6784565.09 rows=20000 width=12)\n -\u003e Nested Loop (cost=6511439.38..374544016.70 rows=26949684 width=12)\n -\u003e Nested Loop (cost=6511438.96..192122439.64 rows=26949684 width=16)\n -\u003e WindowAgg (cost=6511438.52..7050906.24 rows=26973386 width=24)\n -\u003e Sort (cost=6511438.52..6578871.98 rows=26973386 width=16)\n Sort Key: posts_2.topic_id, posts_2.score DESC\n -\u003e Seq Scan on posts posts_2 (cost=0.00..2721272.86 rows=26973386 width=16)\n -\u003e Index Scan using posts_pkey on posts posts_1 (cost=0.44..6.84 rows=1 width=16)\n Index Cond: (id = posts_2.id)\n Filter: ((percent_rank IS NULL) OR ((percent_rank() OVER (?)) \u003c\u003e percent_rank))\n -\u003e Index Only Scan using topics_pkey on topics (cost=0.42..6.77 rows=1 width=4)\n Index Cond: (id = posts_1.topic_id)\n -\u003e Index Scan using posts_pkey on posts (cost=0.44..7.97 rows=1 width=784)\n Index Cond: (id = x.id)\n JIT:\n Functions: 21\n Options: Inlining true, Optimization true, Expressions true, Deforming true\n(20 rows)\n```
1 个赞
您可以尝试调整限制，也许在限制为 1000 时，速度对您来说就足够了。
更改限制似乎对查询计划（成本或其他方面）影响不大。问题在于，查询必须先对整个 posts 表（在我们的案例中约为 2650 万行）进行排序，然后才能执行该操作。此处可能存在创建索引的机会。我目前未看到 posts 表的任何索引中包含了 score 列。
排名是按主题进行的，而非对整个数据集进行排名。
你或许可以按主题 ID 进行分片处理，例如 WHERE topic_id < 1000、2000、10000 等。在初始更新完成后，此操作可能会运行得更快。
1 个赞