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 indicates to the connector that queries can go to a slave, although they may still be executed on the master.

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