System tables

Elena Dvoryadkina

Overview

ADQM system tables are tables in the system database providing different kind of information that can be useful to administrators and developers for database management, query optimization, system performance monitoring, and error analysis and troubleshooting. For example, system tables contain:

  • information on server states and internal processes;

  • details on configured clusters, existing databases, tables, columns, and data parts;

  • ClickHouse server metrics collected in real time and events occurred in the system;

  • current values ​​of configuration parameters;

  • help information about options available for building queries.

Most of the system tables store their data in RAM. These tables are automatically created when the ClickHouse server starts, and they are read-only. They cannot be deleted or altered but can be detached.

System tables also include tables storing different logs — log tables named as *_log. If you activate logging, ADQM creates and starts populating the corresponding log table, which becomes accessible from the system database. Unlike other system tables, log tables are based on the MergeTree table engine. Data of these tables is stored in the file system and is not automatically deleted — this guarantees access to logs for analysis after the server restart. The Logging article lists types of logs that can be written to system tables and explains how to enable logging and configure log tables.

List of system tables

You can view the full list of available system tables using the query:

SHOW TABLES FROM system;
List of available system tables

 

For example, the output can look like:

    ┌─name───────────────────────────┐
 1. │ aggregate_function_combinators │
 2. │ asynchronous_inserts           │
 3. │ asynchronous_loader            │
 4. │ asynchronous_metrics           │
 5. │ backups                        │
 6. │ build_options                  │
 7. │ certificates                   │
 8. │ clusters                       │
 9. │ collations                     │
10. │ columns                        │
11. │ contributors                   │
12. │ current_roles                  │
13. │ dashboards                     │
14. │ data_skipping_indices          │
15. │ data_type_families             │
16. │ database_engines               │
17. │ databases                      │
18. │ detached_parts                 │
19. │ dictionaries                   │
20. │ disks                          │
21. │ distributed_ddl_queue          │
22. │ distribution_queue             │
23. │ dns_cache                      │
24. │ dropped_tables                 │
25. │ dropped_tables_parts           │
26. │ enabled_roles                  │
27. │ errors                         │
28. │ events                         │
29. │ filesystem_cache               │
30. │ formats                        │
31. │ functions                      │
32. │ generateSeries                 │
33. │ generate_series                │
34. │ grants                         │
35. │ graphite_retentions            │
36. │ jemalloc_bins                  │
37. │ kafka_consumers                │
38. │ keywords                       │
39. │ licenses                       │
40. │ macros                         │
41. │ merge_tree_settings            │
42. │ merges                         │
43. │ metrics                        │
44. │ models                         │
45. │ moves                          │
46. │ mutations                      │
47. │ mysql_binlogs                  │
48. │ named_collections              │
49. │ numbers                        │
50. │ numbers_mt                     │
51. │ one                            │
52. │ part_moves_between_shards      │
53. │ parts                          │
54. │ parts_columns                  │
55. │ privileges                     │
56. │ processes                      │
57. │ projection_parts               │
58. │ projection_parts_columns       │
59. │ query_cache                    │
60. │ quota_limits                   │
61. │ quota_usage                    │
62. │ quotas                         │
63. │ quotas_usage                   │
64. │ remote_data_paths              │
65. │ replicas                       │
66. │ replicated_fetches             │
67. │ replicated_merge_tree_settings │
68. │ replication_queue              │
69. │ rocksdb                        │
70. │ role_grants                    │
71. │ roles                          │
72. │ row_policies                   │
73. │ s3queue                        │
74. │ scheduler                      │
75. │ schema_inference_cache         │
76. │ server_settings                │
77. │ settings                       │
78. │ settings_changes               │
79. │ settings_profile_elements      │
80. │ settings_profiles              │
81. │ stack_trace                    │
82. │ storage_policies               │
83. │ symbols                        │
84. │ table_engines                  │
85. │ table_functions                │
86. │ tables                         │
87. │ time_zones                     │
88. │ user_directories               │
89. │ user_processes                 │
90. │ users                          │
91. │ view_refreshes                 │
92. │ warnings                       │
93. │ zeros                          │
94. │ zeros_mt                       │
95. │ zookeeper                      │
96. │ zookeeper_connection           │
    └────────────────────────────────┘

This list does not include *_log tables as logging has not been enabled.

 

Below are ADQM system tables grouped by the type of information they provide.

Server states | Current processes | Caches
System table Table data

merges

Information about data part merges and mutations that are currently being performed for MergeTree tables

mutations

Information about mutations of MergeTree tables and their progress

processes

Information about all queries that are currently running

moves

