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:

Name Description Return Type
--- --- ---
id A unique ID generated by the system. TEXT
license The current CrateDB license information. Always NULL. This exists for backward compatibility OBJECT
name The cluster name. TEXT
master_node Node ID of the node which currently operates as master TEXT
settings The cluster settings. OBJECT

The result has at most 1 row:

1. cr> select name from sys.cluster;
2. +-----------------+
3. | name            |
4. +-----------------+
5. | Testing-CrateDB |
6. +-----------------+
7. 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

Column Name Description Return Type
--- --- ---
license The current CrateDB license information or NULL on CrateDB CE. OBJECT
license[‘expiry_date’] The Date and time types on which the license expires. TIMESTAMP WITH TIME ZONE
license[‘issued_to’] The organisation for which the license is issued. TEXT
license[‘max_nodes’] The maximum number of nodes the license is valid for. INTEGER

Cluster settings

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

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

1. cr> select column_name, data_type from information_schema.columns
2. ... where column_name like 'settings%'
3. ... and table_name = 'cluster';
4. +-----------------------------------------------------------------------------------+------------------+
5. | column_name                                                                       | data_type        |
6. +-----------------------------------------------------------------------------------+------------------+
7. | settings                                                                          | object           |
8. | settings['bulk']                                                                  | object           |
9. | settings['bulk']['request_timeout']                                               | text             |
10. | settings['cluster']                                                               | object           |
11. | settings['cluster']['graceful_stop']                                              | object           |
12. | settings['cluster']['graceful_stop']['force']                                     | boolean          |
13. | settings['cluster']['graceful_stop']['min_availability']                          | text             |
14. | settings['cluster']['graceful_stop']['timeout']                                   | text             |
15. | settings['cluster']['info']                                                       | object           |
16. | settings['cluster']['info']['update']                                             | object           |
17. | settings['cluster']['info']['update']['interval']                                 | text             |
18. | settings['cluster']['max_shards_per_node']                                        | integer          |
19. | settings['cluster']['routing']                                                    | object           |
20. | settings['cluster']['routing']['allocation']                                      | object           |
21. | settings['cluster']['routing']['allocation']['allow_rebalance']                   | text             |
22. | settings['cluster']['routing']['allocation']['balance']                           | object           |
23. | settings['cluster']['routing']['allocation']['balance']['index']                  | real             |
24. | settings['cluster']['routing']['allocation']['balance']['shard']                  | real             |
25. | settings['cluster']['routing']['allocation']['balance']['threshold']              | real             |
26. | settings['cluster']['routing']['allocation']['cluster_concurrent_rebalance']      | integer          |
27. | settings['cluster']['routing']['allocation']['disk']                              | object           |
28. | settings['cluster']['routing']['allocation']['disk']['threshold_enabled']         | boolean          |
29. | settings['cluster']['routing']['allocation']['disk']['watermark']                 | object           |
30. | settings['cluster']['routing']['allocation']['disk']['watermark']['flood_stage']  | text             |
31. | settings['cluster']['routing']['allocation']['disk']['watermark']['high']         | text             |
32. | settings['cluster']['routing']['allocation']['disk']['watermark']['low']          | text             |
33. | settings['cluster']['routing']['allocation']['enable']                            | text             |
34. | settings['cluster']['routing']['allocation']['exclude']                           | object           |
35. | settings['cluster']['routing']['allocation']['exclude']['_host']                  | text             |
36. | settings['cluster']['routing']['allocation']['exclude']['_id']                    | text             |
37. | settings['cluster']['routing']['allocation']['exclude']['_ip']                    | text             |
38. | settings['cluster']['routing']['allocation']['exclude']['_name']                  | text             |
39. | settings['cluster']['routing']['allocation']['include']                           | object           |
40. | settings['cluster']['routing']['allocation']['include']['_host']                  | text             |
41. | settings['cluster']['routing']['allocation']['include']['_id']                    | text             |
42. | settings['cluster']['routing']['allocation']['include']['_ip']                    | text             |
43. | settings['cluster']['routing']['allocation']['include']['_name']                  | text             |
44. | settings['cluster']['routing']['allocation']['node_concurrent_recoveries']        | integer          |
45. | settings['cluster']['routing']['allocation']['node_initial_primaries_recoveries'] | integer          |
46. | settings['cluster']['routing']['allocation']['require']                           | object           |
47. | settings['cluster']['routing']['allocation']['require']['_host']                  | text             |
48. | settings['cluster']['routing']['allocation']['require']['_id']                    | text             |
49. | settings['cluster']['routing']['allocation']['require']['_ip']                    | text             |
50. | settings['cluster']['routing']['allocation']['require']['_name']                  | text             |
51. | settings['cluster']['routing']['allocation']['total_shards_per_node']             | integer          |
52. | settings['cluster']['routing']['rebalance']                                       | object           |
53. | settings['cluster']['routing']['rebalance']['enable']                             | text             |
54. | settings['discovery']                                                             | object           |
55. | settings['discovery']['zen']                                                      | object           |
56. | settings['discovery']['zen']['publish_timeout']                                   | text             |
57. | settings['gateway']                                                               | object           |
58. | settings['gateway']['expected_nodes']                                             | integer          |
59. | settings['gateway']['recover_after_nodes']                                        | integer          |
60. | settings['gateway']['recover_after_time']                                         | text             |
61. | settings['indices']                                                               | object           |
62. | settings['indices']['breaker']                                                    | object           |
63. | settings['indices']['breaker']['fielddata']                                       | object           |
64. | settings['indices']['breaker']['fielddata']['limit']                              | text             |
65. | settings['indices']['breaker']['fielddata']['overhead']                           | double precision |
66. | settings['indices']['breaker']['query']                                           | object           |
67. | settings['indices']['breaker']['query']['limit']                                  | text             |
68. | settings['indices']['breaker']['query']['overhead']                               | double precision |
69. | settings['indices']['breaker']['request']                                         | object           |
70. | settings['indices']['breaker']['request']['limit']                                | text             |
71. | settings['indices']['breaker']['request']['overhead']                             | double precision |
72. | settings['indices']['breaker']['total']                                           | object           |
73. | settings['indices']['breaker']['total']['limit']                                  | text             |
74. | settings['indices']['recovery']                                                   | object           |
75. | settings['indices']['recovery']['internal_action_long_timeout']                   | text             |
76. | settings['indices']['recovery']['internal_action_timeout']                        | text             |
77. | settings['indices']['recovery']['max_bytes_per_sec']                              | text             |
78. | settings['indices']['recovery']['recovery_activity_timeout']                      | text             |
79. | settings['indices']['recovery']['retry_delay_network']                            | text             |
80. | settings['indices']['recovery']['retry_delay_state_sync']                         | text             |
81. | settings['logger']                                                                | object_array     |
82. | settings['logger']['level']                                                       | text_array       |
83. | settings['logger']['name']                                                        | text_array       |
84. | settings['memory']                                                                | object           |
85. | settings['memory']['allocation']                                                  | object           |
86. | settings['memory']['allocation']['type']                                          | text             |
87. | settings['stats']                                                                 | object           |
88. | settings['stats']['breaker']                                                      | object           |
89. | settings['stats']['breaker']['log']                                               | object           |
90. | settings['stats']['breaker']['log']['jobs']                                       | object           |
91. | settings['stats']['breaker']['log']['jobs']['limit']                              | text             |
92. | settings['stats']['breaker']['log']['jobs']['overhead']                           | double precision |
93. | settings['stats']['breaker']['log']['operations']                                 | object           |
94. | settings['stats']['breaker']['log']['operations']['limit']                        | text             |
95. | settings['stats']['breaker']['log']['operations']['overhead']                     | double precision |
96. | settings['stats']['enabled']                                                      | boolean          |
97. | settings['stats']['jobs_log_expiration']                                          | text             |
98. | settings['stats']['jobs_log_filter']                                              | text             |
99. | settings['stats']['jobs_log_persistent_filter']                                   | text             |
100. | settings['stats']['jobs_log_size']                                                | integer          |
101. | settings['stats']['operations_log_expiration']                                    | text             |
102. | settings['stats']['operations_log_size']                                          | integer          |
103. | settings['stats']['service']                                                      | object           |
104. | settings['stats']['service']['interval']                                          | text             |
105. | settings['udc']                                                                   | object           |
106. | settings['udc']['enabled']                                                        | boolean          |
107. | settings['udc']['initial_delay']                                                  | text             |
108. | settings['udc']['interval']                                                       | text             |
109. | settings['udc']['url']                                                            | text             |
110. +-----------------------------------------------------------------------------------+------------------+
111. 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

