Очень медленная проблема Sidekiq с большой очередью из-за огромного количества непрочитанных уведомлений пользователей

[markersocial]

Спасибо @supermathie — вывод команды rake db:stats приведён ниже. Я также выполню vacuum analyze и сообщу о результатах.

table_name                  | row_estimate | table_size | index_size | total_size
--------------------------------------------------------------------------
notifications               | 1779605      | 16 ГБ      | 112 ГБ     | 128 ГБ
posts                       | 10409870     | 5102 МБ    | 16 ГБ      | 21 ГБ
post_search_data            | 9493434      | 5741 МБ    | 1178 МБ    | 6919 МБ
top_topics                  | 468690       | 110 МБ     | 4773 МБ    | 4883 МБ
email_logs                  | 13731364     | 1981 МБ    | 2861 МБ    | 4842 МБ
topic_views                 | 23909392     | 1109 МБ    | 2821 МБ    | 3930 МБ
skipped_email_logs          | 16700245     | 1862 МБ    | 1973 МБ    | 3835 МБ
user_actions                | 10063331     | 752 МБ     | 2779 МБ    | 3531 МБ
post_timings                | 16937276     | 749 МБ     | 1556 МБ    | 2305 МБ
topics                      | 790818       | 489 МБ     | 1799 МБ    | 2288 МБ
user_auth_token_logs        | 4103959      | 978 МБ     | 244 МБ     | 1221 МБ
incoming_emails             | 8451         | 1112 МБ    | 18 МБ      | 1130 МБ
post_stats                  | 10220498     | 683 МБ     | 446 МБ     | 1129 МБ
topic_users                 | 4802350      | 492 МБ     | 369 МБ     | 860 МБ
users                       | 194463       | 67 МБ      | 698 МБ     | 765 МБ
incoming_links              | 5315556      | 306 МБ     | 394 МБ     | 700 МБ
directory_items             | 960523       | 74 МБ      | 587 МБ     | 661 МБ
unsubscribe_keys            | 1112989      | 196 МБ     | 297 МБ     | 493 МБ
topic_search_data           | 718454       | 357 МБ     | 83 МБ      | 440 МБ
topic_links                 | 976001       | 156 МБ     | 275 МБ     | 431 МБ
user_profile_views          | 2493043      | 130 МБ     | 294 МБ     | 424 МБ
user_histories              | 747481       | 220 МБ     | 166 МБ     | 387 МБ
topic_custom_fields         | 448070       | 111 МБ     | 155 МБ     | 266 МБ
topic_link_clicks           | 2554179      | 151 МБ     | 113 МБ     | 264 МБ
post_custom_fields          | 674805       | 103 МБ     | 123 МБ     | 226 МБ
user_visits                 | 627095       | 36 МБ      | 161 МБ     | 196 МБ
optimized_images            | 551150       | 121 МБ     | 69 МБ      | 189 МБ
post_uploads                | 808927       | 41 МБ      | 127 МБ     | 168 МБ
stylesheet_cache            | 1801         | 154 МБ     | 328 КБ     | 154 МБ
user_auth_tokens            | 75096        | 44 МБ      | 63 МБ      | 107 МБ
draft_sequences             | 834116       | 48 МБ      | 52 МБ      | 100 МБ
post_revisions              | 89088        | 76 МБ      | 7304 КБ    | 83 МБ
post_reply_keys             | 221484       | 32 МБ      | 35 МБ      | 67 МБ
topic_tags                  | 592744       | 36 МБ      | 28 МБ      | 64 МБ
topic_allowed_users         | 476226       | 29 МБ      | 33 МБ      | 62 МБ
uploads                     | 74026        | 20 МБ      | 41 МБ      | 61 МБ
email_tokens                | 234846       | 27 МБ      | 32 МБ      | 58 МБ
user_badges                 | 130677       | 22 МБ      | 34 МБ      | 56 МБ
user_stats                  | 194451       | 38 МБ      | 7904 КБ    | 46 МБ
user_uploads                | 359469       | 20 МБ      | 26 МБ      | 46 МБ
plugin_store_rows           | 162014       | 31 МБ      | 13 МБ      | 44 МБ
user_emails                 | 186562       | 16 МБ      | 23 МБ      | 39 МБ
post_replies                | 192842       | 26 МБ      | 12 МБ      | 38 МБ
user_profiles               | 194156       | 12 МБ      | 24 МБ      | 35 МБ
reviewables                 | 45869        | 21 МБ      | 12 МБ      | 33 МБ
drafts                      | 17429        | 8224 КБ    | 25 МБ      | 33 МБ
user_search_data            | 189320       | 16 МБ      | 16 МБ      | 32 МБ
user_options                | 189057       | 28 МБ      | 4376 КБ    | 32 МБ
user_avatars                | 194998       | 12 МБ      | 19 МБ      | 31 МБ
group_users                 | 218600       | 15 МБ      | 15 МБ      | 30 МБ
scheduler_stats             | 95437        | 23 МБ      | 3968 КБ    | 27 МБ
post_actions                | 101213       | 8040 КБ    | 18 МБ      | 26 МБ
user_custom_fields          | 186893       | 14 МБ      | 12 МБ      | 26 МБ
search_logs                 | 208901       | 14 МБ      | 10 МБ      | 24 МБ
user_associated_accounts    | 10523        | 21 МБ      | 1952 КБ    | 23 МБ
reviewable_scores           | 43728        | 10152 КБ   | 7000 КБ    | 17 МБ
incoming_referers           | 38785        | 6296 КБ    | 7824 КБ    | 14 МБ
reviewable_histories        | 63567        | 4288 КБ    | 4352 КБ    | 8640 КБ
user_archived_messages      | 58362        | 3552 КБ    | 2800 КБ    | 6352 КБ
categories                  | 73           | 2088 КБ    | 728 КБ     | 2816 КБ
application_requests        | 18330        | 1328 КБ    | 848 КБ     | 2176 КБ
given_daily_likes           | 22399        | 1016 КБ    | 1136 КБ    | 2152 КБ
quoted_posts                | 12205        | 808 КБ     | 920 КБ     | 1728 КБ
screened_emails             | 5011         | 664 КБ     | 688 КБ     | 1352 КБ
category_tag_stats          | 2600         | 264 КБ     | 1064 КБ    | 1328 КБ
screened_ip_addresses       | 4401         | 648 КБ     | 408 КБ     | 1056 КБ
web_crawler_requests        | 2544         | 408 КБ     | 592 КБ     | 1000 КБ
category_users              | 5465         | 272 КБ     | 672 КБ     | 944 КБ
topic_allowed_groups        | 6577         | 320 КБ     | 544 КБ     | 864 КБ
category_featured_topics    | 404          | 152 КБ     | 592 КБ     | 744 КБ
tag_users                   | 3563         | 248 КБ     | 424 КБ     | 672 КБ
theme_fields                | 94           | 440 КБ     | 32 КБ      | 472 КБ
incoming_domains            | 2139         | 160 КБ     | 176 КБ     | 336 КБ
javascript_caches           | 16           | 216 КБ     | 64 КБ      | 280 КБ
schema_migration_details    | 1015         | 144 КБ     | 88 КБ      | 232 КБ
push_subscriptions          | 387          | 200 КБ     | 16 КБ      | 216 КБ
tags                        | 70           | 136 КБ     | 48 КБ      | 184 КБ
user_open_ids               | 293          | 96 КБ      | 64 КБ      | 160 КБ
user_security_keys          | 1            | 48 КБ      | 112 КБ     | 160 КБ
invites                     | 6            | 48 КБ      | 80 КБ      | 128 КБ
schema_migrations           | 1015         | 80 КБ      | 48 КБ      | 128 КБ
email_change_requests       | 393          | 88 КБ      | 32 КБ      | 120 КБ
badges                      | 50           | 80 КБ      | 32 КБ      | 112 КБ
api_keys                    | 1            | 64 КБ      | 48 КБ      | 112 КБ
screened_urls               | 95           | 56 КБ      | 48 КБ      | 104 КБ
category_custom_fields      | 201          | 56 КБ      | 48 КБ      | 104 КБ
poll_votes                  | 6            | 40 КБ      | 64 КБ      | 104 КБ
site_settings               | 175          | 72 КБ      | 32 КБ      | 104 КБ
user_second_factors         | 27           | 48 КБ      | 48 КБ      | 96 КБ
poll_options                | 10           | 48 КБ      | 48 КБ      | 96 КБ
groups                      | 9            | 48 КБ      | 48 КБ      | 96 КБ
polls                       | 1            | 48 КБ      | 48 КБ      | 96 КБ
muted_users                 | 41           | 40 КБ      | 48 КБ      | 88 КБ
user_warnings               | 1            | 40 КБ      | 48 КБ      | 88 КБ
child_themes                | 9            | 40 КБ      | 48 КБ      | 88 КБ
permalinks                  | 4            | 48 КБ      | 32 КБ      | 80 КБ
onceoff_logs                | 36           | 48 КБ      | 32 КБ      | 80 КБ
color_scheme_colors         | 40           | 48 КБ      | 32 КБ      | 80 КБ
category_search_data        | 70           | 48 КБ      | 32 КБ      | 80 КБ
watched_words               | 22           | 48 КБ      | 32 КБ      | 80 КБ
translation_overrides       | 1            | 48 КБ      | 32 КБ      | 80 КБ
tag_search_data             | 70           | 48 КБ      | 32 КБ      | 80 КБ
badge_types                 | 3            | 40 КБ      | 32 КБ      | 72 КБ
category_groups             | 16           | 40 КБ      | 32 КБ      | 72 КБ
theme_settings              | 17           | 56 КБ      | 16 КБ      | 72 КБ
themes                      | 14           | 40 КБ      | 32 КБ      | 72 КБ
badge_groupings             | 5            | 48 КБ      | 16 КБ      | 64 КБ
user_fields                 | 2            | 48 КБ      | 16 КБ      | 64 КБ
backup_metadata             | 6            | 48 КБ      | 16 КБ      | 64 КБ
color_schemes               | 4            | 48 КБ      | 16 КБ      | 64 КБ
web_hook_event_types        | 10           | 48 КБ      | 16 КБ      | 64 КБ
ar_internal_metadata        | 1            | 48 КБ      | 16 КБ      | 64 КБ
remote_themes               | 6            | 48 КБ      | 16 КБ      | 64 КБ
bookmarks                   | 0            | 8192 байт  | 56 КБ      | 64 КБ
user_exports                | 7            | 40 КБ      | 16 КБ      | 56 КБ
post_action_types           | 8            | 40 КБ      | 16 КБ      | 56 КБ
group_histories             | 0            | 8192 байт  | 40 КБ      | 48 КБ
group_requests              | 0            | 8192 байт  | 32 КБ      | 40 КБ
user_api_keys               | 0            | 8192 байт  | 32 КБ      | 40 КБ
theme_translation_overrides | 0            | 8192 байт  | 24 КБ      | 32 КБ
backup_draft_posts          | 0            | 8192 байт  | 24 КБ      | 32 КБ
single_sign_on_records      | 0            | 8192 байт  | 24 КБ      | 32 КБ
oauth2_user_infos           | 0            | 8192 байт  | 24 КБ      | 32 КБ
group_mentions              | 0            | 0 байт     | 24 КБ      | 24 КБ
post_details                | 0            | 8192 байт  | 16 КБ      | 24 КБ
shared_drafts               | 0            | 0 байт     | 24 КБ      | 24 КБ
message_bus                 | 0            | 8192 байт  | 16 КБ      | 24 КБ
tag_group_permissions       | 0            | 0 байт     | 24 КБ      | 24 КБ
ignored_users               | 0            | 0 байт     | 24 КБ      | 24 КБ
topic_embeds                | 0            | 8192 байт  | 16 КБ      | 24 КБ
topic_invites               | 0            | 0 байт     | 24 КБ      | 24 КБ
group_custom_fields         | 0            | 8192 байт  | 16 КБ      | 24 КБ
topic_timers                | 0            | 0 байт     | 24 КБ      | 24 КБ
github_user_infos           | 0            | 0 байт     | 24 КБ      | 24 КБ
custom_emojis               | 0            | 8192 байт  | 16 КБ      | 24 КБ
category_tags               | 0            | 0 байт     | 24 КБ      | 24 КБ
web_hook_events             | 0            | 8192 байт  | 16 КБ      | 24 КБ
anonymous_users             | 0            | 0 байт     | 24 КБ      | 24 КБ
backup_draft_topics         | 0            | 0 байт     | 24 КБ      | 24 КБ
category_tag_groups         | 0            | 0 байт     | 16 КБ      | 16 КБ
developers                  | 0            | 0 байт     | 16 КБ      | 16 КБ
topic_groups                | 0            | 0 байт     | 16 КБ      | 16 КБ
web_hooks                   | 0            | 8192 байт  | 8192 байт  | 16 КБ
embeddable_hosts            | 0            | 8192 байт  | 8192 байт  | 16 КБ
group_archived_messages     | 0            | 0 байт     | 16 КБ      | 16 КБ
reviewable_claimed_topics   | 0            | 0 байт     | 16 КБ      | 16 КБ
tag_group_memberships       | 0            | 0 байт     | 16 КБ      | 16 КБ
tag_groups                  | 0            | 8192 байт  | 8192 байт  | 16 КБ
categories_web_hooks        | 0            | 0 байт     | 8192 байт  | 8192 байт
web_hook_event_types_hooks  | 0            | 0 байт     | 8192 байт  | 8192 байт
invited_groups              | 0            | 0 байт     | 8192 байт  | 8192 байт
user_field_options          | 0            | 0 байт     | 8192 байт  | 8192 байт
groups_web_hooks            | 0            | 0 байт     | 8192 байт  | 8192 байт
tags_web_hooks              | 0            | 0 байт     | 8192 байт  | 8192 байт
pg_stat_statements          | 0            | 0 байт     | 0 байт     | 0 байт
badge_posts                 | 0            | 0 байт     | 0 байт     | 0 байт

