Query Replication Reports

On this page Carat arrow pointing down
Warning:
As of May 12, 2021, CockroachDB v19.2 is no longer supported. For more details, refer to the Release Support Policy.

New in v19.2: Several new and updated tables (listed below) are available to help you query the status of your cluster's data replication, data placement, and zone constraint conformance. For example, you can:

  • See what data is under-replicated or unavailable.
  • Show which of your localities (if any) are critical. A locality is "critical" for a range if all of the nodes in that locality becoming unreachable would cause the range to become unavailable. In other words, the locality contains a majority of the range's replicas.
  • See if any of your cluster's data placement constraints are being violated.
Note:

The information on this page assumes you are familiar with replication zones and partitioning.

Warning:

This is an experimental feature. The interface and output are subject to change.

In particular, the direct access to system tables shown here will not be a supported way to inspect CockroachDB in future versions. We're committed to adding stable ways to inspect these replication reports in the future, likely via SHOW statements and/or views and built-in functions in the crdb_internal schema.

Conformance reporting tables

The following new and updated tables are available for verifying constraint conformance.

  • system.reports_meta contains metadata about the replication report data listed in the system.replication_* tables. Currently, the only metadata available is the report generation time.
  • system.replication_stats shows information about whether data is under-replicated, over-replicated, or unavailable.
  • system.replication_constraint_stats shows the status of any data placement constraints you've configured.
  • system.replication_critical_localities shows which localities in your cluster (if any) are critical. A locality is "critical" for a range if all of the nodes in that locality becoming unreachable would cause the range to become unavailable. In other words, the locality contains a majority of the range's replicas.
  • crdb_internal.zones can be used with the tables above to figure out the databases and table names where the non-conforming or at-risk data is located.

To configure how often the conformance reports are run, adjust the kv.replication_reports.interval cluster setting, which accepts an INTERVAL. For example, to run it every five minutes:

icon/buttons/copy
SET CLUSTER setting kv.replication_reports.interval = '5m';

Only members of the admin role can access these tables. By default, the root user belongs to the admin role. For more information about users and roles, see Manage Users and Manage Roles.

Note:

The replication reports are only generated for zones that meet the following criteria:

  • The zone overrides some replication attributes compared to their parent zone. Ranges in zones for which a report is not generated are counted in the report of the first ancestor zone for which a report is generated.

The attributes that must be overridden to trigger each report to run are:

Report Field(s)
replication_constraint_stats constraints
replication_critical_localities constraints, num_replicas
replication_stats constraints, num_replicas

In addition to the above, the system's default zone always gets a report.

system.replication_stats

The system.replication_stats report contains information about whether data is under-replicated, over-replicated, or unavailable.

For an example using this table, see Find out which databases and tables have under-replicated ranges.

Columns

Column name Data type Description
zone_id INT8 The ID of the replication zone.
subzone_id INT8 The ID of the subzone (i.e., partition).
report_id INT8 The ID of the report that generated all of the rows in this table.
total_ranges INT8 Total ranges in the zone this report entry is referring to.
unavailable_ranges INT8 Unavailable ranges in the zone this report entry is referring to.
under_replicated_ranges INT8 Under-replicated ranges in the zone this report entry is referring to.
over_replicated_ranges INT8 Over-replicated ranges in the zone this report entry is referring to.

system.replication_critical_localities

The system.replication_critical_localities report contains which of your localities (if any) are critical. A locality is "critical" for a range if all of the nodes in that locality becoming unreachable would cause the range to become unavailable. In other words, the locality contains a majority of the range's replicas.

That said, a locality being critical is not necessarily a bad thing as long as you are aware of it. What matters is that you configure the topology of your cluster to get the resiliency you expect.

As described in Configure Replication Zones, localities are key-value pairs defined at node startup time, and are ordered into locality tiers that range from most inclusive to least inclusive (e.g., region before datacenter as in region=eu,dc=paris).