Column Name Description Return Type
--- --- ---
id A unique ID within the cluster generated by the system. TEXT

name

Column Name Description Return Type
--- --- ---
name The node name within a cluster. The system will choose a random name. You can also customize the node name, see Node-specific settings. TEXT

hostname

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

rest_url

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. TEXT

port

Column Name Description Return Type
--- --- ---
port The specified ports for both HTTP and binary transport interfaces. You can also customize the ports setting, see Ports. OBJECT
port[‘http’] CrateDB’s HTTP port. INTEGER
port[‘transport’] CrateDB’s binary transport port. INTEGER
port[‘psql’] The PostgreSQL wire protocol port. INTEGER

load

Column Name Description Return Type
--- --- ---
load System load statistics OBJECT
load[‘1’] Average load over the last 1 minute. DOUBLE PRECISION
load[‘5’] Average load over the last 5 minutes. DOUBLE PRECISION
load[‘15’] Average load over the last 15 minutes. DOUBLE PRECISION
load[‘probe_timestamp’] Unix timestamp at the time of collection of the load probe. BIGINT

mem

Column Name Description Return Type
--- --- ---
mem Memory utilization statistics of the host. OBJECT
mem[‘used’] Currently used memory in bytes. BIGINT
mem[‘used_percent’] Currently used memory in percent of total. SMALLINT
mem[‘free’] Currently available memory in bytes. BIGINT
mem[‘free_percent’] Currently available memory in percent of total. SMALLINT
mem[‘probe_timestamp’] Unix timestamp at the time of collection of the memory probe. BIGINT

