System Information

Crate 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:


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

Name Description Return Type
id A unique id generated by the system. String
name The cluster name. String
master_node Node id of the node which currently operates as master String
settings The cluster settings. Object

The result has at most 1 row:

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

Cluster Settings

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

cr> select settings from sys.cluster;
| settings                                                                                                                |
| {"bulk": {...}, "cluster": {...}, "routing": {...}, "discovery": {...}, "indices": {...}, "logger": [], "stats": {...}} |
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']                                               | string       |
| settings['cluster']                                                               | object       |
| settings['cluster']['graceful_stop']                                              | object       |
| settings['cluster']['graceful_stop']['force']                                     | boolean      |
| settings['cluster']['graceful_stop']['min_availability']                          | string       |
| settings['cluster']['graceful_stop']['reallocate']                                | boolean      |
| settings['cluster']['graceful_stop']['timeout']                                   | string       |
| settings['cluster']['info']                                                       | object       |
| settings['cluster']['info']['update']                                             | object       |
| settings['cluster']['info']['update']['interval']                                 | string       |
| settings['cluster']['routing']                                                    | object       |
| settings['cluster']['routing']['allocation']                                      | object       |
| settings['cluster']['routing']['allocation']['allow_rebalance']                   | string       |
| settings['cluster']['routing']['allocation']['balance']                           | object       |
| settings['cluster']['routing']['allocation']['balance']['index']                  | float        |
| settings['cluster']['routing']['allocation']['balance']['primary']                | float        |
| settings['cluster']['routing']['allocation']['balance']['shard']                  | float        |
| settings['cluster']['routing']['allocation']['balance']['threshold']              | float        |
| 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']['high']         | string       |
| settings['cluster']['routing']['allocation']['disk']['watermark']['low']          | string       |
| settings['cluster']['routing']['allocation']['enable']                            | string       |
| settings['cluster']['routing']['allocation']['exclude']                           | object       |
| settings['cluster']['routing']['allocation']['exclude']['_host']                  | string       |
| settings['cluster']['routing']['allocation']['exclude']['_id']                    | string       |
| settings['cluster']['routing']['allocation']['exclude']['_ip']                    | string       |
| settings['cluster']['routing']['allocation']['exclude']['_name']                  | string       |
| settings['cluster']['routing']['allocation']['include']                           | object       |
| settings['cluster']['routing']['allocation']['include']['_host']                  | string       |
| settings['cluster']['routing']['allocation']['include']['_id']                    | string       |
| settings['cluster']['routing']['allocation']['include']['_ip']                    | string       |
| settings['cluster']['routing']['allocation']['include']['_name']                  | string       |
| 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']                  | string       |
| settings['cluster']['routing']['allocation']['require']['_id']                    | string       |
| settings['cluster']['routing']['allocation']['require']['_ip']                    | string       |
| settings['cluster']['routing']['allocation']['require']['_name']                  | string       |
| settings['discovery']                                                             | object       |
| settings['discovery']['zen']                                                      | object       |
| settings['discovery']['zen']['minimum_master_nodes']                              | integer      |
| settings['discovery']['zen']['ping_timeout']                                      | string       |
| settings['discovery']['zen']['publish_timeout']                                   | string       |
| settings['gateway']                                                               | object       |
| settings['gateway']['expected_nodes']                                             | integer      |
| settings['gateway']['recover_after_nodes']                                        | integer      |
| settings['gateway']['recover_after_time']                                         | string       |
| settings['indices']                                                               | object       |
| settings['indices']['breaker']                                                    | object       |
| settings['indices']['breaker']['query']                                           | object       |
| settings['indices']['breaker']['query']['limit']                                  | string       |
| settings['indices']['breaker']['query']['overhead']                               | double       |
| settings['indices']['breaker']['request']                                         | object       |
| settings['indices']['breaker']['request']['limit']                                | string       |
| settings['indices']['breaker']['request']['overhead']                             | double       |
| settings['indices']['fielddata']                                                  | object       |
| settings['indices']['fielddata']['breaker']                                       | object       |
| settings['indices']['fielddata']['breaker']['limit']                              | string       |
| settings['indices']['fielddata']['breaker']['overhead']                           | double       |
| settings['indices']['recovery']                                                   | object       |
| settings['indices']['recovery']['activity_timeout']                               | string       |
| settings['indices']['recovery']['compress']                                       | boolean      |
| settings['indices']['recovery']['concurrent_streams']                             | integer      |
| settings['indices']['recovery']['file_chunk_size']                                | string       |
| settings['indices']['recovery']['internal_action_long_timeout']                   | string       |
| settings['indices']['recovery']['internal_action_timeout']                        | string       |
| settings['indices']['recovery']['max_bytes_per_sec']                              | string       |
| settings['indices']['recovery']['retry_delay_network']                            | string       |
| settings['indices']['recovery']['retry_delay_state_sync']                         | string       |
| settings['indices']['recovery']['translog_ops']                                   | integer      |
| settings['indices']['recovery']['translog_size']                                  | string       |
| settings['indices']['store']                                                      | object       |
| settings['indices']['store']['throttle']                                          | object       |
| settings['indices']['store']['throttle']['max_bytes_per_sec']                     | string       |
| settings['indices']['store']['throttle']['type']                                  | string       |
| settings['logger']                                                                | object_array |
| settings['logger']['level']                                                       | string       |
| settings['logger']['name']                                                        | string       |
| settings['stats']                                                                 | object       |
| settings['stats']['enabled']                                                      | boolean      |
| settings['stats']['jobs_log_size']                                                | integer      |
| settings['stats']['operations_log_size']                                          | integer      |
| settings['udc']                                                                   | object       |
| settings['udc']['enabled']                                                        | boolean      |
| settings['udc']['initial_delay']                                                  | string       |
| settings['udc']['interval']                                                       | string       |
| settings['udc']['url']                                                            | string       |
SELECT 93 rows in set (... sec)

