7 ms·

Faster MySQL with HTTP/3

185 points by mattrobenolt 4y ago

kurtextrem 4y ago
Crazy results, really interesting experiment!
- MuffinFlavored 4y ago
  Could the same also be done for Postgre?
  - mattrobenolt 4y ago
    Anything is possible with computers.
dveeden2 4y ago
Would be nice to see a comparison with MySQL's X Protocol (protobuf based) as well as the "Classic" protocol. I don't think I've seen any MySQL compatible databases implement this protocol yet, so that's probably why it wasn't included. https://dev.mysql.com/doc/dev/mysql-server/latest/page_mysqlx_protocol.html https://dev.mysql.com/doc/dev/mysql-server/latest/page_mysql...
- mattrobenolt 4y ago
  I have vaguely looked into it, but since we don't support it at PlanetScale and unlikely we will, it didn't seem worth it. It'd be hard to construct a practical test. Similarly, since support is so low, it didn't make a lot of sense to double down and support it when we could do what works for us.
YarickR2 4y ago
This is optimizing edge cases. Handshake is done once per execution , and it's constant time , so "connect + select 1" should be measured as "connect", then "select 1". Other than that, 5ms latency is too high anyway for modules doing hundreds of small requests, so you better have read replicas real close to your code, and shard your masters.
- mattrobenolt 4y ago
  Maybe in your case, but not always. But I do explicitly call out that I intentionally wanted a test of a "cold start". This is extremely relevant for short lived applications and processes. Think PHP, and serverless environments, etc. The other tests are measuring already a warmed up connection. There's also reason why I intentionally coupled "connect + select 1" as the test, because I wanted to make it as close of a comparison as possible. If it was simply a "connect", our HTTP API would be even more favorable since connecting doesn't do authentication or anything like that like the mysql protocol does.
  - tookledoodle 4y ago
    You call out PHP in both the blog post and this comment, which is interesting, because persistent pooling has been a part of PHP (and advised) for years. Which leaves serverless and scripts (your other example from the blog post). Which, let’s be honest, are both edge cases at this point in time. Maybe that’ll change, but today it’s true. Twenty year SRE here backing up the person you’re dismissing: you’re optimizing an edge case. Literally step one of operationalizing every system in existence is burying your DB behind a pooler. 100ms off a connect call in a script is not useful. The serverless improvement has some potential, but one would be forgiven for asking why you’d use an environment which doesn’t let you speak network protocols you’d like to speak.
    - mattrobenolt 4y ago
      I'm not a PHP expert, so I don't know the landscape there fully. I do know our customer complaints and can say people care about cold start times in the PHP space and others. So while it may be an edge case for you, it's not for others. It also doesn't discredit any of the other testing that doesn't focus on cold starts. Edit since you edited yours after I posted: I'm not going to argue the merits of what platforms people choose and it's not really our position as PlanetScale to do that. We serve our customers.
      - tookledoodle 4y ago
erulabs 4y ago
Awesome work! I'm loving planetscale for lighting a new fire under the butt of MySQL again. Have been demoing Vitess (Planetscale's underlying software) and have only positive things to say so far - the Kubernetes operator is wonderful. I'm also curious about the comparison to the MySQL Classic protocol - would be interesting have an "as-close-as-possible" benchmark between Aurora MySQL "Serverless V2" and Planetscale. Even if it was as naive as "Given 100$ of credits, how many reads can you do at what average latency".
mgaunard 4y ago
Most likely the parallel requests are done using a single connection. So of course HTTP/2 will outperform, that's what it's designed to do. Now try again, but use one connection per thread, and connect it before you start benchmarking, i.e. use it the way it's meant to be used.
- mattrobenolt 4y ago
  The tests cover both cases if you read. But either way, yes, that's fundamentally a benefit of being able to use HTTP. We can multiplex multiple sessions over one underlying connection.
  - mgaunard 4y ago
    Multiplexing is already done for you by your kernel, it's called having multiple TCP sessions. The whole premise of HTTP/[23] is to do the same thing as you do with N TCP sessions, but paying for the session establishment latency only once instead of N times. And most applications couldn't care less about that latency, because you only do it once.
    - mattrobenolt 4y ago
      My apologies for not meeting your bar. I guess you missed the parts where it's faster in a lot of other cases too, and not slower in any. To me, the fact that it's not slower at all is the big win. I didn't anticipate that the results of this are going to say "this is 5x better". The stereotype is that if it's over HTTP, it must be slower. And by every measure, it's not slower. In cases, that may be edge to you, or don't care about extra latency, they're still improved. Why would you not want something that's generically better? There are many other things that are beneficial with using HTTP as a transport that haven't even been discussed here since this was entirely focused on performance. Without at least matching in performance, not many of the other things would matter.
      - mgaunard 4y ago
        99% of benchmarks are wrong and lead to misleading conclusions. You didn't provide the code so it cannot be independently reviewed. This article IMO contributes to spreading the misinformation that HTTP/[23] is useful for many applications, when it is actually a very niche protocol only useful to web browsers, or other similar applications that continuously need to connect to endpoints they don't know in advance. Web tech has already done sufficient damage by pushing HTTP/1.1 and SSL everywhere in IT, we don't need to force those protocols onto everything.
