[Tracking] Investigate usage of db connections of CRCon instances

[FlorianSW]

This issue is to continue the discussion from #1255 with the main question to ask:

What is the total number of DB connections the CRCon needs to run in a normal/default setup, and for what are they used.

With follow up questions being:

• What component is using a connection for what purpose?
• Does the total number need to be so high/low (the answer can either be yes (keep everything) or no (we should change something)

An uncontrolled (or better said: unaccounted) number of DB connections has at least two implications:

• each connection is consuming resoruces (CPU + memory). Even while this might be in the single-digit numbers, the resource intensive setup of CRCon should not be increased by that :P
• Postgres limits the number of connections per server. As CRCon uses one DB server for n CRCon instances, we essentially cap the number of CRCon servers one can start using the default DB setup we provide (without needing to adjust the limit). Increasing that number (the "density" of CRCons per default DB server, if you will) is a good thing for users without much knowledge or energy to look into these config changes. And we shouldn't run after finding the "perfect" default setting, either.

Taking over some comments from that PR for better traceability:

From @ElGuillermo

The default 100 max postgres connections isn't enough when managing more than 4 (?) game servers, triggering http 502 errors in UI (see : https://discord.com/channels/685692524442026020/685695097349734469/1470638305606373452).

This fix increases the default to 300. This arbitrary number should suffice for a number of managed game servers up to 8, but still has to be evaluated in real conditions and edge cases (8+ servers).

From @ElGuillermo

I'm running some tests on my CRCON, but as it only manages one game server, I can't tell much about it. Only thing I know is : people having the issue got it fixed using 300. Highest I got here was 40 (one game server).

From @Fragger

Quick info from a couple of setups. One with 4 full public servers and an event server is sitting at 112 connections. Another with 10 event servers is sitting at 147 connections but they are not being used at the moment.

From @FlorianSW

Just my two cents: Sure we could increase the limit (and as a quick fix this is probably good to go). However, I would really like to take a deeper look into the actual number of connections we need and, more importantly, why.

That's what I can think of out of my head without looking too deeply into the code:

• First of all the backend threads:

  • by default, we use a single process (NB_API_WORKERS) with 8 threads (NB_API_THREADS)
  • from what I understood (and that might be wrong!) is that each thread will use it's own instance of SQLAlchemy's Engine, which holds the connection pool, among other things
  • As each of the backend threads should work on a single HTTP requests at the same time, there shouldn't be any more than a total of NB_API_WORKERS * NB_API_THREADS (in our default config 8), if there are more, then we're most likely doing something in parallel when answering HTTP requests, which we should reconsider (at least when it comes to DB connections)

• all the supervisor programs:

  • each of the defined programs in supervisord.conf are started as their own process, independant of all the other ones
  • they therefore use their own Engine and connection pool because of that
  • we currently have 16 programs (not all fo them might be started on every setup, but let's suppose they do)
  • each of them will, on the other hand, only open a connection to the DB, if actually needed
  • I presume that a good portion of them actually do not need a DB connection, if there were not the DB based config, a lot of the services load their configuration from the DB on startup. I wasn't able, yet, to find out from SQLAlchemy when a connection is released from the pool if unused, but chances are that they stay in the pool (???) for the entire time the service is running, even though it is only used once during startup?

Together that would make about 24 connections (at minimum) for the above mentioned services. I assume that the number increases for some services that do stuff in parallel (each pool can have up to 5 conections, which would allow a total number of 100 connections per server, which will, hopefully, never ever reached, but as a max ceiling).

Maybe we can take a deeper look into where the total number of 40 connections come from and tackle this issue (as wel)? :)

[FlorianSW]

• [Operating] allow to identify DB connection "owner" #1261 can help identify what component is responsible for what connections
• Allow some services to not use a connection pool to the DB #1262 to decrease the number of connections for components that supposedly do not need them over their lifetime