Description

We have a host in EMS with a room that is getting approx 2 events per second into it. A few users sitting in the room are churning the database CPU due to constant sync which is causing the synchrotron process to spend a large amount of transaction time in get_unread_event_push_actions_by_room.

The database query is:

SELECT COUNT(CASE WHEN notif = ? THEN ? END), COUNT(CASE WHEN highlight = ? THEN ? END), COUNT(CASE WHEN unread = ? THEN ? END) FROM event_push_actions ea WHERE user_id = ? AND room_id = ? AND stream_ordering > ?

Two of the users in the room are causing an equal 50/50 of the database load.

Database metrics showing the query being mostly CPU time.

More context in https://matrix.to/#/!ixSKkmjfDhyFWsKSEY:federator.dev/$ZFJ7IDlk0gt_l8ht9hEK3DZVg5mhucbaEtM4qCKPiQk?via=matrix.org&via=vector.modular.im

Version information

• Homeserver: EMS, customer in context link

• Version: v1.51.0

• Install method: Docker, official images

• Platform: EMS