Chat mention mailer performance

Bug
1

We had a client turn on @all mentions in chat, with two channels of 16k users. That was fun!
After 19 mentions to all they quickly turned it off again. 12 hours later their (beefy) system was still at high CPU load.

The problem was resolved by clearing the 320k offending records from the mentions table

delete from chat_mentions 
where chat_message_id in (
  (select chat_message_id 
   from chat_mentions 
   group by chat_message_id having count(*) > 100)
);

but we were still puzzled why this was causing so much issues.
There were a number of instances of this query still running.

Because the rows were already gone, an explain did not give good results, but it is clear that it was doing some heavy grinding. All indexes are in place.

2

3 posts were merged into an existing topic: Query causing 400% CPU Load