Query Rewriting

Vitess works hard to create an illusion of the user having a single connection to a single database. In reality, a single query might interact with multiple databases and may use multiple connections to the same database. Here we’ll go over what Vitess does and how it might impact you.

Query splitting

A complicated query with a cross shard join might need to first fetch information from a tablet keeping vindex lookup tables. Then use this information to query two different shards for more data and subsequently join the incoming results into a single result that the user receives. The queries that MySQL gets are often just pieces of the original query, and the final result will get assembled at the vtgate level.

Connection Pooling

When a tablet talks with a MySQL to execute a query on behalf of a user, it does not use a dedicated connection per user, and instead will share the underlying connection between users. This means that it’s not safe to store any state in the session as you can’t be sure it will continue executing queries on the same connection, and you can’t be sure if this connection will be used by other users later on.

User-Defined Variables

User defined variables are kept in the session state when working with MySQL. You can assign values to them using SET:

SET @my_user_variable = 'foobar'

And later there can be queries using for example SELECT:

> SELECT @my_user_variable;
+-------------------+
| @my_user_variable |
+-------------------+
| foobar            |
+-------------------+

If you execute these queries against a VTGate, the first SET query is not sent to MySQL. Instead, it is evaluated in the VTGate, and VTGate will keep this state for you. The second query is also not sent down. Trivial queries such as this one are actually fully executed on VTGate.

If we try a more complicated query that requires data from MySQL, VTGate will rewrite the query before sending it down. If we were to write something like:

WHERE col = @my_user_variable

What MySQL will see is:

WHERE col = 'foobar'

This way, no session state is needed to evaluate the query in MySQL.

Server System Variables

A user might also want to change one of the many different system variables that MySQL exposes. Vitess handles system variables in one of five different ways:

• No op. For some settings, Vitess will just silently ignore the setting. This is for system variables that don’t make much sense in a sharded setting, and don’t change the behaviour of MySQL in an interesting way.
• Check and fail if not already set. These are settings that should not change, but Vitess will allow SET statements that try to set the variable to whatever it already is.
• Not supported. For these settings, attempting to change them will always result in an error.
• Vitess aware. These are settings that change Vitess’ behaviour, and are not sent down to MySQL
• Reserved connection. For some settings, it makes sense to allow them to be set, but it makes using a shared connection for this user much harder. By default, Vitess will first apply these system variables that are set, and then keep the connection dedicated for this user. Connection pooling is important for the performance of Vitess, so this should not be the normal way to run applications on Vitess. Just make sure that the global variable is set to the same value the application will set it to, and Vitess can use connection pooling. Vitess now has support for connection-pooling even for these settings that originally required reserved connections. You can read more about it here.

In addition to this, Vitess makes sure that @@version includes both the emulated MySQL version and the Vitess version, such as: 5.7.9-vitess-14.0.0. This value can be changed by using the vtgate flag --mysql_server_version.

Special functions

There are a few special functions that Vitess handles without delegating to MySQL.

• DATABASE() - The keyspace name and the underlying database names do not have to be equal. Vitess will rewrite these calls to use the literal string for the keyspace name. (This also applies to the synonym SCHEMA())
• ROW_COUNT() and FOUND_ROWS() - These functions returns how many rows the last query affected/returned. Since this might have been executed on a different connection, these get rewritten to use the literal value of the number of returned rows.
• LAST_INSERT_ID() - Much like FOUND_ROWS(), we can’t trust a pooled connection for these function calls, so they get rewritten before hitting MySQL.

Reference

Here is a list of all the system variables that are handled by Vitess and how they are handled.

Related Vitess Documentation

• VTGate