# Очень медленная проблема Sidekiq с большой очередью из-за огромного количества непрочитанных уведомлений пользователей
**URL:** https://meta.discourse.org/t/very-slow-sidekiq-issue-with-large-queue-due-to-massive-numbers-of-unread-user-notifications/140716
**Category:** Self-hosting
**Created:** [05.Февраль.2020 12:07:57 UTC](https://meta.discourse.org/t/very-slow-sidekiq-issue-with-large-queue-due-to-massive-numbers-of-unread-user-notifications/140716 "2020-02-05T12:07:57Z")
**Posts on this page:** 10
**Page:** 4
### Author:  [@markersocial](https://meta.discourse.org/u/markersocial)
#### Post date: [25.Февраль.2020 16:37:51 UTC](https://meta.discourse.org/t/very-slow-sidekiq-issue-with-large-queue-due-to-massive-numbers-of-unread-user-notifications/140716/61 "2020-02-25T16:37:51Z")
Спасибо @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 байт
```
---
### Author:  [@supermathie](https://meta.discourse.org/u/supermathie)
#### Post date: [25.Февраль.2020 16:56:34 UTC](https://meta.discourse.org/t/very-slow-sidekiq-issue-with-large-queue-due-to-massive-numbers-of-unread-user-notifications/140716/62 "2020-02-25T16:56:34Z")
Ваша таблица уведомлений всё ещё _огромна_. Сравните, например, метрики:
```plaintext
notifications | 1230140 | 316 MB | 581 MB | 897 MB
```
Попробуйте следующее (первая команда может временно вызвать ошибки или перевести базу в режим только для чтения во время выполнения):
`VACUUM FULL VERBOSE notifications`
и
`REINDEX DATABASE «yourdatabasename»`
После этого, пожалуйста, снова опубликуйте статистику.
(Судя по другим таблицам, ваша таблица `posts` также имеет огромные индексы — в meta у нас `3933 MB` при индексах всего `674 MB`. Если вы сможете выделить короткое окно простоя, я рекомендую выполнить `VACUUM FULL VERBOSE`, что оптимизирует всю базу данных, а затем выполнить перестроение индексов.)
---
### Author:  [@markersocial](https://meta.discourse.org/u/markersocial)
#### Post date: [26.Февраль.2020 11:30:44 UTC](https://meta.discourse.org/t/very-slow-sidekiq-issue-with-large-queue-due-to-massive-numbers-of-unread-user-notifications/140716/63 "2020-02-26T11:30:44Z")
Огромное спасибо @supermathie 🍻 Очень ценим.
Я выполнил `VACUUM FULL VERBOSE;`, как вы и советовали, и, похоже, проблема полностью решена! :content: 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.
---
### Author:  [@markersocial](https://meta.discourse.org/u/markersocial)
#### Post date: [26.Февраль.2020 13:11:11 UTC](https://meta.discourse.org/t/very-slow-sidekiq-issue-with-large-queue-due-to-massive-numbers-of-unread-user-notifications/140716/64 "2020-02-26T13:11:11Z")
К сожалению, вернулся в медленный режим :firstworldproblem:
Обновленные статистические данные PostgreSQL:
```
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
```
---
### Author:  [@supermathie](https://meta.discourse.org/u/supermathie)
#### Post date: [26.Февраль.2020 17:18:56 UTC](https://meta.discourse.org/t/very-slow-sidekiq-issue-with-large-queue-due-to-massive-numbers-of-unread-user-notifications/140716/66 "2020-02-26T17:18:56Z")
> [@markersocial](#):
>
> `REINDEX DATABASE discourse;`
>
> Я получил эту ошибку:
>
> ERROR: deadlock detected
Вы можете просто продолжать запускать эту команду, пока она не сработает, или остановить сервер приложения во время её выполнения.
Этот запрос сводки вас убивает — вам нужно временно отключить сводки или снизить их частоту.
Можете ли вы опубликовать результат `EXPLAIN ANALYZE` для этого проблемного запроса?
> [@Falco](#):
>
> У вас есть пользователи, которые следят за категориями, но не замечают количество уведомлений?
Вам нужно проверить настройки пользователей здесь — результаты `EXPLAIN ANALYZE` должны дать вам направление.
---
### Author:  [@eatcodetravel](https://meta.discourse.org/u/eatcodetravel)
#### Post date: [26.Февраль.2020 20:07:48 UTC](https://meta.discourse.org/t/very-slow-sidekiq-issue-with-large-queue-due-to-massive-numbers-of-unread-user-notifications/140716/70 "2020-02-26T20:07:48Z")
Хочу добавить, что выполнение команд `VACUUM FULL VERBOSE <большая_таблица>` и `REINDEX DATABASE <имя_базы_данных>` помогло нам ускорить запросы. Размер индексов уменьшился, и рендеринг снова стал быстрым.
### до
```plaintext
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
```
### после
```plaintext
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), так как в последнее время мы делали их много, включив некоторые настройки и выполнив миграции.
Спасибо!
---
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [26.Февраль.2020 20:29:15 UTC](https://meta.discourse.org/t/very-slow-sidekiq-issue-with-large-queue-due-to-massive-numbers-of-unread-user-notifications/140716/71 "2020-02-26T20:29:15Z")
В некоторых случаях, как в вашем, когда изменились огромные объемы данных, я рекомендую просто сделать полное резервное копирование и восстановление — это полностью пересоздаст базу данных.
---
### Author:  [@markersocial](https://meta.discourse.org/u/markersocial)
#### Post date: [10.Апрель.2020 05:34:00 UTC](https://meta.discourse.org/t/very-slow-sidekiq-issue-with-large-queue-due-to-massive-numbers-of-unread-user-notifications/140716/86 "2020-04-10T05:34:00Z")
Отлично, вот итоги по долгосрочным результатам. Очередь Sidekiq стабильно работает быстро после выполнения ваших рекомендаций:
> VACUUM FULL VERBOSE;
> и
> REINDEX DATABASE;
Команда `REINDEX DATABASE;` выполнилась без ошибок после нескольких запусков.
Огромное спасибо за всю помощь 🙂 🙏
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [11.Апрель.2020 11:09:02 UTC](https://meta.discourse.org/t/very-slow-sidekiq-issue-with-large-queue-due-to-massive-numbers-of-unread-user-notifications/140716/87 "2020-04-11T11:09:02Z")
Выполнит ли восстановление из резервной копии какие-то действия, которые не сделают VACUUM и перестроение индексов? Но это гораздо проще, так что, вероятно, именно это мне и следовало сделать, хотя теперь у меня, возможно, появились полезные навыки работы с PostgreSQL…
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [11.Май.2020 11:09:07 UTC](https://meta.discourse.org/t/very-slow-sidekiq-issue-with-large-queue-due-to-massive-numbers-of-unread-user-notifications/140716/88 "2020-05-11T11:09:07Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.
[Предыдущая страница](https://meta.discourse.org/t/very-slow-sidekiq-issue-with-large-queue-due-to-massive-numbers-of-unread-user-notifications/140716.md?page=3)