| Auto Read/Write Splitting | Yes |
| Primary Selection | Automatically, by SQL examination |
| Replica Selection | Automatically, by SQL examination |
| QoS Compatibility | None |
| SmartScale Compatibility | None |
Direct routing is a simplified form of SmartScale that uses a highly-efficient automated read/write splitting system, where of all auto-committed read-only transactions are routed to a pool of read-only Replica datasources. 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 Replica.
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
Where applications are performing writes, followed by immediate reads of this data, for example conferencing and discussion systems, where reading stale data that has recently been written would create significant application failures, the solution should use should use SmartScale.
Read/Write splitting is supported through examination of the submitted SQL statement:
If the statement contains
SELECT and does not contain
FOR UPDATE, the query is
routed to an available Replica.
If the statement starts SHOW
... then it is routed to a Replica.
All other queries are routed to the Primary.
To enable direct routing for a specific user, the
user.map must be modified. Update the file with
the @direct directive on every
host running a connector. The connector will automatically read the
changes after the file is saved.For example:
@direct sales
In this mode, any client application can open a connection to the connector, and queries will automatically be redirected according to the SQL statement content.
Prepared statements must be enclosed within an explicit transaction boundary in order to be correctly routed to a Primary. For example:
BEGIN PREPARE... EXECUTE... COMMIT
Ephemeral objects (i.e. anything that is not replicated), will not be available on the Replica. Session variables are an excellent example of this.
Stored procedures that update data in the database should never be
called using a basic
SELECT statement:
mysql> SELECT update_function('data');
Instead, add the FOR
UPDATE keywords to ensure it is routed to the Primary:
mysql> SELECT update_function('data') FOR UPDATE;