Vitess query consolidation is a feature that saves you a surprising amount of CPU by identifying and executing identical queries only once, even if they come from different application threads or clients.
Imagine you have an application that, on startup, queries the current time from the database. If you have 100 instances of this application starting simultaneously, they might all issue SELECT NOW(). Without consolidation, the database would process each of those 100 identical queries independently. With consolidation, Vitess sees that SELECT NOW() has already been executed recently and is still valid, so it returns the cached result for the subsequent 99 requests, drastically reducing database load.
Let’s see this in action. Consider a simple application that frequently queries a user’s name by their ID.
func getUserName(ctx context.Context, db *sql.DB, userID int) (string, error) {
var name string
err := db.QueryRowContext(ctx, "SELECT name FROM users WHERE id = ?", userID).Scan(&name)
if err != nil {
return "", err
}
return name, nil
}
If multiple goroutines in your application call getUserName with the same userID, Vitess’s query consolidation can help. When the first SELECT name FROM users WHERE id = 123 query arrives at the VTGate, Vitess checks its cache. If it’s not there, or has expired, Vitess forwards it to the VTTablet. The VTTablet executes the query against the MySQL server, and the result is sent back to VTGate. VTGate then caches this result. When the second SELECT name FROM users WHERE id = 123 query arrives at VTGate, it finds the result in its cache and returns it immediately without bothering the VTTablet or MySQL.
The core mechanism for query consolidation in Vitess relies on a combination of query string hashing and a time-to-live (TTL) cache. When a query is received by VTGate, it’s first normalized (e.g., whitespace trimmed, keywords standardized) and then a hash is generated. This hash, along with the bound values, uniquely identifies the query. VTGate maintains an in-memory cache (often using a library like groupcache or a similar LRU cache implementation) that maps these query identifiers to their results. Each cached entry has a TTL, so the results are eventually re-validated by hitting the actual database. This prevents stale data from being served indefinitely.
The primary levers you control for query consolidation are within the VTGate configuration. The most critical parameters are:
query_cache_size: This defines the maximum number of distinct query results that VTGate will cache. A larger size can improve hit rates but consumes more memory. For example, settingquery_cache_size: 100000allows VTGate to store up to 100,000 unique query results.query_cache_ttl: This specifies how long a query result remains valid in the cache before it’s considered stale and needs to be re-executed against the database. A typical value might bequery_cache_ttl: "5m"(5 minutes). Shorter TTLs lead to fresher data but potentially more database hits. Longer TTLs reduce database load but might serve slightly stale data.query_cache_pool_size: This controls the number of concurrent outstanding queries that VTGate will track for consolidation. It’s not about the number of cached results, but the number of active queries being processed. For example,query_cache_pool_size: 10000means VTGate can track up to 10,000 simultaneously executing queries for potential deduplication.
The actual mechanics of how Vitess handles bound parameters is crucial. It doesn’t just hash the SQL string. It computes a hash that incorporates both the normalized query string and the actual values of the bound parameters. This means SELECT * FROM users WHERE id = 123 and SELECT * FROM users WHERE id = 456 are treated as distinct queries, which is exactly what you want. The cache key is effectively a tuple of (normalized_query_string, bound_parameter_values_hash).
What many people miss is that query consolidation also applies to errors. If an identical query fails with the same error, Vitess will cache that error as well. This means if 50 application instances simultaneously try to insert a row with a unique key violation, only the first one will actually hit the database and get the error. The subsequent 49 will receive the cached error from VTGate, saving significant database contention and CPU cycles.
The next major optimization you’ll encounter after mastering query consolidation is understanding how Vitess handles transactions and their impact on caching.