6.3.8. SQL Routing

Auto Read/Write Splitting No
Master Selection Manually, by SQL comments
Slave Selection Manually, by SQL comments
QoS Compatibility Supported
SmartScale Compatibility Yes
Direct Compatibility Yes

With SQL-based routing, the redirection of queries and operations through the Connector is controlled by hints on the QoS provided in the comments of individual statements. Note that this is explicit routing using SQL comments, not the automated read/write splitting supported by Direct or SmartScale routing.

Unless otherwise specified, statements will go to the current master to be executed. To specify that a statement can be executed on the slave, place a comment before the statement:

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

This style of comment indicates to the connector that the specific query that follows should go to a slave. If unavailable, the query may still be executed on the master.

-- TUNGSTEN USE qos=RO_RELAXED

This style of comment indicates to the connector that all queries that follow should go to a slave. If unavailable, any query may still be executed on the master.

Warning

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. The application is fully responsible for where the traffic is routed to. if care is not taken, the application could send writes to a slave this way which is unacceptable from a clustering perspective. All writes must go to the master or they will be lost to a non-authoritative node, and may corrupt the data badly.

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

-- TUNGSTEN USE qos=RW_STRICT

6.3.8.1. Enabling SQL Routing

To enable SQL routing, use the following operations with tpm:

shell> tpm configure alpha
    --property=selective.rwsplitting=true

6.3.8.2. Limitations of SQL Routing

  • Read/write splitting must be handled entirely by the client application using the comments to specify which statements are slave safe. Unless applications explicit make the decision to write and read to the hosts using the comment system, operations may go to the wrong hosts.

  • Prepared statements must be executed against the master.

  • When testing the operation of the read/write splitting through the mysql client, ensure that command-line client is called using the -c option to ensure that comments are preserved:

    shell> mysql -c -h host -u tungsten -ppassword -P9999 test