Canary Statistics

The canary statistics feature improves by allowing CockroachDB to try newly collected on a small number of queries before deploying them for all queries. By testing the efficiency of made with new statistics on a fraction of queries, canary statistics blocks bad plans based on inaccurate statistics from impacting the full workload.

The canary window

When CockroachDB generates new statistics on a table, they begin in canary status. The sql_stats_canary_window sets a canary window defining how long the statistics remain in canary status before being promoted to stable status. However, if new statistics are generated, the previous statistics are automatically promoted to stable status even if their canary window has not yet elapsed. If sql_stats_canary_window is set to 0, the canary statistics feature is disabled.

Example usage:

CREATE TABLE t (x int) WITH (sql_stats_canary_window = '1h');

If you create statistics manually with , they are also subject to the canary window. However, you can force manually created statistics to take effect immediately using with the subcommand.

Example:

ANALYZE t;
-- reset the canary window to 0, causing queries to use the new stats immediately
ALTER TABLE t RESET (sql_stats_canary_window);
-- wait 1 hour for the skipped canary widow to elapse, then set the window again
ALTER TABLE t SET (sql_stats_canary_window = '1h');

Queries with canary statistics

When the makes a plan for a query, it must determine whether to use canary statistics or stable statistics in its calculations. This determination is based on a probability configured in the . Queries do not mix stastistics types, so if a query is selected to use canary statistics, it uses them for all tables. Setting sql.stats.canary_fraction to 0 disables canary statistics across all queries.

Example usage:

SET CLUSTER SETTING sql.stats.canary_fraction = 0.2;

You can override the configured probability of using canary statistics for a particular session using the canary_stats_mode . Set canary_stats_mode to force_canary to use only canary statistics for the duration of the session, or set it to force_stable to use only stable statistics. This variable is useful for debugging or troubleshooting specific queries.

Example usage:

SET canary_stats_mode = 'force_canary';

Analytical tools for canary statistics

Analyzing canary statistics allows you to visualize how new statistics affect query performance and catch problematic statistics before they impact your full workload. The contains metrics about canary statistics on the . The Canary vs Stable Statement Times chart shows planning and execution latency for each statistics set, and the Canary vs Stable Plan Distribution chart shows which plans the chose using each statistics set. These charts only appear when canary statistics are enabled.

When using the canary statistics feature, the and statements show the table’s current canary window and whether a query is using canary statistics or stable statistics. These fields only appear when canary statistics are enabled.

See also