': uninitialized constant Developers (NameError)
discourse@2d5c3bf04550:~$ cat make_me_dev.rb
Developers.create!(user_id: 123)
```
我将尝试 `discourse.conf` 方法，并尽快提供所需数据。
感谢您到目前为止的建议！
---
### Author:  [@Falco](https://meta.discourse.org/u/Falco)
#### Post date: [2019年十月25日 16:35 UTC](https://meta.discourse.org/t/long-loading-times-for-user-summary-page-with-slow-database/131870/11 "2019-10-25T16:35:49Z")
> [@klopsknoedel](#):
>
> 不幸的是，它不起作用（是的，我之前已经替换过 123 了：slight\_smile:)）：
这就是为什么我们总是建议人们使用官方支持的方法来安装 Discourse，这样在需要帮助时，我们可以使用标准工具提供帮助。
---
### Author:  [@klopsknoedel](https://meta.discourse.org/u/klopsknoedel)
#### Post date: [2019年十月25日 18:18 UTC](https://meta.discourse.org/t/long-loading-times-for-user-summary-page-with-slow-database/131870/12 "2019-10-25T18:18:41Z")
> [@Falco](#):
>
> Discourse 支持的安装方法
是的，可能是因为我们使用 supervisord 来启动所有进程。
无论如何，这是您请求的输出（压缩的 HTML 内容）：
[\_u\_x\_strom\_summary.json-13498.2ms-ProfilingResults.zip|附件](https://meta.discourse.org/uploads/short-url/16UAa2ogGVMmZZg4G3CKyCwVTAg.zip) (94.5 KB)
此请求耗时 13 秒，但我不得不加载该站点两次。第一次尝试耗时过长，导致出现 502 错误。因此，可能某些内容已经被缓存了。
---
### Author:  [@Falco](https://meta.discourse.org/u/Falco)
#### Post date: [2019年十月25日 18:26 UTC](https://meta.discourse.org/t/long-loading-times-for-user-summary-page-with-slow-database/131870/13 "2019-10-25T18:26:03Z")
运行 51 条 SQL 查询耗时超过 13 秒。您的数据库要么配置严重错误，要么性能严重不足。
> **主要瓶颈**
>
> ```plaintext
> SELECT "posts"."id" AS t0_r0, "posts"."user_id" AS t0_r1, "posts"."topic_id" AS t0_r2, "posts"."post_number" AS t0_r3, "posts"."raw" AS t0_r4, "posts"."cooked" AS t0_r5, "posts"."created_at" AS t0_r6, "posts"."updated_at" AS t0_r7, "posts"."reply_to_post_number" AS t0_r8, "posts"."reply_count" AS t0_r9, "posts"."quote_count" AS t0_r10, "posts"."deleted_at" AS t0_r11, "posts"."off_topic_count" AS t0_r12, "posts"."like_count" AS t0_r13, "posts"."incoming_link_count" AS t0_r14, "posts"."bookmark_count" AS t0_r15, "posts"."avg_time" AS t0_r16, "posts"."score" AS t0_r17, "posts"."reads" AS t0_r18, "posts"."post_type" AS t0_r19, "posts"."sort_order" AS t0_r20, "posts"."last_editor_id" AS t0_r21, "posts"."hidden" AS t0_r22, "posts"."hidden_reason_id" AS t0_r23, "posts"."notify_moderators_count" AS t0_r24, "posts"."spam_count" AS t0_r25, "posts"."illegal_count" AS t0_r26, "posts"."inappropriate_count" AS t0_r27, "posts"."last_version_at" AS t0_r28, "posts"."user_deleted" AS t0_r29, "posts"."reply_to_user_id" AS t0_r30, "posts"."percent_rank" AS t0_r31, "posts"."notify_user_count" AS t0_r32, "posts"."like_score" AS t0_r33, "posts"."deleted_by_id" AS t0_r34, "posts"."edit_reason" AS t0_r35, "posts"."word_count" AS t0_r36, "posts"."version" AS t0_r37, "posts"."cook_method" AS t0_r38, "posts"."wiki" AS t0_r39, "posts"."baked_at" AS t0_r40, "posts"."baked_version" AS t0_r41, "posts"."hidden_at" AS t0_r42, "posts"."self_edits" AS t0_r43, "posts"."reply_quoted" AS t0_r44, "posts"."via_email" AS t0_r45, "posts"."raw_email" AS t0_r46, "posts"."public_version" AS t0_r47, "posts"."action_code" AS t0_r48, "posts"."image_url" AS t0_r49, "posts"."locked_by_id" AS t0_r50, "topics"."id" AS t1_r0, "topics"."title" AS t1_r1, "topics"."last_posted_at" AS t1_r2, "topics"."created_at" AS t1_r3, "topics"."updated_at" AS t1_r4, "topics"."views" AS t1_r5, "topics"."posts_count" AS t1_r6, "topics"."user_id" AS t1_r7, "topics"."last_post_user_id" AS t1_r8, "topics"."reply_count" AS t1_r9, "topics"."featured_user1_id" AS t1_r10, "topics"."featured_user2_id" AS t1_r11, "topics"."featured_user3_id" AS t1_r12, "topics"."avg_time" AS t1_r13, "topics"."deleted_at" AS t1_r14, "topics"."highest_post_number" AS t1_r15, "topics"."image_url" AS t1_r16, "topics"."like_count" AS t1_r17, "topics"."incoming_link_count" AS t1_r18, "topics"."category_id" AS t1_r19, "topics"."visible" AS t1_r20, "topics"."moderator_posts_count" AS t1_r21, "topics"."closed" AS t1_r22, "topics"."archived" AS t1_r23, "topics"."bumped_at" AS t1_r24, "topics"."has_summary" AS t1_r25, "topics"."archetype" AS t1_r26, "topics"."featured_user4_id" AS t1_r27, "topics"."notify_moderators_count" AS t1_r28, "topics"."spam_count" AS t1_r29, "topics"."pinned_at" AS t1_r30, "topics"."score" AS t1_r31, "topics"."percent_rank" AS t1_r32, "topics"."subtype" AS t1_r33, "topics"."slug" AS t1_r34, "topics"."deleted_by_id" AS t1_r35, "topics"."participant_count" AS t1_r36, "topics"."word_count" AS t1_r37, "topics"."excerpt" AS t1_r38, "topics"."pinned_globally" AS t1_r39, "topics"."pinned_until" AS t1_r40, "topics"."fancy_title" AS t1_r41, "topics"."highest_staff_post_number" AS t1_r42, "topics"."featured_link" AS t1_r43, "topics"."reviewable_score" AS t1_r44 FROM "posts" INNER JOIN "topics" ON ("topics"."deleted_at" IS NULL) AND "topics"."id" = "posts"."topic_id" WHERE ("posts"."deleted_at" IS NULL) AND (posts.post_type IN (1,2,3,4)) AND ("topics"."deleted_at" IS NULL) AND (topics.archetype <> 'private_message') AND "topics"."visible" = TRUE AND (topics.category_id IS NULL OR topics.category_id IN (SELECT id FROM categories WHERE NOT read_restricted OR id IN (1,3,6,100,141,202,229,233,235,248,262,594,595,609))) AND "posts"."user_id" = 643476 AND (post_number > 1) ORDER BY posts.like_count DESC, posts.created_at DESC LIMIT 6;
>
> ```
>
> 耗时 6248.3 毫秒
>
> ```plaintext
> SELECT "topic_links"."id" AS t0_r0, "topic_links"."topic_id" AS t0_r1, "topic_links"."post_id" AS t0_r2, "topic_links"."user_id" AS t0_r3, "topic_links"."url" AS t0_r4, "topic_links"."domain" AS t0_r5, "topic_links"."internal" AS t0_r6, "topic_links"."link_topic_id" AS t0_r7, "topic_links"."created_at" AS t0_r8, "topic_links"."updated_at" AS t0_r9, "topic_links"."reflection" AS t0_r10, "topic_links"."clicks" AS t0_r11, "topic_links"."link_post_id" AS t0_r12, "topic_links"."title" AS t0_r13, "topic_links"."crawled_at" AS t0_r14, "topic_links"."quote" AS t0_r15, "topic_links"."extension" AS t0_r16, "topics"."id" AS t1_r0, "topics"."title" AS t1_r1, "topics"."last_posted_at" AS t1_r2, "topics"."created_at" AS t1_r3, "topics"."updated_at" AS t1_r4, "topics"."views" AS t1_r5, "topics"."posts_count" AS t1_r6, "topics"."user_id" AS t1_r7, "topics"."last_post_user_id" AS t1_r8, "topics"."reply_count" AS t1_r9, "topics"."featured_user1_id" AS t1_r10, "topics"."featured_user2_id" AS t1_r11, "topics"."featured_user3_id" AS t1_r12, "topics"."avg_time" AS t1_r13, "topics"."deleted_at" AS t1_r14, "topics"."highest_post_number" AS t1_r15, "topics"."image_url" AS t1_r16, "topics"."like_count" AS t1_r17, "topics"."incoming_link_count" AS t1_r18, "topics"."category_id" AS t1_r19, "topics"."visible" AS t1_r20, "topics"."moderator_posts_count" AS t1_r21, "topics"."closed" AS t1_r22, "topics"."archived" AS t1_r23, "topics"."bumped_at" AS t1_r24, "topics"."has_summary" AS t1_r25, "topics"."archetype" AS t1_r26, "topics"."featured_user4_id" AS t1_r27, "topics"."notify_moderators_count" AS t1_r28, "topics"."spam_count" AS t1_r29, "topics"."pinned_at" AS t1_r30, "topics"."score" AS t1_r31, "topics"."percent_rank" AS t1_r32, "topics"."subtype" AS t1_r33, "topics"."slug" AS t1_r34, "topics"."deleted_by_id" AS t1_r35, "topics"."participant_count" AS t1_r36, "topics"."word_count" AS t1_r37, "topics"."excerpt" AS t1_r38, "topics"."pinned_globally" AS t1_r39, "topics"."pinned_until" AS t1_r40, "topics"."fancy_title" AS t1_r41, "topics"."highest_staff_post_number" AS t1_r42, "topics"."featured_link" AS t1_r43, "topics"."reviewable_score" AS t1_r44, "posts"."id" AS t2_r0, "posts"."user_id" AS t2_r1, "posts"."topic_id" AS t2_r2, "posts"."post_number" AS t2_r3, "posts"."raw" AS t2_r4, "posts"."cooked" AS t2_r5, "posts"."created_at" AS t2_r6, "posts"."updated_at" AS t2_r7, "posts"."reply_to_post_number" AS t2_r8, "posts"."reply_count" AS t2_r9, "posts"."quote_count" AS t2_r10, "posts"."deleted_at" AS t2_r11, "posts"."off_topic_count" AS t2_r12, "posts"."like_count" AS t2_r13, "posts"."incoming_link_count" AS t2_r14, "posts"."bookmark_count" AS t2_r15, "posts"."avg_time" AS t2_r16, "posts"."score" AS t2_r17, "posts"."reads" AS t2_r18, "posts"."post_type" AS t2_r19, "posts"."sort_order" AS t2_r20, "posts"."last_editor_id" AS t2_r21, "posts"."hidden" AS t2_r22, "posts"."hidden_reason_id" AS t2_r23, "posts"."notify_moderators_count" AS t2_r24, "posts"."spam_count" AS t2_r25, "posts"."illegal_count" AS t2_r26, "posts"."inappropriate_count" AS t2_r27, "posts"."last_version_at" AS t2_r28, "posts"."user_deleted" AS t2_r29, "posts"."reply_to_user_id" AS t2_r30, "posts"."percent_rank" AS t2_r31, "posts"."notify_user_count" AS t2_r32, "posts"."like_score" AS t2_r33, "posts"."deleted_by_id" AS t2_r34, "posts"."edit_reason" AS t2_r35, "posts"."word_count" AS t2_r36, "posts"."version" AS t2_r37, "posts"."cook_method" AS t2_r38, "posts"."wiki" AS t2_r39, "posts"."baked_at" AS t2_r40, "posts"."baked_version" AS t2_r41, "posts"."hidden_at" AS t2_r42, "posts"."self_edits" AS t2_r43, "posts"."reply_quoted" AS t2_r44, "posts"."via_email" AS t2_r45, "posts"."raw_email" AS t2_r46, "posts"."public_version" AS t2_r47, "posts"."action_code" AS t2_r48, "posts"."image_url" AS t2_r49, "posts"."locked_by_id" AS t2_r50 FROM "topic_links" INNER JOIN "topics" ON ("topics"."deleted_at" IS NULL) AND "topics"."id" = "topic_links"."topic_id" INNER JOIN "posts" ON ("posts"."deleted_at" IS NULL) AND "posts"."id" = "topic_links"."post_id" WHERE (posts.post_type IN (1,2,3,4)) AND ("topics"."deleted_at" IS NULL) AND (topics.archetype <> 'private_message') AND "topics"."visible" = TRUE AND (topics.category_id IS NULL OR topics.category_id IN (SELECT id FROM categories WHERE NOT read_restricted OR id IN (1,3,6,100,141,202,229,233,235,248,262,594,595,609))) AND "topic_links"."user_id" = 643476 AND "topic_links"."internal" = FALSE AND "topic_links"."reflection" = FALSE AND "topic_links"."quote" = FALSE ORDER BY clicks DESC, topic_links.created_at DESC LIMIT 6;
>
> ```
>
> 耗时 258.3 毫秒
>
> ```plaintext
> SELECT acting_user_id, COUNT(*) FROM "user_actions" INNER JOIN "topics" ON ("topics"."deleted_at" IS NULL) AND "topics"."id" = "user_actions"."target_topic_id" INNER JOIN "posts" ON ("posts"."deleted_at" IS NULL) AND "posts"."id" = "user_actions"."target_post_id" WHERE ("topics"."deleted_at" IS NULL) AND (topics.archetype <> 'private_message') AND "topics"."visible" = TRUE AND (topics.category_id IS NULL OR topics.category_id IN (SELECT id FROM categories WHERE NOT read_restricted OR id IN (1,3,6,100,141,202,229,233,235,248,262,594,595,609))) AND "user_actions"."user_id" = 643476 AND "user_actions"."action_type" = 2 GROUP BY "user_actions"."acting_user_id" ORDER BY COUNT(*) DESC LIMIT 6;
>
> ```
>
> 耗时 245.5 毫秒
>
> ```plaintext
> SELECT user_actions.user_id, COUNT(*) FROM "user_actions" INNER JOIN "topics" ON ("topics"."deleted_at" IS NULL) AND "topics"."id" = "user_actions"."target_topic_id" INNER JOIN "posts" ON ("posts"."deleted_at" IS NULL) AND "posts"."id" = "user_actions"."target_post_id" WHERE ("topics"."deleted_at" IS NULL) AND (topics.archetype <> 'private_message') AND "topics"."visible" = TRUE AND (topics.category_id IS NULL OR topics.category_id IN (SELECT id FROM categories WHERE NOT read_restricted OR id IN (1,3,6,100,141,202,229,233,235,248,262,594,595,609))) AND "user_actions"."action_type" = 2 AND "user_actions"."acting_user_id" = 643476 GROUP BY "user_actions"."user_id" ORDER BY COUNT(*) DESC LIMIT 6;
>
> ```
>
> 耗时 277.2 毫秒
>
> ```plaintext
> SELECT replies.user_id, COUNT(*) FROM "posts" INNER JOIN "topics" ON ("topics"."deleted_at" IS NULL) AND "topics"."id" = "posts"."topic_id" JOIN posts replies ON posts.topic_id = replies.topic_id AND posts.reply_to_post_number = replies.post_number WHERE ("posts"."deleted_at" IS NULL) AND (posts.post_type IN (1,2,3,4)) AND ("topics"."deleted_at" IS NULL) AND (topics.archetype <> 'private_message') AND "topics"."visible" = TRUE AND (topics.category_id IS NULL OR topics.category_id IN (SELECT id FROM categories WHERE NOT read_restricted OR id IN (1,3,6,100,141,202,229,233,235,248,262,594,595,609))) AND "posts"."user_id" = 643476 AND (replies.user_id <> 643476) GROUP BY replies.user_id ORDER BY COUNT(*) DESC LIMIT 6;
>
> ```
>
> 耗时 3064.6 毫秒
>
> ```plaintext
> SELECT category_id FROM "posts" INNER JOIN "topics" ON ("topics"."deleted_at" IS NULL) AND "topics"."id" = "posts"."topic_id" WHERE ("posts"."deleted_at" IS NULL) AND (posts.post_type IN (1,2,3,4)) AND ("topics"."deleted_at" IS NULL) AND (topics.archetype <> 'private_message') AND "topics"."visible" = TRUE AND (topics.category_id IS NULL OR topics.category_id IN (SELECT id FROM categories WHERE NOT read_restricted OR id IN (1,3,6,100,141,202,229,233,235,248,262,594,595,609))) AND "posts"."user_id" = 643476 GROUP BY topics.category_id ORDER BY COUNT(*) DESC LIMIT 6;
>
> ```
>
> 耗时 2283.3 毫秒
>
> ```plaintext
> SELECT category_id, COUNT(*) FROM "posts" INNER JOIN "topics" ON ("topics"."deleted_at" IS NULL) AND "topics"."id" = "posts"."topic_id" WHERE ("posts"."deleted_at" IS NULL) AND (posts.post_type IN (1,2,3,4)) AND ("topics"."deleted_at" IS NULL) AND (topics.archetype <> 'private_message') AND "topics"."visible" = TRUE AND (topics.category_id IS NULL OR topics.category_id IN (SELECT id FROM categories WHERE NOT read_restricted OR id IN (1,3,6,100,141,202,229,233,235,248,262,594,595,609))) AND "posts"."user_id" = 643476 AND (post_number > 1) AND (topics.category_id in (64,13,59,124,327,122)) GROUP BY topics.category_id ORDER BY COUNT(*) DESC;
>
> ```
>
> 耗时 811.5 毫秒
我假设 Azure 为托管服务提供某种数据库统计信息，您需要检查这些统计信息并评估需要做出哪些调整。
此外，请检查您安装环境中的 `/sidekiq` 网址，查看是否存在队列争用导致数据库繁忙的情况。
---
### Author:  [@klopsknoedel](https://meta.discourse.org/u/klopsknoedel)
#### Post date: [2019年十月25日 18:47 UTC](https://meta.discourse.org/t/long-loading-times-for-user-summary-page-with-slow-database/131870/14 "2019-10-25T18:47:50Z")
> [@Falco](#):
>
> 您的数据库配置可能严重不当，或者性能不足。
这是我们首先检查的。我们的实例配置如下：
- 8 核（利用率约 16.18%，峰值达 60%）
- 40GB 内存（已用约 60%，无峰值波动）
- 2244 可用 IOPS（利用率约 2.5%，峰值达 24%）
如前所述，我们使用了您在 [postgres.10.template.yml](https://github.com/discourse/discourse_docker/blob/master/templates/postgres.10.template.yml) 中描述的配置文件。其他设置均为默认值。看来我们需要再次仔细检查。
> [@Falco](#):
>
> 检查 `/sidekiq` 网址
Sidekiq 目前状态良好。0 个繁忙任务，0 个排队任务，0 次重试，1 个已调度任务，0 个死亡任务。任务列表当前为空。
因此，我们得出初步结论。首先，我们将检查 PostgreSQL 配置，因为有一些优化措施可以应用。如果无效，我们应考虑升级 PostgreSQL 实例和/或执行完整的 VACUUM 操作。
感谢您到目前为止的宝贵时间，祝您周末愉快！
此致
Sascha
---
### Author:  [@riking](https://meta.discourse.org/u/riking)
#### Post date: [2019年十月29日 02:49 UTC](https://meta.discourse.org/t/long-loading-times-for-user-summary-page-with-slow-database/131870/15 "2019-10-29T02:49:40Z")
如果您的机器拥有 40GB 内存，postgres.10.template.yml 中的默认设置已不再适用，正如 samples/standalone.yml 所指出的：
>
---
### Author:  [@klopsknoedel](https://meta.discourse.org/u/klopsknoedel)
#### Post date: [2019年十月29日 09:06 UTC](https://meta.discourse.org/t/long-loading-times-for-user-summary-page-with-slow-database/131870/16 "2019-10-29T09:06:37Z")
您好，
感谢您的回复。在 Azure 上使用 PostgreSQL 实例存在一些限制。您无法设置某些参数，例如 `effective_cache_size`、`max_connections`、`maintenance_worker_mem` 或 `shared_buffers`。
在提交此支持请求时，我们使用的是“通用用途”（General Purpose）层级，该层级提供 40GB 内存，但仅分配了 1GB 的 `shared_buffers` 和 2.5GB 的 `effective_cache_size`。
从昨天起，我们切换到了“内存优化”（Memory Optimized）层级，内存翻倍至 80GB，`shared_buffers` 增至 2GB，`effective_cache_size` 增至 5GB。
`work_mem` 是我们能够调整的参数，目前设置为 128MB，因为我们的日志中没有出现 `temporary file` 相关条目。
有趣的是，一旦我们切换层级，即使未增加 vCore 数量或存储空间，CPU 和 IOPS 的使用率也明显下降。这确实表明之前的问题是由缓存和缓冲区过小引起的。
目前，某些用户（尤其是员工用户或长期用户）的摘要页面以及 _关于_ 页面的加载时间仍然较长，但相比之前已有显著改善（约为之前的 50%）。
此致
附注：
对于遇到类似问题且对 PostgreSQL 安装拥有更多控制权的用户，以下指南可能有所帮助：
> **[Tuning Your PostgreSQL Server - PostgreSQL wiki](https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server)**
> **[Architecture and Tuning of Memory in PostgreSQL Databases](https://severalnines.com/blog/architecture-and-tuning-memory-postgresql-databases/)**
>
> How does PostgreSQL manage memory, and how can we tune it? This blog provides an overview of memory management in PostgreSQL, the configuration parameters available, and tips on how to optimize them.
---
### Author:  [@riking](https://meta.discourse.org/u/riking)
#### Post date: [2019年十月29日 17:42 UTC](https://meta.discourse.org/t/long-loading-times-for-user-summary-page-with-slow-database/131870/17 "2019-10-29T17:42:13Z")
> [@klopsknoedel](#):
>
> 有趣的是，一旦我们切换了层级，CPU 和 IOPS 利用率就下降了，而无需增加 vCores 和存储。因此，我们确实遇到了由缓存和缓冲区过小引起的问题。
很高兴听到这个消息。下一步是看看是否可以通过使用 4vCPU 内存优化型实例来优化成本，或者考虑自行部署运行。
---
### Author:  [@klopsknoedel](https://meta.discourse.org/u/klopsknoedel)
#### Post date: [2019年十月29日 18:01 UTC](https://meta.discourse.org/t/long-loading-times-for-user-summary-page-with-slow-database/131870/18 "2019-10-29T18:01:14Z")
> [@riking](#):
>
> 侥幸使用了 4 vCPU 内存优化型实例
哦，我们目前运行的是 8 vCore，对应 80GB 内存。`shared_buffers` 当前设置为 2GB，但针对该层级和 vCore 数量，应设置为 16GB。不过，我们已联系 Azure 支持团队以解决此问题。
附言：
我们实际上已经将 `shared_buffers` 设置为 16GB。我之前误解了 `SELECT * FROM pg_settings WHERE name = 'shared_buffers';` 的输出结果。其单位是 `8KB`。
因此，我认为这个话题可以关闭了。感谢大家付出的努力，希望接下来几天在 Azure 支持团队的协助下，我们能共同实现更好的性能。
再附言：
抱歉 @riking，我误会了您。我认为我们使用 8 vCore 是合适的，而不需要将其减少到 4。一旦我们达到了预期的性能目标，我们可以尝试一下。
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [2023年七月13日 10:05 UTC](https://meta.discourse.org/t/long-loading-times-for-user-summary-page-with-slow-database/131870/19 "2023-07-13T10:05:05Z")
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [2023年七月13日 10:25 UTC](https://meta.discourse.org/t/long-loading-times-for-user-summary-page-with-slow-database/131870/20 "2023-07-13T10:25:50Z")