3 ms·

Add schema migrations and bulk loads to this. So many systems crap out doing things like schema migrations at scale. Query latencies degrade over time due to in

Add schema migrations and bulk loads to this. So many systems crap out doing things like schema migrations at scale. Query latencies degrade over time due to internal structures keeping track of things, stuff runs out of buffer / log space in memory or on disk, you have a traffic spike but you can't pause or throttle a 40 hour long ALTER, things that should never lock do lock for indeterminate times during cutover, stuff craps out after cutover due to surprising behavior but there is no rollback, or even worse I've seen things just flat out crash with some random assert fail or segfault. It's a world of pain, and there are so much scar tissue of third party tooling doing crazy stuff dealing with this problem among large companies that really should be the DB vendor's problem.

sgarland 2y ago
If you spend time reading docs – I mean really reading them, you’ll probably find out why something locked. For example, MySQL 8.? can extend metadata locks to cover foreign key relationships. This seemingly innocuous line in docs can cause the entire DB to grind to a halt under load. I’m not saying this is good, only that RDBMS management is very much still in the land of arcane incantations, and anyone saying otherwise is selling something. It’s also why I have a job, so from my perspective it isn’t all bad.
- makmanalp 2y ago
  Hi friend. I, like you, am exactly that person who reads those docs very carefully and has to know about the seemingly innocuous one liners as well as the one liners that are not there but should be, that eventually make it in after the next point release. Or the bug report that's marked "S3 (non-critical)" that sneakily awaits for days in production until it whacks something very much critical. Or the bug that celebrated its 14th birthday. What I understand about how some of these implementations work makes me very sad sometimes. And I'm telling you that the state of things is silly. The thing is - and I imagine you know this - that when you run tens of thousands of database instances it's not a matter of if, it's a matter of when. I can't chase after thousands of engineers to make sure they're not hitting some obscure pitfall nor can I demand that they know the ins and outs of the 8 different types of locks in FooDB. And "read the docs lol" definitely doesn't scale. So we build automated mass-guardrails and linters as best we can and use various shenanigans to deal with the real world. In the context of schema migrations, there's tools like pt-osc, gh-ost and pgroll that are excellent for what they are and I'm grateful to those who created them but these are still in effect huge hacks that are bolted on top of the database. They're teeming with footguns. I can tell you about a time where badly conceived timeout / error handling code in one of these tools in their golang database driver resulted in most of a database dropped instantly with no fast rollback, for example. That was fun. If a third party bolt on tool can implement zero(ish) downtime migrations and cutovers, at least much much much better than the regular DDL, why the heck is the database not doing whatever that thing is doing? It's tech debt, history, crud, lack of caring, etc. Schema migrations is just the tip of the iceberg too, don't get me started on replication, durability settings, resource usage vs capacities ... My point is that academics and vendors should be taking operational problems seriously and owning them, and solving them properly at the database layer. And just like you I also make living off of the fact that the real world operational characteristics of truly busy databases are very different than what gets benchmarked and tested. I'm just saying that some of this stuff even I don't want to have to deal with. There's better problems to be working on.

sgarland 2y ago

If you spend time reading docs – I mean really reading them, you’ll probably find out why something locked. For example, MySQL 8.? can extend metadata locks to cover foreign key relationships. This seemingly innocuous line in docs can cause the entire DB to grind to a halt under load. I’m not saying this is good, only that RDBMS management is very much still in the land of arcane incantations, and anyone saying otherwise is selling something. It’s also why I have a job, so from my perspective it isn’t all bad.

makmanalp 2y ago
Hi friend. I, like you, am exactly that person who reads those docs very carefully and has to know about the seemingly innocuous one liners as well as the one liners that are not there but should be, that eventually make it in after the next point release. Or the bug report that's marked "S3 (non-critical)" that sneakily awaits for days in production until it whacks something very much critical. Or the bug that celebrated its 14th birthday. What I understand about how some of these implementations work makes me very sad sometimes. And I'm telling you that the state of things is silly. The thing is - and I imagine you know this - that when you run tens of thousands of database instances it's not a matter of if, it's a matter of when. I can't chase after thousands of engineers to make sure they're not hitting some obscure pitfall nor can I demand that they know the ins and outs of the 8 different types of locks in FooDB. And "read the docs lol" definitely doesn't scale. So we build automated mass-guardrails and linters as best we can and use various shenanigans to deal with the real world. In the context of schema migrations, there's tools like pt-osc, gh-ost and pgroll that are excellent for what they are and I'm grateful to those who created them but these are still in effect huge hacks that are bolted on top of the database. They're teeming with footguns. I can tell you about a time where badly conceived timeout / error handling code in one of these tools in their golang database driver resulted in most of a database dropped instantly with no fast rollback, for example. That was fun. If a third party bolt on tool can implement zero(ish) downtime migrations and cutovers, at least much much much better than the regular DDL, why the heck is the database not doing whatever that thing is doing? It's tech debt, history, crud, lack of caring, etc. Schema migrations is just the tip of the iceberg too, don't get me started on replication, durability settings, resource usage vs capacities ... My point is that academics and vendors should be taking operational problems seriously and owning them, and solving them properly at the database layer. And just like you I also make living off of the fact that the real world operational characteristics of truly busy databases are very different than what gets benchmarked and tested. I'm just saying that some of this stuff even I don't want to have to deal with. There's better problems to be working on.