Hemos eliminado TODAS las menciones recientemente y desde entonces la carga de la CPU bajó al 15-20%, así que bastante bien, pero por lo tanto la consulta anterior no devolverá resultados. (o solo unos pocos ya que la eliminamos)
1 me gusta
La consulta anterior debería devolver resultados en cualquier caso, comienza con EXPLAIN VERBOSE por lo que devuelve un plan de ejecución. Cuando tenga tiempo, ¿podría copiar y pegar la consulta exacta de mi mensaje, ejecutarla y compartir los resultados?
Limit (cost=123.37..22204.48 rows=1000 width=40)
Output: users.id, (array_agg(ARRAY[uccm.id, c_msg.id])), uccm.user_id
-> GroupAggregate (cost=123.37..339399.59 rows=15365 width=40)
Output: users.id, array_agg(ARRAY[uccm.id, c_msg.id]), uccm.user_id
Group Key: users.id, uccm.user_id
-> Nested Loop (cost=123.37..336077.65 rows=417317 width=24)
Output: users.id, uccm.user_id, uccm.id, c_msg.id
Inner Unique: true
-> Merge Join (cost=123.23..271629.01 rows=417317 width=28)
Output: users.id, group_users.group_id, uccm.id, uccm.user_id, c_msg.id
Merge Cond: (user_options.user_id = group_users.user_id)
-> Nested Loop Left Join (cost=121.72..221430.16 rows=132835 width=28)
Output: users.id, user_options.user_id, uccm.id, uccm.user_id, c_msg.id
Filter: (((uccm.user_id = c_mentions.user_id) AND (uccm.following IS TRUE) AND ((cc.chatable_type)::text = 'Category'::text)) OR ((cc.chatable_type)::text = 'DirectMessage'::text))
-> Nested Loop (cost=121.32..149930.76 rows=132967 width=42)
Output: users.id, user_options.user_id, uccm.id, uccm.user_id, uccm.following, cc.chatable_type, c_msg.id
Join Filter: ((c_msg.user_id <> users.id) AND (cc.id = c_msg.chat_channel_id))
-> Nested Loop (cost=120.91..57868.92 rows=21066 width=54)
Output: 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 (cost=120.63..51231.73 rows=21066 width=33)
Output: 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 (cost=120.21..36835.88 rows=21096 width=29)
Output: 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
Merge Cond: (uccm.user_id = users.id)
-> Index Scan using user_chat_channel_unique_memberships on public.user_chat_channel_memberships uccm (cost=0.41..12177.25 rows=21618 width=25)
Output: 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 (cost=0.42..23823.53 rows=237544 width=4)
Output: users.id
Filter: (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 (cost=0.42..0.68 rows=1 width=4)
Output: user_options.user_id
Index Cond: (user_options.user_id = users.id)
Filter: (user_options.chat_enabled AND (user_options.chat_email_frequency = 1))
-> Index Scan using chat_channels_pkey on public.chat_channels cc (cost=0.28..0.32 rows=1 width=21)
Output: 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 (cost=0.41..4.19 rows=12 width=16)
Output: c_msg.id, c_msg.chat_channel_id, c_msg.user_id
Index Cond: ((c_msg.chat_channel_id = uccm.chat_channel_id) AND (c_msg.created_at > '2023-11-20 08:00:00'::timestamp without time zone))
Filter: ((c_msg.deleted_at IS NULL) AND ((uccm.last_read_message_id IS NULL) OR (c_msg.id > uccm.last_read_message_id)) AND ((uccm.last_unread_mention_when_emailed_id IS NULL) OR (c_msg.id > uccm.last_unread_mention_when_emailed_id)))
-> Index Only Scan using chat_mentions_index on public.chat_mentions c_mentions (cost=0.40..0.52 rows=1 width=8)
Output: 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 (cost=0.42..44019.59 rows=760157 width=8)
Output: group_users.user_id, group_users.group_id
-> Index Only Scan using groups_pkey on public.groups (cost=0.14..0.16 rows=1 width=4)
Output: groups.id
Index Cond: (groups.id = group_users.group_id)
(51 filas)
2 Me gusta
@sok777 ¡gracias por compartir el plan de consulta! Es muy útil.
Tu instancia contiene una gran cantidad de usuarios y una gran cantidad de registros de user_chat_channel_memberships (como compartiste aquí).
Antes de que compartieras el plan de prueba, intenté reproducir el problema en sitios con una gran cantidad de usuarios, pero la consulta funcionó bien. Ahora, veo que esos sitios no tienen una gran cantidad de user_chat_channel_memberships, es muy probable que esto sea lo específico de tu sitio y lo que causó problemas tan serios en tu caso.
Según entiendo, ahora no tienes problemas porque eliminaste todas las menciones en algún momento, pero esperaría que esta consulta volviera a ser problemática en algún momento, incluso con una pequeña cantidad de menciones. Así que la optimizaré de todos modos.
Actualmente estoy trabajando en solucionar el problema subyacente que causó la creación de enormes cantidades de registros de chat_mention en la base de datos. Mientras está en progreso, es importante mantener @everyone y @here en las palabras vigiladas. Publicaré una actualización aquí cuando me ocupe de eso, y después de eso me aseguraré de optimizar esta consulta.
6 Me gusta
El problema subyacente ha sido resuelto por
Ya no debería haber tales problemas de rendimiento, y no hay necesidad de mantener menciones masivas en palabras vigiladas.
Con respecto a esto:
Parece que no hay quejas sobre esta consulta, así que no la estaba optimizando. Por favor, no dudes en informar en caso de problemas.
5 Me gusta