Experience the Power of the Tungsten Connector, an Intelligent MySQL Proxy

In this blog post, we talk about the basic function and features of the Tungsten Connector. The Tungsten Connector is an intelligent MySQL proxy that provides key high-availability and read-scaling features. This includes the ability to route MySQL queries by inspecting them in-flight.

Connector Basics

Understanding the Two Key Features

The most important function of the Connector is failover handling. 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. Next is the ability to route MySQL read-only queries to a slave (or the master, if no slave is available), increasing performance by spreading the load across multiple database hosts. This is also known as "read/write splitting". By default, ALL queries both reads and writes are sent (routed) to the current master node, and the read/write splitting features are disabled.

Connector Query Routing

Traffic a la Mode

The Mode defines the layer of operation and determines the Connector's session intercept vs. non-intercept behavior. The Connector has two modes for how to handle traffic from the client, Bridge mode and Proxy mode. In the default Bridge mode, traffic is routed at the TCP layer, and the session is established directly between the client and the MySQL server. In Bridge mode, there is no query inspection, so only manual read/write splitting (routing) can occur. In Proxy mode, the Connector functions as an application-layer proxy. In this mode, client sessions connect to the Connector which in turn creates a separate connection to the MySQL server on the client's behalf. This application-layer session intercept allows the Connector to inspect the queries and performs intelligent routing based on a number of configurable parameters.

Query Routing Methods

Control the Flow

Connector modes each have associated routing methods, which define the various ways of signaling the connector how to decide where to send queries. Below we cover the five routing methods, three manual and two automatic, that are available in the Tungsten Connector.

Bridge Mode Routing Methods (TCP-layer proxy)

To enable the optional read/write splitting features, choose from the available methods below:

• Port Based - manual routing of reads vs writes based on port number the client connects TO - all queries to port are sent to a slave. For example, to create a read-only port:

  shell$ tpm configure alpha --connector-readonly-listen-port=3307
  shell$ tpm update
  shell$ mysql -P3307

  https://docs.continuent.com/tungsten-clustering-6.0/connector-routing-port.html

• Host Based - manual routing of reads vs writes based on IP address/hostname the client connects TO - i.e. all queries to specific IP are sent to a slave. For example, to create a read-only IP address:

  shell$ sudo ifconfig eth0:1 127.0.0.2
  shell$ vi user.map
  ...
  @hostoption 127.0.0.2 qos=RO_RELAXED
  
  shell$ mysql -h127.0.0.2

  https://docs.continuent.com/tungsten-clustering-6.0/connector-routing-host.html

Proxy Mode Routing Methods (Application-layer proxy)

Proxy mode requires a text configuration file called user.map which defines the username/password credentials that allow the creation of client sessions to the Connector itself, as well as the session between the Connector and the database nodes. Each listed user and password must be identical to those defined in the MySQL server, and each entry has three required fields and one optional field (affinity): {user} {password} {service} [affinity] For example: app_user secret global west In Proxy mode, communications via the Connector will fail without at least one user entry. There are two automatic Proxy mode routing methods (Direct and SmartScale), where the Connector intelligently inspects each query to determine if it is a read-only event. In both cases, queries are intercepted and inspected by the Connector. The decisions made are tunable based on configuration parameters. For more fine-grained control, Proxy mode provides three manual ways to control the flow of requests, Port, Host and SQL. To enable the optional read/write splitting features, choose from the available methods below:

• Port Based - manual routing of reads vs writes based on port number the client connects TO - i.e. all queries to port are sent to a slave (configured the same way as Bridge mode above)

• Host Based - manual routing of reads vs writes based on IP address/hostname the client connects TO - i.e. all queries to specific IP are sent to a slave (configured the same way as Bridge mode above)