For further details, see the Cluster Settings configuration section.


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:


Column Name Description Return Type
id A unique id within the cluster generated by the system. String


Column Name Description Return Type
name The node name within a cluster. The system will choose a random name. You can specify the node name via your own custom configuration. String


Column Name Description Return Type
hostname The specified host name of the machine the node is running on. String


Column Name Description Return Type
rest_url Full http(s) address where the REST API of the node is exposed, including schema, hostname (or IP) and port. String


Column Name Description Return Type
port The specified ports for both HTTP and binary transport interfaces. You can specify the ports via your own custom configuration. Object
port['http'] Crate’s HTTP port. Integer
port['transport'] Crate’s binary transport port. Integer


Column Name Description Return Type
load System load statistics Object
load['1'] Average load over the last 1 minute. Double
load['5'] Average load over the last 5 minutes. Double
load['15'] Average load over the last 15 minutes. Double
load['probe_timestamp'] Unix timestamp at the time of collection of the load probe. Long


Column Name Description Return Type
mem Memory utilization statistics of the host. Object
mem['used'] Currently used memory in bytes. Long
mem['used_percent'] Currently used memory in percent of total. Short
mem['free'] Currently available memory in bytes. Long
mem['free_percent'] Currently available memory in percent of total. Short
mem['probe_timestamp'] Unix timestamp at the time of collection of the memory probe. Long


Column Name Description Return Type
heap Heap memory utilization statistics. Object
heap['used'] Currently used heap memory in bytes. Long
heap['max'] Maximum available heap memory. You can specify the max heap memory Crate should use in the configuration. Long
heap['free'] Currently available heap memory in bytes. Long
heap['probe_timestamp'] Unix timestamp at the time of collection of the heap probe. Long


Column Name Description Return Type
version Crate version information. Object
version['number'] Version string in format "major.minor.hotfix" String
version['build_hash'] SHA hash of the Github commit which this build was built from. String
version['build_snapshot'] Indicates whether this build is a snapshot build. Boolean


