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 four 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 also means that we can’t use a shared connection for this user. What this means is that every connection done on this users behalf will need to first have these system variables set, and then keep the connection dedicated. Connection pooling is important for the performance of Vitess, and reserved connections can’t be pooled, 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.

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.

System variableHandled
autocommitVitessAware
client_found_rowsVitessAware
skip_query_plan_cacheVitessAware
tx_read_onlyVitessAware
transaction_read_onlyVitessAware
sql_select_limitVitessAware
transaction_modeVitessAware
workloadVitessAware
charsetVitessAware
namesVitessAware
big_tablesNoOp
bulk_insert_buffer_sizeNoOp
debugNoOp
default_storage_engineNoOp
default_tmp_storage_engineNoOp
innodb_strict_modeNoOp
innodb_support_xaNoOp
innodb_table_locksNoOp
innodb_tmpdirNoOp
join_buffer_sizeNoOp
keep_files_on_createNoOp
lc_messagesNoOp
long_query_timeNoOp
low_priority_updatesNoOp
max_delayed_threadsNoOp
max_insert_delayed_threadsNoOp
multi_range_countNoOp
net_buffer_lengthNoOp
newNoOp
query_cache_typeNoOp
query_cache_wlock_invalidateNoOp
query_prealloc_sizeNoOp
sql_buffer_resultNoOp
transaction_alloc_block_sizeNoOp
wait_timeoutNoOp
audit_log_read_buffer_sizeNotSupported
auto_increment_incrementNotSupported
auto_increment_offsetNotSupported
binlog_direct_non_transactional_updatesNotSupported
binlog_row_imageNotSupported
binlog_rows_query_log_eventsNotSupported
innodb_ft_enable_stopwordNotSupported
innodb_ft_user_stopword_tableNotSupported
max_points_in_geometryNotSupported
max_sp_recursion_depthNotSupported
myisam_repair_threadsNotSupported
myisam_sort_buffer_sizeNotSupported
myisam_stats_methodNotSupported
ndb_allow_copying_alter_tableNotSupported
ndb_autoincrement_prefetch_szNotSupported
ndb_blob_read_batch_bytesNotSupported
ndb_blob_write_batch_bytesNotSupported
ndb_deferred_constraintsNotSupported
ndb_force_sendNotSupported
ndb_fully_replicatedNotSupported
ndb_index_stat_enableNotSupported
ndb_index_stat_optionNotSupported
ndb_join_pushdownNotSupported
ndb_log_binNotSupported
ndb_log_exclusive_readsNotSupported
ndb_row_checksumNotSupported
ndb_use_exact_countNotSupported
ndb_use_transactionsNotSupported
ndbinfo_max_bytesNotSupported
ndbinfo_max_rowsNotSupported
ndbinfo_show_hiddenNotSupported
ndbinfo_table_prefixNotSupported
old_alter_tableNotSupported
preload_buffer_sizeNotSupported
rbr_exec_modeNotSupported
sql_log_offNotSupported
thread_pool_high_priority_connectionNotSupported
thread_pool_prio_kickup_timerNotSupported
transaction_write_set_extractionNotSupported
default_week_formatReservedConn
end_markers_in_jsonReservedConn
eq_range_index_dive_limitReservedConn
explicit_defaults_for_timestampReservedConn
foreign_key_checksReservedConn
group_concat_max_lenReservedConn
max_heap_table_sizeReservedConn
max_seeks_for_keyReservedConn
max_tmp_tablesReservedConn
min_examined_row_limitReservedConn
old_passwordsReservedConn
optimizer_prune_levelReservedConn
optimizer_search_depthReservedConn
optimizer_switchReservedConn
optimizer_traceReservedConn
optimizer_trace_featuresReservedConn
optimizer_trace_limitReservedConn
optimizer_trace_max_mem_sizeReservedConn
transaction_isolationReservedConn
tx_isolationReservedConn
optimizer_trace_offsetReservedConn
parser_max_mem_sizeReservedConn
profilingReservedConn
profiling_history_sizeReservedConn
query_alloc_block_sizeReservedConn
range_alloc_block_sizeReservedConn
range_optimizer_max_mem_sizeReservedConn
read_buffer_sizeReservedConn
read_rnd_buffer_sizeReservedConn
show_create_table_verbosityReservedConn
show_old_temporalsReservedConn
sort_buffer_sizeReservedConn
sql_big_selectsReservedConn
sql_modeReservedConn
sql_notesReservedConn
sql_quote_show_createReservedConn
sql_safe_updatesReservedConn
sql_warningsReservedConn
tmp_table_sizeReservedConn
transaction_prealloc_sizeReservedConn
unique_checksReservedConn
updatable_views_with_limitReservedConn
binlog_formatCheckAndIgnore
block_encryption_modeCheckAndIgnore
character_set_clientCheckAndIgnore
character_set_connectionCheckAndIgnore
character_set_databaseCheckAndIgnore
character_set_filesystemCheckAndIgnore
character_set_resultsCheckAndIgnore
character_set_serverCheckAndIgnore
collation_connectionCheckAndIgnore
collation_databaseCheckAndIgnore
collation_serverCheckAndIgnore
completion_typeCheckAndIgnore
div_precision_incrementCheckAndIgnore
innodb_lock_wait_timeoutCheckAndIgnore
interactive_timeoutCheckAndIgnore
lc_time_namesCheckAndIgnore
lock_wait_timeoutCheckAndIgnore
max_allowed_packetCheckAndIgnore
max_error_countCheckAndIgnore
max_execution_timeCheckAndIgnore
max_join_sizeCheckAndIgnore
max_length_for_sort_dataCheckAndIgnore
max_sort_lengthCheckAndIgnore
max_user_connectionsCheckAndIgnore
net_read_timeoutCheckAndIgnore
net_retry_countCheckAndIgnore
net_write_timeoutCheckAndIgnore
session_track_gtidsCheckAndIgnore
session_track_schema", boolean:CheckAndIgnore
session_track_state_change", boolean:CheckAndIgnore
session_track_system_variablesCheckAndIgnore
session_track_transaction_infoCheckAndIgnore
sql_auto_is_null", boolean:CheckAndIgnore
time_zoneCheckAndIgnore
version_tokens_sessionCheckAndIgnore

Related Vitess Documentation