TechPrufer
Get started
PlanetScale
PlanetScale logoPlanetScale · Feature trail

Vitess SQL Evaluation Engine

8 posts · Oct 2022 – Mar 2025

What this trail covers

Vitess's SQL evaluation engine has evolved from an AST-based interpreter to a high-performance bytecode virtual machine. This transition, driven by the need for greater efficiency and maintainability in handling complex SQL expressions across sharded environments, involved static typing of SQL expressions at compile time and the implementation of a specialized VM. The new VM offers significant performance gains while remaining easier to maintain than the previous Go interpreter. This post details a specific optimization within the query planner to push aggregation down to MySQL, reducing data fetched by VTGate and preventing OOM errors. The solution involves delaying the 'ordering under aggregation' rewriter until the 'split aggregation' phase, allowing aggregation to be pushed down under the join.

All PlanetScale trails
  1. Faster interpreters in Go: Catching up with C++This post details the transition of Vitess's SQL evaluation engine from an AST-based interpreter to a bytecode virtual machine. It explains the rationale behind this shift, focusing on performance improvements and maintainability. Key technical contributions include the implementation of static typing for SQL expressions derived from semantic analysis, enabling pre-specialized bytecode, and the design of an efficient Go-based VM loop. The post also highlights the discovery of upstream MySQL bugs through comprehensive testing.
  2. Optimizing aggregation in the Vitess query plannerThis post details a specific optimization within the Vitess query planner to address an issue where aggregation was not being pushed down to MySQL, leading to excessive data fetching by VTGate and potential OOM errors. The optimization involves strategically delaying the 'ordering under aggregation' rewriter until the 'split aggregation' phase. This allows the aggregation to be pushed down under the join, with only the SUM of SUMs being handled at the VTGate level. The post illustrates the initial plan, the problematic intermediate plan, and the final optimized plan, highlighting the importance of tree rewriting phases in query planning.
  3. Announcing Vitess 19Vitess 19 introduces performance improvements such as a new connection pool for Tablets, faster hashing in sharded Vitess clusters, and faster comparisons in cross-shard aggregations. Query compatibility is enhanced with support for multi-table delete operations and non-recursive CTEs. Online DDL now includes backoff for cut-over attempts and forced cut-over. Support for MySQL 5.7 is being dropped, and tablet throttlers now communicate exclusively via gRPC. The release also deprecates several VTTablet flags and changes the EXPLAIN statement format. ExecuteFetchAsDBA now rejects multi-statement SQL.
  4. Announcing Vitess 17Vitess v17 introduces a new virtual machine-based SQL evaluation engine (not enabled by default) that offers over 2x performance improvement compared to the AST-based engine. It also enhances MySQL compatibility with support for `Prepare`, `Execute`, `Deallocate` statements and numerous functions. Replication enhancements include support for `noblob` binlog_row_image and MySQL 8.0 transaction compression, which can significantly reduce binary log size and I/O. Usability improvements include transaction throttling in autocommit mode and faster dead primary recoveries in VTOrc.
  5. Optimizing query planning in Vitess: a step-by-step approachThis post details a new query planning model in Vitess where each optimization step results in a runnable plan. This contrasts with the previous top-down approach and offers benefits such as simpler understanding, ease of testing, and the ability to use arbitrary expressions in ordering, grouping, and aggregations. The post illustrates these benefits with an example query and its step-by-step optimization process under the new model.
  7. Announcing Vitess 16Vitess 16 introduces a bytecode virtual machine for its SQL evaluation engine, improving performance and maintainability. It adds experimental support for Views across shards, makes VTOrc mandatory, and introduces native incremental backups with point-in-time recovery. VDiff v2 is now GA, and a new VEXPLAIN command is available for query planning insights.
  8. What is a query planner?This post provides a detailed explanation of the query planning process in database systems, drawing parallels to compiler design. It breaks down the process into lexing/parsing, semantic analysis, optimization, and code generation. For optimization, it specifically discusses predicate rewriting and join order selection, using examples from Vitess. It also highlights Vitess's strategy of pushing down work to MySQL and performing aggregations in stages. The post emphasizes the critical role of a good query planner in database performance.
  9. Announcing Vitess 15Vitess 15 is released, featuring the General Availability of VTOrc for automated monitoring and recovery, and VTAdmin for multi-cluster management. It also includes progress on VEP-4 for code reorganization and flag standardization, and VDiff v2 which is feature complete for resumable data migration checks. Performance enhancements include improved query plans for subqueries and derived tables, and updates to the benchmarking infrastructure.