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
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
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
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
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.
wait_timeout). When Vitess discovers that you are changing
a system setting to the global value, Vitess just ignores those
Once a session has been marked as reserved, it remain reserved until the user
Enabling reserved connections #
Use of reserved connections are controlled by the
--enable_system_settings. This flag has traditionally defaulted to false
(or off) in release versions (i.e.
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
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
(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
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.