8 ms·

One Million Database Connections

119 points by StratusBen 4y ago

unilynx 4y ago
> making MySQL live outside its means (i.e. overcommitting memory) opens the door to dangerous crashes and potential data corruption, so this is not recommended. data corruption? how? I'm no mySQL fan but is this FUD or referring to a real issue?
- chomp 4y ago
  If you OOM, it's gonna crash.
  - Volundr 4y ago
    Sure but data corruption? Data integrity on powerloss or other crash is a core feature of pretty much any RDMS, and OOM is actually probably one of the safer ways a database can crash (any disk caches still get flushed so no unpredictability around what is actually persistented)
    - lizztheblizz 4y ago
      Article author here. I fully endorse Aaron's correction and appreciate the call-out. For context: I initially wrote this paragraph to include more flavor and history around crash recovery challenges with relational databases, implying that while your data might be safe, it is still 100% preferable, even today, to avoid crashes by accurately sizing AND limiting the database to live within its means. Crash recovery can still take a certain amount of time, and when people are weighing whether to bring their app up faster or maintain their data integrity, taking a shortcut in a high pressure situation is sadly not unheard of. Alas, in my editing, I opted to spend less time in the weeds there, and without the proper context, the use of the term "data corruption" lost all meaning, and no longer belonged in that sentence. Totally fair correction.
- samlambert 4y ago
  Every server crash risks data loss. That said MySQL is exceptionally good at avoiding corruption and data loss.
  - paulryanrogers 4y ago
    I wouldn't say exceptionally. InnoDB is reasonably robust on a modern FS with sane mount options. Yet I'll never forget the MySQL 8 table rename bug that crashes the server and corrupts/truncates the table. Appeared in a GA release and took several patch releases before it was fixed.
- aarondf 4y ago
  This is a good call out. Data corruption is always possible due to software, firmware, hardware bugs and failures... but that’s not specific to OOMs. You could have non-crash safe settings like sync_binlog != 1 or innodb_flush_log_at_trx_commit != 1, but with the default settings of MySQL 8.0 it’s entirely crash safe in every way (binlog events etc). I think that part of the post needs to be updated. It was a bit of an off-handed comment but it's not as clear and accurate as it should be. I’m going to replace `potential data corruption` with `potential downtime` as it could initiate a failover and the process will have to restart and go through crash recovery which can take some time.
gsanderson 4y ago
Impressive! But I guess the trade-off of having all that power is the potentially terrifying cost. As you detail in the post, AWS Lambda comes with a default throttle (1000 concurrent) which can be adjusted. Is any throttle/limit like that supported, or in the road-map? Only I've been thinking I may want a service to fail beyond a certain point, as that amount of load would indicate an attack, not genuine usage.
- hanselot 4y ago
  I was also about to ask. How much? What would this example cost if someone decided to test it themselves?
  - lizztheblizz 4y ago
    So, disclaimer, I am not an Amazon Billing wizard, but given that we ran the Lambdas from an isolated sub account, I can be particularly certain that I was able to filter this down accurately. We hit the one million connections total probably between 10-20 times over the course of a couple of days, and probably spent at least another 20-30 runs working our way up to it, testing various things along the way. Keep in mind, these were all very short-lived test runs, lasting maybe up to 8 minutes at the most. Our total bill for Lambda in the month of October came out to just over 50USD.
- lizztheblizz 4y ago
  Absolutely agreed. As others have already pointed out, there is no underlying implication of preference to this kind of application architecture. Since we do run an actual DBaaS, one of our main internal goals in running these experiments was to specifically test our Global Routing Infrastructure, and construct a scenario that allowed us to help size specifically those components for capacity planning. As long-time DBA's ourselves, we do as much as we can to educate and empower users to architect their applications wisely... but we still need to be prepared for the worst. As it turns out, Lambda was an easy way to accomplish that. :)
jzelinskie 4y ago
Awesome to hear more about MySQL/Vitess connection pooling. Folks typically only consider memory usage for database connections, but we've also had to consider the p99 latency for establishing a connection. For SpiceDB[0] one place we've struggled for our MySQL backend (originally contributed by GitHub who are big Vitess users) is preemptively establishing connections in the pool so that it's always full. PGX[1] has been fantastic for Postgres and CockroachDB, but I haven't found something with enough control for MySQL. PS: Lots of love to to all my friends at Planetscale! SpiceDB is also a big user of vtprotobuf[2] -- a great contribution to the Go gRPC ecosystem. [0]: https://github.com/authzed/spicedb https://github.com/authzed/spicedb [1]: https://github.com/jackc/pgx https://github.com/jackc/pgx [2]: https://github.com/planetscale/vtprotobuf https://github.com/planetscale/vtprotobuf
- derekperkins 4y ago
  Setting MaxIdleConns to be the same as MaxOpenConns isn't sufficient? https://pkg.go.dev/database/sql#DB.SetMaxIdleConns https://pkg.go.dev/database/sql#DB.SetMaxIdleConns Otherwise, I guess you'd have to poll DBStats and run dummy queries to keep the pool full. https://pkg.go.dev/database/sql#DBStats https://pkg.go.dev/database/sql#DBStats
