Host got stuck trying to resolve room state possibly due to low cache size

This issue has been migrated from [#11747](https://github.com/matrix-org/synapse/issues/11747).

---

### Description

An EMS host went into a crash loop cycle due to being limited by memory. Once freed from that limit, it tried to resolve state for room `!aLUvbtiCWIkfYAjXTc:libera.chat` (`1 biggest rooms for state-res by CPU time: ['!aLUvbtiCWIkfYAjXTc:libera.chat (37.16s)']`) . From the graphs it looks like this was failing due to low cache sizes (100K events, 1 global factor). The database was being hammered into death until we got an alert for that and proceeded to up the caches (750K events, 4 global factor).

After restarting, the database hammering stopped and it looks like the host managed to move forward to continue resolving state for the room.

The host is still furiously working but not killing the database and seemingly not stuck.

The most intensive queries to the database seem to be:

`SELECT e.event_id, e.stream_ordering, ej.internal_metadata, ej.json, ej.format_version, r.room_version, rej.reason, e.outlier FROM events AS e JOIN event_json AS ej USING (event_id) LEFT JOIN rooms r ON r.room_id = e.room_id LEFT JOIN rejections as rej USING (event_id) WHERE e.event_id = ANY(ARRAY[?])`

`SELECT event_id FROM event_auth_chains AS c, (VALUES (?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?),(?,?,?)`

Times: UTC+2
Crash loop fixed: ~9:05
Caches bumped: ~10:40

Please ping for EMS host name if logs needed.








### Version information

- **Homeserver**: EMS customer, please ping for host name

- **Version**: v1.49.2

- **Platform**: EMS