Smarter Postgres Max Connections Autotuning

[FSG-Cat]

Add smarter autotuning for max postgres connections based on enabled roles / Synapse worker counts.

As for why i even came across this idea. A friend helping a friend of theirs who run a public synapse using the playbook ran out of postgres connections seemingly on 500 and i think i saw one of each as the pattern the workers displayed.

I should note that the exact details of this other setup that ran into trouble prompting this feature request are unknown to me so i wont be making any report on that.

[spantaleev]

Even now, the playbook auto-adjusts postgres_max_connections based on whether Synapse workers are enabled, like this:

matrix-docker-ansible-deploy/group_vars/matrix_servers

Line 4107 in abc74a6

postgres_max_connections: "{{ 500 if matrix_synapse_workers_enabled else 200 }}"

---

Perhaps 500 is too low of a value, but only when one enables too many workers (a non-default confirmation)? It's hard to figure out what it should be to make everybody happen.

---

The Make Synapse faster documentation section of our Synapse maintenance docs tells people they may need to the Tuning PostgreSQL section of the docs, which does mention some auto-tuning being done by the Postgres role (based on system resources, not on Synapse configuration) and it also mentiones postgres_max_connections (but doesn't mention what values are used by default).

[FSG-Cat]

Im thinking about stuff like having the math work on a baseline plus setting aware limits. So for example having the limit be a baseline of we say 200 plus synapses cp_max setting times worker count to account for the 20 connections each worker process is allowed to eat.

Expand this to all playbook roles where its configurable and we could have it be way less of a concern to ever manage to run out of connections.

Edit: i should note that if Synapse runs out it manifests in degraded server performance and E2EE starting to go crazy and other odd behaviors last time i had this problem.

[aine-etke]

500 is definitely too small for specialized workers; I guess it can be increased to 800-1000