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?
6 Likes
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
6 Likes
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.
12 Likes
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. 
1 Like
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?
7 Likes
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.
1 Like
I would recommend repeating the PG query analysis in light of the new changes deployed.
4 Likes
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:
2 Likes
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:
2 Likes
That top one looks like it’s building a digest…
Could of general troubleshooting steps:
- can you run
rake db:statsfrom the container and post the output? - can you run a
vacuum analyzein the DB and see if it gets better?
4 Likes
Thanks @supermathie - the output is below for rake db:stats. I will also run vacuum analyze and report back.
table_name | row_estimate | table_size | index_size | total_size
--------------------------------------------------------------------------
notifications | 1779605 | 16 GB | 112 GB | 128 GB
posts | 10409870 | 5102 MB | 16 GB | 21 GB
post_search_data | 9493434 | 5741 MB | 1178 MB | 6919 MB
top_topics | 468690 | 110 MB | 4773 MB | 4883 MB
email_logs | 13731364 | 1981 MB | 2861 MB | 4842 MB
topic_views | 23909392 | 1109 MB | 2821 MB | 3930 MB
skipped_email_logs | 16700245 | 1862 MB | 1973 MB | 3835 MB
user_actions | 10063331 | 752 MB | 2779 MB | 3531 MB
post_timings | 16937276 | 749 MB | 1556 MB | 2305 MB
topics | 790818 | 489 MB | 1799 MB | 2288 MB
user_auth_token_logs | 4103959 | 978 MB | 244 MB | 1221 MB
incoming_emails | 8451 | 1112 MB | 18 MB | 1130 MB
post_stats | 10220498 | 683 MB | 446 MB | 1129 MB
topic_users | 4802350 | 492 MB | 369 MB | 860 MB
users | 194463 | 67 MB | 698 MB | 765 MB
incoming_links | 5315556 | 306 MB | 394 MB | 700 MB
directory_items | 960523 | 74 MB | 587 MB | 661 MB
unsubscribe_keys | 1112989 | 196 MB | 297 MB | 493 MB
topic_search_data | 718454 | 357 MB | 83 MB | 440 MB
topic_links | 976001 | 156 MB | 275 MB | 431 MB
user_profile_views | 2493043 | 130 MB | 294 MB | 424 MB
user_histories | 747481 | 220 MB | 166 MB | 387 MB
topic_custom_fields | 448070 | 111 MB | 155 MB | 266 MB
topic_link_clicks | 2554179 | 151 MB | 113 MB | 264 MB
post_custom_fields | 674805 | 103 MB | 123 MB | 226 MB
user_visits | 627095 | 36 MB | 161 MB | 196 MB
optimized_images | 551150 | 121 MB | 69 MB | 189 MB
post_uploads | 808927 | 41 MB | 127 MB | 168 MB
stylesheet_cache | 1801 | 154 MB | 328 kB | 154 MB
user_auth_tokens | 75096 | 44 MB | 63 MB | 107 MB
draft_sequences | 834116 | 48 MB | 52 MB | 100 MB
post_revisions | 89088 | 76 MB | 7304 kB | 83 MB
post_reply_keys | 221484 | 32 MB | 35 MB | 67 MB
topic_tags | 592744 | 36 MB | 28 MB | 64 MB
topic_allowed_users | 476226 | 29 MB | 33 MB | 62 MB
uploads | 74026 | 20 MB | 41 MB | 61 MB
email_tokens | 234846 | 27 MB | 32 MB | 58 MB
user_badges | 130677 | 22 MB | 34 MB | 56 MB
user_stats | 194451 | 38 MB | 7904 kB | 46 MB
user_uploads | 359469 | 20 MB | 26 MB | 46 MB
plugin_store_rows | 162014 | 31 MB | 13 MB | 44 MB
user_emails | 186562 | 16 MB | 23 MB | 39 MB
post_replies | 192842 | 26 MB | 12 MB | 38 MB
user_profiles | 194156 | 12 MB | 24 MB | 35 MB
reviewables | 45869 | 21 MB | 12 MB | 33 MB
drafts | 17429 | 8224 kB | 25 MB | 33 MB
user_search_data | 189320 | 16 MB | 16 MB | 32 MB
user_options | 189057 | 28 MB | 4376 kB | 32 MB
user_avatars | 194998 | 12 MB | 19 MB | 31 MB
group_users | 218600 | 15 MB | 15 MB | 30 MB
scheduler_stats | 95437 | 23 MB | 3968 kB | 27 MB
post_actions | 101213 | 8040 kB | 18 MB | 26 MB
user_custom_fields | 186893 | 14 MB | 12 MB | 26 MB
search_logs | 208901 | 14 MB | 10 MB | 24 MB
user_associated_accounts | 10523 | 21 MB | 1952 kB | 23 MB
reviewable_scores | 43728 | 10152 kB | 7000 kB | 17 MB
incoming_referers | 38785 | 6296 kB | 7824 kB | 14 MB
reviewable_histories | 63567 | 4288 kB | 4352 kB | 8640 kB
user_archived_messages | 58362 | 3552 kB | 2800 kB | 6352 kB
categories | 73 | 2088 kB | 728 kB | 2816 kB
application_requests | 18330 | 1328 kB | 848 kB | 2176 kB
given_daily_likes | 22399 | 1016 kB | 1136 kB | 2152 kB
quoted_posts | 12205 | 808 kB | 920 kB | 1728 kB
screened_emails | 5011 | 664 kB | 688 kB | 1352 kB
category_tag_stats | 2600 | 264 kB | 1064 kB | 1328 kB
screened_ip_addresses | 4401 | 648 kB | 408 kB | 1056 kB
web_crawler_requests | 2544 | 408 kB | 592 kB | 1000 kB
category_users | 5465 | 272 kB | 672 kB | 944 kB
topic_allowed_groups | 6577 | 320 kB | 544 kB | 864 kB
category_featured_topics | 404 | 152 kB | 592 kB | 744 kB
tag_users | 3563 | 248 kB | 424 kB | 672 kB
theme_fields | 94 | 440 kB | 32 kB | 472 kB
incoming_domains | 2139 | 160 kB | 176 kB | 336 kB
javascript_caches | 16 | 216 kB | 64 kB | 280 kB
schema_migration_details | 1015 | 144 kB | 88 kB | 232 kB
push_subscriptions | 387 | 200 kB | 16 kB | 216 kB
tags | 70 | 136 kB | 48 kB | 184 kB
user_open_ids | 293 | 96 kB | 64 kB | 160 kB
user_security_keys | 1 | 48 kB | 112 kB | 160 kB
invites | 6 | 48 kB | 80 kB | 128 kB
schema_migrations | 1015 | 80 kB | 48 kB | 128 kB
email_change_requests | 393 | 88 kB | 32 kB | 120 kB
badges | 50 | 80 kB | 32 kB | 112 kB
api_keys | 1 | 64 kB | 48 kB | 112 kB
screened_urls | 95 | 56 kB | 48 kB | 104 kB
category_custom_fields | 201 | 56 kB | 48 kB | 104 kB
poll_votes | 6 | 40 kB | 64 kB | 104 kB
site_settings | 175 | 72 kB | 32 kB | 104 kB
user_second_factors | 27 | 48 kB | 48 kB | 96 kB
poll_options | 10 | 48 kB | 48 kB | 96 kB
groups | 9 | 48 kB | 48 kB | 96 kB
polls | 1 | 48 kB | 48 kB | 96 kB
muted_users | 41 | 40 kB | 48 kB | 88 kB
user_warnings | 1 | 40 kB | 48 kB | 88 kB
child_themes | 9 | 40 kB | 48 kB | 88 kB
permalinks | 4 | 48 kB | 32 kB | 80 kB
onceoff_logs | 36 | 48 kB | 32 kB | 80 kB
color_scheme_colors | 40 | 48 kB | 32 kB | 80 kB
category_search_data | 70 | 48 kB | 32 kB | 80 kB
watched_words | 22 | 48 kB | 32 kB | 80 kB
translation_overrides | 1 | 48 kB | 32 kB | 80 kB
tag_search_data | 70 | 48 kB | 32 kB | 80 kB
badge_types | 3 | 40 kB | 32 kB | 72 kB
category_groups | 16 | 40 kB | 32 kB | 72 kB
theme_settings | 17 | 56 kB | 16 kB | 72 kB
themes | 14 | 40 kB | 32 kB | 72 kB
badge_groupings | 5 | 48 kB | 16 kB | 64 kB
user_fields | 2 | 48 kB | 16 kB | 64 kB
backup_metadata | 6 | 48 kB | 16 kB | 64 kB
color_schemes | 4 | 48 kB | 16 kB | 64 kB
web_hook_event_types | 10 | 48 kB | 16 kB | 64 kB
ar_internal_metadata | 1 | 48 kB | 16 kB | 64 kB
remote_themes | 6 | 48 kB | 16 kB | 64 kB
bookmarks | 0 | 8192 bytes | 56 kB | 64 kB
user_exports | 7 | 40 kB | 16 kB | 56 kB
post_action_types | 8 | 40 kB | 16 kB | 56 kB
group_histories | 0 | 8192 bytes | 40 kB | 48 kB
group_requests | 0 | 8192 bytes | 32 kB | 40 kB
user_api_keys | 0 | 8192 bytes | 32 kB | 40 kB
theme_translation_overrides | 0 | 8192 bytes | 24 kB | 32 kB
backup_draft_posts | 0 | 8192 bytes | 24 kB | 32 kB
single_sign_on_records | 0 | 8192 bytes | 24 kB | 32 kB
oauth2_user_infos | 0 | 8192 bytes | 24 kB | 32 kB
group_mentions | 0 | 0 bytes | 24 kB | 24 kB
post_details | 0 | 8192 bytes | 16 kB | 24 kB
shared_drafts | 0 | 0 bytes | 24 kB | 24 kB
message_bus | 0 | 8192 bytes | 16 kB | 24 kB
tag_group_permissions | 0 | 0 bytes | 24 kB | 24 kB
ignored_users | 0 | 0 bytes | 24 kB | 24 kB
topic_embeds | 0 | 8192 bytes | 16 kB | 24 kB
topic_invites | 0 | 0 bytes | 24 kB | 24 kB
group_custom_fields | 0 | 8192 bytes | 16 kB | 24 kB
topic_timers | 0 | 0 bytes | 24 kB | 24 kB
github_user_infos | 0 | 0 bytes | 24 kB | 24 kB
custom_emojis | 0 | 8192 bytes | 16 kB | 24 kB
category_tags | 0 | 0 bytes | 24 kB | 24 kB
web_hook_events | 0 | 8192 bytes | 16 kB | 24 kB
anonymous_users | 0 | 0 bytes | 24 kB | 24 kB
backup_draft_topics | 0 | 0 bytes | 24 kB | 24 kB
category_tag_groups | 0 | 0 bytes | 16 kB | 16 kB
developers | 0 | 0 bytes | 16 kB | 16 kB
topic_groups | 0 | 0 bytes | 16 kB | 16 kB
web_hooks | 0 | 8192 bytes | 8192 bytes | 16 kB
embeddable_hosts | 0 | 8192 bytes | 8192 bytes | 16 kB
group_archived_messages | 0 | 0 bytes | 16 kB | 16 kB
reviewable_claimed_topics | 0 | 0 bytes | 16 kB | 16 kB
tag_group_memberships | 0 | 0 bytes | 16 kB | 16 kB
tag_groups | 0 | 8192 bytes | 8192 bytes | 16 kB
categories_web_hooks | 0 | 0 bytes | 8192 bytes | 8192 bytes
web_hook_event_types_hooks | 0 | 0 bytes | 8192 bytes | 8192 bytes
invited_groups | 0 | 0 bytes | 8192 bytes | 8192 bytes
user_field_options | 0 | 0 bytes | 8192 bytes | 8192 bytes
groups_web_hooks | 0 | 0 bytes | 8192 bytes | 8192 bytes
tags_web_hooks | 0 | 0 bytes | 8192 bytes | 8192 bytes
pg_stat_statements | 0 | 0 bytes | 0 bytes | 0 bytes
badge_posts | 0 | 0 bytes | 0 bytes | 0 bytes
2 Likes
Your notifications table is still massive. Compare, for instance, meta:
notifications | 1230140 | 316 MB | 581 MB | 897 MB
Try this (the first may briefly cause errors or read-only while it runs):
VACUUM FULL VERBOSE notifications
and
REINDEX DATABASE «yourdatabasename»
And then can you re-post the stats?
(looking at the other tables, your posts table also has massive indexes - on meta we have 3933 MB with only 674 MB of indices. If you can take a brief window of downtime I would recommend VACUUM FULL VERBOSE which will do the entire DB, followed by the reindex.)
9 Likes
Thanks a lot @supermathie 
Really appreciate it.
I ran VACUUM FULL VERBOSE; as you suggested and it appears that the issue is completely fixed now! 
Sidekiq is moving super fast again (I’ll keep monitoring) and the notifications table is so much smaller now.
Here are the db stats now:
table_name | row_estimate | table_size | index_size | total_size
--------------------------------------------------------------------------
posts | 10494274 | 4774 MB | 2279 MB | 7053 MB
post_search_data | 9714575 | 5767 MB | 916 MB | 6683 MB
email_logs | 13754610 | 1982 MB | 2546 MB | 4528 MB
topic_views | 24376588 | 1038 MB | 2693 MB | 3731 MB
skipped_email_logs | 16726146 | 1860 MB | 1792 MB | 3652 MB
user_actions | 10261730 | 750 MB | 1983 MB | 2733 MB
post_timings | 17263574 | 730 MB | 1259 MB | 1989 MB
post_stats | 10466804 | 682 MB | 448 MB | 1130 MB
user_auth_token_logs | 4429591 | 922 MB | 190 MB | 1112 MB
notifications | 2119967 | 679 MB | 419 MB | 1098 MB
topic_users | 4991144 | 464 MB | 321 MB | 785 MB
topics | 792564 | 432 MB | 266 MB | 697 MB
incoming_links | 5318560 | 306 MB | 388 MB | 694 MB
topic_search_data | 731054 | 355 MB | 75 MB | 430 MB
user_profile_views | 2504236 | 126 MB | 279 MB | 404 MB
topic_links | 1037616 | 152 MB | 214 MB | 366 MB
user_histories | 782653 | 219 MB | 143 MB | 362 MB
top_topics | 470222 | 78 MB | 273 MB | 351 MB
unsubscribe_keys | 1339739 | 162 MB | 151 MB | 313 MB
directory_items | 961457 | 71 MB | 191 MB | 262 MB
topic_link_clicks | 2590419 | 149 MB | 111 MB | 260 MB
topic_custom_fields | 456728 | 111 MB | 131 MB | 242 MB
post_custom_fields | 674208 | 88 MB | 118 MB | 206 MB
optimized_images | 551930 | 107 MB | 52 MB | 159 MB
stylesheet_cache | 1808 | 125 MB | 216 kB | 125 MB
post_uploads | 857416 | 36 MB | 74 MB | 110 MB
draft_sequences | 835660 | 46 MB | 48 MB | 94 MB
user_visits | 631932 | 31 MB | 60 MB | 91 MB
users | 194620 | 52 MB | 33 MB | 85 MB
post_revisions | 92193 | 76 MB | 6096 kB | 82 MB
topic_tags | 612147 | 35 MB | 26 MB | 61 MB
topic_allowed_users | 494981 | 28 MB | 32 MB | 60 MB
uploads | 77700 | 19 MB | 31 MB | 50 MB
email_tokens | 235071 | 26 MB | 23 MB | 50 MB
incoming_emails | 9073 | 42 MB | 2240 kB | 44 MB
plugin_store_rows | 167014 | 31 MB | 12 MB | 42 MB
user_uploads | 363421 | 18 MB | 23 MB | 42 MB
user_emails | 194618 | 15 MB | 21 MB | 36 MB
user_search_data | 194619 | 15 MB | 17 MB | 33 MB
post_reply_keys | 218642 | 16 MB | 16 MB | 32 MB
user_profiles | 194620 | 11 MB | 21 MB | 32 MB
reviewables | 43424 | 21 MB | 10 MB | 31 MB
user_options | 194620 | 26 MB | 4288 kB | 30 MB
user_stats | 194620 | 25 MB | 4288 kB | 29 MB
user_avatars | 194622 | 12 MB | 17 MB | 29 MB
group_users | 218803 | 14 MB | 14 MB | 28 MB
post_replies | 272241 | 16 MB | 12 MB | 27 MB
user_custom_fields | 188203 | 14 MB | 11 MB | 25 MB
user_badges | 166918 | 12 MB | 12 MB | 24 MB
search_logs | 214914 | 14 MB | 9488 kB | 23 MB
post_actions | 102196 | 7904 kB | 14 MB | 22 MB
user_associated_accounts | 12464 | 20 MB | 1504 kB | 22 MB
user_auth_tokens | 51622 | 12 MB | 7352 kB | 20 MB
scheduler_stats | 105630 | 12 MB | 2344 kB | 14 MB
incoming_referers | 38786 | 6264 kB | 7320 kB | 13 MB
drafts | 17781 | 7520 kB | 1104 kB | 8624 kB
reviewable_histories | 63641 | 4256 kB | 4224 kB | 8480 kB
reviewable_scores | 44953 | 5344 kB | 3000 kB | 8344 kB
user_archived_messages | 59312 | 3496 kB | 2640 kB | 6136 kB
given_daily_likes | 22436 | 976 kB | 1024 kB | 2000 kB
application_requests | 18341 | 832 kB | 848 kB | 1680 kB
quoted_posts | 12694 | 752 kB | 888 kB | 1640 kB
screened_emails | 5011 | 544 kB | 536 kB | 1080 kB
category_users | 5466 | 240 kB | 592 kB | 832 kB
topic_allowed_groups | 6607 | 288 kB | 504 kB | 792 kB
web_crawler_requests | 2622 | 312 kB | 352 kB | 664 kB
screened_ip_addresses | 4401 | 304 kB | 360 kB | 664 kB
category_tag_stats | 2620 | 160 kB | 448 kB | 608 kB
tag_users | 3587 | 216 kB | 352 kB | 568 kB
theme_fields | 94 | 280 kB | 32 kB | 312 kB
incoming_domains | 2139 | 128 kB | 176 kB | 304 kB
categories | 73 | 128 kB | 112 kB | 240 kB
push_subscriptions | 390 | 168 kB | 32 kB | 200 kB
schema_migration_details | 1015 | 112 kB | 88 kB | 200 kB
category_featured_topics | 404 | 72 kB | 120 kB | 192 kB
javascript_caches | 16 | 96 kB | 64 kB | 160 kB
user_open_ids | 293 | 64 kB | 64 kB | 128 kB
user_security_keys | 1 | 16 kB | 112 kB | 128 kB
email_change_requests | 393 | 56 kB | 64 kB | 120 kB
invites | 6 | 16 kB | 80 kB | 96 kB
schema_migrations | 1015 | 48 kB | 48 kB | 96 kB
category_custom_fields | 201 | 24 kB | 48 kB | 72 kB
screened_urls | 95 | 24 kB | 48 kB | 72 kB
poll_votes | 6 | 8192 bytes | 64 kB | 72 kB
groups | 9 | 16 kB | 48 kB | 64 kB
api_keys | 1 | 16 kB | 48 kB | 64 kB
tags | 70 | 16 kB | 48 kB | 64 kB
bookmarks | 0 | 8192 bytes | 56 kB | 64 kB
user_second_factors | 27 | 16 kB | 48 kB | 64 kB
badges | 50 | 32 kB | 32 kB | 64 kB
polls | 1 | 16 kB | 48 kB | 64 kB
poll_options | 10 | 16 kB | 48 kB | 64 kB
site_settings | 175 | 32 kB | 32 kB | 64 kB
muted_users | 41 | 8192 bytes | 48 kB | 56 kB
child_themes | 9 | 8192 bytes | 48 kB | 56 kB
user_warnings | 1 | 8192 bytes | 48 kB | 56 kB
watched_words | 17 | 16 kB | 32 kB | 48 kB
group_histories | 0 | 8192 bytes | 40 kB | 48 kB
translation_overrides | 1 | 16 kB | 32 kB | 48 kB
tag_search_data | 70 | 16 kB | 32 kB | 48 kB
color_scheme_colors | 40 | 16 kB | 32 kB | 48 kB
permalinks | 4 | 16 kB | 32 kB | 48 kB
onceoff_logs | 36 | 16 kB | 32 kB | 48 kB
category_search_data | 70 | 16 kB | 32 kB | 48 kB
group_requests | 0 | 8192 bytes | 32 kB | 40 kB
badge_types | 3 | 8192 bytes | 32 kB | 40 kB
themes | 14 | 8192 bytes | 32 kB | 40 kB
user_api_keys | 0 | 8192 bytes | 32 kB | 40 kB
category_groups | 16 | 8192 bytes | 32 kB | 40 kB
web_hook_event_types | 10 | 16 kB | 16 kB | 32 kB
color_schemes | 4 | 16 kB | 16 kB | 32 kB
badge_groupings | 5 | 16 kB | 16 kB | 32 kB
theme_settings | 17 | 16 kB | 16 kB | 32 kB
ar_internal_metadata | 1 | 16 kB | 16 kB | 32 kB
single_sign_on_records | 0 | 8192 bytes | 24 kB | 32 kB
oauth2_user_infos | 0 | 8192 bytes | 24 kB | 32 kB
backup_metadata | 6 | 16 kB | 16 kB | 32 kB
backup_draft_posts | 0 | 8192 bytes | 24 kB | 32 kB
remote_themes | 6 | 16 kB | 16 kB | 32 kB
theme_translation_overrides | 0 | 8192 bytes | 24 kB | 32 kB
user_fields | 2 | 16 kB | 16 kB | 32 kB
message_bus | 0 | 8192 bytes | 16 kB | 24 kB
backup_draft_topics | 0 | 0 bytes | 24 kB | 24 kB
anonymous_users | 0 | 0 bytes | 24 kB | 24 kB
category_tags | 0 | 0 bytes | 24 kB | 24 kB
github_user_infos | 0 | 0 bytes | 24 kB | 24 kB
group_custom_fields | 0 | 8192 bytes | 16 kB | 24 kB
ignored_users | 0 | 0 bytes | 24 kB | 24 kB
post_details | 0 | 8192 bytes | 16 kB | 24 kB
tag_group_permissions | 0 | 0 bytes | 24 kB | 24 kB
topic_embeds | 0 | 8192 bytes | 16 kB | 24 kB
topic_timers | 0 | 0 bytes | 24 kB | 24 kB
web_hook_events | 0 | 8192 bytes | 16 kB | 24 kB
custom_emojis | 0 | 8192 bytes | 16 kB | 24 kB
group_mentions | 0 | 0 bytes | 24 kB | 24 kB
post_action_types | 8 | 8192 bytes | 16 kB | 24 kB
shared_drafts | 0 | 0 bytes | 24 kB | 24 kB
topic_invites | 0 | 0 bytes | 24 kB | 24 kB
user_exports | 5 | 8192 bytes | 16 kB | 24 kB
developers | 0 | 0 bytes | 16 kB | 16 kB
web_hooks | 0 | 8192 bytes | 8192 bytes | 16 kB
tag_groups | 0 | 8192 bytes | 8192 bytes | 16 kB
topic_groups | 0 | 0 bytes | 16 kB | 16 kB
tag_group_memberships | 0 | 0 bytes | 16 kB | 16 kB
reviewable_claimed_topics | 0 | 0 bytes | 16 kB | 16 kB
group_archived_messages | 0 | 0 bytes | 16 kB | 16 kB
embeddable_hosts | 0 | 8192 bytes | 8192 bytes | 16 kB
category_tag_groups | 0 | 0 bytes | 16 kB | 16 kB
invited_groups | 0 | 0 bytes | 8192 bytes | 8192 bytes
groups_web_hooks | 0 | 0 bytes | 8192 bytes | 8192 bytes
categories_web_hooks | 0 | 0 bytes | 8192 bytes | 8192 bytes
web_hook_event_types_hooks | 0 | 0 bytes | 8192 bytes | 8192 bytes
tags_web_hooks | 0 | 0 bytes | 8192 bytes | 8192 bytes
user_field_options | 0 | 0 bytes | 8192 bytes | 8192 bytes
pg_stat_statements | 0 | 0 bytes | 0 bytes | 0 bytes
badge_posts | 0 | 0 bytes | 0 bytes | 0 bytes
Regarding the reindex task from the postgres console (connected to the discourse db) :
REINDEX DATABASE discourse;
I got this error:
ERROR: deadlock detected
DETAIL: Process 25728 waits for AccessExclusiveLock on relation 228206 of database 16384; blocked by process 20657.
Process 20657 waits for RowExclusiveLock on relation 22162 of database 16384; blocked by process 25728.
HINT: See server log for query details.
4 Likes
Returned back to slow mode unfortunately
Updated postgresql stats:
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 | 15731632.15 | 1381 | 4577.84 | 11391.48 | 26081.28 | 86.22
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 | 627853.90 | 198 | 37.30 | 3170.98 | 13383.02 | 3.44
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 | 615003.14 | 1390 | 163.82 | 442.45 | 1662.54 | 3.37
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)) | 232727.32 | 1389 | 66.42 | 167.55 | 414.41 | 1.28
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 | 195133.38 | 236313 | 0.01 | 0.83 | 46.13 | 1.07
SELECT COUNT(*) FROM "topics" WHERE ("topics"."deleted_at" IS NULL) | 111931.37 | 198 | 256.81 | 565.31 | 2079.29 | 0.61
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 | 59349.56 | 119915 | 0.02 | 0.49 | 78.83 | 0.33
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 | 58304.38 | 119915 | 0.02 | 0.49 | 24.00 | 0.32
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 | 51859.09 | 233858 | 0.03 | 0.22 | 32.47 | 0.28
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 | 44723.23 | 181 | 0.04 | 247.09 | 3643.52 | 0.25
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,$81,$82,$83)) AND (topics.pinned_at IS NULL OR topics.category_id <> $84) ORDER BY topics.bumped_at DESC LIMIT $85 | 39314.84 | 168 | 0.06 | 234.02 | 4435.63 | 0.22
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_r28, "topics"."spam_count" AS t0_r29, "topics"."pinned_at" AS t0_r30, "topics"."score" AS t0_r31, "topics"."percent_rank" AS t0_r32, "topics"."subtype" AS t0_r33, "topics"."slug" AS t0_r34, "topics"."deleted_by_id" AS t0_r35, "topics"."participant_count" AS t0_r36, "topics"."word_count" AS t0_r37, "topics"."excerpt" AS t0_r38, "topics"."pinned_globally" AS t0_r39, "topics"."pinned_until" AS t0_r40, "topics"."fancy_title" AS t0_r41, "topics"."highest_staff_post_number" AS t0_r42, "topics"."featured_link" AS t0_r43, "topics"."reviewable_score" AS t0_r44, "categories"."id" AS t1_r0, "categories"." | 23162.72 | 208 | 0.91 | 111.36 | 6567.87 | 0.13
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" | 17549.16 | 120957 | 0.07 | 0.15 | 35.69 | 0.10
UPDATE "categories" SET "latest_post_id" = $1 WHERE "categories"."id" = $2 | 17141.08 | 547 | 0.03 | 31.34 | 8737.14 | 0.09
SELECT "posts".* FROM "posts" WHERE ("posts"."deleted_at" IS NULL) AND "posts"."topic_id" = $1 AND "posts"."post_type" = $2 AND "posts"."user_deleted" = $3 ORDER BY score desc nulls last LIMIT $4 | 15294.23 | 13810 | 0.04 | 1.11 | 12.88 | 0.08
1 Like
You can just keep running that until it works, or stop the app server while it runs.
That digest query is killing you - you’ll need to disable or throttle back digests for now.
Can you post an EXPLAIN ANALYZE of that problem query?
You need to look at the users’ settings here - the results of the EXPLAIN ANALYZE should guide you.
6 Likes
I just want to add that doing a VACUUM FULL VERBOSE <big table> and REINDEX DATABASE <db_name> worked for us to speed up our requests. Index size dropped and our renders are fast again.
before
table_name | row_estimate | table_size | index_size | total_size
--------------------------------------------------------------------------
post_uploads | 52450 | 619 MB | 1255 MB | 1873 MB
post_custom_fields | 513 | 1246 MB | 9096 kB | 1254 MB
posts | 9969 | 256 MB | 13 MB | 269 MB
uploads | 45818 | 92 MB | 88 MB | 179 MB
stylesheet_cache | 1807 | 122 MB | 296 kB | 123 MB
after
table_name | row_estimate | table_size | index_size | total_size
--------------------------------------------------------------------------
post_custom_fields | 5482 | 1238 MB | 1072 kB | 1239 MB
post_uploads | 52483 | 622 MB | 12 MB | 635 MB
posts | 9969 | 128 MB | 2424 kB | 130 MB
stylesheet_cache | 1839 | 122 MB | 232 kB | 122 MB
uploads | 52305 | 92 MB | 30 MB | 122 MB
request performance
Not sure if this has to do with the rebakes since we did a lot of these lately by enabling some settings and doing migrations.
Thanks!
8 Likes
In some cases like yours where enormous volumes of data changed I recommend just doing a full backup / restore, it will totally rebuild the db
7 Likes
Ok so following up with the long term results here. The sidekiq queue has consistently been running fast since running what you suggested:
VACUUM FULL VERBOSE;
and
REINDEX DATABASE;
REINDEX DATABASE; did go through without errors after running it a few times.
Really appreciate all the help 
5 Likes
Is backup restore going to do something that vacuum and rebuild indexes won’t? But it’s much easier, so it’s probably what I should have done, though I may now have some useful postgres skills…
1 Like
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.