• Direct - automatic routing (R/W splitting) of reads vs writes based on connecting user name. Direct routing is therefore ideal in applications where:

  • Applications perform few writes, but a high number of reads.
  • High proportion of reads on 'old' data. For example, blogs, stores, or machine logging information

  For example, to enable Direct automatic read/write splitting for the sales user:

  shell$ vi user.map
  ...
  @direct sales

  Direct Read/Write splitting is supported through examination of the submitted SQL statement:

  • If the statement starts with SELECT and does not contain FOR UPDATE, the query is routed to an available slave, falling back to the master for reads if a slave is not available.
  • If the statement starts with SHOW then it is routed to an available slave, falling back to the master for reads if a slave is not available.
  • Any query wrapped with BEGIN and COMMIT (i.e. a transaction) will be sent to the master.
  • All other queries are routed to the master.

  For Direct Routing to work properly, MySQL must be configured for autocommit=1 (MySQL Docs). https://docs.continuent.com/tungsten-clustering-6.0/connector-routing-direct.html

• SmartScale - automatic routing (R/W splitting) with intelligent session awareness of stale slave data. SmartScale routing is therefore ideal in applications where:

  • There are relatively few writes and many reads. The writes that are performed can be considered to be in a 'silo' of their own, that is, a given application 'session' only writes and reads its own data and is not concerned with the data read/written by other application 'sessions'.
  • PHP applications are good candidates for SmartScale since PHP has embedded session IDs that can be passed at connection time.
  • Web based applications with user profiles match the scenario where users will update their own profile and want to see their modifications right away, but can accept latency on other users profiles.

  SmartScale - Key Points of Interest

  • In Proxy/SmartScale mode, an additional check is made on the read slave to determine data "staleness". In this read-write splitting mode, the Connector intelligently determines if slaves are up-to-date with respect to the master, and selects them in such a way that reads are always strictly consistent with the last write of their current session. This is extremely useful when doing read-behind-write operations.
  • MySQL must be configured for autocommit=1 (MySQL Docs) for this to work.
  • Additionally, any query transaction wrapped with BEGIN and COMMIT will be sent to the Master directly.

  SmartScale - Comparison to Direct Reads

  In Proxy/Direct mode, a SELECT-only statement that does no writes will be sent to a read slave automatically. Unlike SmartScale, Direct routing pays no attention to the session state, or replicated data consistency. This means that performing a write and immediately trying to read the information through a Direct routing connection may fail, because the Connector does not ensure that the written transaction exists on the selected slave. For example, to enable SmartScale automatic read/write splitting:

  shell$ tpm configure alpha --connector-smartscale=true \
  --connector-smartscale-sessionid={DATABASE|USER|CONNECTION|PROVIDED_IN_DBNAME}
  shell$ tpm update

  https://docs.continuent.com/tungsten-clustering-6.0/connector-routing-smartscale.html

• SQL Based - manual routing of reads vs writes based on inline SQL comments - routing to master or slave is controlled by the comment contents.

  To specify that a statement can be executed on the slave, place a comment before the SQL statement.

  There are two types of comments available, and they behave differently:

  • The below style of comment indicates to the Connector that only the specific query that follows should go to a slave. If a slave is unavailable, the query would be executed on the master.

    /* TUNGSTEN USE qos=RO_RELAXED */ SELECT * FROM TABLENAME

  • The below style of comment indicates to the Connector that all queries that follow should go to a slave. If a slave is unavailable, the queries would be executed on the master.

    -- TUNGSTEN USE qos=RO_RELAXED

  • The below forces all following queries to go to the master directly, effectively "turning off" reads from the slave.

    -- TUNGSTEN USE qos=RW_STRICT

    Warnings

    If you force the Connector to send traffic to a slave using qos=RO_RELAXED, then any write operations that follow will also go to the slave until you tell the Connector to go back to the master by indicating qos=RW_STRICT.

    Please note that employing the -- style will override any /* */ comments.

    https://docs.continuent.com/tungsten-clustering-6.0/connector-routing-sql.html

Connector Deployment

Oh So Flexible!

Best of all, each Connector node may be configured differently, so that it is possible to have all of the following available at once using four separate node instances:

• Bridge mode read/write (very fast)
• Bridge mode read-only via manual routing(very fast)
• Proxy mode automatic read/write splitting via Direct Reads (slower due to inspection)
• Proxy mode automatic read/write splitting via SmartScale, with session and latency-sensitive routing (slower still due to slave status query)

In summary, the Connector allows for both proper failover handing as well as a variety of ways to route MySQL queries to read slaves. In future articles, we will cover more advanced subjects like failover behavior tuning. Questions? Contact Continuent 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