June 2022 Summaries

In Rails 7, the Marginalia gem has been replaced by a native feature that adds comments to queries, making it easier to track down slow queries and improve performance. The new format is sqlcommenter#, which is machine-readable and more valuable in logging and performance monitoring tools. A gem for Rails 7 enables this feature, allowing developers to add comments to queries using the sqlcommenter# format. This gem also introduces a new method called annotate, which lets developers add specific comments to queries. The combination of Rails + sqlcommenter# + PlanetScale Query Insights can greatly improve query debugging experience and is recommended for Rails applications. PlanetScale's built-in query debugging and analysis tool is compatible with the sqlcommenter# format, allowing developers to quickly find and fix issues in their application.

Vitess 14 is now generally available, offering several major improvements in usability and reliability. The new release includes the general availability of online DDL functionality, a new query planner called Gen4 which replaces the older V3 planner, and a revamped vtctldServer and client for cluster management. Additionally, VTAdmin, the next generation of cluster management API and UIs for Vitess, is included in beta form. The release also addresses reliability concerns with VTOrc, including federation and durability policy configuration refinements. Performance improvements have been observed due to the removal of internal SAVEPOINT query execution, resulting in a performance boost of around 10% compared to the previous version. Users are encouraged to try out Vitess 14, report issues via GitHub, and provide feedback on VTAdmin to ensure its readiness for general availability in Vitess 15.

Vitess is a database proxy that shards databases into multiple MySQL instances, allowing for efficient query execution and aggregation. The system uses a planner to push as much work as possible down to the MySQL layer, but in some cases, it needs to perform operations at the VTGate level, such as joins and aggregations. A recent paper on orthogonal optimization of subqueries and aggregation provided insights into how to improve this process by breaking down aggregations into smaller pieces that can be pushed down under joins. By adapting algorithms from academia, Vitess was able to optimize its query execution plan, allowing for more efficient results and better performance.

The completion of requests in a distributed system is crucial to ensure durability and consistency. A two-phase protocol can be used to meet the requirements, which involves transmitting the request as tentative to all nodes, receiving necessary acknowledgements, and then issuing messages to complete the request. The process has three stages: incomplete, durable, and complete, with the leader being free to transmit the request as complete once it becomes durable. Completion and cancellation are mutually exclusive, and timing the response can be optimized for performance or consistency depending on the system's design. In MySQL, a semi-sync protocol does not support this two-phase method, introducing corner cases that require mitigation. The completion of requests is an essential part of the consensus algorithm, and understanding it is crucial to designing reliable distributed systems.