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

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

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

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

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

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

异步插入是通过 async_insert 设置将数据插入 ClickHouse Cloud 的推荐方法。有关其工作原理和相关最佳实践的更多详细信息，请参见此处。

当使用 async_insert 时，并非在每个 INSERT SQL 查询后都会创建新的分片，而是在 async_insert_busy_timeout_ms 或 async_insert_max_data_size 设置超过时才会创建新的分片 - 这些设置控制缓冲区的刷新。

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

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

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

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

对于同步插入，除了观察创建的新分片数量外，用户还应监控已处理的插入批量请求数量和写入的行总数。这两个指标之间应该有很大差异。如果没有，则表示您每次 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 提供了通过函数分位数计算中位数的功能。因此，新的查询变为

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 引擎的准确数据。

现在我们对摄取了多少数据有了大致的了解，我们可以深入了解分区和数据块，从而更深入地了解插入操作的行为。

让我们先来定义这两个术语。

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

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

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

您可以这里了解更多关于分区的知识。

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

常见的 数据块过多 问题可能由以下几个原因造成：

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

如果您想了解更多关于此问题的信息，请查看博文 ClickHouse 入门？以下是 13 个致命错误以及如何避免它们。

现在假设您是一位 DevOps 工程师。在深入研究特定数据库或表之前，您希望先获得全局视角。在这种情况下，您需要一个查询来告诉您分区和数据块中是否存在任何热点。

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

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

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

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

现在我们知道了哪些数据块和分区存在问题，查看此数据块消耗的峰值内存使用情况就很有用。

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 表包含有关数据块生命周期中一系列事件的所有信息。event_type 列可以对这些事件进行分类。

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

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

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 查询并在用户升级之前尽早识别问题。