For an example using this table, see Find out which databases and tables have ranges in critical localities.

Columns

Column name Data type Description
zone_id INT8 The ID of the replication zone.
subzone_id INT8 The ID of the subzone (i.e., partition).
locality STRING The name of the critical locality.
report_id INT8 The ID of the report that generated all of the rows in this table.
at_risk_ranges INT8 The ranges that are at risk of becoming unavailable as of the time of this report.
Note:

If you have not defined any localities, this report will not return any results. It only reports on localities that have been explicitly defined.

system.replication_constraint_stats

The system.replication_constraint_stats report lists violations to any data placement requirements you've configured.

For an example using this table, see Find out which of your tables have a constraint violation.

Columns

Column name Data type Description
zone_id INT8 The ID of the replication zone.
subzone_id INT8 The ID of the subzone (i.e., partition).
type STRING The type of zone configuration that was violated, e.g., constraint.
config STRING The YAML key-value pair used to configure the zone, e.g., +region=europe-west1.
report_id INT8 The ID of the report that generated all of the rows in this table.
violation_start TIMESTAMPTZ The time when the violation was detected. Will return NULL if the number of violating_ranges is 0.
violating_ranges INT8 The ranges that are in violation of the configuration.

system.reports_meta

The system.reports_meta report contains metadata about when the replication reports were last run. Each report contains a number of report entries, one per zone.

Replication reports are run at the interval specified by the kv.replication_reports.interval cluster setting.

Columns

Column name Data type Description
id INT8 The ID of the report that this report entry is part of.
generated TIMESTAMPTZ When the report was generated.

crdb_internal.zones

The crdb_internal.zones table is useful for:

  • Viewing your cluster's zone configurations in various formats: YAML, SQL, etc.
  • Matching up data returned from the various replication reports with the names of the databases and tables, indexes, and partitions where that data lives.
icon/buttons/copy
SHOW COLUMNS FROM crdb_internal.zones;
column_name data_type description
zone_id INT8 The ID of the replication zone.
subzone_id INT8 The ID of the subzone (i.e., partition).
target STRING The "object" that the constraint is being applied to, e.g., PARTITION us_west OF INDEX movr.public.users@primary.
range_name STRING The zone's name.
database_name STRING The database where the target's data is located.
table_name STRING The table where the target's data is located.
index_name STRING The index where the target's data is located.
partition_name STRING The partition where the target's data is located.
full_config_yaml STRING The YAML you used to configure this replication zone.
full_config_sql STRING The SQL you used to configure this replication zone.
raw_config_yaml STRING The YAML for this replication zone, showing only values the user changed from the defaults.
raw_config_sql STRING The SQL for this replication zone, showing only values the user changed from the defaults.
raw_config_protobuf BYTES A protobuf representation of the configuration for this replication zone.

Examples

Setup

The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App.

To follow along, run cockroach demo with the --geo-partitioned-replicas flag. This command opens an interactive SQL shell to a temporary, 9-node in-memory cluster with the Geo-Partitioned Replicas Topology applied to the movr database.

icon/buttons/copy
$ cockroach demo --geo-partitioned-replicas

Find out which of your tables have a constraint violation

By default, this geo-distributed demo cluster will not have any constraint violations.

To introduce a violation that we can then query for, we'll modify the zone configuration of the users table.

First, let's see what existing zone configurations are attached to the users table, so we know what to modify.

icon/buttons/copy
SHOW CREATE TABLE users;
  table_name |                                  create_statement                                    
