Direct Routing

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 "Smartscale Routing"

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.

Enabling Direct Routing

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.

Limitations of Direct Routing

• 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;