avargas 4y ago
One of the best ways I've managed latency with MySQL is basically this: 1) use persistent connections, let the OS handle them and tweak it to allow (both connecting server and mysql server). And never close the connection on the application side. (This could lead to potential deadlocks, but there are ways around it, like closing bad connections to clear thread info on mysql). 2) run the whole thing in a transaction, simply begin transaction or autocommit if allowed (same thing) Doing so, when you are done rendering the content, flush it and send the correct signal to say nginx or apache to say it's done (like PHP's fastcgi_finish_request when working with FPM), and then run your commit. Obviously used when you can safely disregard failed inserts.
- mattrobenolt 4y ago
  > 1) use persistent connections, let the OS handle them and tweak it to allow (both connecting server and mysql server). And never close the connection on the application side. (This could lead to potential deadlocks, but there are ways around it, like closing bad connections to clear thread info on mysql). This is definitely ideal, but one thing that you can't entirely control is the server side or what's between. Sometimes your connections get interrupted, and it's not possible to maintain a connection forever. Yes tho, this is the ideal thing you should do with a connection pooler. > 2) run the whole thing in a transaction, simply begin transaction or autocommit if allowed (same thing) This shouldn't really help with latency. Being in a transaction doesn't reduce latency. If we're being pedantic, it would likely increase latency due to having to execute a BEGIN and COMMIT query, which is typically two more round trips, one per query. I think what you're getting at is something like pipelining, where you can send multiple queries in one request, and get multiple results back. This is technically supported over the mysql protocol, but isn't very widely adopted in practice.
  - still_grokking 4y ago
    > but one thing that you can't entirely control is the server side or what's between. Why? If you're not running stuff on other peoples computers you're very much in control. What do I miss? > If you're not running stuff on other people's computers you're very much in control.
kamikaz1k 4y ago
Love love love planetscale. When I found it (you can thank Theo), shocked this isn't what AWS' serverless DB offering already was. I agree with what the author mentioned in another comment, not dropping performance for non-serverless use cases is a decided win. I deeply appreciate the work being done to enable serverless applications, so thank you for the work and thank you for sharing your findings OP.
- mattrobenolt 4y ago
  Much love. <3
- thatwasunusual 4y ago
  I've barely heard about PlanetScale, but from the looks of it, it looks interesting. However, is it compatible with "normal" MySQL, meaning that I just can change my connection information from `currentMySQLhost` to `planetscaleMySQLhost`, and it just works (after creating the database, tables etc., of course)?
  - mattrobenolt 4y ago
    Yup! There are a few caveats, but for the most part it'd be compatible. https://planetscale.com/docs/reference/mysql-compatibility https://planetscale.com/docs/reference/mysql-compatibility
    - thatwasunusual 4y ago
      That is pretty great, especially with such a generous free plan for testing. However, no support for FOREIGN KEYs is a bit of a bummer. However, they explain it very well.[0] Thanks for the reply! Will definitely give PlanetScale a try over the weekend! [0] https://planetscale.com/docs/learn/operating-without-foreign-key-constraints https://planetscale.com/docs/learn/operating-without-foreign...
alberth 4y ago
What's so great about PlanetScale? I feel like I live under a rock because I just don't get what's so great.
- erulabs 4y ago
  They're a hosted version of Vitess, which is sharded MySQL (and more), which is generally a pain in the butt to implement / gets built in-house at major tech companies over and over. Planetscale is doing for Vitess what Fastly does for Varnish, if that makes sense. Or maybe, what Datadog does to statd? It's a hosted platform around an awesome and complex-to-maintain bit of open source software.
