4 ms·

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 anyho

by 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.
    - user5994461 6y ago
      You realize the database server was misconfigured to drop connections transparently after 30 seconds? If it were not for that, TCP race conditions are extremely rare.
tankenmate 6y ago
TCP connections are not necessarily symmetric, if a read fails doesn't necessarily mean a write will fail; there is a difference between shutdown() and close() (check a packet dump or this stackoverflow question; https://stackoverflow.com/questions/11436013/writing-to-a-closed-local-tcp-socket-not-failing https://stackoverflow.com/questions/11436013/writing-to-a-cl...). The non blocking read method is fine if you wish to try to treat any client connection with a server initiated FIN as non useable (since the client will never be able to read a response from the server); it cleanly distinguishes on the client side between a CLOSE_WAIT and ESTABLISHED but with an empty read buffer.
user5994461 6y ago
>>> a far more common reason in the wild for a connection disappearing is... You are correct about the failure modes and mitigations in the general case, however for MySQL the most common reason by far for connections to disappear is the wait_timeout systematically being misconfigured and way too short. Databases configured to drop connections every few seconds do drop client connections all the time. No surprise.
jerf 6y ago
"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." It wouldn't be an optimization; you need it for transactions for DBs where transactions are tied to the connections, which is I believe basically all of them. It seems like this would still leave "I sent a request and it was completed but I never heard that it was, so now I don't know if I need to resend it" unaffected... I find myself concluding that there's a certain amount of fundamental brokenness in trying to communicate with a database with a mutable-data based protocol over TCP, period. I'm not sure that can necessarily be resolved in any practical manner, but there may be ways of moving around the fundamental brokenness, once it is acknowledged to exist, to be less like to bite, and less likely to bite hard, and easier to detect if it happens.

by wahern 6y ago

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.
  - user5994461 6y ago
    You realize the database server was misconfigured to drop connections transparently after 30 seconds? If it were not for that, TCP race conditions are extremely rare.

tankenmate 6y ago

TCP connections are not necessarily symmetric, if a read fails doesn't necessarily mean a write will fail; there is a difference between shutdown() and close() (check a packet dump or this stackoverflow question; https://stackoverflow.com/questions/11436013/writing-to-a-closed-local-tcp-socket-not-failing https://stackoverflow.com/questions/11436013/writing-to-a-cl...). The non blocking read method is fine if you wish to try to treat any client connection with a server initiated FIN as non useable (since the client will never be able to read a response from the server); it cleanly distinguishes on the client side between a CLOSE_WAIT and ESTABLISHED but with an empty read buffer.

user5994461 6y ago

>>> a far more common reason in the wild for a connection disappearing is... You are correct about the failure modes and mitigations in the general case, however for MySQL the most common reason by far for connections to disappear is the wait_timeout systematically being misconfigured and way too short. Databases configured to drop connections every few seconds do drop client connections all the time. No surprise.

jerf 6y ago

"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." It wouldn't be an optimization; you need it for transactions for DBs where transactions are tied to the connections, which is I believe basically all of them. It seems like this would still leave "I sent a request and it was completed but I never heard that it was, so now I don't know if I need to resend it" unaffected... I find myself concluding that there's a certain amount of fundamental brokenness in trying to communicate with a database with a mutable-data based protocol over TCP, period. I'm not sure that can necessarily be resolved in any practical manner, but there may be ways of moving around the fundamental brokenness, once it is acknowledged to exist, to be less like to bite, and less likely to bite hard, and easier to detect if it happens.