Very slow Sidekiq issue with large queue due to massive numbers of unread user notifications

supermathie · February 19, 2020, 4:20pm

You have to let it run for a while, then try this query:

SELECT * from (
SELECT trim( 
    substring(regexp_replace(query, E'[ \\t\\n\\r]+', ' ', 'g'), 1, 80)
  ) AS query,
  round(total_time::numeric, 2) AS total_time,
  calls,
  round(min_time::numeric, 2) AS min,
  round(mean_time::numeric, 2) AS mean,
  round(max_time::numeric, 2) AS max,
  round((100 * total_time /
    sum(total_time::numeric) OVER ())::numeric, 2) AS pct_cpu
  FROM    pg_stat_statements
  ORDER BY total_time DESC
) AS queries
WHERE calls >= 100
LIMIT 20;

possibly adapting the 80 (line 3), 100 (line -2) and 20 (line -1) constants.

markersocial · February 23, 2020, 7:57am

Thanks @pfaffman! - I’ll work on adding the changes to the app.yml using a replace stanza so that the changes persist. I see examples of using a replace stanza on this forum so should be able to sort it out.

markersocial · February 23, 2020, 8:48am

Wow great! Thanks @supermathie

I did the query and modified 80 (line 3) to 1000. Here are the results:

                                           query                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |  total_time   |   calls   |   min    |   mean   |    max    | pct_cpu

SELECT COUNT(*) FROM ( SELECT $1 FROM notifications n LEFT JOIN topics t ON t.id = n.topic_id WHERE t.deleted_at IS NULL AND n.notification_type <> $2 AND n.user_id = $3 AND n.id > $4 AND NOT read LIMIT $5 ) AS X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     | 2587572022.08 |  72909919 |     0.01 |    35.49 |  23899.56 |   78.03