Information about in-progress data part movements between disks or volumes of a storage (initiated, for example, by a TTL rule or the ALTER TABLE …​ MOVE PARTITION|PART …​ TO DISK|VOLUME query)

replicated_fetches

Information about currently running background fetches of data parts from replicas

stack_trace

Stack traces of all server threads (can be used to introspect the server state)

asynchronous_inserts

Information about pending asynchronous inserts in a queue

asynchronous_loader

Information and status for recent asynchronous jobs (for example, for loading tables)

query_cache

Information about the content of the query cache

dns_cache

Information about cached DNS records

filesystem_cache

Information about all entries in filesystem cache for remote objects
Clusters | Sharding | Replication | ZooKeeper
System table Table data

clusters

Information about configured clusters

macros

Macros that will be used to automatically substitute host-specific shard and replica identifiers when creating replicated tables on clusters (macros for all clusters are defined in the macros section of the server configuration)

replicas

Information about replicated tables located on the server

replication_queue

Information about tasks from replication queues stored in ZooKeeper or ClickHouse Keeper for ReplicationMergeTree tables

distribution_queue

Information about local files that are in a queue to be sent to shards (these local files contain new data parts that are created by inserting new data into the Distributed table in asynchronous mode)

distributed_ddl_queue

Information about distributed DDL queries (with the ON CLUSTER clause) that were executed on the cluster

zookeeper

Data from the ZooKeeper or ClickHouse Keeper cluster defined in the configuration (allows you to get a list of children for a specific node or node value)

zookeeper_connection

Current connections to ZooKeeper or ClickHouse Keeper

Examples

  • Obtain information on configured logical clusters:

    SELECT cluster, shard_num, replica_num, host_name FROM system.clusters;

  • Check that replicas are healthy:

    SELECT database, table, last_queue_update_exception, zookeeper_exception FROM system.replicas WHERE is_readonly;

    If a table is read-only (for example, because there is no ClickHouse Keeper/ZooKeeper configuration section in config.xml or an error occurred when re-initializing a session in ClickHouse Keeper/ZooKeeper), you can view error messages in the last_queue_update_exception and zookeeper_exception fields.

  • Check the replication queue status:

    SELECT database, table, replica_name, type, num_postponed, postpone_reason, num_tries, last_exception
FROM system.replication_queue FORMAT Vertical;
Databases | Tables | Columns | Data parts | Dictionaries
System table Table data

databases

Information about databases available to the current user

tables

Metadata of tables the server "knows" about (tables detached via DETACH TABLE are not included)

columns

Information about all columns of all tables

parts

Information about currently existing data parts of all MergeTree tables

parts_columns

Information about columns of all currently existing data parts of MergeTree tables

detached_parts

Information about detached data parts of MergeTree tables

dropped_tables

Information about tables dropped from databases but not completely removed yet

dropped_tables_parts

Information about data parts of dropped MergeTree tables (listed in system.dropped_tables)

projection_parts

Information on currently existing data parts created for all projections of all tables within a cluster (copies of parent parts containing data aggregated or sorted in a different order). This table is similar to system.parts, but contains additional information about parent data parts in columns prefixed with parent_

projection_parts_columns

Information about columns of all currently existing projection data parts of all MergeTree tables

data_skipping_indices

Information about existing data skipping indexes in all tables

dictionaries

Information about dictionaries

named_collections

Configured named collections — collections of key/value pairs to be used to configure integrations with external sources (dictionaries, tables, table functions)

Examples

  • View table columns with their data types:

    SELECT name, type FROM system.columns WHERE table = '<table_name>';

  • View information about table data parts:

    SELECT name, active, rows, data_compressed_bytes, data_uncompressed_bytes
FROM system.parts WHERE table = '<table_name>'
FORMAT Vertical;

    where <table_name> is a table name.

Access control system
System table Table data

users

Information about user accounts configured on the server

grants

Privileges granted to user accounts (the list of privileges available for assignment can be found in the system.privileges table)

roles, role_grants, current_roles, enabled_roles

Information about roles and granting roles to users

row_policies

Information about row policies

settings_profiles

Configured settings profiles

settings_profile_elements

Contents of settings profiles (including settings and their values; constraints; roles and users for which the settings are applied; parent settings profiles)

quota_limits, quota_usage, quotas, quotas_usage

Information about quotas and quota usage
Data storage
System table Table data

disks

Information about configured disks

storage_policies

Information about configured storage policies

Examples

  • View information about data storage disks:

    SELECT
    name,
    path,
    formatReadableSize(free_space) AS free,
    formatReadableSize(total_space) AS total,
    formatReadableSize(keep_free_space) AS reserved