muhammadusman 4y ago
I'm a huge fan of using PlanetScale, I've been using it for a few projects recently and can't wait to try out the "Boost" feature when it becomes available. I think it will reduce one more thing from my stack (caching/redis)! The developer experience with PlanetScale has been my favorite so far, I use it with a few Next.js apps and the "scaling" part has been the easiest as I haven't had to think about a burst of traffic b/c PlanetScale handles it without me lifting a finger.
exabrial 4y ago
This may be a stupid question, but wouldn't connection pooling offer the same benefit?
- mattrobenolt 4y ago
  It helps some cases for sure and what I'd strongly recommend in practice. Connection pooling isn't always a viable option depending on the application though. Connection pooling doesn't solve all the things we can improve by using HTTP as a base. We can be faster in just data transfer through compression, for example. Using HTTP/3 starts to help tail latency that we can't solve with TCP. Unreliable networks with packet loss suffer greatly with TCP and not as badly with QUIC.
  - exabrial 4y ago
    Isn't that solved by this? https://dev.mysql.com/doc/refman/5.6/en/connection-compression-control.html https://dev.mysql.com/doc/refman/5.6/en/connection-compressi...
    - mattrobenolt 4y ago
      In theory. In practice nothing implements this. But in any case, even if your client did support this and the server supported it, we still need HTTP for other things. I don't think it's particularly a "gotcha". HTTP is also stateless, which has lots of benefits for us.
      - exabrial 4y ago
        but it's here? https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-connection-compression-xdevapi.html https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-con... And it's in the C API too...
      - bgrainger 4y ago
        MySqlConnector (the most popular .NET library for MySQL, and the one that I authored) has supported protocol compression for many years: https://github.com/mysql-net/MySqlConnector/issues/31 https://github.com/mysql-net/MySqlConnector/issues/31.
deleted 4y ago
[deleted]
deleted 4y ago
[deleted]
uluyol 4y ago
I can see how you might want HTTP/3 across data centers, but you may want to stick to HTTP/2 for intra-DC workloads. There's a ton of work to optimize TCP including hardware offloads that help push higher throughput. Basically we're talking library + kernel + hardware changes. It might be possible to get some of these into QUIC, but since QUIC is most compelling for WAN traffic, there's probably not much incentive for that.
- mattrobenolt 4y ago
  Mostly agree here. I don't think in practice, you'd default want to use HTTP/3. I think as technology, it's still just very immature for reasons you mentioned. In our case though, lots of our customers and lots of use cases do communicate over a WAN, and potentially large geographic distances. I think having this as an option is super interesting to see what we can do with it in the future.

185 points by mattrobenolt 4y ago

kurtextrem 4y ago

Crazy results, really interesting experiment!

MuffinFlavored 4y ago
Could the same also be done for Postgre?
- mattrobenolt 4y ago
  Anything is possible with computers.

dveeden2 4y ago

Would be nice to see a comparison with MySQL's X Protocol (protobuf based) as well as the "Classic" protocol. I don't think I've seen any MySQL compatible databases implement this protocol yet, so that's probably why it wasn't included. https://dev.mysql.com/doc/dev/mysql-server/latest/page_mysqlx_protocol.html https://dev.mysql.com/doc/dev/mysql-server/latest/page_mysql...

mattrobenolt 4y ago
I have vaguely looked into it, but since we don't support it at PlanetScale and unlikely we will, it didn't seem worth it. It'd be hard to construct a practical test. Similarly, since support is so low, it didn't make a lot of sense to double down and support it when we could do what works for us.

YarickR2 4y ago

This is optimizing edge cases. Handshake is done once per execution , and it's constant time , so "connect + select 1" should be measured as "connect", then "select 1". Other than that, 5ms latency is too high anyway for modules doing hundreds of small requests, so you better have read replicas real close to your code, and shard your masters.

mattrobenolt 4y ago
Maybe in your case, but not always. But I do explicitly call out that I intentionally wanted a test of a "cold start". This is extremely relevant for short lived applications and processes. Think PHP, and serverless environments, etc. The other tests are measuring already a warmed up connection. There's also reason why I intentionally coupled "connect + select 1" as the test, because I wanted to make it as close of a comparison as possible. If it was simply a "connect", our HTTP API would be even more favorable since connecting doesn't do authentication or anything like that like the mysql protocol does.
- tookledoodle 4y ago
  You call out PHP in both the blog post and this comment, which is interesting, because persistent pooling has been a part of PHP (and advised) for years. Which leaves serverless and scripts (your other example from the blog post). Which, let’s be honest, are both edge cases at this point in time. Maybe that’ll change, but today it’s true. Twenty year SRE here backing up the person you’re dismissing: you’re optimizing an edge case. Literally step one of operationalizing every system in existence is burying your DB behind a pooler. 100ms off a connect call in a script is not useful. The serverless improvement has some potential, but one would be forgiven for asking why you’d use an environment which doesn’t let you speak network protocols you’d like to speak.
  - mattrobenolt 4y ago
    I'm not a PHP expert, so I don't know the landscape there fully. I do know our customer complaints and can say people care about cold start times in the PHP space and others. So while it may be an edge case for you, it's not for others. It also doesn't discredit any of the other testing that doesn't focus on cold starts. Edit since you edited yours after I posted: I'm not going to argue the merits of what platforms people choose and it's not really our position as PlanetScale to do that. We serve our customers.
    - tookledoodle 4y ago

