최근 CPU 과부하가 발생하고 있습니다.
수 시간 동안 동시에 계속 실행되는 특정 쿼리(아래 참조)로 원인을 파악했습니다.
실행 중인 작업은 postmaster이며, 모든 프로세스를 종료하면 5분마다 재빌드될 때까지 CPU 사용량이 감소합니다.
SELECT “users”.“id”, ARRAY_AGG(ARRAY [uccm.id, c_msg.id]) AS memberships_with_unread_messages
FROM “users”
INNER JOIN “user_options” ON “user_options”.“user_id” = “users”.“id”
INNER JOIN user_chat_channel_memberships uccm ON uccm.user_id = users.id
INNER JOIN chat_channels cc ON cc.id = uccm.chat_channel_id
INNER JOIN chat_messages c_msg ON c_msg.chat_channel_id = uccm.chat_channel_id
LEFT OUTER JOIN chat_mentions c_mentions ON c_mentions.chat_message_id = c_msg.id
WHERE “user_options”.“chat_enabled” = TRUE
AND “user_options”.“chat_email_frequency” = 1
AND (users.last_seen_at < ‘2023 - 11 - 15 09:54:26.931723’)
AND (c_msg.deleted_at IS NULL AND c_msg.user_id <> users.id)
AND (c_msg.created_at > ‘2023 - 11 - 08 10:09:26.931848’)
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) AND
(uccm.user_id = c_mentions.user_id AND uccm.following IS true AND
cc.chatable_type = ‘Category’
도움을 주실 분이나 해결책을 제안해 주실 분이 계신가요?
추가 정보:
3.2.0.beta4-dev 버전을 사용 중이며, 이 문제는 최소 몇 달 동안 지속되고 있습니다.
한 고객이 채팅에서 @all 멘션을 활성화했는데, 16,000명 규모의 채널이 두 개나 있었습니다. 정말 흥미로운 상황이었습니다!
전체 멘션을 19번 사용한 후, 고객은 이를 빠르게 비활성화했습니다. 하지만 12시간이 지난 후에도 그들의 (성능이 좋은) 시스템은 여전히 높은 CPU 부하를 겪고 있었습니다.
이 문제는 mentions 테이블에서 문제의 320,000개 레코드를 삭제함으로써 해결되었습니다.
delete from chat_mentions
where chat_message_id in (
(select chat_message_id
from chat_mentions
group by chat_message_id having count(*) > 100)
);
하지만 이것이 왜 이렇게 많은 문제를 일으켰는지 여전히 궁금했습니다. 이 쿼리의 여러 인스턴스가 여전히 실행 중이었기 때문입니다.
해당 행들이 이미 삭제되었기 때문에 explain 명령으로는 좋은 결과를 얻을 수 없었지만, 상당한 처리 작업을 수행하고 있었던 것은 분명했습니다. 모든 인덱스는 정상적으로 설정되어 있었습니다.
@all에게 보낸 메시지를 하나 삭제했습니다. 하지만 이로 인해 문제가 해결되지 않았습니다. 쿼리가 5분마다 계속 반복되고 있으며, 처리가 너무 오래 걸려 다른 많은 쿼리가 대기열에 쌓여 있습니다.
채팅을 비활성화하자 CPU 부하가 15%까지 떨어졌습니다. 정말 기가 막힙니다.
@sok777 이 작업에 대해 조금 도움을 주실 수 있을까요? Postrges가 사용자의 사이트에서 사용하는 쿼리 플랜을 확인해 보고 싶습니다. 데이터베이스에서 아래 쿼리를 실행하여 결과를 공유해 주시겠어요?
EXPLAIN VERBOSE SELECT "users"."id", ARRAY_AGG(ARRAY [uccm.id, c_msg.id]) AS memberships_with_unread_messages
FROM "users"
INNER JOIN "user_options" ON "user_options"."user_id" = "users"."id"
INNER JOIN "group_users" ON "group_users"."user_id" = "users"."id"
INNER JOIN "groups" ON "groups"."id" = "group_users"."group_id"
INNER JOIN user_chat_channel_memberships uccm ON uccm.user_id = users.id
INNER JOIN chat_channels cc ON cc.id = uccm.chat_channel_id
INNER JOIN chat_messages c_msg ON c_msg.chat_channel_id = uccm.chat_channel_id
LEFT OUTER JOIN chat_mentions c_mentions ON c_mentions.chat_message_id = c_msg.id
WHERE "user_options"."chat_enabled" = TRUE
AND "user_options"."chat_email_frequency" = 1
AND (users.last_seen_at < '2023-11-27 08:00:00.0000000')
AND (c_msg.deleted_at IS NULL AND c_msg.user_id <> users.id)
AND (c_msg.created_at > '2023-11-20 08:00:00.0000000')
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) AND
(
(uccm.user_id = c_mentions.user_id AND uccm.following IS true AND cc.chatable_type = 'Category') OR
(cc.chatable_type = 'DirectMessage')
)
)
GROUP BY users.id, uccm.user_id
ORDER BY "users"."id" ASC
LIMIT 1000;