FROM system.disks;

  • View information about storage policies configured in ADQM:

    SELECT policy_name, volume_name, disks FROM system.storage_policies;
ClickHouse server metrics | Errors and warnings
System table Table data

metrics

Metrics that are calculated instantly and have up-to-date current values (see Metrics in the Metric types section of the ADQM monitoring metrics)

events

Information about the number of events that have occurred in the system (see ProfileEvents in the Metric types section of the ADQM monitoring metrics)

asynchronous_metrics

Metrics that are periodically calculated in the background (see AsynchronousMetrics in the Metric types section of the ADQM monitoring metrics)

user_processes

Information about memory usage by processes a user has started and ProfileEvents for the user

errors

Information about all errors that have ever occurred (including error code and message, number of occurrences, the last occurrence time, and stack trace for debugging)

warnings

Warnings about server configuration (if the table is not empty, clickhouse-client displays warning messages stored in the table on connection to the ClickHouse server)

backups

Information about all BACKUP and RESTORE operations (the table is not persistent, and it contains only operations executed after the last server restart)

jemalloc_bins

Information about memory allocation made via jemalloc in different size classes (bins) aggregated from all arenas. These statistics may not be absolutely accurate because of thread local caching in jemalloc

Examples

  • View errors:

    SELECT name, code, value, last_error_time FROM system.errors;

  • See a value of a specific metric (for example, Query — the number of queries being executed):

    SELECT * FROM system.metrics WHERE metric = 'Query';
Settings
System table Table data

settings

User-level settings (which can be changed for an individual query or session)

server_settings

Global server settings specified in config.xml

merge_tree_settings

List of all MergeTree table engine settings, their current and default values, as well as descriptions. You can configure these parameters when creating or modifying a table with the SETTINGS clause in the CREATE TABLE or ALTER TABLE query, respectively

replicated_merge_tree_settings

List of all ReplicatedMergeTree table engine settings, their current and default values, as well as descriptions. You can configure these parameters when creating or modifying a table with the SETTINGS clause in the CREATE TABLE or ALTER TABLE query, respectively

settings_changes

Information about changes of settings in different versions of ClickHouse

build_options

ClickHouse server build options

Examples

View which settings ​​have been changed from default values (for example, during troubleshooting, it can be useful to analyze whether any changed settings could affect system behavior):

SELECT name, value FROM system.settings WHERE changed;
SELECT name, value FROM system.server_settings WHERE changed;
Information related to different table engines
System table Table data

mysql_binlogs

List of active binlogs for the MaterializedMySQL database engine

kafka_consumers

Information about Kafka consumers (tables based on the Kafka engine)

rocksdb

RocksDB statistics (for the integration via the EmbeddedRocksDB table engine)

graphite_retentions

Information about the graphite_rollup parameters used in the GraphiteMergeTree tables

s3queue

In-memory state of S3Queue: which files are currently being processed, which files are processed or failed
Data for testing

 
ADQM provides a set of tables that can be useful for testing and generating successive values.

System table Table data

numbers

The table contains a single number column (of the UInt64 type) with all natural numbers starting from 0 in sorted order. Reading from this table is not parallelized. You can get this table from the system database or using the numbers table function

numbers_mt

The table is similar to system.numbers, but reading is parallelized. Numbers can be returned in any order

zeros

The table contains a single zero column (of the Int64 type) with zeros. You can use it for performance tests instead of system.numbers as the fastest way to generate a large number of rows

zeros_mt

The table is similar to system.zeros, but reading is parallelized

generate_series, generateSeries

The table contains a single generate_series column (of the UInt64 type) with all natural numbers from 0 in sorted order. To get the same table with the arithmetic progression of sorted natural numbers in the specified range with the specified step, you can also use the generate_series or generateSeries table function

one

The table contains 0 in a single row with a single dummy column of the UInt8 type. It is used if the SELECT query does not specify the FROM clause — for example, SELECT 1. This table is similar to the DUAL table in other DBMSs
Help information
System table Table data

functions

Supported functions (normal and aggregate)

aggregate_function_combinators

Combinators for aggregate functions

table_functions

Supported table functions

database_engines

Supported database engines

table_engines

Supported table engines and their functionality

data_type_families

Supported data types

formats

Supported input and output formats

privileges

Supported types of privileges that can be granted to users

keywords

Keywords that can be used in query syntax

time_zones

Time zones the ClickHouse server supports

collations

Supported collations for alphabetical comparison of strings that you can pass to queries as ORDER BY …​ COLLATE <'collation_name'>

Example

View supported aggregate functions:

SELECT name FROM system.functions WHERE is_aggregate = 1;
Found a mistake? Seleсt text and press Ctrl+Enter to report it