[supermathie (Michael Brown)]

Ваша таблица уведомлений всё ещё огромна. Сравните, например, метрики:

notifications               | 1230140      | 316 MB     | 581 MB     | 897 MB    

Попробуйте следующее (первая команда может временно вызвать ошибки или перевести базу в режим только для чтения во время выполнения):

VACUUM FULL VERBOSE notifications
и
REINDEX DATABASE «yourdatabasename»

После этого, пожалуйста, снова опубликуйте статистику.

(Судя по другим таблицам, ваша таблица posts также имеет огромные индексы — в meta у нас 3933 MB при индексах всего 674 MB. Если вы сможете выделить короткое окно простоя, я рекомендую выполнить VACUUM FULL VERBOSE, что оптимизирует всю базу данных, а затем выполнить перестроение индексов.)

[markersocial]

Огромное спасибо @supermathie Очень ценим.

Я выполнил VACUUM FULL VERBOSE;, как вы и советовали, и, похоже, проблема полностью решена! Sidekiq снова работает супер быстро (я буду продолжать мониторинг), а таблица уведомлений стала гораздо меньше.

Вот текущая статистика базы данных:

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

Что касается задачи реиндексации из консоли PostgreSQL (подключённой к базе данных Discourse):
REINDEX DATABASE discourse;