+------------+-------------------------------------------------------------------------------------+
  users      | CREATE TABLE users (                                                                 
             |     id UUID NOT NULL,                                                                
             |     city VARCHAR NOT NULL,                                                           
             |     name VARCHAR NULL,                                                               
             |     address VARCHAR NULL,                                                            
             |     credit_card VARCHAR NULL,                                                        
             |     CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),                             
             |     FAMILY "primary" (id, city, name, address, credit_card)                          
             | ) PARTITION BY LIST (city) (                                                         
             |     PARTITION us_west VALUES IN (('seattle'), ('san francisco'), ('los angeles')),   
             |     PARTITION us_east VALUES IN (('new york'), ('boston'), ('washington dc')),       
             |     PARTITION europe_west VALUES IN (('amsterdam'), ('paris'), ('rome'))             
             | );                                                                                   
             | ALTER PARTITION europe_west OF INDEX movr.public.users@primary CONFIGURE ZONE USING  
             |     constraints = '[+region=europe-west1]';                                          
             | ALTER PARTITION us_east OF INDEX movr.public.users@primary CONFIGURE ZONE USING      
             |     constraints = '[+region=us-east1]';                                              
             | ALTER PARTITION us_west OF INDEX movr.public.users@primary CONFIGURE ZONE USING      
             |     constraints = '[+region=us-west1]'                                               
(1 row)

To create a constraint violation, let's tell the ranges in the europe_west partition that they are explicitly supposed to not be in the region=europe-west1 locality by issuing the following statement:

icon/buttons/copy
ALTER PARTITION europe_west of INDEX movr.public.users@primary CONFIGURE ZONE USING constraints = '[-region=europe-west1]';

Once the statement above executes, the ranges currently stored in that locality will now be in a state where they are explicitly not supposed to be in that locality, and are thus in violation of a constraint.

In other words, we are telling the ranges "where you are now is exactly where you are not supposed to be". This will cause the cluster to rebalance the ranges, which will take some time. During the time it takes for the rebalancing to occur, the ranges will be in violation.

By default, the system constraint conformance report runs once every minute. You can change that interval by modifying the kv.replication_reports.interval cluster setting.

After the internal constraint conformance report has run again, the following query should report a violation:

icon/buttons/copy
SELECT * FROM system.replication_constraint_stats WHERE violating_ranges > 0;
  zone_id | subzone_id |    type    |        config        | report_id |         violation_start         | violating_ranges  
+---------+------------+------------+----------------------+-----------+---------------------------------+------------------+
       53 |          2 | constraint | +region=us-east1     |         1 | 2019-10-21 20:28:40.79508+00:00 |                2  
       53 |          3 | constraint | -region=europe-west1 |         1 | 2019-10-21 20:28:40.79508+00:00 |                2  
       54 |          2 | constraint | +region=us-west1     |         1 | 2019-10-21 20:28:40.79508+00:00 |                1  
       54 |          4 | constraint | +region=us-east1     |         1 | 2019-10-21 20:28:40.79508+00:00 |                2  
       55 |          6 | constraint | +region=us-east1     |         1 | 2019-10-21 20:28:40.79508+00:00 |                4  
       55 |          9 | constraint | +region=europe-west1 |         1 | 2019-10-21 20:28:40.79508+00:00 |                6  
       56 |          2 | constraint | +region=us-east1     |         1 | 2019-10-21 20:28:40.79508+00:00 |                2  
       56 |          3 | constraint | +region=europe-west1 |         1 | 2019-10-21 20:28:40.79508+00:00 |                1  
       58 |          2 | constraint | +region=us-east1     |         1 | 2019-10-21 20:28:40.79508+00:00 |                2  
(9 rows)

To be more useful, we'd like to find out the database and table names where these constraint-violating ranges live. To get that information we'll need to join the output of system.replication_constraint_stats report with the crdb_internal.zones table using a query like the following:

icon/buttons/copy
WITH
    partition_violations
        AS (
            SELECT
                *
            FROM
                system.replication_constraint_stats
            WHERE
                violating_ranges > 0
        ),
    report
        AS (
            SELECT
                crdb_internal.zones.zone_id,
                crdb_internal.zones.subzone_id,
                target,
                database_name,
                table_name,
                index_name,
                partition_violations.type,
                partition_violations.config,
                partition_violations.violation_start,
                partition_violations.violating_ranges
            FROM
                crdb_internal.zones, partition_violations
            WHERE
                crdb_internal.zones.zone_id
                = partition_violations.zone_id
        )
