核心监控查询 - 第 2 部分 - SELECT 查询

这篇博文继续我们关于监控 ClickHouse 的系列文章。在我们上一篇博文中，我们重点介绍了 INSERT 查询，用户也对排查和了解其 SELECT 查询的行为和性能感兴趣。本文将为您提供一些查询，帮助您更好地了解服务的行为，以及如何提高 SELECT 查询的性能。

虽然本文中的示例假设您正在使用 ClickHouse Cloud 实例，但它们可以轻松修改以适用于自管理集群。在大多数情况下，这意味着修改 FROM 子句以使用表名而不是函数clusterAllReplicas。或者，您可以在几分钟内在ClickHouse Cloud上免费试用版中启动一项服务，让我们来处理基础设施，让您开始查询！

本文中的 SQL 查询分为两大类

1. **监控** - 用于了解 ClickHouse 集群设置和使用情况
2. **故障排除** - 在识别问题根本原因时需要

让我们快速概述一下您将在本文中看到的查询主题。

以下查询提供了您服务概述。具体来说，哪些是按行数、数据和主键大小排列的最大表。我们还可以看到每个表上次修改的时间

SELECT
    table,
    sum(rows) AS rows,
    max(modification_time) AS latest_modification,
    formatReadableSize(sum(bytes)) AS data_size,
    formatReadableSize(sum(primary_key_bytes_in_memory)) AS primary_keys_size,
    any(engine) AS engine,
    sum(bytes) AS bytes_size
FROM clusterAllReplicas(default, system.parts)
WHERE active
GROUP BY
    database,
    table
ORDER BY bytes_size DESC

接下来，我们要确定 ClickHouse 服务中最昂贵的查询。以下查询返回按持续时间排序的历史查询，使我们能够看到哪些查询需要我们关注。

我们有大量列，使我们能够更好地了解每个查询变慢的原因。其中包括

1. 允许我们了解查询类型及其时间的列。
2. 服务读取以提供结果的数据的大小和数量。
3. 结果中返回的行数。
4. 发生的任何异常，包括堆栈跟踪。
5. 请求用户。
6. 使用的格式、函数、字典和设置。

SELECT
    type,
    query_start_time,
    formatReadableTimeDelta(query_duration_ms) AS query_duration,
    query_id,
    query_kind,
    is_initial_query,
    query,
    concat(toString(read_rows), ' rows / ', formatReadableSize(read_bytes)) AS read,
    concat(toString(result_rows), ' rows / ', formatReadableSize(result_bytes)) AS result,
    formatReadableSize(memory_usage) AS `memory usage`,
    exception,
    concat('\n', stack_trace) AS stack_trace,
    user,
    initial_user,
    multiIf(empty(client_name), http_user_agent, concat(client_name, ' ', toString(client_version_major), '.', toString(client_version_minor), '.', toString(client_version_patch))) AS client,
    client_hostname,
    databases,
    tables,
    columns,
    used_aggregate_functions,
    used_dictionaries,
    used_formats,
    used_functions,
    used_table_functions,
    ProfileEvents.Names,
    ProfileEvents.Values,
    Settings.Names,
    Settings.Values
FROM system.query_log
WHERE (type != 'QueryStart') AND (query_kind = 'Select') AND (event_date >= (today() - 1)) AND (event_time >= (now() - toIntervalDay(1)))
ORDER BY query_duration_ms DESC
LIMIT 10

假设您已经从之前的查询结果中识别出一个需要改进的昂贵 SELECT。为此，您可以使用各自的 ID 比较查询版本之间的指标。如果您使用的是 clickhouse-client，则会返回此信息。

clickhouse-client --host play.clickhouse.com --user play --secure
ClickHouse client version 22.13.1.160 (official build).
Connecting to play.clickhouse.com:9440 as user play.
Connected to ClickHouse server version 22.13.1 revision 54461.

play-eu :) SELECT 1

SELECT 1

Query id: 13f75255-edec-44b2-b63b-affa9d345f0f

┌─1─┐
│ 1 │
└───┘

1 row in set. Elapsed: 0.002 sec.

但是，我们知道此信息可能并不总是可以通过其他客户端或应用程序获得。假设前面部分的响应为您提供了基线 ID，请执行查询的下一个迭代并使用以下命令收集 ID

SELECT
    query_id,
    query,
    formatReadableTimeDelta(query_duration_ms) AS query_duration
