Nous avons récemment supprimé TOUTES les mentions et depuis lors, la charge du processeur est tombée à 15-20 %, donc plutôt bien, mais par conséquent, la requête ci-dessus ne renverra aucun résultat. (ou seulement quelques-uns puisque nous l’avons supprimée)
1 « J'aime »
La requête ci-dessus devrait renvoyer des résultats dans tous les cas, elle commence par EXPLAIN VERBOSE donc elle renvoie un plan d’exécution. Quand vous aurez le temps, pourriez-vous s’il vous plaît copier-coller la requête exacte de mon message, l’exécuter et partager les résultats ?
Limit (coût=123.37..22204.48 lignes=1000 largeur=40)
Sortie : users.id, (array_agg(ARRAY[uccm.id, c_msg.id])), uccm.user_id
-> GroupAggregate (coût=123.37..339399.59 lignes=15365 largeur=40)
Sortie : users.id, array_agg(ARRAY[uccm.id, c_msg.id]), uccm.user_id
Clé de groupe : users.id, uccm.user_id
-> Nested Loop (coût=123.37..336077.65 lignes=417317 largeur=24)
Sortie : users.id, uccm.user_id, uccm.id, c_msg.id
Inner Unique : true
-> Merge Join (coût=123.23..271629.01 lignes=417317 largeur=28)
Sortie : users.id, group_users.group_id, uccm.id, uccm.user_id, c_msg.id
Condition de fusion : (user_options.user_id = group_users.user_id)
-> Nested Loop Left Join (coût=121.72..221430.16 lignes=132835 largeur=28)
Sortie : users.id, user_options.user_id, uccm.id, uccm.user_id, c_msg.id
Filtre : ((uccm.user_id = c_mentions.user_id) ET (uccm.following IS TRUE) ET ((cc.chatable_type)::text = 'Category'::text)) OU ((cc.chatable_type)::text = 'DirectMessage'::text))
-> Nested Loop (coût=121.32..149930.76 lignes=132967 largeur=42)
Sortie : users.id, user_options.user_id, uccm.id, uccm.user_id, uccm.following, cc.chatable_type, c_msg.id
Filtre de jointure : ((c_msg.user_id <> users.id) ET (cc.id = c_msg.chat_channel_id))
-> Nested Loop (coût=120.91..57868.92 lignes=21066 largeur=54)
Sortie : users.id, user_options.user_id, uccm.id, uccm.user_id, uccm.chat_channel_id, uccm.last_read_message_id, uccm.last_unread_mention_when_emailed_id, uccm.following, cc.id, cc.chatable_type
Inner Unique : true
-> Nested Loop (coût=120.63..51231.73 lignes=21066 largeur=33)
Sortie : users.id, user_options.user_id, uccm.id, uccm.user_id, uccm.chat_channel_id, uccm.last_read_message_id, uccm.last_unread_mention_when_emailed_id, uccm.following
Inner Unique : true
-> Merge Join (coût=120.21..36835.88 lignes=21096 largeur=29)
Sortie : users.id, uccm.id, uccm.user_id, uccm.chat_channel_id, uccm.last_read_message_id, uccm.last_unread_mention_when_emailed_id, uccm.following
Inner Unique : true
Condition de fusion : (uccm.user_id = users.id)
-> Index Scan using user_chat_channel_unique_memberships on public.user_chat_channel_memberships uccm (coût=0.41..12177.25 lignes=21618 largeur=25)
Sortie : uccm.id, uccm.user_id, uccm.chat_channel_id, uccm.last_read_message_id, uccm.following, uccm.muted, uccm.desktop_notification_level, uccm.mobile_notification_level, uccm.created_at, uccm.updated_at, uccm.last_unread_mention_when_emailed_id, uccm.join_mode, uccm.last_viewed_at
-> Index Scan using users_pkey on public.users (coût=0.42..23823.53 lignes=237544 largeur=4)
Sortie : users.id
Filtre : (users.last_seen_at < '2023-11-27 08:00:00'::timestamp without time zone)
-> Index Scan using index_user_options_on_user_id on public.user_options (coût=0.42..0.68 lignes=1 largeur=4)
Sortie : user_options.user_id
Index Cond : (user_options.user_id = users.id)
Filtre : (user_options.chat_enabled ET (user_options.chat_email_frequency = 1))
-> Index Scan using chat_channels_pkey on public.chat_channels cc (coût=0.28..0.32 lignes=1 largeur=21)
Sortie : cc.id, cc.chatable_id, cc.deleted_at, cc.deleted_by_id, cc.featured_in_category_id, cc.delete_after_seconds, cc.chatable_type, cc.created_at, cc.updated_at, cc.name, cc.description, cc.status, cc.user_count, cc.last_message_sent_at, cc.auto_join_users, cc.user_count_stale, cc.type, cc.slug, cc.allow_channel_wide_mentions, cc.messages_count, cc.threading_enabled, cc.last_message_id
Index Cond : (cc.id = uccm.chat_channel_id)
-> Index Scan using index_chat_messages_on_chat_channel_id_and_created_at on public.chat_messages c_msg (coût=0.41..4.19 lignes=12 largeur=16)
Sortie : c_msg.id, c_msg.chat_channel_id, c_msg.user_id
Index Cond : ((c_msg.chat_channel_id = uccm.chat_channel_id) ET (c_msg.created_at > '2023-11-20 08:00:00'::timestamp without time zone))
Filtre : ((c_msg.deleted_at IS NULL) ET ((uccm.last_read_message_id IS NULL) OU (c_msg.id > uccm.last_read_message_id)) ET ((uccm.last_unread_mention_when_emailed_id IS NULL) OU (c_msg.id > uccm.last_unread_mention_when_emailed_id)))
-> Index Only Scan using chat_mentions_index on public.chat_mentions c_mentions (coût=0.40..0.52 lignes=1 largeur=8)
Sortie : c_mentions.chat_message_id, c_mentions.user_id, c_mentions.notification_id
Index Cond : (c_mentions.chat_message_id = c_msg.id)
-> Index Only Scan using index_group_users_on_user_id_and_group_id on public.group_users (coût=0.42..44019.59 lignes=760157 largeur=8)
Sortie : group_users.user_id, group_users.group_id
-> Index Only Scan using groups_pkey on public.groups (coût=0.14..0.16 lignes=1 largeur=4)
Sortie : groups.id
Index Cond : (groups.id = group_users.group_id)
(51 lignes)
2 « J'aime »
@sok777 merci d’avoir partagé le plan de requête ! C’est très utile.
Votre instance contient un grand nombre d’utilisateurs et un grand nombre d’enregistrements user_chat_channel_memberships (comme vous l’avez partagé ici).
Avant que vous ne partagiez le plan de test, j’ai essayé de reproduire le problème sur des sites avec un grand nombre d’utilisateurs, mais la requête a bien fonctionné. Maintenant, je vois que ces sites n’ont pas un grand nombre de user_chat_channel_memberships, il est très probable que ce soit ce qui est spécifique à votre site et ce qui a causé des problèmes si graves dans votre cas.
Si je comprends bien, vous n’avez plus de problèmes, car vous avez supprimé toutes les mentions à un moment donné, mais je m’attendrais à ce que cette requête redevienne problématique à un moment donné, même avec une petite quantité de mentions. Je vais donc l’optimiser dans tous les cas.
Je travaille actuellement à la résolution du problème sous-jacent qui a causé la création d’énormes quantités d’enregistrements chat_mention dans la base de données. Pendant que cela progresse, il est important de garder @ all et @ here dans les mots surveillés. Je publierai une mise à jour ici lorsque je m’en serai occupé, et après cela, je m’assurerai d’optimiser cette requête.
6 « J'aime »
Le problème sous-jacent a été résolu par
Il ne devrait plus y avoir de tels problèmes de performance, et il n’est pas nécessaire de conserver les mentions de masse dans les mots surveillés.
Concernant ceci :
Il semble qu’il n’y ait pas de plaintes concernant cette requête, donc je ne l’optimisais pas. N’hésitez pas à signaler en cas de problèmes.
5 « J'aime »