heap

Column Name Description Return Type
--- --- ---
heap Heap memory utilization statistics. OBJECT
heap[‘used’] Currently used heap memory in bytes. BIGINT
heap[‘max’] Maximum available heap memory. You can specify the max heap memory CrateDB should use in the Configuration. BIGINT
heap[‘free’] Currently available heap memory in bytes. BIGINT
heap[‘probe_timestamp’] Unix timestamp at the time of collection of the heap probe. BIGINT

version

Column Name Description Return Type
--- --- ---
version CrateDB version information. OBJECT
version[‘number’] Version string in format “major.minor.hotfix” TEXT
version[‘build_hash’] SHA hash of the Github commit which this build was built from. TEXT
version[‘build_snapshot’] Indicates whether this build is a snapshot build. BOOLEAN
version[‘minimum_index_compatibility_version’] Indicates the minimum compatible index version which is supported. TEXT
version[‘minimum_wire_compatibility_version’] Indicates the minimum compatible wire protocol version which is supported. TEXT

cluster_state_version

Column Name Description Return Type
--- --- ---
cluster_state_version The current version of the cluster state. The cluster state is an immutable structure and that is recreated when a change is published. BIGINT

fs

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. BIGINT
fs[‘total’][‘used’] Total used space of all disks in bytes. BIGINT
fs[‘total’][‘available’] Total available space of all disks in bytes. BIGINT
fs[‘total’][‘reads’] Total number of reads on all disks. BIGINT
fs[‘total’][‘bytes_read’] Total size of reads on all disks in bytes. BIGINT
fs[‘total’][‘writes’] Total number of writes on all disks. BIGINT
fs[‘total’][‘bytes_written’] Total size of writes on all disks in bytes. BIGINT
fs[‘disks’] Usage statistics of individual disks on the host. ARRAY
fs[‘disks’][‘dev’] Device name TEXT
fs[‘disks’][‘size’] Total size of the disk in bytes. BIGINT
fs[‘disks’][‘used’] Used space of the disk in bytes. BIGINT
fs[‘disks’][‘available’] Available space of the disk in bytes. BIGINT
fs[‘data’] Information about data paths used by the node. ARRAY
fs[‘data’][‘dev’] Device name TEXT
fs[‘data’][‘path’] File path where the data of the node resides. TEXT

thread_pools

Column Name Description Return Type
--- --- ---
thread_pools Usage statistics of Java thread pools. ARRAY
thread_pools[‘name’] Name of the pool. TEXT
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. BIGINT
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. BIGINT
thread_pools[‘threads’] Size of the thread pool. INTEGER
thread_pools[‘queue’] Number of thread currently in the queue. INTEGER

