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—
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
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.
send a prepare message
receive some messages
send an execute message
receive some messages
send a close message
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:
(define IPPROTO_TCP 6) (define TCP_NODELAY 1) (define setsockopt_tcp_nodelay (get-ffi-obj "setsockopt" #f (_fun (socket enabled?) :: (socket : _int) (_int = IPPROTO_TCP) (_int = TCP_NODELAY) (enabled-ptr : (_ptr i _int) = (if enabled? 1 0)) (_int = (compiler-sizeof 'int)) -> (result : _int) -> (if (zero? result) (void) (error 'set-tcp-nodelay! "failed"))))) (define scheme_get_port_socket (get-ffi-obj "scheme_get_port_socket" #f (_fun (port) :: (port : _racket) (socket : (_ptr o _intptr)) -> (result : _int) -> (and (positive? result) socket)))) ; set-tcp-nodelay! : tcp-port boolean -> void (define (set-tcp-nodelay! port enabled?) (let ([socket (scheme_get_port_socket port)]) (setsockopt_tcp_nodelay socket enabled?)))
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.