Postgres locks do not scale(recall.ai)
recall.ai
Postgres locks do not scale
https://www.recall.ai/blog/postgres-locks-do-not-scale
9 comments
Separate high-frequency operational state from large, flexible, searchable metadata. It is bad data models that don't scale
As others have already pointed out, the issue is too high concurrency — IMO that should be the main outcome of the analysis. E.g. InnoDB has innodb_thread_concurrency setting that applies for internal operations to prevent exactly kinds of issues like these. Yet MySQL isn't perfect in any regard either, it's also very much possible to hit lock (or latch as they're called in DB world) contention if you hit the same rows in a table too hard from too many different threads. It's a fundamental limitation of the CPUs that they can't do too much of parallel read-modify-write, and it starts to break down very quickly with the number of cores
15,000 database connections seems like a lot. Is that a scale Postgres supports very well?
That’s well beyond what a stock Postgres server would reasonably handle
> with an unusually large load we surpassed our provisioned IO on the underlying volume
Sounds like they could have benefitted from Aurora's storage model (though there are reasons not to use it, too).
Sounds like they could have benefitted from Aurora's storage model (though there are reasons not to use it, too).
Don't reboot the db during your next outage.
Yeah. This is why we have connection pooling.
> The way our system works is that each bot will emit a BotEvent as it progresses through its lifecycle. One for when the underlying server is ready, one for when the bot is in_waiting_room, and one for when it is joining_call. BotEvents are their own table, but through a trigger they update a status field on the bot, which is indexed as we frequently want to query bots based on where they are in their lifecycle.
This causes a change to the Bot index, so we suddenly need to alter and extend every corresponding index.
....
Why not remove the trigger and put the status field in its own seperate table
This causes a change to the Bot index, so we suddenly need to alter and extend every corresponding index.
....
Why not remove the trigger and put the status field in its own seperate table
The entire “how do we drain connections” issue described in TFA becomes trivial, as does gradually raising the amount of incoming connections, let alone the load advantages from multiplexing the client connections.