SELECT * FROM report;
  zone_id | subzone_id |                                             target                                             | database_name | table_name |                  index_name                   |    type    |        config        |         violation_start         | violating_ranges  
+---------+------------+------------------------------------------------------------------------------------------------+---------------+------------+-----------------------------------------------+------------+----------------------+---------------------------------+------------------+
       53 |          1 | PARTITION us_west OF INDEX movr.public.users@primary                                           | movr          | users      | primary                                       | constraint | -region=europe-west1 | 2019-10-21 20:28:40.79508+00:00 |                1  
       53 |          2 | PARTITION us_east OF INDEX movr.public.users@primary                                           | movr          | users      | primary                                       | constraint | -region=europe-west1 | 2019-10-21 20:28:40.79508+00:00 |                1  
       53 |          3 | PARTITION europe_west OF INDEX movr.public.users@primary                                       | movr          | users      | primary                                       | constraint | -region=europe-west1 | 2019-10-21 20:28:40.79508+00:00 |                1  
       54 |          1 | PARTITION us_west OF INDEX movr.public.vehicles@primary                                        | movr          | vehicles   | primary                                       | constraint | +region=us-east1     | 2019-10-21 20:28:40.79508+00:00 |                1  
       54 |          2 | PARTITION us_west OF INDEX movr.public.vehicles@vehicles_auto_index_fk_city_ref_users          | movr          | vehicles   | vehicles_auto_index_fk_city_ref_users         | constraint | +region=us-east1     | 2019-10-21 20:28:40.79508+00:00 |                1  
       54 |          3 | PARTITION us_east OF INDEX movr.public.vehicles@primary                                        | movr          | vehicles   | primary                                       | constraint | +region=us-east1     | 2019-10-21 20:28:40.79508+00:00 |                1  
       54 |          4 | PARTITION us_east OF INDEX movr.public.vehicles@vehicles_auto_index_fk_city_ref_users          | movr          | vehicles   | vehicles_auto_index_fk_city_ref_users         | constraint | +region=us-east1     | 2019-10-21 20:28:40.79508+00:00 |                1  
       54 |          5 | PARTITION europe_west OF INDEX movr.public.vehicles@primary                                    | movr          | vehicles   | primary                                       | constraint | +region=us-east1     | 2019-10-21 20:28:40.79508+00:00 |                1  
       54 |          6 | PARTITION europe_west OF INDEX movr.public.vehicles@vehicles_auto_index_fk_city_ref_users      | movr          | vehicles   | vehicles_auto_index_fk_city_ref_users         | constraint | +region=us-east1     | 2019-10-21 20:28:40.79508+00:00 |                1  
       55 |          1 | PARTITION us_west OF INDEX movr.public.rides@primary                                           | movr          | rides      | primary                                       | constraint | +region=us-east1     | 2019-10-21 20:28:40.79508+00:00 |                1  
       55 |          2 | PARTITION us_west OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users                | movr          | rides      | rides_auto_index_fk_city_ref_users            | constraint | +region=us-east1     | 2019-10-21 20:28:40.79508+00:00 |                1  
       55 |          3 | PARTITION us_west OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles     | movr          | rides      | rides_auto_index_fk_vehicle_city_ref_vehicles | constraint | +region=us-east1     | 2019-10-21 20:28:40.79508+00:00 |                1  
       55 |          4 | PARTITION us_east OF INDEX movr.public.rides@primary                                           | movr          | rides      | primary                                       | constraint | +region=us-east1     | 2019-10-21 20:28:40.79508+00:00 |                1  
       55 |          5 | PARTITION us_east OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users                | movr          | rides      | rides_auto_index_fk_city_ref_users            | constraint | +region=us-east1     | 2019-10-21 20:28:40.79508+00:00 |                1  
       55 |          6 | PARTITION us_east OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles     | movr          | rides      | rides_auto_index_fk_vehicle_city_ref_vehicles | constraint | +region=us-east1     | 2019-10-21 20:28:40.79508+00:00 |                1  
       55 |          7 | PARTITION europe_west OF INDEX movr.public.rides@primary                                       | movr          | rides      | primary                                       | constraint | +region=us-east1     | 2019-10-21 20:28:40.79508+00:00 |                1  
       55 |          8 | PARTITION europe_west OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users            | movr          | rides      | rides_auto_index_fk_city_ref_users            | constraint | +region=us-east1     | 2019-10-21 20:28:40.79508+00:00 |                1  
       55 |          9 | PARTITION europe_west OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles | movr          | rides      | rides_auto_index_fk_vehicle_city_ref_vehicles | constraint | +region=us-east1     | 2019-10-21 20:28:40.79508+00:00 |                1  
