ClickHouse 中有没有一些能够 “安家立命” 的运维 SQL 语句。我想对于这个问题很多朋友都会有兴趣，所以就在这里做一个简单的分享。
在 ClickHouse 默认的 system 数据库下(databse)，拥有众多的系统表。我们对 ClickHouse 运行状态的各种信息，就主要来自于这些系统表。
接下来就列举一些常用的运维 SQL 语句。
- 当前连接数
众所周知，CH 对外暴露的原生接口分为 TCP 和 HTTP 两类，通过 system.metrics 即可查询当前的 TCP、HTTP 与内部副本的连接数。
ch7.nauu.com :) SELECT * FROM system.metrics WHERE metric LIKE '%Connection';
┌─metric────────────────┬─value─┬─description─────────────────────────────────────────────────────────┐
│ TCPConnection │ 2 │ Number of connections to TCP server (clients with native interface) │
│ HTTPConnection │ 1 │ Number of connections to HTTP server │
│ InterserverConnection │ 0 │ Number of connections from other replicas to fetch parts │
└───────────────────────┴───────┴─────────────────────────────────────────────────────────────────────┘- 当前正在执行的查询
- 通过 system.processes 可以查询目前正在执行的查询，例如：
SELECT query_id, user, address, query FROM system.processes ORDER BY query_id;
┌─query_id─────────────────────────────┬─user────┬─address────────────┬─query─────────────────────────────────────────────────────────────────────────────┐
│ 203f1d0e-944e-472d-8d8f-bae548ff9899 │ default │ ::ffff:10.37.129.4 │ SELECT query_id, user, address, query FROM system.processes ORDER BY query_id ASC │
│ fb7fba85-b2a0-4271-87ff-22da97ae511b │ default │ ::ffff:10.37.129.4 │ INSERT INTO hits_v1 FORMAT TSV
└──────────────────────────────────────┴─────────┴────────────────────┴───────────────────────────────────────────────────────────────────────────────────┘可以看到，CH 目前正在执行两条语句，其中第 2 条是 INSERT 查询正在写入数据。
- 终止查询
- 通过 KILL QUERY 语句，可以终止正在执行的查询:
KILL QUERY WHERE query_id = 'query_id'例如，终止刚才的 INSERT 查询 :
ch7.nauu.com :) KILL QUERY WHERE query_id='ff695827-dbf5-45ad-9858-a853946ea140';
KILL QUERY WHERE query_id = 'ff695827-dbf5-45ad-9858-a853946ea140' ASYNC
Ok.
0 rows in set. Elapsed: 0.024 sec.众所周知，除了常规的 SELECT 和 INSERT 之外，在 ClickHouse 中还存在一类被称作 Mutation 的操作，也就是 ALTER DELETE 和 ALTER UPDATE。
对于 Mutation 操作， ClickHouse 专门提供了 system.mutations 用于查询，例如：
ch7.nauu.com :) SELECT database, table, mutation_id, command, create_time, is_done FROM system.mutations;
┌─database─┬─table──────┬─mutation_id────┬─command──────────────────┬─────────create_time─┬─is_done─┐
│ default │ testcol_v9 │ mutation_2.txt │ DELETE WHERE ID = 'A003' │ 2020-06-29 01:15:04 │ 1 │
└──────────┴────────────┴────────────────┴──────────────────────────┴─────────────────────┴─────────┘
1 rows in set. Elapsed: 0.002 sec.同样的，可以使用 KILL MUTATION 终止正在执行的 Mutation 操作:
KILL MUTATION WHERE mutation_id = 'mutation_id';- 存储空间统计
查询 CH 各个存储路径的空间:
ch5.nauu.com :) SELECT name,path,formatReadableSize(free_space) AS free,formatReadableSize(total_space) AS total,formatReadableSize(keep_free_space) AS reserved FROM system.disks
┌─name──────┬─path──────────────┬─free──────┬─total─────┬─reserved─┐
│ default │ /chbase/data/ │ 36.35 GiB │ 49.09 GiB │ 0.00 B │
│ disk_cold │ /chbase/cloddata/ │ 35.35 GiB │ 48.09 GiB │ 1.00 GiB │
│ disk_hot1 │ /chbase/data/ │ 36.35 GiB │ 49.09 GiB │ 0.00 B │
│ disk_hot2 │ /chbase/hotdata1/ │ 36.35 GiB │ 49.09 GiB │ 0.00 B │
└───────────┴───────────────────┴───────────┴───────────┴──────────┘
4 rows in set. Elapsed: 0.001 sec.- 各数据库占用空间统计
ch7.nauu.com :) SELECT database, formatReadableSize(sum(bytes_on_disk)) on_disk FROM system.parts GROUP BY database;
┌─database─┬─on_disk──┐
│ system │ 1.59 MiB │
│ default │ 3.60 GiB │
└──────────┴──────────┘- 个列字段占用空间统计
- 每个列字段的压缩大小、压缩比率以及该列的每行数据大小的占比
SELECT
database,
table,
column,
any(type),
sum(column_data_compressed_bytes) AS compressed,
sum(column_data_uncompressed_bytes) AS uncompressed,
round(uncompressed / compressed, 2) AS ratio,
compressed / sum(rows) AS bpr,
sum(rows)
FROM system.parts_columns
WHERE active AND database != 'system'
GROUP BY
database,
table,
column
ORDER BY
database ASC,
table ASC,
column ASC
┌─database─┬─table────────┬─column─────────────────────┬─any(type)──────────────────────────────┬─compressed─┬─uncompressed─┬──ratio─┬───────────────────bpr─┬─sum(rows)─┐
│ default │ hits_v1 │ AdvEngineID │ UInt8 │ 351534 │ 26621706 │ 75.73 │ 0.013204788603705563 │ 26621706 │
│ default │ hits_v1 │ Age │ UInt8 │ 7543552 │ 26621706 │ 3.53 │ 0.2833609536518809 │ 26621706 │
│ default │ hits_v1 │ BrowserCountry │ FixedString(2) │ 6549379 │ 53243412 │ 8.13 │ 0.24601650247358303 │ 26621706 │
│ default │ hits_v1 │ BrowserLanguage │ FixedString(2) │ 2819085 │ 53243412 │ 18.89 │ 0.10589422781545255 │ 26621706 │
│ default │ hits_v1 │ CLID │ UInt32 │ 2311006 │ 106486824 │ 46.08 │ 0.08680908729140048 │ 26621706 │
...- 慢查询
SELECT
user,
client_hostname AS host,
client_name AS client,
formatDateTime(query_start_time, '%T') AS started,
query_duration_ms / 1000 AS sec,
round(memory_usage / 1048576) AS MEM_MB,
result_rows AS RES_CNT,
result_bytes / 1048576 AS RES_MB,
read_rows AS R_CNT,
round(read_bytes / 1048576) AS R_MB,
written_rows AS W_CNT,
round(written_bytes / 1048576) AS W_MB,
query
FROM system.query_log
WHERE type = 2
ORDER BY query_duration_ms DESC
LIMIT 10
┌─user────┬─host─────────┬─client────────────┬─started──┬────sec─┬─MEM_MB─┬──RES_CNT─┬────────────────RES_MB─┬────R_CNT─┬─R_MB─┬───W_CNT─┬─W_MB─┬─query───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ default │ ch7.nauu.com │ ClickHouse client │ 01:05:03 │ 51.434 │ 1031 │ 8873898 │ 8706.51146697998 │ 0 │ 0 │ 8873898 │ 8707 │ INSERT INTO hits_v1 FORMAT TSV │
│ default │ ch7.nauu.com │ ClickHouse client │ 01:01:48 │ 43.511 │ 1031 │ 8873898 │ 8706.51146697998 │ 0 │ 0 │ 8873898 │ 8707 │ INSERT INTO hits_v1 FORMAT TSV │
│ default │ ch7.nauu.com │ ClickHouse client │ 17:12:04 │ 11.12 │ 1801 │ 18874398 │ 446.8216323852539 │ 6291466 │ 351 │ 0 │ 0 │ SELECT id, arrayJoin(arrayConcat(groupArray(a), groupArray(b), groupArray(c))) AS v FROM test_y GROUP BY id ORDER BY v ASC │
│ default │ ch7.nauu.com │ ClickHouse client │ 17:13:28 │ 3.992 │ 1549 │ 18874398 │ 446.8216323852539 │ 6291466 │ 351 │ 0 │ 0 │ SELECT id, arrayJoin(arrayConcat(groupArray(a), groupArray(b), groupArray(c))) AS v FROM test_y GROUP BY id │
│ default │ ch7.nauu.com │ ClickHouse client │ 17:13:12 │ 3.976 │ 1549 │ 18874398 │ 446.8216323852539 │ 6291466 │ 351 │ 0 │ 0 │ SELECT id, arrayJoin(arrayConcat(groupArray(a), groupArray(b), groupArray(c))) AS v FROM test_y GROUP BY id │
└─────────┴──────────────┴───────────────────┴──────────┴────────┴────────┴──────────┴───────────────────────┴──────────┴──────┴─────────┴──────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
...
10 rows in set. Elapsed: 0.017 sec. Processed 1.44 thousand rows, 200.81 KB (83.78 thousand rows/s., 11.68 MB/s.)- 副本预警监控<可以对副本的健康状态进行预警：查询出来无数据代表正常>
通过下面的 SQL 语句对副本进行预警监控，其中各个预警的变量可以根据自身情况调整。
SELECT
database,
table,
is_leader,
is_readonly,
is_session_expired,
future_parts,
parts_to_check,
columns_version,
queue_size,
inserts_in_queue,
merges_in_queue,
log_max_index,
log_pointer,
total_replicas,
active_replicas
FROM system.replicas
WHERE
is_readonly
OR is_session_expired
OR future_parts > 20
OR parts_to_check > 10
OR queue_size > 20
OR inserts_in_queue > 10
OR log_max_index - log_pointer > 10
OR total_replicas < 2
OR active_replicas < total_replicas
┌─database─┬─table───────────────────────┬─is_leader─┬─total_replicas─┬─active_replicas─┐
│ default │ replicated_sales_12 │ 0 │ 0 │ 0 │
│ default │ test_fetch │ 0 │ 0 │ 0 │
│ default │ test_sharding_simple2_local │ 0 │ 0 │ 0 │
└──────────┴─────────────────────────────┴───────────┴────────────────┴─────────────────┘- 系统表：system.metrics
包含可以立即计算或具有当前值的指标。 例如，同时处理的查询的数量或当前副本的延迟。 此表始终是最新的。
列:
- metric (字符串) — Metric name.
- value (Int64) — Metric value.
- description (字符串) — Metric description.
总结：这张表显示当前任务的具体指标，延迟、BackgroundPoolTask、副本、ZooKeeperWatch等详细信息
- 系统表：system.events
这张表主要记录clickHouse服务器中历史的信息，比如：您可以找到多少 SELECT 自ClickHouse服务器启动以来已处理查询。
┌─event─────────────────────────────────┬──────────value─┬─description────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ Query │ 7077759 │ Number of queries to be interpreted and potentially executed. Does not include queries that failed to parse or were rejected due to AST size limits, quota limits or limits on the number of simultaneously running queries. May include internal queries initiated by ClickHouse itself. Does not count subqueries. │
│ SelectQuery │ 3350909 │ Same as Query, but only for SELECT queries. │
│ InsertQuery │ 3710781 │ Same as Query, but only for INSERT queries. │
│ FailedQuery │ 3185 │ Number of failed queries. │
│ FailedSelectQuery │ 170 │ Same as FailedQuery, but only for SELECT queries. │
│ FailedInsertQuery │ 2646 │ Same as FailedQuery, but only for INSERT queries. │
│ FileOpen │ 3066693173 │ Number of files opened. │
│ Seek │ 9689284 │ Number of times the 'lseek' function was called. │
│ ReadBufferFromFileDescriptorRead │ 3589854694 │ Number of reads (read/pread) from a file descriptor. Does not include sockets. │
│ ReadBufferFromFileDescriptorReadBytes │ 13078149963727 │ Number of bytes read from file descriptors. If the file is compressed, this will show the compressed data size. │
└───────────────────────────────────────┴────────────────┴──────────────────────────────────────────────────────────────────────────────────────────- 系统表：system.storage_policies
这张表保存，当前集群中磁盘的卷的保留策略（意思是：data 目录下是挂了多块硬盘还是一块）
- 系统表：system.tables.
这张表中保存着所有表的元数据信息、列、引擎名、磁盘大小、数据量
database (String) — The name of the database the table is in.
name (String) — Table name.
engine (String) — Table engine name (without parameters).
is_temporary (UInt8)-指示表是否是临时的标志。
data_path (String)-文件系统中表数据的路径。
metadata_path (String)-文件系统中表元数据的路径。
metadata_modification_time (DateTime)-表元数据的最新修改时间。
dependencies_database (数组(字符串))-数据库依赖关系.
dependencies_table （数组（字符串））-表依赖关系 (MaterializedView 基于当前表的表）。
create_table_query (String)-用于创建表的查询。
engine_full (String)-表引擎的参数。
partition_key (String)-表中指定的分区键表达式。
sorting_key (String)-表中指定的排序键表达式。
primary_key (String)-表中指定的主键表达式。
sampling_key (String)-表中指定的采样键表达式。
storage_policy (字符串)-存储策略:
MergeTree
分布
total_rows (Nullable(UInt64))-总行数，如果可以快速确定表中的确切行数，否则 Null （包括内衣 Buffer 表）。
total_bytes (Nullable(UInt64))-总字节数，如果可以快速确定存储表的确切字节数，否则 Null (不 包括任何底层存储）- 系统表：system.settings
这张表中存储着系统的配置参数信息
SELECT *
FROM system.settings
WHERE name LIKE '%min_i%'
┌─name────────────────────────────────────────┬─value─────┬─changed─┬─description───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─min──┬─max──┬─readonly─┐
│ min_insert_block_size_rows │ 1048576 │ 0 │ Squash blocks passed to INSERT query to specified size in rows, if blocks are not big enough. │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 0 │
│ min_insert_block_size_bytes │ 268435456 │ 0 │ Squash blocks passed to INSERT query to specified size in bytes, if blocks are not big enough. │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 0 │
│ read_backoff_min_interval_between_events_ms │ 1000 │ 0 │ Settings to reduce the number of threads in case of slow reads. Do not pay attention to the event, if the previous one has passed less than a certain amount of time. │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │ 0 │
└─────────────────────────────────────────────┴───────────┴─────────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────┴──────┴──────────┘对于 CH 日常的运维 SQL 远不止这些，这里也只是抱砖引玉。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
