Problème Sidekiq très lent avec une grande file d'attente dû à un nombre massif de notifications utilisateur non lues

markersocial · Février 25, 2020, 4:37

Merci @supermathie - voici la sortie de rake db:stats.

Je lancerai également vacuum analyze et vous tiendrai au courant.

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

supermathie · Février 25, 2020, 4:56

Votre table de notifications est toujours énorme. Comparez, par exemple, avec meta :

notifications               | 1230140      | 316 Mo     | 581 Mo     | 897 Mo

Essayez ceci (la première commande peut brièvement provoquer des erreurs ou passer en lecture seule pendant son exécution) :

VACUUM FULL VERBOSE notifications
et
REINDEX DATABASE «votrenomdebasededonnées»

Ensuite, pouvez-vous publier à nouveau les statistiques ?

(Au vu des autres tables, votre table posts possède également des index massifs — sur meta, nous avons 3933 Mo avec seulement 674 Mo d’index. Si vous pouvez prévoir une courte fenêtre de maintenance, je recommande VACUUM FULL VERBOSE, qui traitera l’ensemble de la base de données, suivi d’une réindexation.)

markersocial · Février 26, 2020, 11:30

Merci beaucoup @supermathie Je l’apprécie vraiment.

J’ai exécuté VACUUM FULL VERBOSE; comme vous l’avez suggéré et il semble que le problème soit complètement résolu maintenant ! Sidekiq tourne à nouveau super vite (je continuerai à surveiller) et la table des notifications est beaucoup plus petite.

Voici les statistiques de la base de données maintenant :

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

Concernant la tâche de réindexation depuis la console postgres (connectée à la base de données Discourse) :
REINDEX DATABASE discourse;

J’ai obtenu cette erreur :

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 · Février 26, 2020, 1:11

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 · Février 26, 2020, 5:18

Vous pouvez simplement continuer à exécuter cette commande jusqu’à ce qu’elle fonctionne, ou arrêter le serveur d’application pendant son exécution.

Cette requête de digest vous met à genoux — vous devrez désactiver ou limiter les digests pour le moment.

Pouvez-vous publier un EXPLAIN ANALYZE de cette requête problématique ?

Vous devez examiner les paramètres des utilisateurs ici — les résultats de l’EXPLAIN ANALYZE devraient vous guider.

eatcodetravel · Février 26, 2020, 8:07

Je tiens simplement à ajouter que l’exécution de VACUUM FULL VERBOSE <big table> et REINDEX DATABASE <db_name> a permis d’accélérer nos requêtes. La taille des index a diminué et nos rendus sont à nouveau rapides.

avant

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

après

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

performance des requêtes

Je ne sais pas si cela est lié aux rebakes, car nous en avons effectué beaucoup récemment en activant certaines paramètres et en exécutant des migrations.

Merci !

sam · Février 26, 2020, 8:29

Dans certains cas comme le vôtre, où d’énormes volumes de données ont été modifiés, je recommande de simplement effectuer une sauvegarde et une restauration complètes ; cela reconstruira entièrement la base de données.

markersocial · Avril 10, 2020, 5:34

D’accord, pour faire suite aux résultats à long terme ici. La file d’attente Sidekiq tourne constamment rapidement depuis que nous avons appliqué vos suggestions :

VACUUM FULL VERBOSE;
et
REINDEX DATABASE;

REINDEX DATABASE; s’est exécuté sans erreur après plusieurs tentatives.

Merci infiniment pour toute l’aide apportée

pfaffman · Avril 11, 2020, 11:09

Est-ce que la restauration de sauvegarde fera quelque chose que le VACUUM et la reconstruction des index ne feront pas ? Mais c’est beaucoup plus simple, donc c’est probablement ce que j’aurais dû faire, même si j’ai maintenant acquis quelques compétences utiles sur PostgreSQL…

Sujet		Réponses	Vues
Slow Sidekiq + Postmaster using 95%+ CPU (32 cores) after Postgresql Version Upgrade Self-hosting server-resources	22	3188	Mai 29, 2020
Notifications are coming again even after reading them [Private Topics plugin] Support notifications	19	1449	Décembre 1, 2023
Could sidekiq queue be reason for 500 errors? Self-hosting server-resources	30	4059	Juin 13, 2018
Long-Running Sidekiq Jobs Feature	21	1773	Décembre 24, 2020
Sidekiq has a lot of errors and queued jobs Support	18	1289	Mars 1, 2024

Problème Sidekiq très lent avec une grande file d'attente dû à un nombre massif de notifications utilisateur non lues

avant

après

performance des requêtes

Sujets connexes