(18 rows)

If you were to repeat this query at 60-second intervals, you would see that the number of results returned decreases and eventually falls to zero as the cluster rebalances the ranges to their new homes. Eventually you will see this output, which will tell you that the rebalancing has finished.

  zone_id | subzone_id | target | database_name | table_name | index_name | type | config | violation_start | violating_ranges  
+---------+------------+--------+---------------+------------+------------+------+--------+-----------------+------------------+
(0 rows)

Find out which databases and tables have under-replicated ranges

By default, this geo-distributed demo cluster will not have any under-replicated ranges.

To force it into a state where some ranges are under-replicated, issue the following statement, which tells it to store 9 copies of each range underlying the rides table (by default it stores 3).

icon/buttons/copy
ALTER TABLE rides CONFIGURE ZONE USING num_replicas=9;

Once the statement above executes, the cluster will rebalance so that it's storing 9 copies of each range underlying the rides table. During the time it takes for the rebalancing to occur, these ranges will be considered "under-replicated", since there are not yet as many copies (9) of each range as you have just specified.

By default, the internal constraint conformance report runs once every minute. You can change that interval by modifying the kv.replication_reports.interval cluster setting.

After the system constraint conformance report has run again, the following query should report under-replicated ranges:

icon/buttons/copy
SELECT * FROM system.replication_stats WHERE under_replicated_ranges > 0;
  zone_id | subzone_id | report_id | total_ranges | unavailable_ranges | under_replicated_ranges | over_replicated_ranges  
+---------+------------+-----------+--------------+--------------------+-------------------------+------------------------+
       55 |          0 |         3 |           28 |                  0 |                       6 |                      0  
       55 |          3 |         3 |            9 |                  0 |                       9 |                      0  
       55 |          6 |         3 |            9 |                  0 |                       9 |                      0  
       55 |          9 |         3 |            9 |                  0 |                       9 |                      0  
(4 rows)

To be more useful, we'd like to find out the database and table names where these under-replicated ranges live. To get that information we'll need to join the output of system.replication_stats report with the crdb_internal.zones table using a query like the following:

icon/buttons/copy
WITH
    under_replicated_zones
        AS (
            SELECT
                zone_id, under_replicated_ranges
            FROM
                system.replication_stats
            WHERE
                under_replicated_ranges > 0
        ),
    report
        AS (
            SELECT
                crdb_internal.zones.zone_id,
                target,
                range_name,
                database_name,
                table_name,
                index_name,
                under_replicated_zones.under_replicated_ranges
            FROM
                crdb_internal.zones, under_replicated_zones
            WHERE
                crdb_internal.zones.zone_id
                = under_replicated_zones.zone_id
        )
SELECT * FROM report;
  zone_id |                                             target                                             | range_name | database_name | table_name |                  index_name                   | under_replicated_ranges  