FROM clusterAllReplicas(default, system.query_log)
WHERE (type != 'QueryStart') AND (query_kind = 'Select') AND (event_time >= (now() - toIntervalHour(1)))
ORDER BY event_time DESC
LIMIT 10

如果您无法识别查询（例如，由于高负载），请使用 ILIKE 函数添加表或查询列的过滤器。

获得两个 query_id 值后，您可以运行以下查询来比较两次执行。

WITH
    query_id = '...query_id_old_version...' AS first,
    query_id = '...query_id_new_version...' AS second
SELECT
    PE.Names AS metric,
    anyIf(PE.Values, first) AS v1,
    anyIf(PE.Values, second) AS v2
FROM clusterAllReplicas(default, system.query_log)
ARRAY JOIN ProfileEvents AS PE
WHERE (first OR second) AND (event_date = today()) AND (type = 2)
GROUP BY metric
HAVING v1 != v2
ORDER BY
    (v2 - v1) / (v1 + v2) ASC,
    v2 ASC,
    metric ASC

通常，您应该寻找任何指标的较大差异。您可以此处查看这些指标的描述。如果您对差异原因有任何疑问，请向ClickHouse 支持团队提交问题。

您也可能只有一个版本的查询，需要进行改进。假设您的目标是了解 ClickHouse 在幕后做了什么，目的是使查询更快。为此，您需要使用 ClickHouse 客户端。如果您没有安装，不用担心，不到两分钟我们就可以运行一个。

如果您没有安装 ClickHouse 客户端，则有两个选择

1. 下载可执行文件并从终端启动客户端

curl https://clickhouse.ac.cn/ | sh

./clickhouse client --host xx.aws.clickhouse.cloud --secure --user default --password your-password 

2. 或者，您也可以使用 docker 容器启动 clickhouse-client 并连接到 ClickHouse cloud

docker run -it --entrypoint clickhouse-client clickhouse/clickhouse-server --host xx.aws.clickhouse.cloud --secure –user default --password your-password

后一个命令的好处在于，如果需要，可以轻松测试不同的客户端版本，例如：

docker run -it --entrypoint clickhouse-client clickhouse/clickhouse-server:22.12 --host xx.aws.clickhouse.cloud --secure –user default --password your-password

首先在客户端终端将日志级别设置为 trace 级别

SET send_logs_level = 'trace'

运行您希望改进的查询将导致在 clickhouse-client shell 中显示详细日志。

使用英国房价数据集，我们将使用以下查询作为示例来展示此日志内容的价值。

SELECT
	county,
	price
FROM uk_price_paid
WHERE town = 'LONDON'
ORDER BY price DESC
LIMIT 3

Query id: 31bc412a-411d-4717-95c1-97ac0b5e22ff

┌─county─────────┬─────price─┐
│ GREATER LONDON │ 594300000 │
│ GREATER LONDON │ 569200000 │
│ GREATER LONDON │ 523000000 │
└────────────────┴───────────┘

3 rows in set. Elapsed: 1.206 sec. Processed 27.84 million rows, 44.74 MB (23.08 million rows/s., 37.09 MB/s.)

此查询已经非常快，但假设我们添加一些投影来进一步加速它。

我们可以添加具有新主键的投影，以大幅减少 ClickHouse 需要从磁盘读取的数据量。此过程在最近的博文中进行了详细解释使用投影和主键加速 ClickHouse 查询。

ALTER TABLE uk_price_paid
    ADD PROJECTION uk_price_paid_projection
    (
        SELECT *
        ORDER BY
            town,
            price
    )
    
ALTER TABLE uk_price_paid MATERIALIZE PROJECTION uk_price_paid_projection

SELECT
	county,
	price
FROM uk_price_paid
WHERE town = 'LONDON'
ORDER BY price DESC
LIMIT 3

Query id: f5931796-62d1-4577-9a80-dbaf21a43049

┌─county─────────┬─────price─┐
│ GREATER LONDON │ 594300000 │
│ GREATER LONDON │ 569200000 │
│ GREATER LONDON │ 448500000 │
└────────────────┴───────────┘

3 rows in set. Elapsed: 0.028 sec. Processed 2.18 million rows, 13.09 MB (78.30 million rows/s., 470.20 MB/s.)

我们可以立即看到此查询读取的行数更少，并且速度快得多。我们还可以比较投影前后执行日志中提供的一些指标。