twawaaay 4y ago
If you have a lot of connections doing similar things, just batch requests to get the data in bulk. Scaling your database up should only be attempted once you can no longer improve efficiency of your application. It is always better to first put effort into improving efficiency than scaling it up. For example, one trick that allowed me to improve throughput of one application using MongoDB as a backend by factor of 50 was capturing queries from multiple requests happening at the same time and sending them as one request (statement) into the database, then when you get the result you fan them out to the respective business logic that needs them. The application was written with Reactor which makes this much easier than a normal thread based request processing. For example, if you have 500 people logging at the same time and fetching their user details, batch those requests for example every 100ms up to 100 users and fetch 100 records with a single query. You will notice that executing a simple fetch by id query even for hundreds of ids will only cost couple times more than fetching a single record. The application in question was able to fetch 2-3 GB of small documents per second during normal traffic (not an idealised performance test) with just couple dozen connections.
- lifeisstillgood 4y ago
  Just wanted to come back and defend you (a little :-) I read "if you have a million database connections then you are doing it wrong" less as a ad hominem attack and more as "if one is doing X one should try a different approach" I did like the "architectural strategy" of I can call it that of batching the calls. It's "tricks" like that, expressed at this level that are somehow missing from the common software dev parlance. They are not in l33tcode tests, they don't fit into neat boxes but they are vital "common knowledge" I wish I had a better term for these sort of optimisations. Anyway. Thanks for the comment. Don't take the blowback personally - frankly I was surprised even if it was a small storm in a teacup. And always do as dang says ;-)
- DSingularity 4y ago
  But the lost elegance!
- andrewbarba 4y ago
  How could you possibly say this is doing it wrong? The only way you could batch requests in the way you describe is if you have 1 (or very small number) compute nodes. You would need all those requests to hit same node so you could try and batch. With serverless compute infrastructure (which is what this blog is demonstrating by using lambda) you can have 1 isolated process per request and therefore need a database that can actually handle this kind of load.
themenomen 4y ago
Any additional insights or information on how latencies relate to number of connections?
- jonahberquist 4y ago
  This is more dependent on parallelism of queries rather than parallelism of connections. Having a ton of relatively idle connections won't increase the query latency. For the related topic, in https://planetscale.com/blog/one-million-queries-per-second-with-mysql https://planetscale.com/blog/one-million-queries-per-second-... we touched on saturating shards through increased parallelism resulting in latency, and then also being an indication of needing to scale more horizontally.
prithvi24 4y ago
Can ya'll sign a BAA for HIPAA? Saw Soc2 - just Hosted Vitess sounds amazing - love this - 0 downtime migrations w/ Percona on RDS still suck and waste a lot of time
- aarondf 4y ago
  that's above my pay grade! you can reach out to support@planetscale.com and we can give you some more information on that
- samlambert 4y ago
  Yes we can.
sulam 4y ago
On the off chance someone associated with this is reading: I’m curious about the networking stack here. Specifically TCP. Is it being used? The reason I ask is because one limit I’ve run into in the past with large scale workloads like this is exhausting the ephemeral port supply to allow connections from new clients. Did you run into this? If not I’m curious why not. And if so, how did you manage it?
- lizztheblizz 4y ago
  Article author here, interesting question! We didn't run into that issue, explicitly. Our setup was effectively as follows: - AWS Lambda functions being spawned in us-east-1, from a separate AWS sub account. - Connections were all made to the public address provisioned for MySQL protocol access to PlanetScale, using port 3306. The infrastructure did also reside in us-east-1. - Between the Vitess components themselves, and once inside our own network boundaries, we use gRPC to communicate. Since the goal we set was to hit one million, and realizing we were staying just barely within the limits of the Lambda default quotas, we didn't aggressively try to push beyond that. Some members of our infrastructure team did notice what appeared to be some kind of rate limiting when running the tests multiple times consecutively. Many tests before and after succeeded with no such issues, so we attributed it to a temporary load balancer quirk, but it might be worth going back to confirm if this is the behavior we saw.
  - sulam 4y ago
    Two hypotheses — one of which you can falsify easily. Perhaps Vitess is doing port concentration? Ie dispatching requests made by multiple clients over fewer db connections? This is quite typical to do. The other is that you may have simply had a fast enough query that Little’s Law worked out for you.

