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

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 :beers: Really appreciate it.

I ran VACUUM FULL VERBOSE; as you suggested and it appears that the issue is completely fixed now! :content: 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 :firstworldproblem:


anditsgone

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 :slight_smile: :pray:

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.