+---------+------------------------------------------------------------------------------------------------+------------+---------------+------------+-----------------------------------------------+-------------------------+
       55 | TABLE movr.public.rides                                                                        | NULL       | movr          | rides      | NULL                                          |                       9  
       55 | TABLE movr.public.rides                                                                        | NULL       | movr          | rides      | NULL                                          |                       9  
       55 | TABLE movr.public.rides                                                                        | NULL       | movr          | rides      | NULL                                          |                       9  
       55 | PARTITION us_west OF INDEX movr.public.rides@primary                                           | NULL       | movr          | rides      | primary                                       |                       9  
       55 | PARTITION us_west OF INDEX movr.public.rides@primary                                           | NULL       | movr          | rides      | primary                                       |                       9  
       55 | PARTITION us_west OF INDEX movr.public.rides@primary                                           | NULL       | movr          | rides      | primary                                       |                       9  
       55 | PARTITION us_west OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users                | NULL       | movr          | rides      | rides_auto_index_fk_city_ref_users            |                       9  
       55 | PARTITION us_west OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users                | NULL       | movr          | rides      | rides_auto_index_fk_city_ref_users            |                       9  
       55 | PARTITION us_west OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users                | NULL       | movr          | rides      | rides_auto_index_fk_city_ref_users            |                       9  
       55 | PARTITION us_west OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles     | NULL       | movr          | rides      | rides_auto_index_fk_vehicle_city_ref_vehicles |                       9  
       55 | PARTITION us_west OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles     | NULL       | movr          | rides      | rides_auto_index_fk_vehicle_city_ref_vehicles |                       9  
       55 | PARTITION us_west OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles     | NULL       | movr          | rides      | rides_auto_index_fk_vehicle_city_ref_vehicles |                       9  
       55 | PARTITION us_east OF INDEX movr.public.rides@primary                                           | NULL       | movr          | rides      | primary                                       |                       9  
       55 | PARTITION us_east OF INDEX movr.public.rides@primary                                           | NULL       | movr          | rides      | primary                                       |                       9  
       55 | PARTITION us_east OF INDEX movr.public.rides@primary                                           | NULL       | movr          | rides      | primary                                       |                       9  
       55 | PARTITION us_east OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users                | NULL       | movr          | rides      | rides_auto_index_fk_city_ref_users            |                       9  
       55 | PARTITION us_east OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users                | NULL       | movr          | rides      | rides_auto_index_fk_city_ref_users            |                       9  
       55 | PARTITION us_east OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users                | NULL       | movr          | rides      | rides_auto_index_fk_city_ref_users            |                       9  
       55 | PARTITION us_east OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles     | NULL       | movr          | rides      | rides_auto_index_fk_vehicle_city_ref_vehicles |                       9  
       55 | PARTITION us_east OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles     | NULL       | movr          | rides      | rides_auto_index_fk_vehicle_city_ref_vehicles |                       9  
       55 | PARTITION us_east OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles     | NULL       | movr          | rides      | rides_auto_index_fk_vehicle_city_ref_vehicles |                       9  
       55 | PARTITION europe_west OF INDEX movr.public.rides@primary                                       | NULL       | movr          | rides      | primary                                       |                       9  
       55 | PARTITION europe_west OF INDEX movr.public.rides@primary                                       | NULL       | movr          | rides      | primary                                       |                       9  
       55 | PARTITION europe_west OF INDEX movr.public.rides@primary                                       | NULL       | movr          | rides      | primary                                       |                       9  
       55 | PARTITION europe_west OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users            | NULL       | movr          | rides      | rides_auto_index_fk_city_ref_users            |                       9  
       55 | PARTITION europe_west OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users            | NULL       | movr          | rides      | rides_auto_index_fk_city_ref_users            |                       9  
       55 | PARTITION europe_west OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users            | NULL       | movr          | rides      | rides_auto_index_fk_city_ref_users            |                       9  
       55 | PARTITION europe_west OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles | NULL       | movr          | rides      | rides_auto_index_fk_vehicle_city_ref_vehicles |                       9  
       55 | PARTITION europe_west OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles | NULL       | movr          | rides      | rides_auto_index_fk_vehicle_city_ref_vehicles |                       9  
       55 | PARTITION europe_west OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles | NULL       | movr          | rides      | rides_auto_index_fk_vehicle_city_ref_vehicles |                       9  