os

Column Name Description Return Type
--- --- ---
os Operating system stats OBJECT
os[‘uptime’] System uptime in milliseconds Requires allowing system calls on Windows and macOS. See notes in Uptime limitations. BIGINT
os[‘timestamp’] UNIX timestamp in millisecond resolution BIGINT
os[‘cpu’] Information about CPU utilization OBJECT
os[‘cpu’][‘used’] System CPU usage as percentage SMALLINT
os[‘probe_timestamp’] Unix timestamp at the time of collection of the OS probe. BIGINT
os[‘cgroup’] Information about Cgroups (Linux only) OBJECT
os[‘cgroup’][‘cpuacct’] Information about CPU accounting OBJECT
os[‘cgroup’][‘cpuacct’][‘control_group’] The path to the cpu accounting cgroup TEXT
os[‘cgroup’][‘cpuacct’][‘usage_nanos’] The total CPU time (in nanoseconds) consumed by all tasks in this cgroup. BIGINT
os[‘cgroup’][‘cpu’] Information about the CPU subsystem OBJECT
os[‘cgroup’][‘cpu’][‘control_group’] The path to the cpu cgroup TEXT
os[‘cgroup’][‘cpu’][‘cfs_period_micros’] The period of time (in microseconds) the cgroup access to the CPU gets reallocated. BIGINT
os[‘cgroup’][‘cpu’][‘cfs_quota_micros’] The total amount of time (in microseconds) for which all tasks in the cgroup can run during one period (cfs_period_micros). BIGINT
os[‘cgroup’][‘cpu’][‘num_elapsed_periods’] The nr. of period intervals (cfs_period_micros) that have elapsed. BIGINT
os[‘cgroup’][‘cpu’][‘num_times_throttled’] The nr. of times tasks in the cgroup have been throttled. BIGINT
os[‘cgroup’][‘cpu’][‘time_throttled_nanos’] The total time (in nanoseconds) for which tasks in the cgroup have been throttled. BIGINT
os[‘cgroup’][‘mem’] Information about memory resources used by tasks in a cgroup. OBJECT
os[‘cgroup’][‘mem’][‘control_group’] The path to the memory cgroup TEXT
os[‘cgroup’][‘mem’][‘usage_bytes’] The total current memory usage by processes in the cgroup. TEXT
os[‘cgroup’][‘mem’][‘limit_bytes’] The max. amount of user memory in the cgroup. TEXT

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

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, macOS) TEXT
os_info[‘arch’] Name of the JVM architecture (ex: amd64, x86) TEXT
os_info[‘version’] Version of the operating system TEXT
os_info[‘jvm’] Information about the JVM (Java Virtual Machine) OBJECT
os_info[‘jvm’][‘version’] The JVM version TEXT
os_info[‘jvm’][‘vm_name’] The name of the JVM (eg. OpenJDK, Java Hotspot(TM) ) TEXT
os_info[‘jvm’][‘vm_vendor’] The vendor name of the JVM TEXT
os_info[‘jvm’][‘vm_version’] The version of the JVM TEXT

network

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

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. BIGINT
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. BIGINT
network[‘tpc’][‘connections’][‘accepted’] Total number of accepted TCP connections. BIGINT
network[‘tpc’][‘connections’][‘curr_established’] Total number of currently established TCP connections. BIGINT
network[‘tcp’][‘connections’][‘dropped’] Total number of dropped TCP connections. BIGINT
network[‘tcp’][‘connections’][‘embryonic_dropped’] Total number of TCP connections that have been dropped before they were accepted. BIGINT
network[‘tcp’][‘packets’] Information about TCP packets. OBJECT
network[‘tpc’][‘packets’][‘sent’] Total number of TCP packets sent. BIGINT
network[‘tcp’][‘packets’][‘received’] Total number of TCP packets received. BIGINT
network[‘tpc’][‘packets’][‘retransmitted’] Total number of TCP packets retransmitted due to an error. BIGINT
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. BIGINT
network[‘tcp’]][‘packets’][‘rst_sent’] Total number of RST packets sent due to left unread data in queue when socket is closed. See tools.ietf.org. BIGINT

connections