Column Name Description Return Type
fs Utilization statistics about the file system. Object
fs['total'] Aggregated usage statistic of all disks on the host. Object
fs['total']['size'] Total size of all disks in bytes. Long
fs['total']['used'] Total used space of all disks in bytes. Long
fs['total']['available'] Total available space of all disks in bytes. Long
fs['total']['reads'] Total number of reads on all disks. Long
fs['total']['bytes_read'] Total size of reads on all disks in bytes. Long
fs['total']['writes'] Total number of writes on all disks. Long
fs['total']['bytes_written'] Total size of writes on all disks in bytes. Long
fs['disks'] Usage statistics of individual disks on the host. Array
fs['disks']['dev'] Device name String
fs['disks']['size'] Total size of the disk in bytes. Long
fs['disks']['used'] Used space of the disk in bytes. Long
fs['disks']['available'] Available space of the disk in bytes. Long
fs['disks']['reads'] Number of reads on the disk. Long
fs['disks']['bytes_read'] Total size of reads on the disk in bytes. Long
fs['disks']['writes'] Number of writes on the disk. Long
fs['disks']['bytes_written'] Total size of writes on the disk in bytes. Long
fs['data'] Information about data paths used by the node. Array
fs['data']['dev'] Device name String
fs['data']['path'] File path where the data of the node resides. String


Column Name Description Return Type
thread_pools Usage statistics of Java thread pools. Array
thread_pools['name'] Name of the pool. String
thread_pools['active'] Number of currently running thread in the thread pool. Integer
thread_pools['rejected'] Total number of rejected threads in the thread pool. Long
thread_pools['largest'] Largest number of threads that have ever simultaniously been in the pool. Integer
thread_pools['completed'] Total number of completed thread in teh thread pool. Long
thread_pools['threads'] Size of the thread pool. Integer
thread_pools['queue'] Number of thread currently in the queue. Integer


Column Name Description Return Type
os Operating system stats Object
os['uptime'] System uptime in milliseconds Long
os['timestamp'] UNIX timestamp in millisecond resolution Long
os['cpu'] Information about CPU utilization Object
os['cpu']['system'] CPU time used by the system Short
os['cpu']['user'] CPU time used by applications Short
os['cpu']['idle'] Idle CPU time Short
os['cpu']['used'] Used CPU (system + user) Short
os['cpu']['stolen'] The amount of CPU ‘stolen’ from this virtual machine by the hypervisor for other tasks. Short
os['probe_timestamp'] Unix timestamp at the time of collection of the OS probe. Long

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.


Column Name Description Return Type
os_info Operating system information Object
os_info['available_processors'] Number of processors that are available in the JVM. This is usually equal to the number of cores of the CPU. Integer
os_info['name'] Name of the operating system (ex: Linux, Windows, Mac OS X) String
os_info['arch'] Name of the JVM architecture (ex: amd64, x86) String
os_info['version'] Version of the operating system String
os_info['jvm'] Information about the JVM (Java Virtual Machine) Object
os_info['jvm']['version'] The JVM version String
os_info['jvm']['vm_name'] The name of the JVM (eg. OpenJDK, Java Hotspot(TM) ) String
os_info['jvm']['vm_vendor'] The vendor name of the JVM String
os_info['jvm']['vm_version'] The version of the JVM String


Column Name Description Return Type
network Statistics about network activity on the host. Object
network['probe_timestamp'] Unix timestamp at the time of collection of the network probe. Long
network['tcp'] TCP network activity on the host. Object
network['tcp']['connections'] Information about TCP network connections. Object
network['tpc']['connections']['initiated'] Total number of initiated TCP connections. Long
network['tpc']['connections']['accepted'] Total number of accepted TCP connections. Long
network['tpc']['connections']['curr_established'] Total number of currently established TCP connections. Long
network['tcp']['connections']['dropped'] Total number of dropped TCP connections. Long
network['tcp']['connections']['embryonic_dropped'] Total number of TCP connections that have been dropped before they were accepted. Long
network['tcp']['packets'] Information about TCP packets. Object
network['tpc']['packets']['sent'] Total number of TCP packets sent. Long
network['tcp']['packets']['received'] Total number of TCP packets received. Long
network['tpc']['packets']['retransmitted'] Total number of TCP packets retransmitted due to an error. Long
network['tcp']['packets']['errors_received'] Total number of TCP packets that contained checksum errors, had a bad offset, were dropped because of a lack of memory or were too short. Long
network['tcp']]['packets']['rst_sent'] Total number of RST packets sent due to left unread data in queue when socket is closed. See Long