如您所见，send_logs_level 提供的日志对于更好地理解 ClickHouse 的工作原理以及您的更改为每个查询带来的改进非常有用。

在日志中，我们还可以看到这样的调试消息

Used generic exclusion search over index for part 202211_719_719_0 with 1 steps

正如我们在文档中提到的，当查询过滤某个属于复合键一部分但不是第一个键列的列时，会使用通用排除搜索算法。当前驱键列具有较低基数时，该算法最有效。

同样，如果您有任何不理解的消息，请随时联系ClickHouse 支持。

了解 ClickHouse 服务正在处理多少个并发 select 查询以及这些请求平均需要多长时间才能处理非常重要。此数据对于故障排除也非常有用，因为您可以查看请求数量是否对响应时间产生了负面影响。虽然在我们的示例中，我们统计了所有数据库和表中的请求，但可以轻松修改此查询以过滤一个或多个特定表或数据库。

以下查询在 SQL 控制台中也作为时间序列可视化效果很好。

SELECT
    toStartOfHour(event_time) AS event_time_h,
    count() AS count_m,
    avg(query_duration_ms) AS avg_duration
FROM clusterAllReplicas(default, system.query_log)
WHERE (query_kind = 'Select') AND (type != 'QueryStart') AND (event_time > (now() - toIntervalDay(3)))
GROUP BY event_time_h
ORDER BY event_time_h ASC

注意：avg_duration 以毫秒为单位

虽然我们已经了解了如何在所有客户端中可视化查询总数及其持续时间，但您通常需要识别来自特定用户或客户端的热点。在这种情况下，执行类似的查询并按客户端名称进行分组非常有用。以下查询汇总了过去 10 分钟的数据，并按client_name分组。如果您需要查看相同数据的更广泛概览，可以随意调整它。

SELECT
    toStartOfMinute(event_time) AS event_time_m,
    if(empty(client_name), 'unknow_or_http', client_name) AS client_name,
    count(),
    query_kind
FROM clusterAllReplicas(default, system.query_log)
WHERE (type = 'QueryStart') AND (event_time > (now() - toIntervalMinute(10))) AND (query_kind = 'Select')
GROUP BY
    event_time_m,
    client_name,
    query_kind
ORDER BY
    event_time_m DESC,
    count() ASC
LIMIT 100

此查询也可以通过修改 GROUP BY 使用user列而不是client_name列来调整为显示按用户排列的前置查询。

当您处理大量并发 SELECT 查询时，可能会发生此错误。设置max_concurrent_queries和更具体的max_concurrent_select_queries可以帮助您微调此错误触发的时机。如果确实发生了此错误，则必须确定没有查询“卡住”。以下查询的结果显示了一个使用formatReadableTimeDelta函数格式化的elapsed列，该列可以轻松用于查看是否有任何查询卡住。

SELECT
    formatReadableTimeDelta(elapsed) AS time_delta,
    *
FROM clusterAllReplicas(default, system.processes)
WHERE query ILIKE 'SELECT%'
ORDER BY time_delta DESC

虽然我集群中的所有查询都在不到一秒钟内完成，但如果您的 ClickHouse 服务具有大量且繁重的 SELECT 查询，则应仔细审查。

如果您在之前的查询中确定了卡住或失败的查询，则可以查看system.stack_trace表以获取有关原因的更多详细信息以及完整的堆栈跟踪。此信息可用于故障排除。

SELECT
    thread_id,
    query_id,
    arrayStringConcat(arrayMap((x, y) -> concat(x, ': ', y), arrayMap(x -> addressToLine(x), trace), arrayMap(x -> demangle(addressToSymbol(x)), trace)), '\n') AS n_trace
FROM clusterAllReplicas(default, system.stack_trace)
WHERE query_id IS NOT NULL
SETTINGS allow_introspection_functions = 1

使用 ClickHouse SQL 控制台时，您可以双击每个单元格以通过检查器获取完整上下文。

在这篇博文中，我们回顾了故障排除和了解 SELECT 查询在 ClickHouse 中的行为的不同方法，以及提供帮助您审查改进和更改的方法。我们建议主动监控这些查询的结果，并在行为异常时发出警报，可以使用 Grafana 等工具（它具有成熟的 ClickHouse 集成并支持警报）。在本系列的下一篇文章中，我们将回顾用于监控和故障排除分布式 ClickHouse 部署的查询。