Visual Aggregation Modeler System and Method for Performance Analysis and Optimization of Databases

摘要

An aggregation lattice provides an effective visualization tool to integrate complex and multifaceted feedback required for aggregation modeling. The aggregation lattice provides a framework for reasoning about aggregates, qualifying workloads that benefit from them, and picking most effective levels from a total performance perspective. A Visual Aggregate Modeler (VAM) tool puts the designer in control of choices made by using visualization to communicate aggregate query and maintenance costs along with other relevant factors assisting the designer in arriving at optimal aggregation materialization decisions. The VAM tool includes a SQL query parser and algorithms to determine the “free aggregation distance” that is the portion of aggregation cost queries incur because they do not have a close enough aggregate materialized, making informed performance enhancement cost/benefit decisions possible with existing workloads. A “materialization desirability” scale (“allow”-“discourage”-“avoid”) is implemented to systematically prioritize recastable aggregates and to limit them to specific dimension levels.