Column Name Description Return Type
process Statistics about the Crate process. Object
process['open_file_descriptors'] Number of currently open file descriptors used by the Crate process. Long
process['max_open_file_descriptors'] The maximum number of open file descriptors Crate can use. Long
process['probe_timestamp'] The system UNIX timestamp at the moment of the probe collection. Long
process['cpu'] Information about the CPU usage of the Crate process. Object
process['cpu']['percent'] The CPU usage of the Crate JVM process given in percent. Short
process['cpu']['user'] The process CPU user time in milliseconds. Long
process['cpu']['system'] The process CPU kernel time in milliseconds. Long

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.


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

The table schema is as follows:


Column Name Description Return Type
id The shard id. This shard id is managed by the system ranging from 0 and up to the specified number of shards of a table (by default the number of shards is 5). Integer


Column Name Description Return Type
schema_name The schema name. This will be “blob” for shards of blob tables and “doc” for shards of common tables. String


Column Name Description Return Type
table_name The table name. String


Column Name Description Return Type
partition_ident The partition ident of a partitioned table. Empty string on non-partitioned tables. String


Column Name Description Return Type
num_docs The total amount of docs within a shard. Long


Column Name Description Return Type
primary Describes if the shard is the primary shard. Boolean


Column Name Description Return Type
relocating_node The node id which the shard is getting relocated to at the time String


Column Name Description Return Type
size Current size in bytes. This value is cached for max. 10 seconds to reduce file system access. Long


Column Name Description Return Type
state The current state of the shard. Possible states are: CREATED, RECOVERING, POST_RECOVERY, STARTED, RELOCATED, CLOSED, INITIALIZING, UNASSIGNED String


Column Name Description Return Type
routing_state The current state of a shard as defined by the routing. Possible states of the shard routing are: UNASSIGNED, INITIALIZING, STARTED, RELOCATING String


Column Name Description Return Type
orphan_partition True if the partition has NO table associated with. In rare situations the table is missing. False on non-partitioned tables. Boolean


Column Name Description Return Type
_node Information about the node the shard is located at at. Contains the same information as the sys.nodes table. Object


Recovery is the process of moving a table shard to a different node or loading it from disk, e.g. during node startup (local gateway recovery), replication, shard rebalancing or snapshot recovery.

Column Name Description Return Type
recovery Represents recovery statistic of the particular shard. Object

Recovery stage:

  • init: Recovery has not started
  • index: Reading the Lucene index meta-data and copying bytes from source to destination
  • start: Starting the engine, opening the index for use
  • translog: Replaying transaction log
  • finalize: Cleanup
  • done: Complete

Recovery type:

  • gateway
  • snapshot
  • replica
  • relocating
recovery['size'] Shards recovery statistic in bytes. Object
recovery['size']['used'] Total number of bytes in the shard. Long
recovery['size']['reused'] Number of bytes reused from a local copy while recovering the shard. Long
recovery['size']['recovered'] Number of actual bytes recovered in the shard. Includes both existing and reused bytes. Long
recovery['size']['percent'] Percentage of bytes already recovered. Float
recovery['files'] Shards recovery statistic in files. Object
recovery['files']['used'] Total number of files in the shard. Integer
recovery['files']['reused'] Total number of files reused from a local copy while recovering the shard. Integer
recovery['files']['recovered'] Number of actual files recovered in the shard. Includes both existing and reused files. Integer
recovery['files']['percent'] Percentage of files already recovered. Float
recovery['total_time'] Returns elapsed time from the start of the shard recovery. Long

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)

Jobs, Operations and Logs

To see current activities in the cluster, crate provides the and sys.operations tables and their corresponding “logs”, sys.jobs_log and sys.operations_log.

These tables are by default always empty. Jobs and operations aren’t tracked unless stats collection is activated as tracking statistics adds a slight performance overhead.

To activate stats tracking use SET / RESET:

cr> set global stats.enabled = true;
SET OK (... sec)