SELECT "posts".* FROM "posts" INNER JOIN (SELECT "topics"."id" FROM "topics" LEFT OUTER JOIN topic_users ON topic_users.topic_id = topics.id AND topic_users.user_id = $1 LEFT OUTER JOIN category_users ON category_users.category_id = topics.category_id AND category_users.user_id = $2 LEFT OUTER JOIN users ON users.id = topics.user_id WHERE ("topics"."deleted_at" IS NULL) AND "topics"."visible" = $3 AND (topics.category_id IS NULL OR topics.category_id IN (SELECT id FROM categories WHERE NOT read_restricted)) AND "topics"."closed" = $4 AND "topics"."archived" = $5 AND (COALESCE(topic_users.notification_level, $6) <> $7) AND (topics.created_at > $8) AND (topics.created_at < $9) AND (topics.archetype <> $10) AND (COALESCE(users.trust_level, $11) > $12) AND (topics.id NOT IN ($13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$6 |  318231790.49 |     46133 |  4166.13 |  6898.14 |  19043.22 |    9.60

SELECT "topics"."id" FROM "topics" JOIN topic_search_data s ON topics.id = s.topic_id LEFT JOIN categories c ON topics.id = c.topic_id WHERE ("topics"."deleted_at" IS NULL) AND "topics"."visible" = $1 AND (topics.archetype <> $2) AND (topics.category_id IS NULL OR topics.category_id IN (SELECT id FROM categories WHERE NOT read_restricted)) AND (search_data @@ TO_TSQUERY($3, $4)) AND (c.topic_id IS NULL) ORDER BY ts_rank(search_data, TO_TSQUERY($5, $6)) DESC LIMIT $7                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |   64262116.71 |     29209 |     0.01 |  2200.08 |  14001.99 |    1.94

SELECT "posts".* FROM "posts" WHERE ("posts"."deleted_at" IS NULL) AND (posts.post_type IN ($1,$2,$3)) AND (post_number > COALESCE(( SELECT last_read_post_number FROM topic_users tu WHERE tu.user_id = $4 AND tu.topic_id = $5 ),$6)) AND (reply_to_user_id = $7 OR exists( SELECT $8 from topic_users tu WHERE tu.user_id = $9 AND tu.topic_id = $10 AND notification_level = $11 )) AND "posts"."topic_id" = $12 ORDER BY post_number LIMIT $13                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |   32885093.00 |  36525877 |     0.02 |     0.90 |    467.73 |    0.99

SELECT COUNT(*) FROM "posts" WHERE ("posts"."deleted_at" IS NULL) AND (posts.post_type IN ($1,$2,$3)) AND (post_number > COALESCE(( SELECT last_read_post_number FROM topic_users tu WHERE tu.user_id = $4 AND tu.topic_id = $5 ),$6)) AND (reply_to_user_id = $7 OR exists( SELECT $8 from topic_users tu WHERE tu.user_id = $9 AND tu.topic_id = $10 AND notification_level = $11 )) AND "posts"."topic_id" = $12                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |   32115586.83 |  36525910 |     0.02 |     0.88 |    174.24 |    0.97

SELECT COUNT(*) FROM notifications n LEFT JOIN topics t ON t.id = n.topic_id WHERE t.deleted_at IS NULL AND n.notification_type = $1 AND n.user_id = $2 AND NOT read                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |   28310171.77 |  72933752 |     0.01 |     0.39 |    188.55 |    0.85

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) AND (topics.bumped_at > $1 ) AND (topics.posts_count < $2) LIMIT $3 ) AS X WHERE posts.id = X.id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |   22376250.74 |       616 | 26194.91 | 36325.08 |  73038.20 |    0.67

SELECT * FROM ( SELECT n.id, n.read FROM notifications n LEFT JOIN topics t ON n.topic_id = t.id WHERE t.deleted_at IS NULL AND n.notification_type = $1 AND n.user_id = $2 AND NOT read ORDER BY n.id DESC LIMIT $3 ) AS x UNION ALL SELECT * FROM ( SELECT n.id, n.read FROM notifications n LEFT JOIN topics t ON n.topic_id = t.id WHERE t.deleted_at IS NULL AND (n.notification_type <> $4 OR read) AND n.user_id = $5 ORDER BY n.id DESC LIMIT $6 ) AS y                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |   18708222.97 |  72597791 |     0.03 |     0.26 |    583.21 |    0.56

SELECT "topics".* FROM "topics" LEFT OUTER JOIN topic_users ON topic_users.topic_id = topics.id AND topic_users.user_id = $1 LEFT OUTER JOIN category_users ON category_users.category_id = topics.category_id AND category_users.user_id = $2 LEFT OUTER JOIN users ON users.id = topics.user_id LEFT OUTER JOIN top_topics ON top_topics.topic_id = topics.id WHERE ("topics"."deleted_at" IS NULL) AND "topics"."visible" = $3 AND (topics.category_id IS NULL OR topics.category_id IN (SELECT id FROM categories WHERE NOT read_restricted)) AND "topics"."closed" = $4 AND "topics"."archived" = $5 AND (COALESCE(topic_users.notification_level, $6) <> $7) AND (topics.created_at > $8) AND (topics.created_at < $9) AND (topics.archetype <> $10) AND (COALESCE(users.trust_level, $11) > $12) AND (topics.id NOT IN ($13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$ |   13707361.91 |     46116 |   137.46 |   297.24 |   1735.95 |    0.41

SELECT COUNT(*) FROM "topics" WHERE ("topics"."deleted_at" IS NULL)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |   12677901.42 |     29210 |   115.55 |   434.03 |   4025.25 |    0.38

SELECT "topics"."id" FROM "topics" LEFT OUTER JOIN "categories" ON "categories"."id" = "topics"."category_id" LEFT OUTER JOIN topic_users AS tu ON (topics.id = tu.topic_id AND tu.user_id = $1) WHERE (categories.id IN ( WITH RECURSIVE subcategories AS ( SELECT $2 id, $3 depth UNION SELECT categories.id, (subcategories.depth + $4) depth FROM categories JOIN subcategories ON subcategories.id = categories.parent_category_id WHERE subcategories.depth < $5 ) SELECT subcategories.id FROM subcategories ) AND (categories.id = $6 OR topics.id != categories.topic_id) ) AND (topics.archetype <> $7) AND (COALESCE(categories.topic_id, $8) <> topics.id) AND "topics"."visible" = $9 AND "topics"."id" != $10 AND (topics.deleted_at IS NULL) AND (topics.pinned_at IS NULL OR topics.category_id <> $11) ORDER BY topics.bumped_at DESC LIMIT $12                                                                                                                                                                         |    8287232.95 |     32429 |     0.03 |   255.55 |   9004.21 |    0.25

UPDATE posts p SET score = x.score FROM ( SELECT posts.id, COALESCE(posts.reply_count, $1) * $2 + COALESCE(posts.like_score, $3) * $4 + COALESCE(posts.incoming_link_count, $5) * $6 + COALESCE(posts.bookmark_count, $7) * $8 + COALESCE(posts.reads, $9) * $10 as score FROM posts join topics on posts.topic_id = topics.id WHERE (posts.score IS NULL OR posts.score <> COALESCE(posts.reply_count, $11) * $12 + COALESCE(posts.like_score, $13) * $14 + COALESCE(posts.incoming_link_count, $15) * $16 + COALESCE(posts.bookmark_count, $17) * $18 + COALESCE(posts.reads, $19) * $20) AND (topics.bumped_at > $21 ) AND (topics.posts_count < $22) limit $23 ) AS x WHERE x.id = p.id                                                                                                                                                                                                                                                                                                                                              |    7470148.22 |       317 | 17028.65 | 23565.14 |  85294.25 |    0.23

SELECT "topics"."id" FROM "topics" LEFT OUTER JOIN "categories" ON "categories"."id" = "topics"."category_id" WHERE (categories.id IN ( WITH RECURSIVE subcategories AS ( SELECT $1 id, $2 depth UNION SELECT categories.id, (subcategories.depth + $3) depth FROM categories JOIN subcategories ON subcategories.id = categories.parent_category_id WHERE subcategories.depth < $4 ) SELECT subcategories.id FROM subcategories ) AND (categories.id = $5 OR topics.id != categories.topic_id) ) AND (topics.archetype <> $6) AND (COALESCE(categories.topic_id, $7) <> topics.id) AND "topics"."visible" = $8 AND "topics"."id" NOT IN ($9, $10, $11, $12) AND (topics.deleted_at IS NULL) AND (topics.category_id IS NULL or topics.category_id IN ($13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78,$79,$80,$ |    7254910.11 |     30069 |     0.04 |   241.28 |   9380.76 |    0.22

SELECT COUNT(*) FROM "topics" LEFT OUTER JOIN topic_users ON topic_users.topic_id = topics.id AND topic_users.user_id = $1 LEFT OUTER JOIN category_users ON category_users.category_id = topics.category_id AND category_users.user_id = $2 LEFT OUTER JOIN users ON users.id = topics.user_id WHERE ("topics"."deleted_at" IS NULL) AND "topics"."visible" = $3 AND (topics.category_id IS NULL OR topics.category_id IN (SELECT id FROM categories WHERE NOT read_restricted)) AND "topics"."closed" = $4 AND "topics"."archived" = $5 AND (COALESCE(topic_users.notification_level, $6) <> $7) AND (topics.created_at > $8) AND (topics.created_at < $9) AND (topics.archetype <> $10) AND (COALESCE(users.trust_level, $11) > $12) AND (topics.id NOT IN ($13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78,$ |    6777648.00 |     46133 |    56.03 |   146.92 |   1339.64 |    0.20

INSERT INTO "notifications" ("notification_type", "user_id", "data", "created_at", "updated_at", "topic_id", "post_number") VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING "id"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |    6304063.76 |  36659888 |     0.07 |     0.17 |   3016.91 |    0.19

SELECT "posts".* FROM "posts" JOIN (SELECT *, row_number() over() row_number FROM (SELECT topics.id, min(posts.post_number) post_number FROM "posts" INNER JOIN "post_search_data" ON "post_search_data"."post_id" = "posts"."id" INNER JOIN "topics" ON "topics"."id" = "posts"."topic_id" AND ("topics"."deleted_at" IS NULL) LEFT JOIN categories ON categories.id = topics.category_id WHERE ("posts"."deleted_at" IS NULL) AND "posts"."post_type" IN ($1, $2, $3) AND (topics.visible) AND (topics.archetype <> $4) AND (post_search_data.search_data @@ TO_TSQUERY($5, $6)) AND (categories.id NOT IN ( SELECT categories.id WHERE categories.search_priority = $7 ) ) AND ((categories.id IS NULL) OR (NOT categories.read_restricted)) GROUP BY topics.id ORDER BY MAX(( TS_RANK_CD( post_search_data.search_data, TO_TSQUERY($8, $9), $10|$11 ) * ( CASE categories.search_priority WHEN $12 THEN $13 WHEN $14 THEN $15 WHEN $16 THEN $17 WHEN $18 THEN $19 ELSE CASE WHEN topics.closed THEN $20 ELSE $21 END END ) ) ) DESC, |    5202046.52 |      2758 |     0.02 |  1886.17 | 188756.42 |    0.16

SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |    4802482.60 | 118738376 |     0.01 |     0.04 |     79.49 |    0.14

SELECT "topics"."id" AS t0_r0, "topics"."title" AS t0_r1, "topics"."last_posted_at" AS t0_r2, "topics"."created_at" AS t0_r3, "topics"."updated_at" AS t0_r4, "topics"."views" AS t0_r5, "topics"."posts_count" AS t0_r6, "topics"."user_id" AS t0_r7, "topics"."last_post_user_id" AS t0_r8, "topics"."reply_count" AS t0_r9, "topics"."featured_user1_id" AS t0_r10, "topics"."featured_user2_id" AS t0_r11, "topics"."featured_user3_id" AS t0_r12, "topics"."avg_time" AS t0_r13, "topics"."deleted_at" AS t0_r14, "topics"."highest_post_number" AS t0_r15, "topics"."image_url" AS t0_r16, "topics"."like_count" AS t0_r17, "topics"."incoming_link_count" AS t0_r18, "topics"."category_id" AS t0_r19, "topics"."visible" AS t0_r20, "topics"."moderator_posts_count" AS t0_r21, "topics"."closed" AS t0_r22, "topics"."archived" AS t0_r23, "topics"."bumped_at" AS t0_r24, "topics"."has_summary" AS t0_r25, "topics"."archetype" AS t0_r26, "topics"."featured_user4_id" AS t0_r27, "topics"."notify_moderators_count" AS t0_r |    4206585.37 |     83547 |     0.71 |    50.35 |  10222.11 |    0.13

UPDATE "categories" SET "latest_post_id" = $1 WHERE "categories"."id" = $2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |    3893026.04 |    230921 |     0.02 |    16.86 |  36764.83 |    0.12

SELECT $1 AS one FROM "push_subscriptions" WHERE "push_subscriptions"."user_id" = $2 LIMIT $3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |    3873915.19 |  36657971 |     0.02 |     0.11 |     66.23 |    0.12

Falco · February 23, 2020, 3:09pm

As I said this is a notification problem:

You can try


SELECT user_id, COUNT(*) 
FROM notifications
WHERE NOT read
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;

markersocial · February 23, 2020, 5:36pm

Thanks @Falco

I ran what you provided and here are the results:

user_id | count
---------±-------
1533 | 378920
58665 | 353471
58958 | 329729
29398 | 267843
38294 | 247314
47859 | 243399
60600 | 242684
178196 | 238539
19012 | 235748
54552 | 234956

pfaffman · February 23, 2020, 6:08pm

That looks like users who are oblivious to notifications. My guess is that you should delete notifications for users with more than, say 1000, or older than some cutoff.

Falco · February 23, 2020, 6:39pm

@sam This is similar to what we saw in the last time we optimized notifications.

We fixed the query to read notifications, but to produce it is expensive, especially updating the index btrees with so many elements.

Can I introduce a background job to purge notifications or mark those as read, default off for this release?

pfaffman · February 23, 2020, 6:42pm

My idea was to delete notifications older than some cutoff like 30 days.

sam · February 23, 2020, 7:31pm

I actually think we ought to have a default on setting here, there is almost zero value in a single user having more than 10,000 notifications, we should just purge anything beyond 10,000 out of the box

@codinghorror you happy with this ?

There is arguably value in capping unread at an even lower value like say 1000 per user, but the strong notification cap would completely resolve the issue experienced here anyway

Falco · February 23, 2020, 7:34pm

I have put some thoughts on a couple of limits on this. I will write em down on a new topic early next week. For example, after a thousand unread notifications, we need to remove the user watching options, as it’s pointless to create more notifications just to be purged afterwards.

codinghorror · February 23, 2020, 11:58pm

Yes, we urgently need default rate limit safety here @falco … not sure we should do it in this release though since we are so close to the edge of release?

sam · February 24, 2020, 12:02am

I think for now I will just do a purge in the weekly job, it is a very safe change.

For context ever since we started meta I accrued 45 thousand notifications, you accrued 49 thousand. The hard limit would push it back down to 10 thousand you would be able to see.

Agree we can investigated other limits, but they are far more complex to work out

sam · February 24, 2020, 12:46am

This is now complete per:

@markersocial if you wish to rush the job that only runs weekly run:

./launcher rebuild app
./launcher enter app
rails c
Notification.purge_old!

The site setting max_notifications_per_user controls the limit, it is set to 10,000 out of the box, I think you can easily crank it down to 1000 or so without any users complaining, if you are noticing perf issue post this change, try cranking the number down.

markersocial · February 25, 2020, 3:44am

Wow awesome! Thanks @sam - I really appreciate it.

I updated/rebuilt and successfully purged old notifications following your instructions. Started with the default (10,000), then used 1000.

Speed didn’t change at first, so I flushed redis and rebooted the server. It started going very fast (200-500 jobs per second) for maybe ~20 minutes. Then it’s like it hit a brick wall, back to 2-7 jobs per second and has stayed there since.

Falco · February 25, 2020, 4:04am

If a user can accrue 400000 unread notification there is something really misconfigured in the site.

Why are those users “Watching” so much if they never read?

markersocial · February 25, 2020, 9:07am

Would love to figure that out. This configuration could handle ~10m sidekiq jobs per day (~115 per second) for months. Then plummeted to ~300k in a day (~3.5 per second) without changing the configuration. Forum activity has not changed significantly. Users getting that many unread notifications might be bots.

sam · February 25, 2020, 9:11am

I would recommend repeating the PG query analysis in light of the new changes deployed.

markersocial · February 25, 2020, 10:01am

Good call @sam , here is what I have so far:

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             query                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | total_time  | calls  |   min   |   mean   |   max    | pct_cpu
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+--------+---------+----------+----------+---------
SELECT "posts".* FROM "posts" INNER JOIN (SELECT "topics"."id" FROM "topics" LEFT OUTER JOIN topic_users ON topic_users.topic_id = topics.id AND topic_users.user_id = $1 LEFT OUTER JOIN category_users ON category_users.category_id = topics.category_id AND category_users.user_id = $2 LEFT OUTER JOIN users ON users.id = topics.user_id WHERE ("topics"."deleted_at" IS NULL) AND "topics"."visible" = $3 AND (topics.category_id IS NULL OR topics.category_id IN (SELECT id FROM categories WHERE NOT read_restricted)) AND "topics"."closed" = $4 AND "topics"."archived" = $5 AND (COALESCE(topic_users.notification_level, $6) <> $7) AND (topics.created_at > $8) AND (topics.created_at < $9) AND (topics.archetype <> $10) AND (COALESCE(users.trust_level, $11) > $12) AND (topics.id NOT IN ($13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78,$79,$80,$81,$82,$83,$84)) AND (topics.category_id NOT IN ($85,$86,$87,$88,$89,$90,$91,$92,$93,$94))) AS digest_topics ON digest_topics.id = posts.topic_id WHERE ("posts"."deleted_at" IS NULL) AND (posts.created_at > $95) AND "posts"."post_type" != $96 AND (posts.post_type = $97) AND (posts.deleted_at IS NULL AND posts.hidden = $98 AND posts.user_deleted = $99) AND (posts.post_number > $100 AND posts.score > $101) AND (posts.created_at < $102) ORDER BY posts.score DESC, posts.created_at ASC LIMIT $103                                                  | 16328407.01 |   1443 | 4466.36 | 11315.60 | 24279.92 |   84.08

SELECT "topics"."id" FROM "topics" JOIN topic_search_data s ON topics.id = s.topic_id LEFT JOIN categories c ON topics.id = c.topic_id WHERE ("topics"."deleted_at" IS NULL) AND "topics"."visible" = $1 AND (topics.archetype <> $2) AND (topics.category_id IS NULL OR topics.category_id IN (SELECT id FROM categories WHERE NOT read_restricted)) AND (search_data @@ TO_TSQUERY($3, $4)) AND (c.topic_id IS NULL) ORDER BY ts_rank(search_data, TO_TSQUERY($5, $6)) DESC LIMIT $7                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |   704810.90 |    199 |  483.18 |  3541.76 | 12601.37 |    3.63

SELECT "topics".* FROM "topics" LEFT OUTER JOIN topic_users ON topic_users.topic_id = topics.id AND topic_users.user_id = $1 LEFT OUTER JOIN category_users ON category_users.category_id = topics.category_id AND category_users.user_id = $2 LEFT OUTER JOIN users ON users.id = topics.user_id LEFT OUTER JOIN top_topics ON top_topics.topic_id = topics.id WHERE ("topics"."deleted_at" IS NULL) AND "topics"."visible" = $3 AND (topics.category_id IS NULL OR topics.category_id IN (SELECT id FROM categories WHERE NOT read_restricted)) AND "topics"."closed" = $4 AND "topics"."archived" = $5 AND (COALESCE(topic_users.notification_level, $6) <> $7) AND (topics.created_at > $8) AND (topics.created_at < $9) AND (topics.archetype <> $10) AND (COALESCE(users.trust_level, $11) > $12) AND (topics.id NOT IN ($13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78,$79,$80,$81,$82,$83,$84)) AND (topics.category_id NOT IN ($85,$86,$87,$88,$89,$90,$91,$92,$93,$94)) ORDER BY COALESCE(topic_users.notification_level, $95) DESC, COALESCE(category_users.notification_level, $96) DESC, COALESCE(top_topics.weekly_score, $97) DESC, topics.bumped_at DESC LIMIT $98                                                                                                                                                                                                                                                      |   619806.14 |   1447 |  156.90 |   428.34 |  1246.70 |    3.19

SELECT COUNT(*) FROM ( SELECT $1 FROM notifications n LEFT JOIN topics t ON t.id = n.topic_id WHERE t.deleted_at IS NULL AND n.notification_type <> $2 AND n.user_id = $3 AND n.id > $4 AND NOT read LIMIT $5 ) AS X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |   340947.61 | 335444 |    0.01 |     1.02 |  1538.58 |    1.76

SELECT COUNT(*) FROM "topics" LEFT OUTER JOIN topic_users ON topic_users.topic_id = topics.id AND topic_users.user_id = $1 LEFT OUTER JOIN category_users ON category_users.category_id = topics.category_id AND category_users.user_id = $2 LEFT OUTER JOIN users ON users.id = topics.user_id WHERE ("topics"."deleted_at" IS NULL) AND "topics"."visible" = $3 AND (topics.category_id IS NULL OR topics.category_id IN (SELECT id FROM categories WHERE NOT read_restricted)) AND "topics"."closed" = $4 AND "topics"."archived" = $5 AND (COALESCE(topic_users.notification_level, $6) <> $7) AND (topics.created_at > $8) AND (topics.created_at < $9) AND (topics.archetype <> $10) AND (COALESCE(users.trust_level, $11) > $12) AND (topics.id NOT IN ($13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78,$79,$80,$81,$82,$83,$84)) AND (topics.category_id NOT IN ($85,$86,$87,$88,$89,$90,$91,$92,$93,$94))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |   238843.00 |   1447 |   67.83 |   165.06 |   527.34 |    1.23

SELECT "posts".* FROM "posts" WHERE ("posts"."deleted_at" IS NULL) AND (posts.post_type IN ($1,$2,$3)) AND (post_number > COALESCE(( SELECT last_read_post_number FROM topic_users tu WHERE tu.user_id = $4 AND tu.topic_id = $5 ),$6)) AND (reply_to_user_id = $7 OR exists( SELECT $8 from topic_users tu WHERE tu.user_id = $9 AND tu.topic_id = $10 AND notification_level = $11 )) AND "posts"."topic_id" = $12 ORDER BY post_number LIMIT $13                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |   111020.08 | 168390 |    0.03 |     0.66 |    21.30 |    0.57

SELECT COUNT(*) FROM "posts" WHERE ("posts"."deleted_at" IS NULL) AND (posts.post_type IN ($1,$2,$3)) AND (post_number > COALESCE(( SELECT last_read_post_number FROM topic_users tu WHERE tu.user_id = $4 AND tu.topic_id = $5 ),$6)) AND (reply_to_user_id = $7 OR exists( SELECT $8 from topic_users tu WHERE tu.user_id = $9 AND tu.topic_id = $10 AND notification_level = $11 )) AND "posts"."topic_id" = $12                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |   109915.97 | 168387 |    0.02 |     0.65 |    15.39 |    0.57

SELECT * FROM ( SELECT n.id, n.read FROM notifications n LEFT JOIN topics t ON n.topic_id = t.id WHERE t.deleted_at IS NULL AND n.notification_type = $1 AND n.user_id = $2 AND NOT read ORDER BY n.id DESC LIMIT $3 ) AS x UNION ALL SELECT * FROM ( SELECT n.id, n.read FROM notifications n LEFT JOIN topics t ON n.topic_id = t.id WHERE t.deleted_at IS NULL AND (n.notification_type <> $4 OR read) AND n.user_id = $5 ORDER BY n.id DESC LIMIT $6 ) AS y                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |    76365.03 | 332097 |    0.04 |     0.23 |    23.89 |    0.39

SELECT COUNT(*) FROM "topics" WHERE ("topics"."deleted_at" IS NULL)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |    65491.49 |    199 |  123.86 |   329.10 |   901.86 |    0.34

SELECT "topics"."id" FROM "topics" LEFT OUTER JOIN "categories" ON "categories"."id" = "topics"."category_id" LEFT OUTER JOIN topic_users AS tu ON (topics.id = tu.topic_id AND tu.user_id = $1) WHERE (categories.id IN ( WITH RECURSIVE subcategories AS ( SELECT $2 id, $3 depth UNION SELECT categories.id, (subcategories.depth + $4) depth FROM categories JOIN subcategories ON subcategories.id = categories.parent_category_id WHERE subcategories.depth < $5 ) SELECT subcategories.id FROM subcategories ) AND (categories.id = $6 OR topics.id != categories.topic_id) ) AND (topics.archetype <> $7) AND (COALESCE(categories.topic_id, $8) <> topics.id) AND "topics"."visible" = $9 AND "topics"."id" != $10 AND (topics.deleted_at IS NULL) AND (topics.pinned_at IS NULL OR topics.category_id <> $11) ORDER BY topics.bumped_at DESC LIMIT $12                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |    54295.65 |    186 |    0.05 |   291.91 |  5954.67 |    0.28

SELECT "topics"."id" FROM "topics" LEFT OUTER JOIN "categories" ON "categories"."id" = "topics"."category_id" WHERE (categories.id IN ( WITH RECURSIVE subcategories AS ( SELECT $1 id, $2 depth UNION SELECT categories.id, (subcategories.depth + $3) depth FROM categories JOIN subcategories ON subcategories.id = categories.parent_category_id WHERE subcategories.depth < $4 ) SELECT subcategories.id FROM subcategories ) AND (categories.id = $5 OR topics.id != categories.topic_id) ) AND (topics.archetype <> $6) AND (COALESCE(categories.topic_id, $7) <> topics.id) AND "topics"."visible" = $8 AND "topics"."id" NOT IN ($9, $10, $11, $12) AND (topics.deleted_at IS NULL) AND (topics.category_id IS NULL or topics.category_id IN ($13,$14,$15,$1:

markersocial · February 25, 2020, 3:41pm

Update:

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      query                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |  total_time  |  calls  |  min   |  mean   |   max    | pct_cpu
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+---------+--------+---------+----------+---------
SELECT "posts".* FROM "posts" INNER JOIN (SELECT "topics"."id" FROM "topics" LEFT OUTER JOIN topic_users ON topic_users.topic_id = topics.id AND topic_users.user_id = $1 LEFT OUTER JOIN category_users ON category_users.category_id = topics.category_id AND category_users.user_id = $2 LEFT OUTER JOIN users ON users.id = topics.user_id WHERE ("topics"."deleted_at" IS NULL) AND "topics"."visible" = $3 AND (topics.category_id IS NULL OR topics.category_id IN (SELECT id FROM categories WHERE NOT read_restricted)) AND "topics"."closed" = $4 AND "topics"."archived" = $5 AND (COALESCE(topic_users.notification_level, $6) <> $7) AND (topics.created_at > $8) AND (topics.created_at < $9) AND (topics.archetype <> $10) AND (COALESCE(users.trust_level, $11) > $12) AND (topics.id NOT IN ($13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78,$79,$80,$81,$82,$83,$84)) AND (topics.category_id NOT IN ($85,$86,$87,$88,$89,$90,$91,$92,$93,$94))) AS digest_topics ON digest_topics.id = posts.topic_id WHERE ("posts"."deleted_at" IS NULL) AND (posts.created_at > $95) AND "posts"."post_type" != $96 AND (posts.post_type = $97) AND (posts.deleted_at IS NULL AND posts.hidden = $98 AND posts.user_deleted = $99) AND (posts.post_number > $100 AND posts.score > $101) AND (posts.created_at < $102) ORDER BY posts.score DESC, posts.created_at ASC LIMIT $103                                                                                                    | 171791616.39 |   18846 |  79.94 | 9115.55 | 25522.47 |   78.33

SELECT "topics".* FROM "topics" LEFT OUTER JOIN topic_users ON topic_users.topic_id = topics.id AND topic_users.user_id = $1 LEFT OUTER JOIN category_users ON category_users.category_id = topics.category_id AND category_users.user_id = $2 LEFT OUTER JOIN users ON users.id = topics.user_id LEFT OUTER JOIN top_topics ON top_topics.topic_id = topics.id WHERE ("topics"."deleted_at" IS NULL) AND "topics"."visible" = $3 AND (topics.category_id IS NULL OR topics.category_id IN (SELECT id FROM categories WHERE NOT read_restricted)) AND "topics"."closed" = $4 AND "topics"."archived" = $5 AND (COALESCE(topic_users.notification_level, $6) <> $7) AND (topics.created_at > $8) AND (topics.created_at < $9) AND (topics.archetype <> $10) AND (COALESCE(users.trust_level, $11) > $12) AND (topics.id NOT IN ($13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78,$79,$80,$81,$82,$83,$84)) AND (topics.category_id NOT IN ($85,$86,$87,$88,$89,$90,$91,$92,$93,$94)) ORDER BY COALESCE(topic_users.notification_level, $95) DESC, COALESCE(category_users.notification_level, $96) DESC, COALESCE(top_topics.weekly_score, $97) DESC, topics.bumped_at DESC LIMIT $98                                                                                                                                                                                                                                                                                                        |   6657945.83 |   18831 | 112.24 |  353.56 |  1798.91 |    3.04

SELECT COUNT(*) FROM ( SELECT $1 FROM notifications n LEFT JOIN topics t ON t.id = n.topic_id WHERE t.deleted_at IS NULL AND n.notification_type <> $2 AND n.user_id = $3 AND n.id > $4 AND NOT read LIMIT $5 ) AS X                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |   6571007.07 | 5698660 |   0.01 |    1.15 |  1538.58 |    3.00

SELECT "topics"."id" FROM "topics" JOIN topic_search_data s ON topics.id = s.topic_id LEFT JOIN categories c ON topics.id = c.topic_id WHERE ("topics"."deleted_at" IS NULL) AND "topics"."visible" = $1 AND (topics.archetype <> $2) AND (topics.category_id IS NULL OR topics.category_id IN (SELECT id FROM categories WHERE NOT read_restricted)) AND (search_data @@ TO_TSQUERY($3, $4)) AND (c.topic_id IS NULL) ORDER BY ts_rank(search_data, TO_TSQUERY($5, $6)) DESC LIMIT $7                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |   6306264.76 |    2173 |  18.42 | 2902.10 | 12601.37 |    2.88

SELECT COUNT(*) FROM "topics" LEFT OUTER JOIN topic_users ON topic_users.topic_id = topics.id AND topic_users.user_id = $1 LEFT OUTER JOIN category_users ON category_users.category_id = topics.category_id AND category_users.user_id = $2 LEFT OUTER JOIN users ON users.id = topics.user_id WHERE ("topics"."deleted_at" IS NULL) AND "topics"."visible" = $3 AND (topics.category_id IS NULL OR topics.category_id IN (SELECT id FROM categories WHERE NOT read_restricted)) AND "topics"."closed" = $4 AND "topics"."archived" = $5 AND (COALESCE(topic_users.notification_level, $6) <> $7) AND (topics.created_at > $8) AND (topics.created_at < $9) AND (topics.archetype <> $10) AND (COALESCE(users.trust_level, $11) > $12) AND (topics.id NOT IN ($13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78,$79,$80,$81,$82,$83,$84)) AND (topics.category_id NOT IN ($85,$86,$87,$88,$89,$90,$91,$92,$93,$94))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |   3147774.90 |   18850 |  11.78 |  166.99 |  1284.50 |    1.44

SELECT COUNT(*) FROM "posts" WHERE ("posts"."deleted_at" IS NULL) AND (posts.post_type IN ($1,$2,$3)) AND (post_number > COALESCE(( SELECT last_read_post_number FROM topic_users tu WHERE tu.user_id = $4 AND tu.topic_id = $5 ),$6)) AND (reply_to_user_id = $7 OR exists( SELECT $8 from topic_users tu WHERE tu.user_id = $9 AND tu.topic_id = $10 AND notification_level = $11 )) AND "posts"."topic_id" = $12                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |   2024613.07 | 2860061 |   0.02 |    0.71 |    33.57 |    0.92

SELECT "posts".* FROM "posts" WHERE ("posts"."deleted_at" IS NULL) AND (posts.post_type IN ($1,$2,$3)) AND (post_number > COALESCE(( SELECT last_read_post_number FROM topic_users tu WHERE tu.user_id = $4 AND tu.topic_id = $5 ),$6)) AND (reply_to_user_id = $7 OR exists( SELECT $8 from topic_users tu WHERE tu.user_id = $9 AND tu.topic_id = $10 AND notification_level = $11 )) AND "posts"."topic_id" = $12 ORDER BY post_number LIMIT $13                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |   2014126.63 | 2860062 |   0.02 |    0.70 |    50.40 |    0.92

SELECT * FROM ( SELECT n.id, n.read FROM notifications n LEFT JOIN topics t ON n.topic_id = t.id WHERE t.deleted_at IS NULL AND n.notification_type = $1 AND n.user_id = $2 AND NOT read ORDER BY n.id DESC LIMIT $3 ) AS x UNION ALL SELECT * FROM ( SELECT n.id, n.read FROM notifications n LEFT JOIN topics t ON n.topic_id = t.id WHERE t.deleted_at IS NULL AND (n.notification_type <> $4 OR read) AND n.user_id = $5 ORDER BY n.id DESC LIMIT $6 ) AS y                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |   1417946.43 | 5658937 |   0.03 |    0.25 |    23.89 |    0.65

SELECT COUNT(*) FROM "topics" WHERE ("topics"."deleted_at" IS NULL)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |    907420.61 |    2175 | 123.86 |  417.20 |  1265.40 |    0.41

SELECT "topics"."id" FROM "topics" LEFT OUTER JOIN "categories" ON "categories"."id" = "topics"."category_id" LEFT OUTER JOIN topic_users AS tu ON (topics.id = tu.topic_id AND tu.user_id = $1) WHERE (categories.id IN ( WITH RECURSIVE subcategories AS ( SELECT $2 id, $3 depth UNION SELECT categories.id, (subcategories.depth + $4) depth FROM categories JOIN subcategories ON subcategories.id = categories.parent_category_id WHERE subcategories.depth < $5 ) SELECT subcategories.id FROM subcategories ) AND (categories.id = $6 OR topics.id != categories.topic_id) ) AND (topics.archetype <> $7) AND (COALESCE(categories.topic_id, $8) <> topics.id) AND "topics"."visible" = $9 AND "topics"."id" != $10 AND (topics.deleted_at IS NULL) AND (topics.pinned_at IS NULL OR topics.category_id <> $11) ORDER BY topics.bumped_at DESC LIMIT $12                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |    604347.67 |    2244 |   0.04 |  269.32 |  5954.67 |    0.28

SELECT "topics"."id" FROM "topics" LEFT OUTER JOIN "categories" ON "categories"."id" = "topics"."category_id" WHERE (categories.id IN ( WITH RECURSIVE subcategories AS ( SELECT $1 id, $2 depth UNION SELECT categories.id, (subcategories.depth + $3) depth FROM categories JOIN subcategories ON subcategories.id = categories.parent_category_id WHERE subcategories.depth < $4 ) SELECT subcategories.id FROM subcategories ) AND (categories.id = $5 OR topics.id != categories.topic_id) ) AND (topics.archetype <> $6) AND (COALESCE(categories.topic_id, $7) <> topics.id) AND "topics"."visible" = $8 AND "topics"."id" NOT IN ($9, $10, $11, $12) AND (topics.deleted_at IS NULL) AND (topics.category_id IS NULL or topics.category_id IN ($13,$14,$15,$1:

supermathie · February 25, 2020, 4:08pm

That top one looks like it’s building a digest…

Could of general troubleshooting steps:

can you run rake db:stats from the container and post the output?
can you run a vacuum analyze in the DB and see if it gets better?

Topic		Replies	Views
Slow Sidekiq + Postmaster using 95%+ CPU (32 cores) after Postgresql Version Upgrade Installation server-resources	23	2824	June 28, 2020
Sidekiq has a lot of errors and queued jobs Support	19	715	March 1, 2024
Long-Running Sidekiq Jobs Feature	21	1495	December 24, 2020
Sidekiq not working Installation	10	921	October 2, 2023
Moving posts returns 502 bad gateway Installation performance , server-resources	70	4483	December 13, 2021

Very slow Sidekiq issue with large queue due to massive numbers of unread user notifications

Related topics