System information

CrateDB provides the sys schema which contains virtual tables. These tables are read-only and can be queried to get statistical real-time information about the cluster, its nodes and their shards:

Cluster

Basic information about the CrateDB cluster can be retrieved from the sys.cluster table:

The result has at most 1 row:

cr> select name from sys.cluster;
+-----------------+
| name            |
+-----------------+
| Testing-CrateDB |
+-----------------+
SELECT 1 row in set (... sec)

Cluster license

The sys.cluster.license expression returns information about the currently registered license.

Note

Licenses were removed in CrateDB 4.5. Accordingly, these values are deprecated and return NULL in CrateDB 4.5 and higher.

license

Cluster settings

The sys.cluster.settings expression returns information about the currently applied cluster settings.

cr> select settings from sys.cluster;
+-------------------------------------------------------...-+
| settings                                                  |
+-------------------------------------------------------...-+
| {"bulk": {...}, "cluster": {...}, "discovery": {...}, ... |
+-------------------------------------------------------...-+
SELECT 1 row in set (... sec)

cr> select column_name, data_type from information_schema.columns
... where column_name like 'settings%'
... and table_name = 'cluster';
+-----------------------------------------------------------------------------------+------------------+
| column_name                                                                       | data_type        |
+-----------------------------------------------------------------------------------+------------------+
| settings                                                                          | object           |
| settings['bulk']                                                                  | object           |
| settings['bulk']['request_timeout']                                               | text             |
| settings['cluster']                                                               | object           |
| settings['cluster']['graceful_stop']                                              | object           |
| settings['cluster']['graceful_stop']['force']                                     | boolean          |
| settings['cluster']['graceful_stop']['min_availability']                          | text             |
| settings['cluster']['graceful_stop']['timeout']                                   | text             |
| settings['cluster']['info']                                                       | object           |
| settings['cluster']['info']['update']                                             | object           |
| settings['cluster']['info']['update']['interval']                                 | text             |
| settings['cluster']['max_shards_per_node']                                        | integer          |
| settings['cluster']['routing']                                                    | object           |
| settings['cluster']['routing']['allocation']                                      | object           |
| settings['cluster']['routing']['allocation']['allow_rebalance']                   | text             |
| settings['cluster']['routing']['allocation']['balance']                           | object           |
| settings['cluster']['routing']['allocation']['balance']['index']                  | real             |
| settings['cluster']['routing']['allocation']['balance']['shard']                  | real             |
| settings['cluster']['routing']['allocation']['balance']['threshold']              | real             |
| settings['cluster']['routing']['allocation']['cluster_concurrent_rebalance']      | integer          |
| settings['cluster']['routing']['allocation']['disk']                              | object           |
| settings['cluster']['routing']['allocation']['disk']['threshold_enabled']         | boolean          |
| settings['cluster']['routing']['allocation']['disk']['watermark']                 | object           |
| settings['cluster']['routing']['allocation']['disk']['watermark']['flood_stage']  | text             |
| settings['cluster']['routing']['allocation']['disk']['watermark']['high']         | text             |
| settings['cluster']['routing']['allocation']['disk']['watermark']['low']          | text             |
| settings['cluster']['routing']['allocation']['enable']                            | text             |
| settings['cluster']['routing']['allocation']['exclude']                           | object           |
| settings['cluster']['routing']['allocation']['exclude']['_host']                  | text             |
| settings['cluster']['routing']['allocation']['exclude']['_id']                    | text             |
| settings['cluster']['routing']['allocation']['exclude']['_ip']                    | text             |
| settings['cluster']['routing']['allocation']['exclude']['_name']                  | text             |
| settings['cluster']['routing']['allocation']['include']                           | object           |
| settings['cluster']['routing']['allocation']['include']['_host']                  | text             |
| settings['cluster']['routing']['allocation']['include']['_id']                    | text             |
| settings['cluster']['routing']['allocation']['include']['_ip']                    | text             |
| settings['cluster']['routing']['allocation']['include']['_name']                  | text             |
| settings['cluster']['routing']['allocation']['node_concurrent_recoveries']        | integer          |
| settings['cluster']['routing']['allocation']['node_initial_primaries_recoveries'] | integer          |
| settings['cluster']['routing']['allocation']['require']                           | object           |
| settings['cluster']['routing']['allocation']['require']['_host']                  | text             |
| settings['cluster']['routing']['allocation']['require']['_id']                    | text             |
| settings['cluster']['routing']['allocation']['require']['_ip']                    | text             |
| settings['cluster']['routing']['allocation']['require']['_name']                  | text             |
| settings['cluster']['routing']['allocation']['total_shards_per_node']             | integer          |
| settings['cluster']['routing']['rebalance']                                       | object           |
| settings['cluster']['routing']['rebalance']['enable']                             | text             |
| settings['discovery']                                                             | object           |
| settings['discovery']['zen']                                                      | object           |
| settings['discovery']['zen']['publish_timeout']                                   | text             |
| settings['gateway']                                                               | object           |
| settings['gateway']['expected_nodes']                                             | integer          |
| settings['gateway']['recover_after_nodes']                                        | integer          |
| settings['gateway']['recover_after_time']                                         | text             |
| settings['indices']                                                               | object           |
| settings['indices']['breaker']                                                    | object           |
| settings['indices']['breaker']['fielddata']                                       | object           |
| settings['indices']['breaker']['fielddata']['limit']                              | text             |
| settings['indices']['breaker']['fielddata']['overhead']                           | double precision |
| settings['indices']['breaker']['query']                                           | object           |
| settings['indices']['breaker']['query']['limit']                                  | text             |
| settings['indices']['breaker']['query']['overhead']                               | double precision |
| settings['indices']['breaker']['request']                                         | object           |
| settings['indices']['breaker']['request']['limit']                                | text             |
| settings['indices']['breaker']['request']['overhead']                             | double precision |
| settings['indices']['breaker']['total']                                           | object           |
| settings['indices']['breaker']['total']['limit']                                  | text             |
| settings['indices']['recovery']                                                   | object           |
| settings['indices']['recovery']['internal_action_long_timeout']                   | text             |
| settings['indices']['recovery']['internal_action_timeout']                        | text             |
| settings['indices']['recovery']['max_bytes_per_sec']                              | text             |
| settings['indices']['recovery']['recovery_activity_timeout']                      | text             |
| settings['indices']['recovery']['retry_delay_network']                            | text             |
| settings['indices']['recovery']['retry_delay_state_sync']                         | text             |
| settings['logger']                                                                | object_array     |
| settings['logger']['level']                                                       | text_array       |
| settings['logger']['name']                                                        | text_array       |
| settings['memory']                                                                | object           |
| settings['memory']['allocation']                                                  | object           |
| settings['memory']['allocation']['type']                                          | text             |
| settings['stats']                                                                 | object           |
| settings['stats']['breaker']                                                      | object           |
| settings['stats']['breaker']['log']                                               | object           |
| settings['stats']['breaker']['log']['jobs']                                       | object           |
| settings['stats']['breaker']['log']['jobs']['limit']                              | text             |
| settings['stats']['breaker']['log']['jobs']['overhead']                           | double precision |
| settings['stats']['breaker']['log']['operations']                                 | object           |
| settings['stats']['breaker']['log']['operations']['limit']                        | text             |
| settings['stats']['breaker']['log']['operations']['overhead']                     | double precision |
| settings['stats']['enabled']                                                      | boolean          |
| settings['stats']['jobs_log_expiration']                                          | text             |
| settings['stats']['jobs_log_filter']                                              | text             |
| settings['stats']['jobs_log_persistent_filter']                                   | text             |
| settings['stats']['jobs_log_size']                                                | integer          |
| settings['stats']['operations_log_expiration']                                    | text             |
| settings['stats']['operations_log_size']                                          | integer          |
| settings['stats']['service']                                                      | object           |
| settings['stats']['service']['interval']                                          | text             |
| settings['udc']                                                                   | object           |
| settings['udc']['enabled']                                                        | boolean          |
| settings['udc']['initial_delay']                                                  | text             |
| settings['udc']['interval']                                                       | text             |
| settings['udc']['url']                                                            | text             |
+-----------------------------------------------------------------------------------+------------------+
SELECT ... rows in set (... sec)

For further details, see the Cluster Settings configuration section.

Nodes

To get information about the nodes query for sys.nodes.

This table can be queried for one, multiple or all nodes within a cluster.

The table schema is as follows:

id

name

hostname

rest_url

port

load

mem

heap

version

cluster_state_version

fs

thread_pools

os

The cpu information values are cached for 1s. They might differ from the actual values at query time. Use the probe timestamp to get the time of collection. When analyzing the cpu usage over time, always use os['probe_timestamp'] to calculate the time difference between 2 probes.

Cgroup limitations

Note

Cgroup metrics only work if the stats are available from /sys/fs/cgroup/cpu and /sys/fs/cgroup/cpuacct.

Uptime limitations

Note

os[‘uptime’] required a system call when running CrateDB on Windows or macOS, however, system calls are not permitted by default. If you require this metric you need to allow system calls by setting bootstrap.seccomp to false. This setting must be set in the crate.yml or via command line argument and cannot be changed at runtime.

os_info

network

Network statistics are deprecated in CrateDB 2.3 and may completely be removed in subsequent versions. All BIGINT columns always return 0.

connections

process

The cpu information values are cached for 1s. They might differ from the actual values at query time. Use the probe timestamp to get the time of the collect. When analyzing the cpu usage over time, always use process['probe_timestamp'] to calculate the time difference between 2 probes.

Note

If one of the queried nodes is not responding within three seconds it returns null every column except id and name. This behaviour could be used to detect hanging nodes.

Node checks

The table sys.node_checks exposes a list of internal node checks and results of their validation.

The table schema is the following:

Example query:

cr> select id, node_id, description from sys.node_checks order by id, node_id;
+----+---------...-+--------------------------------------------------------------...-+
| id | node_id     | description                                                      |
+----+---------...-+--------------------------------------------------------------...-+
|  1 | ...         | The value of the cluster setting 'gateway.expected_nodes' mus... |
|  2 | ...         | The value of the cluster setting 'gateway.recover_after_nodes... |
|  3 | ...         | If any of the "expected nodes" recovery settings are set, the... |
|  5 | ...         | The high disk watermark is exceeded on the node. The cluster ... |
|  6 | ...         | The low disk watermark is exceeded on the node. The cluster w... |
|  7 | ...         | The flood stage disk watermark is exceeded on the node. Table... |
+----+---------...-+--------------------------------------------------------------...-+
SELECT 6 rows in set (... sec)

Acknowledge failed checks

It is possible to acknowledge every check by updating the acknowledged column. By doing this, specially CrateDB’s built-in Admin-UI won’t complain anymore about failing checks.

Imagine we’ve added a new node to our cluster, but as the gateway.expected_nodes column can only be set via config-file or command-line argument, the check for this setting will not pass on the already running nodes until the config-file or command-line argument on these nodes is updated and the nodes are restarted (which is not what we want on a healthy well running cluster).

In order to make the Admin-UI accept a failing check (so the checks label goes green again), we must acknowledge this check by updating it’s acknowledged flag:

cr> update sys.node_checks set acknowledged = true where id = 1;
UPDATE OK, 1 row affected (... sec)

Caution

Updates on this column are transient, so changed values are lost after the affected node is restarted.

Description of checked node settings

Recovery expected nodes

The check for the gateway.expected_nodes setting checks that the number of nodes that should be waited for the immediate cluster state recovery. This value must be equal to the maximum number of data and master nodes in the cluster.

Recovery after nodes

The check for the gateway.recover_after_nodes verifies that the number of started nodes before the cluster starts must be greater than the half of the expected number of nodes and equal to or less than number of nodes in the cluster.

(E / 2) < R <= E

Here, R is the number of recovery nodes and E is the number of expected nodes.

Recovery after time

If gateway.recover_after_nodes is set, then gateway.recover_after_time must not be set to 0s, otherwise the gateway.recover_after_nodes setting wouldn’t have any effect.

Routing allocation disk watermark high

The check for the cluster.routing.allocation.disk.watermark.high setting verifies that the high watermark is not exceeded on the current node. The usage of each disk for configured CrateDB data paths is verified against the threshold setting. If one or more verification fails the check is marked as not passed.

Routing allocation disk watermark low

The check for the cluster.routing.allocation.disk.watermark.low which controls the low watermark for the node disk usage. The check verifies that the low watermark is not exceeded on the current node. The verification is done against each disk for configured CrateDB data paths. The check is not passed if the verification for one or more disk fails.

Shards

The table sys.shards contains real-time statistics for all shards of all (non-system) tables.

Table schema

Note

The sys.shards table is subject to Shard table permissions.

Example

For example, you can query shards like this:

cr> select schema_name as schema,
...   table_name as t,
...   id,
...   partition_ident as p_i,
...   num_docs as docs,
...   primary,
...   relocating_node as r_n,
...   routing_state as r_state,
...   state,
...   orphan_partition as o_p
... from sys.shards where table_name = 'locations' and id = 1;
+--------+-----------+----+-----+------+---------+------+---------+---------+-------+
| schema | t         | id | p_i | docs | primary | r_n  | r_state |  state  | o_p   |
+--------+-----------+----+-----+------+---------+------+---------+---------+-------+
| doc    | locations |  1 |     |    8 | TRUE    | NULL | STARTED | STARTED | FALSE |
+--------+-----------+----+-----+------+---------+------+---------+---------+-------+
SELECT 1 row in set (... sec)

Segments

The sys.segments table contains information about the Lucene segments of the shards.

The segment information is useful to understand the behaviour of the underlying Lucene file structures for troubleshooting and performance optimization of shards.

Note

The information in the sys.segments table is expensive to calculate and therefore this information should be retrieved with awareness that it can have performance implications on the cluster.

Note

The sys.shards table is subject to Shard table permissions.

Jobs, operations, and logs

To let you inspect the activities currently taking place in a cluster, CrateDB provides system tables that let you track current cluster jobs and operations. See Jobs Table and Operations Table.

Jobs and operations that finished executing are additionally recorded in memory. There are two retention policies available to control how many records should be kept.

One option is to configure the maximum number of records which should be kept. Once the configured table size is reached, the older log records are deleted as newer records are added. This is configurable using stats.jobs_log_size and stats.operations_log_size.

Another option is to configure an expiration time for the records. In this case, the records in the logs tables are periodically cleared if they are older than the expiry time. This behaviour is configurable using stats.jobs_log_expiration and stats.operations_log_expiration.

In addition to these retention policies, there is a memory limit in place preventing these tables from taking up too much memory. The amount of memory that can be used to store the jobs can be configured using stats.breaker.log.jobs.limit and stats.breaker.log.operations.limit. If the memory limit is reached, an error message will be logged and the log table will be cleared completely.

It is also possible to define a filter which must match for jobs to be recorded after they finished executing. This can be useful to only record slow queries or queries that failed due to an error. This filter can be configured using the stats.jobs_log_filer setting.

Furthermore, there is a second filter setting which also results in a log entry in the regular CrateDB log file for all finished jobs that match this filter. This can be configured using stats.jobs_log_persistent_filter. This could be used to create a persistent slow query log.

Jobs

The sys.jobs table is a constantly updated view of all jobs that are currently being executed in the cluster.

Table schema

The field username corresponds to the SESSION_USER that is performing the query:

cr> select stmt, username, started from sys.jobs where stmt like 'sel% from %jobs%';
+---------------------------------------------------------------------------------+----------+-...-----+
| stmt                                                                            | username | started |
+---------------------------------------------------------------------------------+----------+-...-----+
| select stmt, username, started from sys.jobs where stmt like 'sel% from %jobs%' | crate    | ...     |
+---------------------------------------------------------------------------------+----------+-...-----+
SELECT 1 row in set (... sec)

Note

If the user management module is not available, the username is given as crate.

Every request that queries data or manipulates data is considered a “job” if it is a valid query. Requests that are not valid queries (for example, a request that tries to query a non-existent table) will not show up as jobs.

Note

The sys.jobs table is subject to Sys jobs tables permissions.

Jobs metrics

The sys.jobs_metrics table provides an overview of the query latency in the cluster. Jobs metrics are not persisted across node restarts.

The metrics are aggregated for each node and each unique classification of the statements.

Note

In order to reduce the memory requirements for these metrics, the times are statistically sampled and therefore may have slight inaccuracies. In addition, durations are only tracked up to 10 minutes. Statements taking longer than that are capped to 10 minutes.

sys.jobs_metrics Table schema

Classification

Certain statement types (such as SELECT statements) have additional labels in their classification. These labels are the names of the logical plan operators that are involved in the query.

For example, the following UNION statement:

SELECT name FROM t1 where id = 1
UNION ALL
SELECT name FROM t2 where id < 2

would result in the following labels:

• Union` for the UNION ALL

• Get for the left SELECT

• Collect for the right SELECT

Note

Labels may be subject to change as they only represent internal properties of the statement!

Operations

The sys.operations table is a constantly updated view of all operations that are currently being executed in the cluster:

cr> select node['name'], job_id, name, used_bytes from sys.operations
... order by name limit 1;
+--------------+--------...-+-----...-+------------+
| node['name'] | job_id     | name    | used_bytes |
+--------------+--------...-+-----...-+------------+
| crate        | ...        | ...     | ...        |
+--------------+--------...-+-----...-+------------+
SELECT 1 row in set (... sec)

An operation is a node-specific sub-component of a job (for when a job involves multi-node processing). Jobs that do not require multi-node processing will not produce any operations.

Table schema

Note

In some cases, operations are generated for internal CrateDB work that does not directly correspond to a user request. These entries do not have corresponding entries in sys.jobs.

Logs

The sys.jobs and sys.operations tables have corresponding log tables: sys.jobs_log and sys.operations_log.

sys.jobs_log Table schema

Note

You can control which jobs are recorded using the stats.jobs_log_filter

Note

The sys.jobs_log table is subject to Sys jobs tables permissions.

sys.operations_log Table schema

After a job or operation finishes, the corresponding entry will be moved into the corresponding log table:

cr> select id, stmt, username, started, ended, error
... from sys.jobs_log order by ended desc limit 2;
+-...+----------------------------------------------...-+----------+-...-----+-...---+-------+
| id | stmt                                             | username | started | ended | error |
+-...+----------------------------------------------...-+----------+-...-----+-...---+-------+
| ...| select node['name'], ...                         | crate    | ...     | ...   |  NULL |
| ...| select stmt, username, started from sys.jobs ... | crate    | ...     | ...   |  NULL |
+-...+----------------------------------------------...-+----------+-...-----+-...---+-------+
SELECT 2 rows in set (... sec)

Invalid queries are also logged in the sys.jobs_log table, i.e. queries that never make it to the sys.jobs table because they could not be executed.

The log tables are bound by a fixed size (stats.jobs_log_size) or by an expiration time (stats.jobs_log_expiration)

See Collecting stats for information on how to configure logs.

Caution

If you deactivate statistics tracking, the logs tables will be truncated.

Cluster checks

The table sys.checks exposes a list of internal cluster checks and results of their validation.

The sys.checks table looks like this:

Here’s an example query:

cr> select id, description from sys.checks order by id;
+----+--------------------------------------------------------------...-+
| id | description                                                      |
+----+--------------------------------------------------------------...-+
|  2 | The total number of partitions of one or more partitioned tab... |
|  3 | The following tables need to be recreated for compatibility w... |
+----+--------------------------------------------------------------...-+
SELECT 2 rows in set (... sec)

Cluster checks are also indicated in the CrateDB admin console. When all cluster checks (and all Node checks) pass, the Checks icon will be green. Here’s what it looks like when some checks are failing at the CRITICAL severity level:

Current Checks

Number of partitions

This check warns if any partitioned table has more than 1000 partitions to detect the usage of a high cardinality field for partitioning.

Tables need to be recreated

Warning

Do not attempt to upgrade your cluster to a newer major version if this cluster check is failing. Follow the instructions below to get this cluster check passing.

This check warns you if your cluster contains tables that you need to reindex before you can upgrade to a future major version of CrateDB.

If you try to upgrade to a later major CrateDB version without reindexing the tables, CrateDB will refuse to start.

CrateDB table version compatibility scheme

CrateDB maintains backward compatibility for tables created in majorVersion - 1:

Avoiding reindex using partitioned tables

Reindexing tables is an expensive operation which can take a long time. If you are storing time series data for a certain retention period and intend to delete old data, it is possible to use the partitioned tables to avoid reindex operations.

You will have to use a partition column that denotes time. For example, if you have a retention period of nine months, you could partition a table by a month column. Then, every month, the system will create a new partition. This new partition is created using the active CrateDB version and is compatible with the next major CrateDB version. Now to achieve your goal of avoiding a reindex, you must manually delete any partition older than nine months. If you do that, then after nine months you rolled through all partitions and the remaining nine are compatible with the next major CrateDB version.

How to reindex

1. Use SHOW CREATE TABLE to get the schema required to create an empty copy of the table to recreate:

   cr> SHOW CREATE TABLE rx.metrics;
   +-----------------------------------------------------+
   | SHOW CREATE TABLE rx.metrics                        |
   +-----------------------------------------------------+
   | CREATE TABLE IF NOT EXISTS "rx"."metrics" (         |
   |    "id" TEXT,                                       |
   |    "temperature" REAL,                              |
   |    PRIMARY KEY ("id")                               |
   | )                                                   |
   | CLUSTERED BY ("id") INTO 4 SHARDS                   |
   | WITH (                                              |
   |    "allocation.max_retries" = 5,                    |
   |    "blocks.metadata" = false,                       |
   |    "blocks.read" = false,                           |
   |    "blocks.read_only" = false,                      |
   |    "blocks.read_only_allow_delete" = false,         |
   |    "blocks.write" = false,                          |
   |    codec = 'default',                               |
   |    column_policy = 'strict',                        |
   |    "mapping.total_fields.limit" = 1000,             |
   |    max_ngram_diff = 1,                              |
   |    max_shingle_diff = 3,                            |
   |    number_of_replicas = '0-1',                      |
   |    "routing.allocation.enable" = 'all',             |
   |    "routing.allocation.total_shards_per_node" = -1, |
   |    "store.type" = 'fs',                             |
   |    "translog.durability" = 'REQUEST',               |
   |    "translog.flush_threshold_size" = 536870912,     |
   |    "translog.sync_interval" = 5000,                 |
   |    "unassigned.node_left.delayed_timeout" = 60000,  |
   |    "write.wait_for_active_shards" = '1'             |
   | )                                                   |
   +-----------------------------------------------------+
   SHOW 1 row in set (... sec)

2. Create a new temporary table, using the schema returned from SHOW CREATE TABLE:

   cr> CREATE TABLE rx.tmp_metrics (id TEXT PRIMARY KEY, temperature REAL);
   CREATE OK, 1 row affected (... sec)

3. Copy the data:

   cr> INSERT INTO rx.tmp_metrics (id, temperature) (SELECT id, temperature FROM rx.metrics);
   INSERT OK, 2 rows affected (... sec)

4. Swap the tables:

   cr> ALTER CLUSTER SWAP TABLE rx.tmp_metrics TO rx.metrics;
   ALTER OK, 1 row affected  (... sec)

5. Confirm the new your_table contains all data and has the new version:

   cr> SELECT count(*) FROM rx.metrics;
   +----------+
   | count(*) |
   +----------+
   |        2 |
   +----------+
   SELECT 1 row in set (... sec)
   cr> SELECT version['created'] FROM information_schema.tables
   ... WHERE table_schema = 'rx' AND table_name = 'metrics';
   +--------------------+
   | version['created'] |
   +--------------------+
   | 4.6.2              |
   +--------------------+
   SELECT 1 row in set (... sec)

6. Drop the old table, as it is now obsolete:

   cr> DROP TABLE rx.tmp_metrics;
   DROP OK, 1 row affected  (... sec)

After you reindexed all tables, this cluster check will pass.

Note

Snapshots of your tables created prior to them being upgraded will not work with future versions of CrateDB. For this reason, you should create a new snapshot for each of your tables. (See Snapshots.)

License check

Note

This check was removed in version 4.5 because CrateDB no longer requires an enterprise license

This check warns you when your license is close to expiration, is already expired, or if the cluster contains more nodes than allowed by your license. It will yield a MEDIUM alert when your license is valid for less than 15 days and a HIGH alert when your license is valid for less than a day. All other cases, like already expired or max-nodes-violation, it will result in a HIGH alert. We recommend that you request a new license when this check triggers, in order to avoid the situation where operations are rejected due to an invalid license.

Health

The sys.health table lists the health of each table and table partition. The health is computed by checking the states of the shard of each table/partition.

Both missing_shards and underreplicated_shards might return -1 if the cluster is in an unhealthy state that prevents the exact number from being calculated. This could be the case when the cluster can’t elect a master, because there are not enough eligible nodes available.

cr> select * from sys.health order by severity desc, table_name;
+--------+----------------+-----------------+----------+------------+--------------+------------------------+
| health | missing_shards | partition_ident | severity | table_name | table_schema | underreplicated_shards |
+--------+----------------+-----------------+----------+------------+--------------+------------------------+
| GREEN  |              0 |            NULL |        1 | locations  | doc          |                      0 |
| GREEN  |              0 |            NULL |        1 | quotes     | doc          |                      0 |
+--------+----------------+-----------------+----------+------------+--------------+------------------------+
SELECT 2 rows in set (... sec)

The health with the highest severity will always define the health of the query scope.

Example of getting a cluster health (health of all tables):

cr> select health from sys.health order by severity desc limit 1;
+--------+
| health |
+--------+
| GREEN  |
+--------+
SELECT 1 row in set (... sec)

Health definition

Note

The sys.health table is subject to Shard table permissions as it will expose a summary of table shard states.

Repositories

The table sys.repositories lists all configured repositories that can be used to create, manage and restore snapshots (see Snapshots).

cr> SELECT name, type, settings FROM sys.repositories
... ORDER BY name;
+---------+------+---------------------------------------------------...--+
| name    | type | settings                                               |
+---------+------+---------------------------------------------------...--+
| my_repo | fs   | {"compress": "true", "location": "repo_location", ...} |
+---------+------+---------------------------------------------------...--+
SELECT 1 row in set (... sec)

Note

Sensitive user account information will be masked and thus not visible to the user.

Snapshots

The table sys.snapshots lists all existing snapshots in all configured repositories (see Snapshots).

Snapshot/Restore operates on a per-shard basis. Hence, the state column indicates whether all (SUCCESS), some (PARTIAL), or no shards(FAILED) have been backed up. PARTIAL snapshots are the result of some primaries becoming unavailable while taking the snapshot when there are no replicas at hand (cluster state is RED). If there are replicas of the (now unavailable) primaries (cluster state is YELLOW) the snapshot succeeds and all shards are included (state SUCCESS). Building on a PARTIAL snapshot will include all primaries again.

Warning

In case of a PARTIAL state another snapshot should be created in order to guarantee a full backup! Only SUCCESS includes all shards.

The concrete_indices column contains the names of all Elasticsearch indices that were stored in the snapshot. A normal CrateDB table maps to one Elasticsearch index, a partitioned table maps to one Elasticsearch index per partition. The mapping follows the following pattern:

cr> SELECT "repository", name, state, concrete_indices
... FROM sys.snapshots order by "repository", name;
+------------+-------------+---------+-----------------...-+
| repository | name        | state   | concrete_indices    |
+------------+-------------+---------+-----------------...-+
| my_repo    | my_snapshot | SUCCESS | [...]               |
+------------+-------------+---------+-----------------...-+
SELECT 1 row in set (... sec)

Snapshot Restore

The sys.snapshot_restore table contains information about the current state of snapshot restore operations.

To get more information about the restoring snapshots and shards one can join the sys.snapshot_restore with sys.shards or sys.snapshots table.

Summits

The sys.summits table contains the information about the mountains in the Alps higher than 2000m. The mountain names from the table are also used to generate random nodes names.

Users

The sys.users table contains all existing database users in the cluster.

Allocations

The sys.allocations table contains information about shards and their allocation state. The table contains:

• shards that are unassigned and why they are unassigned

• shards that are assigned but cannot be moved or rebalanced and why they remain on their current node

It can help to identify problems if shard allocations behave different than expected, e.g. when a shard stays unassigned or a shard does not move off a node.

Note

The sys.allocations table is subject to Shard table permissions.

Shard table permissions

Accessing tables that return shards (sys.shards, sys.allocations) is subjected to the same privileges constraints as the other tables. Namely, in order to query them, the connected user needs to have the DQL privilege on that particular table, either directly or inherited from the SCHEMA or CLUSTER (for more information on privileges inheritance see Hierarchical Inheritance of Privileges).

However, being able to query shard returning system tables will not allow the user to retrieve all the rows in the table, as they may contain information related to tables, which the connected user does not have any privileges for. The only rows that will be returned will be the ones the user is allowed to access.

For example, if the user john has any privilege on the doc.books table but no privilege at all on doc.locations, when john issues a SELECT * FROM sys.shards statement, the shards information related to the doc.locations table will not be returned.

Sys jobs tables permissions

Accessing sys.jobs and sys.jobs_log tables is subjected to the same privileges constraints as other tables. To query them, the current user needs to have the DQL privilege on that particular table, either directly or inherited from the SCHEMA or CLUSTER.

A user that doesn’t have superuser privileges is allowed to retrieve only their own job logs entries, while a user with superuser privileges has access to all.

pg_stats

The pg_stats table in the pg_catalog system schema contains statistical data about the contents of the CrateDB cluster.

Entries are periodically created or updated in the interval configured with the stats.service.interval setting.

Alternatively the statistics can also be updated using the ANALYZE command.

The table contains 1 entry per column for each table in the cluster which has been analyzed.

Note

Not all data types support creating statistics. So some columns may not show up in the table.