erulabs 4y ago

Awesome work! I'm loving planetscale for lighting a new fire under the butt of MySQL again. Have been demoing Vitess (Planetscale's underlying software) and have only positive things to say so far - the Kubernetes operator is wonderful. I'm also curious about the comparison to the MySQL Classic protocol - would be interesting have an "as-close-as-possible" benchmark between Aurora MySQL "Serverless V2" and Planetscale. Even if it was as naive as "Given 100$ of credits, how many reads can you do at what average latency".

mgaunard 4y ago

Most likely the parallel requests are done using a single connection. So of course HTTP/2 will outperform, that's what it's designed to do. Now try again, but use one connection per thread, and connect it before you start benchmarking, i.e. use it the way it's meant to be used.

mattrobenolt 4y ago
The tests cover both cases if you read. But either way, yes, that's fundamentally a benefit of being able to use HTTP. We can multiplex multiple sessions over one underlying connection.
- mgaunard 4y ago
  Multiplexing is already done for you by your kernel, it's called having multiple TCP sessions. The whole premise of HTTP/[23] is to do the same thing as you do with N TCP sessions, but paying for the session establishment latency only once instead of N times. And most applications couldn't care less about that latency, because you only do it once.
  - mattrobenolt 4y ago
    My apologies for not meeting your bar. I guess you missed the parts where it's faster in a lot of other cases too, and not slower in any. To me, the fact that it's not slower at all is the big win. I didn't anticipate that the results of this are going to say "this is 5x better". The stereotype is that if it's over HTTP, it must be slower. And by every measure, it's not slower. In cases, that may be edge to you, or don't care about extra latency, they're still improved. Why would you not want something that's generically better? There are many other things that are beneficial with using HTTP as a transport that haven't even been discussed here since this was entirely focused on performance. Without at least matching in performance, not many of the other things would matter.
    - mgaunard 4y ago
      99% of benchmarks are wrong and lead to misleading conclusions. You didn't provide the code so it cannot be independently reviewed. This article IMO contributes to spreading the misinformation that HTTP/[23] is useful for many applications, when it is actually a very niche protocol only useful to web browsers, or other similar applications that continuously need to connect to endpoints they don't know in advance. Web tech has already done sufficient damage by pushing HTTP/1.1 and SSL everywhere in IT, we don't need to force those protocols onto everything.

avargas 4y ago

One of the best ways I've managed latency with MySQL is basically this: 1) use persistent connections, let the OS handle them and tweak it to allow (both connecting server and mysql server). And never close the connection on the application side. (This could lead to potential deadlocks, but there are ways around it, like closing bad connections to clear thread info on mysql). 2) run the whole thing in a transaction, simply begin transaction or autocommit if allowed (same thing) Doing so, when you are done rendering the content, flush it and send the correct signal to say nginx or apache to say it's done (like PHP's fastcgi_finish_request when working with FPM), and then run your commit. Obviously used when you can safely disregard failed inserts.

mattrobenolt 4y ago
> 1) use persistent connections, let the OS handle them and tweak it to allow (both connecting server and mysql server). And never close the connection on the application side. (This could lead to potential deadlocks, but there are ways around it, like closing bad connections to clear thread info on mysql). This is definitely ideal, but one thing that you can't entirely control is the server side or what's between. Sometimes your connections get interrupted, and it's not possible to maintain a connection forever. Yes tho, this is the ideal thing you should do with a connection pooler. > 2) run the whole thing in a transaction, simply begin transaction or autocommit if allowed (same thing) This shouldn't really help with latency. Being in a transaction doesn't reduce latency. If we're being pedantic, it would likely increase latency due to having to execute a BEGIN and COMMIT query, which is typically two more round trips, one per query. I think what you're getting at is something like pipelining, where you can send multiple queries in one request, and get multiple results back. This is technically supported over the mysql protocol, but isn't very widely adopted in practice.
- still_grokking 4y ago
  > but one thing that you can't entirely control is the server side or what's between. Why? If you're not running stuff on other peoples computers you're very much in control. What do I miss? > If you're not running stuff on other people's computers you're very much in control.