119 points by StratusBen 4y ago

> making MySQL live outside its means (i.e. overcommitting memory) opens the door to dangerous crashes and potential data corruption, so this is not recommended. data corruption? how? I'm no mySQL fan but is this FUD or referring to a real issue?

chomp 4y ago
If you OOM, it's gonna crash.
- Volundr 4y ago
  Sure but data corruption? Data integrity on powerloss or other crash is a core feature of pretty much any RDMS, and OOM is actually probably one of the safer ways a database can crash (any disk caches still get flushed so no unpredictability around what is actually persistented)
  - lizztheblizz 4y ago
    Article author here. I fully endorse Aaron's correction and appreciate the call-out. For context: I initially wrote this paragraph to include more flavor and history around crash recovery challenges with relational databases, implying that while your data might be safe, it is still 100% preferable, even today, to avoid crashes by accurately sizing AND limiting the database to live within its means. Crash recovery can still take a certain amount of time, and when people are weighing whether to bring their app up faster or maintain their data integrity, taking a shortcut in a high pressure situation is sadly not unheard of. Alas, in my editing, I opted to spend less time in the weeds there, and without the proper context, the use of the term "data corruption" lost all meaning, and no longer belonged in that sentence. Totally fair correction.
samlambert 4y ago
Every server crash risks data loss. That said MySQL is exceptionally good at avoiding corruption and data loss.
- paulryanrogers 4y ago
  I wouldn't say exceptionally. InnoDB is reasonably robust on a modern FS with sane mount options. Yet I'll never forget the MySQL 8 table rename bug that crashes the server and corrupts/truncates the table. Appeared in a GA release and took several patch releases before it was fixed.
aarondf 4y ago
This is a good call out. Data corruption is always possible due to software, firmware, hardware bugs and failures... but that’s not specific to OOMs. You could have non-crash safe settings like sync_binlog != 1 or innodb_flush_log_at_trx_commit != 1, but with the default settings of MySQL 8.0 it’s entirely crash safe in every way (binlog events etc). I think that part of the post needs to be updated. It was a bit of an off-handed comment but it's not as clear and accurate as it should be. I’m going to replace `potential data corruption` with `potential downtime` as it could initiate a failover and the process will have to restart and go through crash recovery which can take some time.

gsanderson 4y ago

Impressive! But I guess the trade-off of having all that power is the potentially terrifying cost. As you detail in the post, AWS Lambda comes with a default throttle (1000 concurrent) which can be adjusted. Is any throttle/limit like that supported, or in the road-map? Only I've been thinking I may want a service to fail beyond a certain point, as that amount of load would indicate an attack, not genuine usage.

hanselot 4y ago
I was also about to ask. How much? What would this example cost if someone decided to test it themselves?
- lizztheblizz 4y ago
  So, disclaimer, I am not an Amazon Billing wizard, but given that we ran the Lambdas from an isolated sub account, I can be particularly certain that I was able to filter this down accurately. We hit the one million connections total probably between 10-20 times over the course of a couple of days, and probably spent at least another 20-30 runs working our way up to it, testing various things along the way. Keep in mind, these were all very short-lived test runs, lasting maybe up to 8 minutes at the most. Our total bill for Lambda in the month of October came out to just over 50USD.
lizztheblizz 4y ago
Absolutely agreed. As others have already pointed out, there is no underlying implication of preference to this kind of application architecture. Since we do run an actual DBaaS, one of our main internal goals in running these experiments was to specifically test our Global Routing Infrastructure, and construct a scenario that allowed us to help size specifically those components for capacity planning. As long-time DBA's ourselves, we do as much as we can to educate and empower users to architect their applications wisely... but we still need to be prepared for the worst. As it turns out, Lambda was an easy way to accomplish that. :)

jzelinskie 4y ago

Awesome to hear more about MySQL/Vitess connection pooling. Folks typically only consider memory usage for database connections, but we've also had to consider the p99 latency for establishing a connection. For SpiceDB[0] one place we've struggled for our MySQL backend (originally contributed by GitHub who are big Vitess users) is preemptively establishing connections in the pool so that it's always full. PGX[1] has been fantastic for Postgres and CockroachDB, but I haven't found something with enough control for MySQL. PS: Lots of love to to all my friends at Planetscale! SpiceDB is also a big user of vtprotobuf[2] -- a great contribution to the Go gRPC ecosystem. [0]: https://github.com/authzed/spicedb https://github.com/authzed/spicedb [1]: https://github.com/jackc/pgx https://github.com/jackc/pgx [2]: https://github.com/planetscale/vtprotobuf https://github.com/planetscale/vtprotobuf

