Reserved Connections

Feature Description

Vitess uses connection pooling to minimize memory usage and otherwise optimize the performance of the underlying MySQL servers. Even with tens of thousands of client database connections. This means that different users connecting to a vtgate can effectively share a database session to MySQL. To make this process as transparent as possible to users, Vitess removes all query constructs that would normally need to change the state in the MySQL connection. For example, when a user sets or evaluates a user defined variable, the vtgate will rewrite the query so that it does not actually do anything with user variables. Instead, it keeps the state in the Vitess layer.

In other cases, this approach is not enough, and Vitess can use reserved connections, which are controlled via the --enable_system_settings vtgate flag. Enabling reserved connections means a dedicated connection is maintained for the vtgate session from the relevant vttablet to the MySQL server. Reserved connections are used when changing system variables, using temporary tables, or when using MySQL locking functions to acquire advisory locks. In general, it is better to use reserved connections sparingly, because they reduce the effectiveness of the vttablet connection pooling. This may also reduce, or even eliminate, the advantages of using connection pooling between vttablet and MySQL. As such, take note of the SET statements that your application’s MySQL connector and/or ORM sends to MySQL/vtgate. Or if those settings will result in reserved connections being employed for some/all of the application’s sessions.

Settings pool and reserved connections

We will see how reserved connections get triggered for different use cases in subsequent sections of this document. What we want to highlight at the beginning is that there is a known issue when a reserved connection is used as it cannot be reused by vttablet. More details about it are given below.

To solve this problem, the connection pool implementation used by vttablet has been enhanced to keep the connections with settings in the pool and not to pin the connection to the client session. With this enhancement, we reduce the likelihood of MySQL running out of connections due to reserved connections, because the scenarios where we still need reserved connections are sharply reduced.

This is enabled by default from v17 onwards. It can be disabled by setting the flag --queryserver-enable-settings-pool on vttablet. This change takes effect only for the cases when system variable changes need a reserved connection. There are still cases like temporary tables and advisory locks where reserved connections will continue to be used.

System variables and reserved connections

If a user changes a system variable and reserved connections are enabled, the user connection will be marked as needing reserved connections. For all subsequent calls to Vitess, connection pooling is turned off for a particular session. This only applies to certain system settings. For more details see here. Any queries to a tablet from this session will create a reserved connection on that tablet. This means a connection is reserved only for that session.

Connection pooling is an important part of what makes Vitess performant, so using constructs that turn it off should only be done in rare circumstances.

If you are using an application or library that issues these kind of SET statements, the best way to avoid reserved connections is to make sure the global MySQL settings match the ones the application is trying to set (e.g. sql_mode, or wait_timeout). When Vitess discovers that you are changing a system setting to the global value, Vitess just ignores those SETs.

Once a session has been marked as reserved, it remain reserved until the user disconnects from vtgate.

Enabling reserved connections

Use of reserved connections are controlled by the vtgate flag --enable_system_settings. This flag has traditionally defaulted to false (or off) in release versions (i.e. x.0 and x.0.y) of Vitess, and to true (or on) in development versions.

From Vitess 12.0 onwards, it defaults to true in all release and development versions. You can read more here. Thus you should specify this flag explicitly, so you are sure whether it is enabled or not, independent of which Vitess release/build/version you are running.

If you have reserved connections disabled, you will get the “old” Vitess behavior: where most setting most system settings (e.g. sql_mode) are just silently ignored by Vitess. In situations where you know your backend MySQL defaults are acceptable, this may be the correct tradeoff to ensure the best possible performance of the vttablet <-> MySQL connection pools. As noted above, this comes down to a trade-off between compatibility and performance/scalability. You should also review this section when deciding on whether or not to enable reserved connections.

Avoiding the use of reserved connections

In MySQL80 a new query hint (SET_VAR) allows setting the session value of certain system variables during the execution of a statement. More information about this MySQL feature on the MySQL documentation. Vitess leverages this query hint to reduce the number of reserved connections. When setting a system variable, instead of creating a reserved connection, the variable and its new value will be sent to MySQL using the SET_VAR query hint. This applies only if the system variable is supported by the SET_VAR hint (list of supported variables here).

For example, executing: set @@sql_mode = 'NO_ZERO_DATE' will not create a reserved connection for future queries. If we execute a select statement like: select foo from bar, VTGate will rewrite the query as select /*+ SET_VAR(sql_mode = 'NO_ZERO_DATE') foo from bar */.

This feature can be disabled using the VTGate flag --enable_set_var (by default set to true).

Temporary tables and reserved connections

Temporary tables exist only in the context of a particular MySQL connection. If using a temporary table, Vitess will mark the session as needing a reserved connection. It will continue to use the reserved connection until the user disconnects. Note that removing the temp table is not enough to reset this. More info can be found here.

GET_LOCK() and reserved connections

The MySQL locking functions allow users to work with user level locks. Since the locks are tied to the connection, and the lock must be released in the same connection as it was acquired, use of these functions will force a connection to become a reserved connection. This connection is also kept alive so it does not time out due to inactivity. More information can be found here.

Shutting down reserved connections

Whenever a connection gets transformed into a reserved connection, a fresh connection is created in the connection pool to replace it. Once the vtgate session that initiated the reserved connections disconnects, all reserved connections created for this session between the vttablets and MySQL are terminated. You may want to configure your application or application connector to disconnect idle sessions that are likely to use reserved connections promptly. In order to release resources that cannot otherwise be reused.

Number of vttablet <-> MySQL connections

As a result of how reserved connections work, it is possible for there to be significantly more vttablet <-> MySQL connections than the limit you set by sizing the vttablet connection pools. This is because the connection pools are still being maintained. Which results in a set maximum number of connections, plus the number of reserved connections. This is at least partially based on the number of connected vtgate clients that are using reserved connections. As such, it may be challenging to size your MySQL max_connections configuration setting appropriately in order to deal with the potentially (much) larger number of connections.

We recommend you review the value of this setting carefully, and keep this in mind when you decide whether to enable or disable reserved connections.