kamikaz1k 4y ago

Love love love planetscale. When I found it (you can thank Theo), shocked this isn't what AWS' serverless DB offering already was. I agree with what the author mentioned in another comment, not dropping performance for non-serverless use cases is a decided win. I deeply appreciate the work being done to enable serverless applications, so thank you for the work and thank you for sharing your findings OP.

mattrobenolt 4y ago
Much love. <3
thatwasunusual 4y ago
I've barely heard about PlanetScale, but from the looks of it, it looks interesting. However, is it compatible with "normal" MySQL, meaning that I just can change my connection information from `currentMySQLhost` to `planetscaleMySQLhost`, and it just works (after creating the database, tables etc., of course)?
- mattrobenolt 4y ago
  Yup! There are a few caveats, but for the most part it'd be compatible. https://planetscale.com/docs/reference/mysql-compatibility https://planetscale.com/docs/reference/mysql-compatibility
  - thatwasunusual 4y ago
    That is pretty great, especially with such a generous free plan for testing. However, no support for FOREIGN KEYs is a bit of a bummer. However, they explain it very well.[0] Thanks for the reply! Will definitely give PlanetScale a try over the weekend! [0] https://planetscale.com/docs/learn/operating-without-foreign-key-constraints https://planetscale.com/docs/learn/operating-without-foreign...

alberth 4y ago

What's so great about PlanetScale? I feel like I live under a rock because I just don't get what's so great.

erulabs 4y ago
They're a hosted version of Vitess, which is sharded MySQL (and more), which is generally a pain in the butt to implement / gets built in-house at major tech companies over and over. Planetscale is doing for Vitess what Fastly does for Varnish, if that makes sense. Or maybe, what Datadog does to statd? It's a hosted platform around an awesome and complex-to-maintain bit of open source software.

muhammadusman 4y ago

I'm a huge fan of using PlanetScale, I've been using it for a few projects recently and can't wait to try out the "Boost" feature when it becomes available. I think it will reduce one more thing from my stack (caching/redis)! The developer experience with PlanetScale has been my favorite so far, I use it with a few Next.js apps and the "scaling" part has been the easiest as I haven't had to think about a burst of traffic b/c PlanetScale handles it without me lifting a finger.

exabrial 4y ago

This may be a stupid question, but wouldn't connection pooling offer the same benefit?

mattrobenolt 4y ago
It helps some cases for sure and what I'd strongly recommend in practice. Connection pooling isn't always a viable option depending on the application though. Connection pooling doesn't solve all the things we can improve by using HTTP as a base. We can be faster in just data transfer through compression, for example. Using HTTP/3 starts to help tail latency that we can't solve with TCP. Unreliable networks with packet loss suffer greatly with TCP and not as badly with QUIC.
- exabrial 4y ago
  Isn't that solved by this? https://dev.mysql.com/doc/refman/5.6/en/connection-compression-control.html https://dev.mysql.com/doc/refman/5.6/en/connection-compressi...
  - mattrobenolt 4y ago
    In theory. In practice nothing implements this. But in any case, even if your client did support this and the server supported it, we still need HTTP for other things. I don't think it's particularly a "gotcha". HTTP is also stateless, which has lots of benefits for us.
    - exabrial 4y ago
      but it's here? https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-connection-compression-xdevapi.html https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-con... And it's in the C API too...
    - bgrainger 4y ago
      MySqlConnector (the most popular .NET library for MySQL, and the one that I authored) has supported protocol compression for many years: https://github.com/mysql-net/MySqlConnector/issues/31 https://github.com/mysql-net/MySqlConnector/issues/31.

deleted 4y ago

[deleted]

deleted 4y ago

[deleted]

uluyol 4y ago

I can see how you might want HTTP/3 across data centers, but you may want to stick to HTTP/2 for intra-DC workloads. There's a ton of work to optimize TCP including hardware offloads that help push higher throughput. Basically we're talking library + kernel + hardware changes. It might be possible to get some of these into QUIC, but since QUIC is most compelling for WAN traffic, there's probably not much incentive for that.

mattrobenolt 4y ago
Mostly agree here. I don't think in practice, you'd default want to use HTTP/3. I think as technology, it's still just very immature for reasons you mentioned. In our case though, lots of our customers and lots of use cases do communicate over a WAN, and potentially large geographic distances. I think having this as an option is super interesting to see what we can do with it in the future.