基本监控查询 - 第 1 部分 - INSERT 查询

在过去一年中，我们构建并发布了 ClickHouse Cloud，在 9 月份发布了 Beta 版本，并且最近在本月达到了 GA 版本。 随着我们的 Cloud 使用量增长，我们的支持团队越来越需要监控和帮助诊断客户集群上的问题。为此，我们经常依赖 ClickHouse 提供的系统表。在本博客系列中，我们分享一些常用查询，以使我们自我管理的社区受益，利用 SQL 查询来监控您的 SQL 查询！在我们的第一篇文章中，我们重点关注有关 INSERT 语句的监控数据，以解决早期的配置错误或误用问题。在后续文章中，我们将研究如何监控 SELECT 查询，以及如何尽早检测用户遇到的问题，从而为您提供更好的 ClickHouse 体验。

本文中的所有示例均在 ClickHouse Cloud 中创建，您可以在其中在几分钟内启动免费试用集群，让我们处理基础设施并开始查询！这包括访问 SQL 控制台，我们在此处使用它来可视化查询结果。

在本博客文章中，您将找到有用的 SQL 查询列表，用于监控 INSERT 语句的结果。这些查询分为两个主要类别

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

让我们快速浏览一下您将在本博客文章中看到的查询。

异步插入是通过 async_insert 设置将数据插入 ClickHouse Cloud 的推荐方式。有关它们如何工作以及随附的最佳实践的更多详细信息，请参阅此处。

当使用 async_insert 时，新 part 不是在每个 INSERT SQL 查询之后创建的，而是在超过 async_insert_busy_timeout_ms 或 async_insert_max_data_size 设置时创建的 - 这些设置控制缓冲区的刷新。

此过程将写入系统数据库中的多个有用的日志表。插入过程可视化如下，并在每个阶段指示相关日志文件。

我们可以使用以下查询来查看在过去两小时内创建了多少（以及多久）新 part。

SELECT
    count() AS new_parts,
    toStartOfMinute(event_time) AS modification_time_m,
    table,
    sum(rows) AS total_written_rows,
    formatReadableSize(sum(size_in_bytes)) AS total_bytes_on_disk
FROM clusterAllReplicas(default, system.part_log)
WHERE (event_type = 'NewPart') AND (event_time > (now() - toIntervalHour(2)))
GROUP BY
    modification_time_m,
    table
ORDER BY
    modification_time_m ASC,
    table DESC

第一个查询也适用于同步插入。

我们还可以使用 system.asynchronous_insert_log 表来查看每个异步插入的状态。以下查询的结果按 flush_time 排序，结果使我们能够确定数据何时插入（多行可能属于同一刷新，您可以按 flush_query_id 分组以使每次刷新有一行）。

SELECT
    event_time,
    query,
    database,
    table,
    bytes,
    status,
    flush_time
FROM clusterAllReplicas(default, system.asynchronous_insert_log)
ORDER BY flush_time DESC

虽然在大多数情况下，异步插入是最实用的数据插入方式，但如果用户可以在客户端批量处理插入请求或执行批量加载，则可能不会使用此方法。

对于同步插入，除了监视创建的新 part 的数量外，用户还应监视已处理的 插入批量请求的数量和写入的总行数。这些指标之间应该有很大的差异。否则，这意味着您在每个 INSERT SQL 请求中插入少量行，这可能会产生错误，例如 DB::Exception: Too many parts。

我们将使用两个不同的系统表来收集这两个指标。

1. 表 system.query_logs 将为我们提供有关 ClickHouse 服务已处理多少插入批量请求的指标 - 具体来说，对于以下查询，请参阅 nb_bulk_inserts 的值。

SELECT
    toStartOfMinute(event_time) AS event_time_m,
    count(*) AS nb_bulk_inserts
FROM clusterAllReplicas(default, system.query_log)
WHERE (query ILIKE '%insert%') AND (query_kind = 'Insert') AND (type = 'QueryFinish') AND (NOT (Settings['async_insert']) = '1') AND (event_time > (now() - toIntervalDay(3)))
GROUP BY event_time_m
ORDER BY event_time_m ASC

2. system.part_log 表提供了有关写入数据量的重要统计信息。通过此查询，我们将能够计算 total_written_rows 和 total_bytes_on_disk。此查询将主要用于故障排除或调整摄取策略。