Я получил следующую ошибку:

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.

[markersocial]

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

[supermathie (Michael Brown)]

@markersocial

REINDEX DATABASE discourse;

Я получил эту ошибку:

ERROR: deadlock detected

Вы можете просто продолжать запускать эту команду, пока она не сработает, или остановить сервер приложения во время её выполнения.

Этот запрос сводки вас убивает — вам нужно временно отключить сводки или снизить их частоту.

Можете ли вы опубликовать результат EXPLAIN ANALYZE для этого проблемного запроса?

@Falco

У вас есть пользователи, которые следят за категориями, но не замечают количество уведомлений?

Вам нужно проверить настройки пользователей здесь — результаты EXPLAIN ANALYZE должны дать вам направление.

[eatcodetravel (Orlando Del Aguila)]

Хочу добавить, что выполнение команд VACUUM FULL VERBOSE <большая_таблица> и REINDEX DATABASE <имя_базы_данных> помогло нам ускорить запросы. Размер индексов уменьшился, и рендеринг снова стал быстрым.

до

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

после

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

производительность запросов

Не уверен, связано ли это с повторной генерацией (rebakes), так как в последнее время мы делали их много, включив некоторые настройки и выполнив миграции.

Спасибо!

[sam (Sam Saffron)]

В некоторых случаях, как в вашем, когда изменились огромные объемы данных, я рекомендую просто сделать полное резервное копирование и восстановление — это полностью пересоздаст базу данных.

[markersocial]

Отлично, вот итоги по долгосрочным результатам. Очередь Sidekiq стабильно работает быстро после выполнения ваших рекомендаций:

VACUUM FULL VERBOSE;
и
REINDEX DATABASE;

Команда REINDEX DATABASE; выполнилась без ошибок после нескольких запусков.

Огромное спасибо за всю помощь

[pfaffman (Jay Pfaffman)]

Выполнит ли восстановление из резервной копии какие-то действия, которые не сделают VACUUM и перестроение индексов? Но это гораздо проще, так что, вероятно, именно это мне и следовало сделать, хотя теперь у меня, возможно, появились полезные навыки работы с PostgreSQL…