Postgres has 100% CPU for large databases, Discourse 2.7.7

Hi,

we are running Docker based Discourse 2.7.7 with a very large user base. After upgrading from 2.4.2 to 2.7.7, we are experiencing that some Postgres process are stuck at 100% CPU. Further investigation results that such queries seem to cause the problem:

discourse_prod=# select pid, datid, query from pg_stat_activity WHERE pid = '244906';
  pid   |  datid  |                         query
--------+---------+-------------------------------------------------------
 244906 | 2068583 | DELETE FROM user_badges                              +
        |         |   WHERE id IN (                                      +
        |         |     SELECT ub.id                                     +
        |         |     FROM user_badges ub                              +
        |         |     LEFT JOIN (                                      +
        |         |       SELECT id user_id, current_timestamp granted_at+
        |         | FROM users                                           +
        |         | WHERE id  IN (                                       +
        |         |        SELECT p1.user_id                             +
        |         |        FROM post_custom_fields pc                    +
        |         |        JOIN badge_posts p1 ON p1.id = pc.post_id     +
        |         |        JOIN topics t1 ON p1.topic_id = t1.id         +
        |         |        WHERE p1.user_id <> t1.user_id AND            +
        |         |                     name = 'is_accepted_answer' AND  +
        |         |             p1.user_id IN (                          +
        |         |                    SELECT user_id                    +
        |         |                    FROM posts                        +
        |         |                    WHERE TRUE OR  p1.id IN (-1)      +
        |         |             )                                        +
        |         |         GROUP BY p1.user_id                          +
        |         |         HAVING COUNT(*) > 9                          +
        |         | )                                                    +
        |         |     ) q ON q.user_id = ub.user_id                    +
        |         |                                                      +
        |         |     WHERE ub.badge_id = 103 AND q.user_id IS NULL    +
        |         |   )                                                  +
        |         |
(1 row)

We found this query which, I assume, will cause a huge overhead, doesn’t it?

        |         |                    SELECT user_id                    +
        |         |                    FROM posts                        +
        |         |                    WHERE TRUE OR  p1.id IN (-1)      +

Any suggestions on this?
Let me know if you need further info.

Thanks,
Daniel.

This query seems to come from Jobs::BadgeGrant.

We’ve found the issue was caused by a custom SQL query in a user badge. So this is not an upstream issue. Thanks.

3 Likes

I suspect that you’ll also want to make sure that you vacuum and reindex as described in PostgreSQL 13 update.

2 Likes