When implementing a wire protocol, one occasionally needs to know something about the wires. This blog post is the story of how the placement of a call to flush-output caused a factor-of-20 variation in performance.

(If you’re an experienced network programmer, everything I’m about to say may be old hat, basic knowledge. It’s new to me, though, so I thought I’d share.)

As I previously mentioned, the Racket db library always prepares statements before executing them, even if the statement is supplied directly as a SQL string, and even if no query parameters are given.

Prepared statements involve server-side resources. Normally, prepared statements (on the server side) are closed when the corresponding prepared statement object (on the Racket side) becomes unreachable; a finalizer issues the commands to release the server-side resource. A busy program, however, can create prepared statements far faster than the garbage collector can finalize them, so it makes sense to add an optimization: a prepared statement created for the execution of a SQL string is closed immediately after execution. (When the statement cache is used, it’s more complicated but fundamentally similar—a set of statements is closed at once when the cache is flushed.)

This “close-on-exec” optimization works great for PostgreSQL, SQLite, and ODBC connections, but it caused the execution of the test suite to slow down dramatically—by a factor of roughly 20—for MySQL connections. “Maybe closing prepared statements is just really slow in MySQL,” I thought. “I’ll look into it again right after I implement a few more Really Cool Features, like nested transaction support.” After all, the lack of close-on-exec isn’t an issue for short-lived connections—where “short-lived” means less than several thousand statements. It’s not a problem if the connection gets occasional breaks long enough for the garbage collector to catch up. It’s even okay if the same statement is executed (possibly with different parameters) many times within a transaction, because of the statement cache. But it’s a problem for very busy connections that miss (or don’t use) the statement cache, and basic stress tests cause MySQL connections to quickly hit the prepared statement limit and die.

It turns out that the problem with “close-on-exec” for MySQL connections is due to a bad interaction between the structure of the MySQL protocol and TCP’s anti-congestion tricks.

Preparing a statement involves sending a message to the server and getting several messages back: parameter and result descriptions, etc. Executing a statement involves sending a message to the server and getting several messages back: data rows and a status message. Closing a statement involves sending a message to the server... and not getting any messages back.

A 20x slowdown, for this.

One solution, the one used by the MySQL client according to the bug discussion that helped me figure out the problem, is to just disable Nagle’s algorithm by setting the TCP_NODELAY socket option. That’s not too hard to do in Racket using the FFI. Here’s the code:

Calling set-tcp-nodelay! on MySQL connections’ TCP ports reduces the real running time of the db test suite from 54 seconds to 2.8 seconds. Success!

But there’s an even simpler solution that doesn’t involve fussing with socket options. There’s no great urgency to the close statement message. So instead of sending it at the end of the query cycle, we just buffer it without flushing output. That way it gets sent at the start of the next query cycle in the same packet as the prepare message. The prepare message causes the server to send data back immediately, eliminating the ACK delay. This solution performs just as well as the TCP_NODELAY solution, and the changes to the code are minimal.