최근 모든 언급(Mentions)을 삭제한 이후 CPU 사용량이 15~20%로 떨어져서 꽤 좋은 상태입니다. 하지만 이 때문에 위 쿼리는 결과를 반환하지 않습니다. (삭제했기 때문에 몇 개만 반환될 수도 있고요.)
1개의 좋아요
위의 쿼리는 어떤 경우든 결과를 반환해야 합니다. EXPLAIN VERBOSE로 시작하므로 실행 계획을 반환합니다. 시간이 되면 제 메시지에서의 정확한 쿼리를 복사해서 실행한 후 결과를 공유해 주시겠습니까?
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 rows)
2개의 좋아요
@sok777 쿼리 플랜을 공유해 주셔서 감사합니다. 매우 도움이 됩니다.
해당 인스턴스에는 사용자 수가 많을 뿐만 아니라 user_chat_channel_memberships 레코드 수도 매우 많습니다(여기에서 공유해 주신 내용). 테스트 플랜을 공유하기 전에, 사용자 수가 많은 사이트에서 문제를 재현해 보았지만 쿼리는 정상적으로 작동했습니다. 이제 보니, 해당 사이트들은 user_chat_channel_memberships 레코드 수가 많지 않아서, 이것이 사용자의 사이트에서 심각한 문제를 일으킨 특별한 원인일 가능성이 매우 높습니다.
제가 이해하기로는, 어느 시점에 모든 멘션을 삭제했기 때문에 지금은 문제가 없는 것 같습니다. 하지만 멘션 수가 적더라도 언젠가 이 쿼리가 다시 문제가 될 것으로 예상됩니다. 따라서 어쨌든 이 쿼리를 최적화할 것입니다.
현재 데이터베이스에 chat_mention 레코드가 대량으로 생성되는 근본 원인을 수정하는 작업을 진행 중입니다. 이 작업이 진행되는 동안, @ all과 @ here를 감시 단어(watched words)로 유지하는 것이 중요합니다. 이 부분을 처리하면 이곳에 업데이트를 게시할 것이며, 그 이후에는 이 쿼리를 최적화하도록 하겠습니다.
6개의 좋아요
근본적인 문제는 다음을 통해 해결되었습니다.
더 이상 이러한 성능 문제가 발생하지 않아야 하며, 대량 멘션을 감시 대상 단어에 유지할 필요가 없습니다.
이 부분에 대해:
이 쿼리에 대한 불만이 없는 것 같아 최적화하지 않았습니다. 문제가 발생하면 주저하지 말고 보고해 주세요.
5개의 좋아요