1.4 Connection Sharing and Reuse

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

Pooled connections are reused if a new client connection is made or an idle connection becomes active again. 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.

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 (see below) 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() and service_get_write_locks()

• 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