derekperkins 4y ago
Setting MaxIdleConns to be the same as MaxOpenConns isn't sufficient? https://pkg.go.dev/database/sql#DB.SetMaxIdleConns https://pkg.go.dev/database/sql#DB.SetMaxIdleConns Otherwise, I guess you'd have to poll DBStats and run dummy queries to keep the pool full. https://pkg.go.dev/database/sql#DBStats https://pkg.go.dev/database/sql#DBStats

twawaaay 4y ago

If you have a lot of connections doing similar things, just batch requests to get the data in bulk. Scaling your database up should only be attempted once you can no longer improve efficiency of your application. It is always better to first put effort into improving efficiency than scaling it up. For example, one trick that allowed me to improve throughput of one application using MongoDB as a backend by factor of 50 was capturing queries from multiple requests happening at the same time and sending them as one request (statement) into the database, then when you get the result you fan them out to the respective business logic that needs them. The application was written with Reactor which makes this much easier than a normal thread based request processing. For example, if you have 500 people logging at the same time and fetching their user details, batch those requests for example every 100ms up to 100 users and fetch 100 records with a single query. You will notice that executing a simple fetch by id query even for hundreds of ids will only cost couple times more than fetching a single record. The application in question was able to fetch 2-3 GB of small documents per second during normal traffic (not an idealised performance test) with just couple dozen connections.

lifeisstillgood 4y ago
Just wanted to come back and defend you (a little :-) I read "if you have a million database connections then you are doing it wrong" less as a ad hominem attack and more as "if one is doing X one should try a different approach" I did like the "architectural strategy" of I can call it that of batching the calls. It's "tricks" like that, expressed at this level that are somehow missing from the common software dev parlance. They are not in l33tcode tests, they don't fit into neat boxes but they are vital "common knowledge" I wish I had a better term for these sort of optimisations. Anyway. Thanks for the comment. Don't take the blowback personally - frankly I was surprised even if it was a small storm in a teacup. And always do as dang says ;-)
DSingularity 4y ago
But the lost elegance!
andrewbarba 4y ago
How could you possibly say this is doing it wrong? The only way you could batch requests in the way you describe is if you have 1 (or very small number) compute nodes. You would need all those requests to hit same node so you could try and batch. With serverless compute infrastructure (which is what this blog is demonstrating by using lambda) you can have 1 isolated process per request and therefore need a database that can actually handle this kind of load.

themenomen 4y ago

Any additional insights or information on how latencies relate to number of connections?

jonahberquist 4y ago
This is more dependent on parallelism of queries rather than parallelism of connections. Having a ton of relatively idle connections won't increase the query latency. For the related topic, in https://planetscale.com/blog/one-million-queries-per-second-with-mysql https://planetscale.com/blog/one-million-queries-per-second-... we touched on saturating shards through increased parallelism resulting in latency, and then also being an indication of needing to scale more horizontally.

prithvi24 4y ago

Can ya'll sign a BAA for HIPAA? Saw Soc2 - just Hosted Vitess sounds amazing - love this - 0 downtime migrations w/ Percona on RDS still suck and waste a lot of time

aarondf 4y ago
that's above my pay grade! you can reach out to support@planetscale.com and we can give you some more information on that
samlambert 4y ago
Yes we can.

sulam 4y ago

On the off chance someone associated with this is reading: I’m curious about the networking stack here. Specifically TCP. Is it being used? The reason I ask is because one limit I’ve run into in the past with large scale workloads like this is exhausting the ephemeral port supply to allow connections from new clients. Did you run into this? If not I’m curious why not. And if so, how did you manage it?

lizztheblizz 4y ago
Article author here, interesting question! We didn't run into that issue, explicitly. Our setup was effectively as follows: - AWS Lambda functions being spawned in us-east-1, from a separate AWS sub account. - Connections were all made to the public address provisioned for MySQL protocol access to PlanetScale, using port 3306. The infrastructure did also reside in us-east-1. - Between the Vitess components themselves, and once inside our own network boundaries, we use gRPC to communicate. Since the goal we set was to hit one million, and realizing we were staying just barely within the limits of the Lambda default quotas, we didn't aggressively try to push beyond that. Some members of our infrastructure team did notice what appeared to be some kind of rate limiting when running the tests multiple times consecutively. Many tests before and after succeeded with no such issues, so we attributed it to a temporary load balancer quirk, but it might be worth going back to confirm if this is the behavior we saw.
- sulam 4y ago
  Two hypotheses — one of which you can falsify easily. Perhaps Vitess is doing port concentration? Ie dispatching requests made by multiple clients over fewer db connections? This is quite typical to do. The other is that you may have simply had a fast enough query that Little’s Law worked out for you.