Post Syndicated from Pranoy Dutta original https://blog.cloudflare.com/hyperdrive-mysql/

Hyperdrive support for MySQL is now generally available (GA). Hyperdrive, Cloudflare’s database connection pooler for Workers, initially only supported Postgres. Since we announced the beta for MySQL protocol support last year, we have been working on making our MySQL handling as reliable as our Postgres support.

We’ve learned some things from building and running a MySQL connection pooler on Cloudflare’s network. It turns out that the difference between beta and GA is just 20 bug fixes in a trenchcoat.

During the beta, our goal was to validate that real-world customer usage hit our production availability target of 99.99%. We knew there were gaps in our reliability since we track how often client connections from a Worker end in an error related to Hyperdrive. In addition, we had feedback from customers in the beta that led to the discovery of a rare and subtle bug in our production environment. To convey the complexity of this particular bug, we first need to explain how Hyperdrive works.

What is Hyperdrive?

Hyperdrive is a distributed database connection pooler that connects Cloudflare Workers to SQL databases. Hyperdrive holds warm SQL connections open on our global network and shares these connections across many Worker requests. In addition to connection pooling, Hyperdrive can also cache common queries in our global CDN cache, reducing latency and load on the origin database.

Establishing a connection to a SQL database is expensive, taking a minimum of five network round trips. On a serverless compute platform like Workers, establishing a connection for each request would be impractical, both because of the amount of latency added to each request, but also because a large number of open connections can incur high per-connection resource costs. While MySQL fares better here than Postgres since MySQL doesn’t use an entire OS process per connection, a typical deployment can still run into resource issues with a large number of connections.

For more details, see our blog post introducing Hyperdrive.

A journey to stability

Some of the issues we ran into were small and easy to fix once reported. One of the first problems reported in the beta was that emojis written to the database were not being correctly returned. This was a bug related to character set encoding. Hyperdrive negotiates the encoding for the connection in the startup handshake and we negotiate utf8 as the encoding. As it turns out, utf8 is a deprecated alias for utf8mb3 which is not all of UTF-8: it only supports 1-3 bytes per Unicode code point, while full UTF-8 has a max of 4 bytes per code point. To get all of UTF-8, we needed to change our handshake to use utf8mb4 , which fixed the emoji issue.

Another encoding-related bug occurred when we added connection cleanup via COM_RESET_CONNECTION. This is a lightweight MySQL command to clear any session state in a given connection, which is exactly what Hyperdrive needs to ensure that no session-level state leaks between checkouts. But adding this connection cleanup step caused encoding issues once again! It turns out that COM_RESET_CONNECTION does not reset the connection state to the character set encoding we negotiated at connection startup; it instead resets the connection to the server default. This means that after negotiating utf8mb4 on a MySQL server instance that defaults to latin1, once we run COM_RESET_CONNECTION, our connection now has an encoding of latin1. The fix is to always run a command setting the encoding to utf8mb4 after a connection reset.

A hard-to-find bug

During the beta, our highest-volume MySQL customer reported a small but consistent rate of empty query responses for which a non-empty response was always expected. We started the investigation by combing through all of our telemetry and understanding the user’s logic. Then we also increased the trace rate for this Hyperdrive instance, hoping to see the execution flow of this bug. We searched for network issues that could explain the issue — but nothing seemed to account for this behavior. This error occurred about 0.001% of the time, which made it very hard to find relevant end-to-end execution traces.

But we did have one clue: the bug only occurred on Hyperdrive instances with caching enabled. This made us suspect an issue with Hyperdrive’s MySQL caching.

Hyperdrive caching

To understand Hyperdrive's caching, we first need to talk about Hyperdrive's topology and how it interacts with the CDN cache. Every Hyperdrive instance has a split architecture with two nodes: a Client and an Endpoint. The Client node runs on the same machine as the user’s Worker, handles local caching (if enabled), and forwards queries to the Endpoint if needed. The Endpoint node holds the connection pool and is placed in the data center closest to the origin database. Each node uses the Cloudflare CDN cache local to the data center it is running in. Importantly, this means that if the two nodes are running in the same data center, they will share a cache.

Sharing the cache between Client and Endpoint is generally good for performance. We use a hash of the contents of the query’s wire messages with some normalization and store the result bytes in the cache. If a Hyperdrive Client running in the same data center as its Endpoint receives a request, and that Endpoint has recently cached the results of the same query, the Client can serve the response directly from the shared data center cache. To work correctly, this scheme requires that the same bytes are cached for the same response, whether stored by the Client or the Endpoint.

MySQL client/server protocol

Since we couldn’t get a direct trace of the issue, we decided to spend time looking at the MySQL client / server protocol. Perhaps we had an assumption about Postgres’ protocol built into our MySQL handling that was causing these issues.

