I started a new job a little while ago and I’m learning technologies that I was not exposed to at my previous employer. In fact, I took this job specifically knowing that I would be working with unfamiliar technologies.
At my previous job I supported MySQL and saw the myriad issues that customer encountered while running MySQL in their environments. One relatively recent issue came with MySQL 8.0.27 and Innodb ClusterSets. This is a new feature that allows you to create interconnected Innodb Clusters, and one of the steps in doing that is to CLONE a new primary host in the child cluster. The mysqlsh tool is used for this purpose, but when executing the CLONE from the primary side on GCP the operation would fail.
The problem was that the connection being used by mysqlsh was severed, but mysqlsh was never “informed” about this. The underlying cause seems to be the software defined nature of cloud computing wearing through the thin veneer. It was found that the connection would not be terminated if mysqlsh was run on the replica server (where data was being CLONEd to). I wasn’t directly handling this particular issue, simply advising another engineer and reviewing data.
Today I encountered this problem in my new job when a benchmark was creating indexes on a large table it would fail with a Lost connection...2013 error. After scrutinizing the MySQL source code for a while, and performing some strace operations of my own, I concluded that MySQL was not at fault and was not doing anything to cause a client connection to timeout.
I decided to adjust the Linux kernel sysctl variable net.ipv4.tcp_keepalive_time to 300 seconds and see if it had an effect, it did and the outcome was exactly what I’d hoped for. I did some further testing with select sleep(900) and found that GCP silently evicts the idle TCP connection right around 900 seconds since the command was issued.
Why does adjusting the tcp_keepalive_time make a difference? The MySQL client marks the socket connection as “keepalive” by setting the option SO_KEEPALIVE, this causes the Linux kernel to start sending “keepalive” packets ater the tcp_keepalive_time expires. The default value for tcp_keepalive_time is 7200 seconds and after that expires the kernel sends keepalives every 75 seconds to keep the channel open. Setting the tcp_keepalive_time to a value lower than the GCP eviction timeout will prevent connections that are waiting on a long running task from closing, such as index creation on a large table.