SELECT
    toStartOfMinute(event_time) AS modification_time_m,
    table,
    sum(rows) AS total_written_rows,
    formatReadableSize(sum(size_in_bytes)) AS total_bytes_on_disk
FROM clusterAllReplicas(default, system.part_log)
WHERE (event_type = 'NewPart') AND (event_time > (now() - toIntervalDay(3)))
GROUP BY
    modification_time_m,
    table
ORDER BY
    modification_time_m ASC,
    table DESC

之前的查询将显示过去 3 天的数据。修改此设置以适应您的摄取率，例如，从现在开始的几个小时（event_time > now() - toIntervalHour(2)）。

通过此查询，我们旨在通过插入批次的数量和完成这些批次的平均持续时间来衡量插入操作的性能。通过绘制此数据，我们可以看到 ClickHouse 服务在这些操作中的性能如何。这两条线之间的关系使您可以查看系统在高负载期间的瓶颈。如果您的所有批次都具有一致的大小，则这些瓶颈将更容易关联。

这再次仅适用于同步插入，因为计时不捕获异步操作的整个事件 - 而只是缓冲阶段。

SELECT
    toStartOfMinute(event_time) AS event_time_m,
    count() AS count_batches,
    avg(query_duration_ms) AS avg_duration
FROM clusterAllReplicas(default, system.query_log)
WHERE (query_kind = 'Insert') AND (type != 'QueryStart') AND (event_time > (now() - toIntervalDay(2)))
GROUP BY event_time_m
ORDER BY event_time_m ASC

作为计算均值的替代方法，ClickHouse 提供了通过函数 quantiles 计算中位数的功能。因此，新查询变为

SELECT
    toStartOfHour(event_time) AS event_time_h,
    count() AS count_batches,
    quantile(0.25)(query_duration_ms),
    quantile(0.5)(query_duration_ms),
    quantile(0.75)(query_duration_ms),
    quantile(0.95)(query_duration_ms),
    quantile(0.99)(query_duration_ms)
FROM clusterAllReplicas(default, system.query_log)
WHERE (query_kind = 'Insert') AND (type != 'QueryStart') AND (event_time > (now() - toIntervalDay(2)))
GROUP BY event_time_h
ORDER BY event_time_h ASC

限制：此信息不反映 async_insert 或 Buffer 引擎的准确数据。

为了了解插入操作的资源消耗，我们需要深入了解一下。以下查询返回每个插入操作的内存和 CPU 使用率。这些结果使您可以查看每个批次所需的资源量。这可用于调整您的服务大小或吞吐量。

再次注意，这仅适用于同步插入。

SELECT
    event_time,
    formatReadableSize(memory_usage) AS memory,
    ProfileEvents['UserTimeMicroseconds'] AS userCPU,
    ProfileEvents['SystemTimeMicroseconds'] AS systemCPU,
    replaceRegexpAll(query, CHAR(10), ''),
    initial_query_id
FROM clusterAllReplicas(default, system.query_log)
WHERE (query_kind = 'Insert') AND (type = 'QueryFinish')
ORDER BY memory_usage DESC
LIMIT 10

限制：此信息不反映 async_insert 或 Buffer 引擎的准确数据。

现在我们对摄取了多少数据有了一个大致的了解，我们可以深入了解幕后，了解有关分区和 part 的更多信息，以更深入地了解插入操作的行为。

让我们从定义这两个术语开始

ClickHouse 中的分区是按字段值对数据进行的内部拆分。每个分区单独存储，以简化对此数据的操作。按月分区就是一个很好的例子。这将导致每个月的数据存储在同一分区中。此分区信息将捕获在文件夹名称中，从而可以快速识别磁盘上与分区关联的 part。这简化了 TTL 规则的实施，并在读取特定月份时加速了 SELECT 查询（请注意，如果查询多个月份，它也可能会减慢查询速度）。

在这里，我们将提取 VisitDate 列的月份，并按结果值对表进行分区。

