Using Keep-Alives To Ensure Long-Running MySQL & MariaDB Sessions Stay Connected

Published Date Author: , Posted June 14th, 2019 at 6:29:40am

Overview

The Skinny

In this blog post we will discuss how to use the Tungsten Connector keep-alive feature to ensure long-running MySQL & MariaDB/Percona Server client sessions stay connected in a Tungsten Cluster.


Agenda

What’s Here?
  • Briefly explore how the Tungsten Connector works
  • Describe the Connector keep-alives – what are they and why do we use them?
  • Discuss why the keep-alive feature is not available in Bridge mode and why
  • Examine how to tune the keep-alive feature in the Tungsten Connector

Tungsten Connector: A Primer

A Very Brief Summary

The Tungsten Connector is an intelligent MySQL database proxy located between the clients and the database servers, providing a single connection point, while routing queries to the database servers.

√ High-Availability

The most important function of the Connector is failover handling.

In the event of a failure, the Tungsten Connector can automatically route queries away from the failed server and towards servers that are still operating.

When the cluster detects a failed master because the MySQL server port is no longer reachable, the Connectors are signaled and traffic is re-routed to the newly-elected Master node.

√ Read-Scaling

Next is the ability to provide read-scaling and route MySQL queries based on various factors.

During the routing process, Tungsten Connector communicates with the Tungsten Manager to determine which datasources are the most up to date, and their current role so that the packets can be routed properly.

In the default Bridge mode, traffic is routed at the TCP layer, and read-only queries must be directed to a different port (normally 3306 for writes and 3307 for reads).

There are additional modes, Proxy/Direct and Proxy/SmartScale. In both cases, queries are intercepted and inspected by the Connector. The decisions made are tunable based on configuration parameters.

For more detailed information about how the Tungsten Connector works, please read our blog post, “Experience the Power of the Tungsten Connector

For a comparison of Routing methods, please see the documentation page: http://docs.continuent.com/tungsten-clustering-6.0/connector-routing-types.html


Tungsten Connector: Keep-Alives

What are they and why do we use them?

Connections to MySQL servers can automatically time-out according to the wait_timeout variable configured within the MySQL server.

To prevent these connections being automatically closed, the connector can be configured to keep the connection alive by submitting a simple SELECT statement (actually SELECT ‘KEEP_ALIVE’;) periodically to ensure that the MySQL timeout is not reached and the connection closed.

The keep-alive feature was designed with Proxy modes in mind (Proxy/Direct and Proxy/SmartScale). When using either, Proxy mode, every single client connection gets 2 mysql server-side connections: one for reads and one for writes.

If your application is read-intensive, the server-side read-only connection gets updated often and is kept alive by MySQL. Under those conditions, the write connection is NOT being unused, and so there is a risk the MySQL server’s wait_timeout to expire, so the next write on the client side connection would get an error.

In response to the above scenario, the keep-alive feature was implemented.

Keep-alives by default are enabled and set to autodetect, which will compute suitable values based on the MySQL server wait_timeout in order to be totally transparent to the application. This design will produce the exact same behavior as if the application were connected directly to the database server.


Keep-Alives and Bridge Mode

Why They Do Not Work Together

The Connector Keep-alive feature is NOT compatible with Bridge mode.

In Bridge mode, the client session is directly connected to the MySQL server at the TCP level, literally forwarding the client’s packet to the server. This means that closing connections is the responsibility of the MySQL server based on the configured wait_timeout value, not the Connector.


Summary

The Wrap-Up

In this blog post we discussed the basics of the Tungsten Connector, the Keep-alive feature and how to tune the values that control it.

To learn about Continuent solutions in general, check out https://www.continuent.com/solutions


The Library

Please read the docs!

For more information about Tungsten Connector Keep-alives, please visit http://docs.continuent.com/tungsten-clustering-6.0/connector-states-keepalive.html

Tungsten Clustering is the most flexible, performant global database layer available today – use it underlying your SaaS offering as a strong base upon which to grow your worldwide business!

For more information, please visit https://www.continuent.com/solutions

Want to learn more or run a POC? Contact us.

Comments're closed  Comments are closed.