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. As are session variables.
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
Connection sharing is not supported in PASSTHROUGH mode or if
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.
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.
also block connection sharing until the transaction is closed,
or the lock released.
Connection sharing is configured using the following options:
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