CREATE TABLE visits
(
   VisitDate Date,
   Hour UInt8,
   ClientID UUID
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(VisitDate)
ORDER BY Hour;

您可以在此处阅读有关分区的更多信息。

Parts 是与每个分区关联的数据块。当数据插入到表中时，将创建单独的数据 part，并且每个数据 part 都按主键进行字典排序。主键允许有效地扫描 part。但是，如果 part 太多，则 SELECT 查询将很慢，因为需要评估更多索引并读取更多文件。

常见的 Too many parts 问题可能是由多种原因造成的，包括

• 分区键的基数过高，
• 许多小型插入，
• 过多的物化视图。

如果您有兴趣了解有关此问题的更多信息，请查看博客文章ClickHouse 入门？以下是 13 个致命罪以及如何避免它们。

现在，假设您是一名 DevOps 工程师。您希望首先有一个全局视角，然后再深入研究特定的数据库或表。在这种情况下，您需要一个查询来告诉您分区和 part 中是否有任何热点。

以下查询生成一个 MaxPartCountForPartition 值，显示您当前每个分区的最大 part 数量。如果此关键指标开始超过您的正常趋势线，您将需要识别负责的表以减少其 part 数量。

SELECT
    toStartOfMinute(event_time) AS event_time_m,
    avg(value) AS avg_MaxPartCountForPartition
FROM clusterAllReplicas(default, system.asynchronous_metric_log)
WHERE (event_time > (now() - toIntervalDay(1))) AND (metric = 'MaxPartCountForPartition')
GROUP BY event_time_m
ORDER BY event_time_m ASC

假设之前的查询已识别出潜在问题。在这种情况下，以下查询允许您更深入地挖掘，并确定每个数据库表的分区数以及每个分区中的 part 数。请记住，虽然 Clickhouse 在内部合并 part，但过多的 part 会导致更大的合并压力，从而尝试保持较低的 part 数量。这会消耗资源，并可能导致上面突出显示的错误。

SELECT
    concat(database, '.', table) AS table,
    count() AS parts_per_partition,
    partition_id
FROM clusterAllReplicas(default, system.parts)
WHERE active AND (database != 'system')
GROUP BY
    database,
    table,
    partition_id
HAVING parts_per_partition > 1
ORDER BY parts_per_partition DESC

现在我们知道哪些 part 和分区存在问题，因此查看此 part 已消耗的内存峰值使用情况非常有用。

SELECT
   event_date,
   argMax(table, peak_memory_usage) AS table,
   argMax(event_time, peak_memory_usage) AS event_time,
   formatReadableSize(MAX(peak_memory_usage)) AS max_peak_memory_usage
FROM 
   clusterAllReplicas(default, system.part_log)
WHERE
   peak_memory_usage > 0
GROUP BY
   event_date
ORDER BY
   event_date DESC

system.part_log 表包含有关 part 生命周期的所有信息，作为一系列事件。event_type 列可以对这些事件进行分类

• NEW_PART - 插入新的数据 part。
• MERGE_PARTS - 合并数据 part。
• DOWNLOAD_PART - 下载数据 part。
• REMOVE_PART - 使用 DETACH PARTITION 删除或分离数据 part。
• MUTATE_PART - 变异数据 part。
• MOVE_PART - 将数据 part 从一个磁盘移动到另一个磁盘。

error 列捕获每个事件执行的成功与否。以下查询将过滤并仅收集包含错误的 part 事件。使用响应，我们可以看到尝试的操作、表的名称和错误原因。

SELECT
    event_date,
    event_type,
    table,
    error,
    errorCodeToName(error) AS error_code,
    COUNT()
FROM clusterAllReplicas(default, system.part_log)
WHERE (database = 'default') AND (error > 0)
GROUP BY
    event_date,
    event_type,
    error,
    error_code,
    table

在下面的具体示例中，我们可以看到，当在表 visits 和 visits_o 上创建 NewPart 时，我们有重复数据删除的插入，这促使我们检查我们的摄取代码，了解我们为什么多次发送相同的数据。

您可能有不同的错误和不同的 event_types。如果您对这些错误有任何疑问，请随时在 ClickHouse Cloud 门户中创建案例。

在本博客文章中，我们展示了一些最重要的查询，用于监控您的 INSERT 操作。我们建议主动监控这些查询的结果，并在行为异常时发出警报，可以考虑使用 Grafana 等工具，它具有成熟的 ClickHouse 集成并支持警报。在本系列的下一篇文章中，我们将研究如何监控您的 SELECT 查询并在问题升级到用户之前尽早发现问题。