3.4 Connection Sharing and Reuse

MySQL Router enables server connections to be pooled and shared. If a client disconnects, the server connection is moved to the connection pool, where it is available for reuse. If the client connection is idle for more than a specified time, the server connection idles until a new client connection is established. This lowers the number of connections the server has to maintain and frees up resources normally bound to idling connections.

MySQL Router tracks the statements executed by the client and the SQL state of the session to ensure client connections do not lose their session state. If a connection is shared, the reconnected session is in the state the client left it. If that is not possible, the connection is not shared.

Warnings and errors generated by statements are captured and returned when requested by the client. As are session variables.

Note

The default number of I/O threads is the same as the number of CPU threads supported by the host and can be configured with the threads configuration option.

Limitations

• Connection sharing is not supported in PASSTHROUGH mode or if server-ssl-mode=AS_CLIENT and client-ssl-mode=PREFERRED.

• Connection sharing is only supported for classic connections.

• SQL statements that depend on previous session state will not work when connection sharing is active, unless inside a transaction.

• Certain features will leave the connection in a state that blocks it from being shared when idle. Closing or resetting the connection (COM_RESET_CONNECTION) will allow the connection to be reused again.

Unsupported SQL Features

The following statements and functions are not supported when connection sharing is active, except inside a transaction.

• GET DIAGNOSTICS

• LAST_INSERT_ID()

SQL Features which Prevent Sharing

The following SQL features prevent the connection from being pooled until the connection is closed or reset by the client.

• SQL_CALC_FOUND_ROWS,

• GET_LOCK(), service_get_write_locks(). and SQL_CALC_FOUND_ROWS

• User variables

• Temporary tables

• Prepared statements

Note

Transactions and LOCK TABLES also block connection sharing until the transaction is closed, or the lock released.

Configuration

Connection sharing is configured using the following options:

• connection_sharing

• connection_sharing_delay

• max_idle_server_connections

• idle_timeout

The following is an example of configuring connection sharing during bootstrap:

        --conf-set-option=routing:bootstrap_rw.connection_sharing=1 
        --conf-set-option=routing:bootstrap_ro.connection_sharing=1 
        --conf-set-option=connection_pool.max_idle_server_connections=32