(30 rows)

Find out which databases and tables have ranges in critical localities

The system.replication_critical_localities report contains which of your localities (if any) are critical. A locality is "critical" for a range if all of the nodes in that locality becoming unreachable would cause the range to become unavailable. In other words, the locality contains a majority of the range's replicas.

That said, a locality being critical is not necessarily a bad thing as long as you are aware of it. What matters is that you configure the topology of your cluster to get the resiliency you expect.

By default, the movr demo cluster has some ranges in critical localities. This is expected because it follows the geo-partitioned replicas topology pattern, which ties data for latency-sensitive queries to specific geographies at the cost of data unavailability during a region-wide failure.

icon/buttons/copy
SELECT * FROM system.replication_critical_localities WHERE at_risk_ranges > 0;
  zone_id | subzone_id |      locality       | report_id | at_risk_ranges  
+---------+------------+---------------------+-----------+----------------+
       53 |          1 | region=us-west1     |         2 |              3  
       53 |          2 | region=us-east1     |         2 |              3  
       53 |          3 | region=europe-west1 |         2 |              3  
       54 |          2 | region=us-west1     |         2 |              6  
       54 |          4 | region=us-east1     |         2 |              6  
       54 |          6 | region=europe-west1 |         2 |              6  
       55 |          3 | region=us-west1     |         2 |              9  
       55 |          6 | region=us-east1     |         2 |              9  
       55 |          9 | region=europe-west1 |         2 |              9  
       56 |          1 | region=us-west1     |         2 |              3  
       56 |          2 | region=us-east1     |         2 |              3  
       56 |          3 | region=europe-west1 |         2 |              3  
       58 |          1 | region=us-west1     |         2 |              3  
       58 |          2 | region=us-east1     |         2 |              3  
       58 |          3 | region=europe-west1 |         2 |              3  
(15 rows)

To be more useful, we'd like to find out the database and table names where these ranges live that are at risk of unavailability in the event of a locality becoming unreachable. To get that information we'll need to join the output of system.replication_critical_localities report with the crdb_internal.zones table using a query like the following:

icon/buttons/copy
WITH
    at_risk_zones AS (
            SELECT
                zone_id, locality, at_risk_ranges
            FROM
                system.replication_critical_localities
            WHERE
                at_risk_ranges > 0
        ),
    report AS (
            SELECT
                crdb_internal.zones.zone_id,
                target,
                database_name,
                table_name,
                index_name,
                at_risk_zones.at_risk_ranges
            FROM
                crdb_internal.zones, at_risk_zones
            WHERE
                crdb_internal.zones.zone_id
                = at_risk_zones.zone_id
        )
SELECT DISTINCT * FROM report;
  zone_id |                                             target                                             | database_name |         table_name         |                  index_name                   | at_risk_ranges  
