Synapse 1.100rc1 makes Postgres eat the whole CPU

[tulir]

Description

Didn't happen immediately after upgrade, I'm not exactly sure what triggered it. #16833 sounds relevant since postgres active queries had lots of FROM event_auth_chain_links WHERE origin_chain_id = ANY(ARRAY[...]). Postgres was using the whole CPU by having multiple threads running similar queries.

After restarting synapse it calmed down slightly, but at least one postgres thread started running those queries again and immediately used 100% of a core, so I downgraded to 1.99 to avoid the whole CPU being eaten again overnight.

Steps to reproduce

1. Upgrade to Synapse 1.100rc1
2. Wait a while?
3. Observe the system load exploding

Homeserver

maunium.net

Synapse Version

1.100rc1

Installation Method

Docker (dock.mau.dev/maunium/synapse)

Database

PostgreSQL

Workers

Multiple workers

Platform

Docker on Ubuntu 22.04

[erikjohnston]

Hopefully fixed by #16849

[Revertron]

It seems that this problem is not fixed in 1.115.0+bookworm1.
I get very often these kind of slow requests:

2024-09-23 20:48:38.842 CEST [155885] synapse@synapse LOG:  duration: 13944.595 ms  statement:
                    WITH RECURSIVE links(chain_id) AS (
                        SELECT
                            DISTINCT origin_chain_id
                        FROM event_auth_chain_links WHERE origin_chain_id = ANY(ARRAY[<exactly 1000 ids>])
                        UNION
                        SELECT
                            target_chain_id
                        FROM event_auth_chain_links
                        INNER JOIN links ON (chain_id = origin_chain_id)
                    )
                    SELECT
                        origin_chain_id, origin_sequence_number,
                        target_chain_id, target_sequence_number
                    FROM links
                    INNER JOIN event_auth_chain_links ON (chain_id = origin_chain_id)