Balancing Website & Worker Workload (Performance)

[gamemann]

Recently, I've been trying to tune the query system we have in-place to update as many servers as possible.

Goal

• Allow the separate worker app repsonsible for adding/updating servers, players, and stats to utilize as much database resources as possible without impacting the main website's database performance.
• Try to have all servers scanned and updated within 5 - 10 minutes.
  • There are 50,000+ servers to scan right now.
  • The query program itself isn't the issue, the issue is finding a balance with things like pool sizes, connection limits, etc. for both the main website and worker app so that the worker app doesn't hurt the performance of the main website's queries.

Initial Setup

Until today, I used the built-in Prisma operations to add and update servers, players, etc. Locally, this worked great because my home server has a processor with high single-threaded performance (an Intel i7-12700K). However, in production, while the server has many more cores, the cores aren't rated well for single-threaded performance.

Since Node is single-threaded, this created a big bottleneck. Even running the worker app in a custom cluster mode so that there are multiple node processes, it was still choking using 4+ cores.

Last night, I ended up converting the built-in Prisma logic in the worker app to raw PostgreSQL queries which was a HUGE pain by the way, but was definitely worth it given Node is certainly not a bottleneck anymore.

Current Issue

Since the ridiculous overhead from the Prisma queries is no longer an issue (due to slow single-threaded performance), the PostgreSQL processes/threads consume all of the resources.

This allows our query scanner to update all online servers within <10 minutes. However, while it's updating servers, the main website seems to load the server list a bit slow. I haven't seen any issues when loading server pages (because it's a simple select query), but the server browser is obviously important.

Dedicated Pools & Such

I ended up setting up pgbouncer and creating dedicated pools for the main website and worker.

Now, it's about finding the best settings for this. Additionally, the add/updates supports batching and such, but I've found large batches causes issues so it's fairly low anyways.

[gamemann]

I believe I've found a decent balance for now, but more tuning will be required down the road and maybe a more proactive system (e.g. automatically spawning workers depending on how backed up things are instead of fixed settings).

All online servers are updated at <10 minutes right now which is great in my opinion. Stats seems to be a bit backed up for some reason though (most servers insert a stat every 10 minutes or so right now which isn't bad, but it's configured for every 5 minutes so it's backed up still).

[gamemann]

I've continued to make tuning adjustments which has been a big WIP due to running into constant issues with Prisma and Postgres (e.g. Prisma connection pools are a huge pain). Due to the core speeds of the server we have, I've found batch-updating 250 servers at a time to be better for the query system than say 5000. I've also made a ton of adjustments to how much RAM the database can use, how much RAM each query uses, etc. This has resulted in performance improvements overall, but the query system still puts a lot of load onto the database (not much we can do due to how many servers we're updating in such a short period of time).

At this time, all servers are updated every 10 - 12 minutes. I'll try to get this down a little more, but tuning will be a pain. The big plus is Node is no longer a big CPU waster for the workers and now all the load is on the database.

If the website starts generating traffic, I'll upgrade the server and we'll go with something much better than we have now so we won't have to worry about this much in the future.