Column Name Description Return Type
--- --- ---
http Number of connections established via HTTP OBJECT
http[‘open’] The currently open connections established via HTTP BIGINT
http[‘total’] The total number of connections that have been established via HTTP over the life time of a CrateDB node BIGINT
psql Number of connections established via Postgres protocol OBJECT
psql[‘open’] The currently open connections established via Postgres protocol BIGINT
psql[‘total’] The total number of connections that have been established via Postgres protocol over the life time of a CrateDB node BIGINT
transport Number of connections established via Transport protocol OBJECT
transport[‘open’] The currently open connections established via Transport protocol BIGINT

process

Column Name Description Return Type
--- --- ---
process Statistics about the CrateDB process. OBJECT
process[‘open_file_descriptors’] Number of currently open file descriptors used by the CrateDB process. BIGINT
process[‘max_open_file_descriptors’] The maximum number of open file descriptors CrateDB can use. BIGINT
process[‘probe_timestamp’] The system UNIX timestamp at the moment of the probe collection. BIGINT
process[‘cpu’] Information about the CPU usage of the CrateDB process. OBJECT
process[‘cpu’][‘percent’] The CPU usage of the CrateDB JVM process given in percent. SMALLINT

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:

Column Name Description Return Type
--- --- ---
id The unique check ID. INTEGER
node_id The unique node ID. TEXT
severity The level of severity. The higher the value of the field the higher severity. INTEGER
description The description message for the setting check. TEXT
passed The flag determines whether the check for the setting has passed. BOOLEAN
acknowledged The flag determines whether the check for this setting has been acknowledged by the user in order to ignored the value of passed column. This column can be updated. BOOLEAN

Example query:

1. cr> select id, node_id, description from sys.node_checks order by id, node_id;
2. +----+---------...-+--------------------------------------------------------------...-+
3. | id | node_id     | description                                                      |
4. +----+---------...-+--------------------------------------------------------------...-+
5. |  1 | ...         | The value of the cluster setting 'gateway.expected_nodes' mus... |
6. |  2 | ...         | The value of the cluster setting 'gateway.recover_after_nodes... |
7. |  3 | ...         | If any of the "expected nodes" recovery settings are set, the... |
8. |  5 | ...         | The high disk watermark is exceeded on the node. The cluster ... |
9. |  6 | ...         | The low disk watermark is exceeded on the node. The cluster w... |
10. |  7 | ...         | The flood stage disk watermark is exceeded on the node. Table... |
11. +----+---------...-+--------------------------------------------------------------...-+
12. 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:

1. cr> update sys.node_checks set acknowledged = true where id = 1;
2. 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.

