Synapse is unusably slow after presence enabled.

[matrixbot]

This issue has been migrated from #14090.

---

This is an issue to track the problems happening on @tomsisk's deployment of Synapse.

Previously discussed on matrix-org/synapse#12160 (comment), #13955 and #13901.

Suspect a regression between 1.60 and 1.64, see matrix-org/synapse#12160 (comment).

---

I updated this last night and used it for about an hour with a couple of users. There was nothing of note, presence was working and the service was responsive. So I let the service run over night with presence on and we never saw a huge memory spike, but not many people are online during off hours. By the morning, the service was still running albeit extremely slow. Nothing weird in CPU or memory usage, but it was taking up to 60 seconds to send a message, login, etc. It was running, but unusable, so I had to turn presence off. The slowness is what we normally see after just a couple of minutes if we turn presence on during the day. In those cases, I've never been able to see a huge memory spike like we have in the past, but it seems those may have been connected to specific users.

Question: would it hurt anything if I truncated the presence_stream table? I don't know whether it would help anything either, but I noticed there is a lot of data going back a few years that hasn't been updated.

Back to the topic at hand: even with presence off, there appears to still be a small leak somewhere, I'm hoping these graphs help.
Here's a spike in memory.

Which coincides with a spike in _handle_new_device_update_async calls:

Now if we look at the cache memory, we see an increase on getEvent, but after 30 minutes the metrics say this memory was released. There is no similar drop in memory in the first image.

Originally posted by @tomsisk in matrix-org/synapse#13955 (comment)