The table provides an overview over all jobs that are currently being executed in the cluster:

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

Each request sent to crate that queries data or manipulates data is considered a job if it passes the analysis step.

For example a request that fails because it attempts to query a table that doesn’t exist won’t get listed.


The sys.operations table lists all operations that are currently being executed in the cluster.

A operation is part of a job but not all jobs list their operations. A operation is listed on each node it’s being executed on.

To see on which nodes the operations are being executed, use the _node system column in the query:

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)


Both and sys.operations have a corresponding _log table.

After a job or operation finishes the entries will be moved into the _log table. The _log tables are bound in size and once a table has reached its limit old entries are discarded as new entries are added:

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

See SET / RESET on how to change the size of the tables.

As described above the stats tracking is deactivated by default and has to be enabled using the SET statement. The same statement can be used to deactivate the stats tracking. This will wipe all existing log entries:

cr> set global stats.enabled = false;
SET OK (... sec)
cr> select count(*) from sys.jobs_log;
| count(*) |
|        0 |
SELECT 1 row in set (... sec)


Instead of SET, the RESET statement could have been used to reset the value of stats.enabled to its default which is false.

Cluster Checks

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

The table schema is the following:

Name Description Return Type
id The unique check id. Integer
severity The level of severity. The higher the value of the field the higher severity.  
description The description message for the setting check. String
passed The flag determines whether the check for the setting has passed. Boolean

Currently Crate checks the following:

id Check name
1 Minimum master nodes.
2 Recovery expected nodes.
3 Recovery after nodes.
4 Recovery after time.
5 Number of partitions.
6 Java 7 deprecation warning.

Settings checks:

All the settings must be present in the cluster configuration

Minimum master nodes

The check for the discovery.zen.minimum_master_nodes setting verifies that the minimum number of nodes is equal/greater than the half of maximum number of nodes in the cluster.

(N / 2) + 1 <= M

where N is the number of nodes in the cluster, and M is the value of the setting discovery.zen.minimum_master_nodes.

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, 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/less than number of nodes in the cluster.

(E / 2) < R <= E

where R is the number of recovery nodes, E is the number of expected nodes.

Recovery after time

The check for the gateway.recover_after_time settings verifies that the time before recovery starts must not be less than default value of 5m.

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.

For instance, if the gateway.expected_nodes and discovery.zen.minimum_master_nodes settings were misconfigured, then the SQL query below will have the following results

cr> select id, severity, description, passed
... from sys.checks where id=5;
| id | severity | description                                                                                                                                                                               | passed |
|  5 |        2 | The total number of partitions of one or more partitioned tables should not be greater than 1000. A large amount of shards can significantly reduce performance. | TRUE   |
SELECT 1 row in set (... sec)

Java 7 deprecation warning

Starting with Crate 0.55.0 using Java 7 runtime environment is deprecated. Please update to Java 8 (>= update 20). See also here .


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

Name Description Return Type
name The repository name String
type The type of the repository determining how and where the repository stores its snapshots. String
settings The configuration settings the repository has been created with. The specific settings depend on the repository type, see CREATE REPOSITORY. Object
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)


The table sys.snapshots lists all existing snapshots in all configured repositories (see Backup with Snapshot and Restore).

Name Description Return Type
name The name of the snapshot String
repository The name of the repository that contains this snapshot. String
concrete_indices Contains the names of all tables and partitions that are contained in this snapshot how they are represented as ES index names. Array
started The point in time when the creation of the snapshot started. Changes made after that are not stored in this snapshot. Timestamp
finished The point in time when the snapshot creation finished. Timestamp
state The current state of the snapshot. One of: IN_PROGRESS, SUCCESS, PARTIAL, or FAILED. String
version The Crate version this snapshot was created with. String

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.


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 crate table maps to one Elasticsearch index, a partitioned table maps to one Elasticsearch index per partition. The mapping follows the following pattern:

CRATE table / partition name concrete_indices entry
doc.my_table my_table
my_schema.my_table my_schema.my_table
doc.parted_table (value=null) .partitioned.my_table.0400
my_schema.parted_table (value=null) my_schema..partitioned.my_table.0400
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)