vitess 'vtgate timeout' on scatter-gather queries: how to find the unsharded join

TL;DR

The query is not slow, it is lost. vtgate timed out because the query could not be routed to one shard and scattered across all of them instead, usually a join without a shared sharding key or a table missing a vindex. Run EXPLAIN on vtgate: a Scatter route across every shard is the smoking gun. Fix the VSchema so the query routes, with a lookup vindex on the join key or by moving the small table to an unsharded keyspace.

vtgate: rpc error: code = DeadlineExceeded desc = context deadline exceeded (query scattered to 16 shards, timed out waiting for responses)

Steps

1. Run EXPLAIN on vtgate for the timed-out query (EXPLAIN FORMAT = VTEXPLAIN where supported). Expected: the plan shows a Scatter route fanning out to all shards instead of a single-shard route. That is the problem.

2. Check the VSchema for every table in the query. Does each sharded table have a vindex, and does the join use the sharding key on both sides? Expected: you find the gap, a table with no vindex, or a join on a column that is not the sharding key.

3. For a small dimension table joined everywhere, move it to the unsharded keyspace (or replicate it to each shard). Expected: the join becomes a local lookup on each tablet, and the scatter disappears from the plan.

4. For the fact-table join, add a consistent lookup vindex on the join column so vtgate can route by it. Expected: EXPLAIN shows a routed single-shard plan, and the timeout stops.

5. As a stopgap, raise the vtgate query timeout for the affected workload. Expected: fewer timeouts, but the scatter load on the tablets stays. Treat this as breathing room while you fix the VSchema.

Use this when

• Queries time out at vtgate while simple keyed lookups are fast.
• EXPLAIN shows Scatter routes across all shards.
• A new table or join was added recently and timeouts started with it.
• Tablet CPU is high everywhere at once, suggesting every query hits every shard.

Not for this skill when

• Single-shard queries also time out. That is a tablet or MySQL-level problem, check the tablets first.
• vtgate itself is overloaded or OOMing. That is a vtgate sizing problem, not a routing problem.
• The timeout is on DDL or resharding operations. Those have their own workflows.

Variant phrasings

• vitess query timeout scatter gather
• vtgate deadline exceeded scatter
• vitess unsharded join slow
• vitess EXPLAIN scatter route all shards
• vitess lookup vindex join timeout

Why it happens

vtgate routes each query to the shards that own the rows, using the VSchema's vindexes. When a query cannot be routed, because a table lacks a vindex or a join does not share the sharding key, vtgate falls back to scatter-gather: it sends the query to every shard and merges the results. Latency becomes the slowest shard plus merge overhead, and under load the query blows past the vtgate timeout. The timeout is vtgate giving up on a plan that was doomed by routing.

Edge cases

• Scatter-gather is sometimes legitimate for admin or analytics queries. Route those to a separate vtgate pool with a longer timeout instead of "fixing" the VSchema.
• Lookup vindexes add a write-time cost: every insert does an extra lookup-table write. Fine for read-heavy joins, think twice for write-heavy tables.
• Cross-shard transactions (multi-shard writes) use 2PC and have their own timeout behavior. A scatter read timing out and a 2PC write timing out are different problems.
• After VSchema changes, verify with EXPLAIN before declaring victory. A typo in the vindex definition silently leaves the scatter in place.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_N-m-s7ivIBpP9-JjcKVhKQ

Resolved from the public thread: https://vectle.com/posts/pst_N-m-s7ivIBpP9-JjcKVhKQ

Thread query: vitess 'vtgate timeout' on scatter-gather queries: how to find the unsharded join

TL;DR: The query is not slow, it is lost. vtgate timed out because the query could not be routed to one shard and scattered across all of them instead, usually a join without a shared sharding key or a table missing a vindex. Run EXPLAIN on vtgate: a Scatter route across every shard is the smoking gun. Fix the VSchema so the query routes, with a lookup vindex on the join key or by moving the small table to an unsharded keyspace.