+---------+------------------------------------------------------------------------------------------------+---------------+----------------------------+-----------------------------------------------+----------------+
       53 | PARTITION us_west OF INDEX movr.public.users@primary                                           | movr          | users                      | primary                                       |              3  
       53 | PARTITION us_east OF INDEX movr.public.users@primary                                           | movr          | users                      | primary                                       |              3  
       53 | PARTITION europe_west OF INDEX movr.public.users@primary                                       | movr          | users                      | primary                                       |              3  
       54 | PARTITION us_west OF INDEX movr.public.vehicles@primary                                        | movr          | vehicles                   | primary                                       |              6  
       54 | PARTITION us_west OF INDEX movr.public.vehicles@vehicles_auto_index_fk_city_ref_users          | movr          | vehicles                   | vehicles_auto_index_fk_city_ref_users         |              6  
       54 | PARTITION us_east OF INDEX movr.public.vehicles@primary                                        | movr          | vehicles                   | primary                                       |              6  
       54 | PARTITION us_east OF INDEX movr.public.vehicles@vehicles_auto_index_fk_city_ref_users          | movr          | vehicles                   | vehicles_auto_index_fk_city_ref_users         |              6  
       54 | PARTITION europe_west OF INDEX movr.public.vehicles@primary                                    | movr          | vehicles                   | primary                                       |              6  
       54 | PARTITION europe_west OF INDEX movr.public.vehicles@vehicles_auto_index_fk_city_ref_users      | movr          | vehicles                   | vehicles_auto_index_fk_city_ref_users         |              6  
       55 | PARTITION us_west OF INDEX movr.public.rides@primary                                           | movr          | rides                      | primary                                       |              9  
       55 | PARTITION us_west OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users                | movr          | rides                      | rides_auto_index_fk_city_ref_users            |              9  
       55 | PARTITION us_west OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles     | movr          | rides                      | rides_auto_index_fk_vehicle_city_ref_vehicles |              9  
       55 | PARTITION us_east OF INDEX movr.public.rides@primary                                           | movr          | rides                      | primary                                       |              9  
       55 | PARTITION us_east OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users                | movr          | rides                      | rides_auto_index_fk_city_ref_users            |              9  
       55 | PARTITION us_east OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles     | movr          | rides                      | rides_auto_index_fk_vehicle_city_ref_vehicles |              9  
       55 | PARTITION europe_west OF INDEX movr.public.rides@primary                                       | movr          | rides                      | primary                                       |              9  
       55 | PARTITION europe_west OF INDEX movr.public.rides@rides_auto_index_fk_city_ref_users            | movr          | rides                      | rides_auto_index_fk_city_ref_users            |              9  
       55 | PARTITION europe_west OF INDEX movr.public.rides@rides_auto_index_fk_vehicle_city_ref_vehicles | movr          | rides                      | rides_auto_index_fk_vehicle_city_ref_vehicles |              9  
       56 | PARTITION us_west OF INDEX movr.public.vehicle_location_histories@primary                      | movr          | vehicle_location_histories | primary                                       |              3  
       56 | PARTITION us_east OF INDEX movr.public.vehicle_location_histories@primary                      | movr          | vehicle_location_histories | primary                                       |              3  
       56 | PARTITION europe_west OF INDEX movr.public.vehicle_location_histories@primary                  | movr          | vehicle_location_histories | primary                                       |              3  
       58 | PARTITION us_west OF INDEX movr.public.user_promo_codes@primary                                | movr          | user_promo_codes           | primary                                       |              3  
       58 | PARTITION us_east OF INDEX movr.public.user_promo_codes@primary                                | movr          | user_promo_codes           | primary                                       |              3  
       58 | PARTITION europe_west OF INDEX movr.public.user_promo_codes@primary                            | movr          | user_promo_codes           | primary                                       |              3  
(24 rows)

To give another example, let's say your cluster were similar to the one shown above, but configured with tiered localities such that you had split us-east1 into {region=us-east1,dc=dc1, region=us-east1,dc=dc2, region=us-east1,dc=dc3}. In that case, you wouldn't expect any DC to be critical, because the cluster would "diversify" each range's location as much as possible across data centers. In such a situation, if you were to see a DC identified as a critical locality, you'd be surprised and you'd take some action. For example, perhaps the diversification process is failing because some localities are filled to capacity. If there is no disk space free in a locality, your cluster cannot move replicas there.

See also


Yes No
On this page

Yes No