4 ms·

While the second and third problems sound specific to Go's `database/sql` API, the first problem (server-closed connections) is an issue any MySQL client librar

While the second and third problems sound specific to Go's `database/sql` API, the first problem (server-closed connections) is an issue any MySQL client library (that implements connection pooling) has to deal with. My .NET MySQL connector (https://github.com/mysql-net/MySqlConnector https://github.com/mysql-net/MySqlConnector) "solves" it at the MySQL level by sending a PING packet, but as the post points out, this adds extra latency. The TCP-based approach of performing a non-blocking read sounds like a much better approach; I'm glad the author shared this, and I want to see if that technique can also be implemented in MySqlConnector: https://github.com/mysql-net/MySqlConnector/issues/821 https://github.com/mysql-net/MySqlConnector/issues/821.

wahern 6y ago
The non-blocking read is a bad hack to an ill-defined problem. For starters, if a read would fail, so would a write. Your write path has to handle errors anyhow, so the non-blocking read is a contrivance. I understand the reason for the contrivance--it's because the typical SQL driver API requires first dequeueing a connection from a pool, passing ownership to a caller, and the caller sending a query. The proper and better API is to combine the "dequeue connection" and "send query" steps, returning a handle back for the response, which may include (as an optimization) assigning the connection to the caller for any additional requests. Additionally, any hack that prevents the majority of write failures, but not all, leaves time bombs around by not stressing the write failure paths of the caller. Secondly, IME a far more common reason in the wild for a connection disappearing is either a) the server crashing or b) NAT association timeout. In either case it's typical for the remote end to simply disappear, and unless TCP keepalive is enabled, a read won't return any error until you first attempt a write and wait for the TCP stack to figure out that the remote end is gone. In the best case that's a R/T, in the worst case some eager firewall rules in the middle or on the remote peer silently drop packets for unassociated connections and/or the ICMP response and you're stuck waiting for TCP timeout. Thirdly, and related to #2, absent TCP keepalive or an application ping (not ICMP ping), connections are far more likely to get dropped on the floor while sitting in the pool. And an application ping is more reliable because some firewalls and application and NAT gateways drop or ignore TCP keepalive. I've written client libraries with connection pooling for HTTP, MySQL, SMTP, RTSP and generic TCP. I learned all this the hard way.
- infogulch 6y ago
  The usual implementation of a using a Connection type tends to couple the persistent-socket connection domain with the authorization domain. But if you defer connection management to some outer scope, then the Connection type remains with just authorization functionality. (To the extent that you even authorize on a per-query basis.) That's an interesting idea.
- dub 6y ago
  The article says the non-blocking read resolved the problem they were having while only introducing five microseconds of latency per borrow from the connection pool. For a consumer-facing, latency-sensitive application, that sounds like much easier-to-stomach hack than introducing a network round-trip at the start of every connection borrow.
  - user5994461 6y ago
    It's funny you say it resolves the problem because if you read the bug report from the author opened since 2017 https://github.com/go-sql-driver/mysql/issues/657 https://github.com/go-sql-driver/mysql/issues/657 It's repeatedly pointed out by one maintainer that his server and application are misconfigured and should be fixed. Yet the author doesn't consider it and goes on a project to redo the connection pool (the library can certainly benefit from better error detection but he would be better off fixing the root cause of connections dropping). Ergo the issue is not fixed. If you look at the chart provided, it's still erroring every few minutes, instead of every minute. It's an improvement but a far cry from zero. https://user-images.githubusercontent.com/42793/57318792-6e3b6800-70fb-11e9-87f7-f9d69e8a3c7b.png https://user-images.githubusercontent.com/42793/57318792-6e3...
    - yencabulator 6y ago
      There's a fundamental unfixable race there: a connection can be picked from the pool while a FIN is already on its way to the client. This is "caused" (as in, not mitigated) by the intersection of a naive protocol, as pretty much all SQL protocols are, and the nature of distributed programming.
twhitmore 6y ago
If the server is closing idle connections, the client connection pool should track idle times & close them earlier. Checking for readability still has a race condition. Really this seems like a poor example of configuration.

wahern 6y ago

The non-blocking read is a bad hack to an ill-defined problem. For starters, if a read would fail, so would a write. Your write path has to handle errors anyhow, so the non-blocking read is a contrivance. I understand the reason for the contrivance--it's because the typical SQL driver API requires first dequeueing a connection from a pool, passing ownership to a caller, and the caller sending a query. The proper and better API is to combine the "dequeue connection" and "send query" steps, returning a handle back for the response, which may include (as an optimization) assigning the connection to the caller for any additional requests. Additionally, any hack that prevents the majority of write failures, but not all, leaves time bombs around by not stressing the write failure paths of the caller. Secondly, IME a far more common reason in the wild for a connection disappearing is either a) the server crashing or b) NAT association timeout. In either case it's typical for the remote end to simply disappear, and unless TCP keepalive is enabled, a read won't return any error until you first attempt a write and wait for the TCP stack to figure out that the remote end is gone. In the best case that's a R/T, in the worst case some eager firewall rules in the middle or on the remote peer silently drop packets for unassociated connections and/or the ICMP response and you're stuck waiting for TCP timeout. Thirdly, and related to #2, absent TCP keepalive or an application ping (not ICMP ping), connections are far more likely to get dropped on the floor while sitting in the pool. And an application ping is more reliable because some firewalls and application and NAT gateways drop or ignore TCP keepalive. I've written client libraries with connection pooling for HTTP, MySQL, SMTP, RTSP and generic TCP. I learned all this the hard way.

infogulch 6y ago
The usual implementation of a using a Connection type tends to couple the persistent-socket connection domain with the authorization domain. But if you defer connection management to some outer scope, then the Connection type remains with just authorization functionality. (To the extent that you even authorize on a per-query basis.) That's an interesting idea.
dub 6y ago
The article says the non-blocking read resolved the problem they were having while only introducing five microseconds of latency per borrow from the connection pool. For a consumer-facing, latency-sensitive application, that sounds like much easier-to-stomach hack than introducing a network round-trip at the start of every connection borrow.
- user5994461 6y ago
  It's funny you say it resolves the problem because if you read the bug report from the author opened since 2017 https://github.com/go-sql-driver/mysql/issues/657 https://github.com/go-sql-driver/mysql/issues/657 It's repeatedly pointed out by one maintainer that his server and application are misconfigured and should be fixed. Yet the author doesn't consider it and goes on a project to redo the connection pool (the library can certainly benefit from better error detection but he would be better off fixing the root cause of connections dropping). Ergo the issue is not fixed. If you look at the chart provided, it's still erroring every few minutes, instead of every minute. It's an improvement but a far cry from zero. https://user-images.githubusercontent.com/42793/57318792-6e3b6800-70fb-11e9-87f7-f9d69e8a3c7b.png https://user-images.githubusercontent.com/42793/57318792-6e3...
  - yencabulator 6y ago
    There's a fundamental unfixable race there: a connection can be picked from the pool while a FIN is already on its way to the client. This is "caused" (as in, not mitigated) by the intersection of a naive protocol, as pretty much all SQL protocols are, and the nature of distributed programming.

twhitmore 6y ago

If the server is closing idle connections, the client connection pool should track idle times & close them earlier. Checking for readability still has a race condition. Really this seems like a poor example of configuration.