1. (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

Column Name Description Return Type
--- --- ---
node Information about the node the shard is located at. OBJECT
node[‘name’] The name of the node the shard is located at. TEXT
node[‘id’] The id of the node the shard is located at. TEXT
blob_path Path to the directory which contains the blob files of the shard, or null if the shard is not a blob shard. TEXT
id The shard id. This shard id is managed by the system, ranging from 0 up to the number of configured shards of the table. INTEGER
min_lucene_version Shows the oldest Lucene segment version used in this shard. TEXT
num_docs The total amount of documents within a shard. BIGINT
oprhan_partition True if this shard belongs to an orphaned partition which doesn’t belong to any table anymore. BOOLEAN
partition_ident The partition ident of a partitioned table. Empty for non-partitioned tables. TEXT
path Path to the shard directory on the filesystem. This directory contains state and index files. TEXT
primary Indicates if this shard is the primary shard. BOOLEAN
recovery Recovery statistics for a shard. OBJECT
recovery[‘files’] File recovery statistics OBJECT
recovery[‘files’][‘percent’] Percentage of files already recovered. REAL
recovery[‘files’][‘recovered’] Number of files recovered in the shard. Includes both existing and reused files. INTEGER
recovery[‘files’][‘reused’] Total number of files reused from a local copy while recovering the shard. INTEGER
recovery[‘files’][‘used’] Total number of files in the shard. INTEGER
recovery[‘size’] Recovery statistics for the shard in bytes OBJECT
recovery[‘size’][‘percent’] Percentage of bytes already recovered REAL
recovery[‘size’][‘recovered’] Number of bytes recovered. Includes both existing and re-used bytes. BIGINT
recovery[‘size’][‘reused’] Number of bytes re-used from a local copy while recovering the shard. BIGINT
recovery[‘size’][‘used’] Total number of bytes in the shard. BIGINT
recovery[‘stage’] 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 TEXT
recovery[‘total_time’] Returns elapsed time from the start of the shard recovery. BIGINT
recovery[‘type’] Recovery type: - gateway - snapshot - replica - relocating TEXT
relocating_node The id of the node to which the shard is getting relocated to. TEXT
routing_state The current state of the shard in the routing table. Possible states are: - UNASSIGNED - INITIALIZING - STARTED - RELOCATING TEXT
schema_name The schema name of the table the shard belongs to TEXT
size The current size in bytes. This value is cached for a short period and may return slightly outdated values. BIGINT
state The current state of the shard. Possible states are: - CREATED - RECOVERING - POST_RECOVERY - STARTED - RELOCATED - CLOSED - INITIALIZING - UNASSIGNED TEXT
closed The state of the table associated with the shard. BOOLEAN
table_name The name of the table this shard belongs to TEXT
seq_no_stats Contains information about internal sequence numbering and checkpoints for these sequence numbers. OBJECT
seq_no_stats[‘max_seq_no’] The highest sequence number that has been issued so far on the shard. BIGINT
seq_no_stats[‘local_checkpoint’] The highest sequence number for which all lower sequence number of been processed on this shard. Due to concurrent indexing this can be lower than max_seq_no. BIGINT
seq_no_stats[‘global_checkpoint’] The highest sequence number for which the local shard can guarantee that all lower sequence numbers have been processed on all active shard copies. BIGINT
translog_stats Contains information for the translog of the shard. OBJECT
translog_stats[‘size’] The current size of the translog file in bytes. BIGINT
translog_stats[‘uncommitted_size’] The size in bytes of the translog that has not been committed to Lucene yet. BIGINT
translog_stats[‘number_of_operations’] The number of operations recorded in the translog. INTEGER
translog_stats[‘uncommitted_operations’] The number of operations in the translog which have not been committed to Lucene yet. INTEGER
retention_leases Versioned collection of retention leases. OBJECT

Note

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

Example

For example, you can query shards like this:

1. cr> select schema_name as schema,
2. ...   table_name as t,
3. ...   id,
4. ...   partition_ident as p_i,
5. ...   num_docs as docs,
6. ...   primary,
7. ...   relocating_node as r_n,
8. ...   routing_state as r_state,
9. ...   state,
10. ...   orphan_partition as o_p
11. ... from sys.shards where table_name = 'locations' and id = 1;
12. +--------+-----------+----+-----+------+---------+------+---------+---------+-------+
13. | schema | t         | id | p_i | docs | primary | r_n  | r_state |  state  | o_p   |
14. +--------+-----------+----+-----+------+---------+------+---------+---------+-------+
15. | doc    | locations |  1 |     |    8 | TRUE    | NULL | STARTED | STARTED | FALSE |
16. +--------+-----------+----+-----+------+---------+------+---------+---------+-------+
17. 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.

Column Name Description Return Type
--- --- ---
segment_name Name of the segment, derived from the segment generation and used internally to create file names in the directory of the shard. TEXT
shard_id ID of the effected shard. INTEGER
table_schema Schema name of the table of the shard. TEXT
table_name Table name of the shard. TEXT
partition_ident The partition ident of a partitioned table. Empty for non-partitioned tables. TEXT
node Information about the node the shard is located at. OBJECT
node[‘name’] The name of the node the shard is located at. TEXT
node[‘id’] The id of the node the shard is located at. TEXT
generation Generation number of the segment, increments for each segment written. LONG
num_docs Number of non-deleted lucene documents in this segment. INTEGER
deleted_docs Number of deleted lucene documents in this segment. INTEGER
size Disk space used by the segment in bytes. LONG
memory Segment data stored in memory for efficient search, -1 if it is unavailable. LONG
committed Indicates if the segments are synced to disk. Segments that are synced can survive a hard reboot. BOOLEAN
primary Describes if this segment is part of a primary shard. BOOLEAN
search Indicates if the segment is searchable. If false, the segment has most likely been written to disk but needs a refresh to be searchable. BOOLEAN
version Version of Lucene used to write the segment. TEXT
compound If true, Lucene merges all files from the segment into a single file to save file descriptors. BOOLEAN
attributes Contains information about whether high compression was enabled. OBJECT

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 [stat

（超出体积上限，已截断）