The protocol describes how a MySQL client communicates with a MySQL server. This can be somewhat confusing in the case of database connection pooling since we have two components acting like clients and two acting like servers. The first client is the Worker using a MySQL client library, and it connects to the first component acting like a server, the Hyperdrive Client. The Hyperdrive Client connects to the Hyperdrive Endpoint which acts as a MySQL client connecting to the actual MySQL origin server.

This protocol starts with the Connection Phase where the MySQL client and the MySQL server agree upon a common authentication method and capabilities. Once a connection is established, the client and server begin the Command Phase, where the client sends commands (e.g. SQL statements) to be executed with the server responding and waiting for the next command.

Capabilities are exchanged during the Connection Phase to support protocol evolution while remaining backwards compatible. Each capability is a feature, like supporting compression during the Command Phase. The clients and servers must only use features which the other supports, otherwise a protocol desync can occur, leading to unexpected behavior.

This capability negotiation can be tricky for Hyperdrive, since Hyperdrive acts as a client to the origin database, and acts as a server to the Worker. To ensure that Hyperdrive can parse the messages from the origin MySQL server, Hyperdrive has a fixed set of capability flags which are used to communicate to the origin database. Since it is possible for Hyperdrive’s set of capabilities to be different from the Worker client’s set of capabilities, Hyperdrive needs to translate all origin responses encoded by its capabilities to the capabilities that the Worker expects.

While trying to debug this issue, we found that a popular MySQL client library for Node.js (mysql2) does not currently support the CLIENT_DEPRECATE_EOF capability, which changes how result sets are terminated. Without this capability, the server sends an EOF packet between the column definitions and rows, then another EOF packet at the end of the result set. When CLIENT_DEPRECATE_EOF is negotiated, the metadata EOF packet is omitted and the final EOF packet is replaced with an OK packet. MySQL server 5.7.5 and later supports this capability, but the newer OK-packet behavior is only used when the client negotiates it.

While Hyperdrive does support the CLIENT_DEPRECATE_EOF capability, it’s possible that the Worker’s MySQL driver does not. To handle this, Hyperdrive adjusts its behavior to support the capabilities that the Worker client driver expects. This means rewriting result sets to include EOFs and to translate all OK packets to EOFs when required. 

After days of looking through telemetry, documentation, and Hyperdrive code, everything clicked. We finally came across the last missing piece: a faulty assumption in our Hyperdrive Client caching code. We baked in an assumption when implementing the Client caching for Postgres: that we can cache exactly the bytes we wrote back to the Worker. While this was a reasonable assumption to make with Postgres’ wire protocol, it no longer holds due to the mismatch in MySQL capabilities. If a Client node is caching exactly what it writes back to the Worker, these bytes are in a different format than what an Endpoint node would be expecting. That’s because the Endpoint node is always working with Hyperdrive’s capabilities, while the Client node writes back bytes that are translated to match the Worker’s capabilities.

Now if we put all these clues together, we can understand scenarios where Hyperdrive can return a MySQL response encoded with the wrong capabilities, leading to the unexpected empty results and hangs. The bug occurs when both the Client and Endpoint are in the same data center, sharing a cache, and when one reads a value cached by the other.

Here’s a closer look at two such scenarios.

Endpoint caches, Client reads

1. The Endpoint is in the middle of servicing a request for query Q. It gets back a response R from the origin server and caches response result bytes R_hyperdrive (Response encoded with Hyperdrive capabilities) into the shared cache.
2. Client receives another request for query Q, checks the shared cache and finds R_hyperdrive.
3. Client responds to the Worker with R_hyperdrive without translating to the Worker’s capabilities.

This diagram shows that flow:

Client caches, Endpoint reads

1. Client is finishing serving a request for query Q, and it writes the translated response bytes R_worker to cache.
2. Endpoint is in the middle of servicing a request for Q, and checks the cache to find R_worker.
3. Endpoint sends R_worker to a different Client, and this Client tries to translate R_worker to the Worker’s capabilities, and ends up sending an early EOF or hangs.

You can see that here:

Once we found this type of failure mode, the fix was simple. We now make sure that we always write back a canonical version of the query result bytes in the cache encoded using Hyperdrive’s capabilities, no matter which node stored it. After releasing this change, the empty row problem went away — and our success rate for Client connections reached 99.99%.

Reliability is a feature

You can use Hyperdrive support for MySQL today! Create a Hyperdrive to an existing MySQL-compatible database via the dashboard or wrangler, or connect to a new PlanetScale database. If you run into any issues or have questions, reach out on our Discord. 

npx wrangler hyperdrive create HYPERDRIVE_NAME --connection-string="mysql://user:[email protected]:3306/dbname"