点击如何保存我们的数据（2020）

一些最大的广播公司使用Mux Data平台监视其最终用户的视频流体验。可以将其像Google Analytics（分析）或New Relic来播放视频。这是我们客户用来确保提供流畅视频的重要工具。

Mux Data每月处理数十亿次视频观看，并为用户提供一种在收集数据时实时查询观看的方法。在过去的几个月中，我们一直在将视频视图的存储从Citus群集过渡到ClickHouse群集，并取得了不错的效果。在这篇文章中，我将分享我们的旧架构，新的ClickHouse架构，性能改进，以及有关如何利用ClickHouse的最佳组成部分来构建更好，更强大的数据产品的一些技巧。我们的顾客。

每个视频视图都包含维度（该视图的属性）和指标（视图期间发生的情况的度量）。总共我们记录了大约100个维度和指标，这意味着生成的模式将近200列。此数据最多可以存储90天。

Mux数据的主要用途之一是查看在时间和维度上汇总的特定指标。例如，客户可以查看过去24小时内观众的重新缓冲频率，以及按操作系统细分的频率。

客户还可以深入到单个视频视图中，以查看事件的确切顺序，如下所示。

从这两个视图中，我们可以看到视图必须既可以单独查询，又可以按任意维度和时间段进行分组。让我们深入了解我们如何首先实现这一目标。

我们的原始体系结构由多个Citus数据库以及执行聚合的Airflow工作人员组成。三个Citus数据库是

视图：存储视图的所有列，用于详细视图查询和长期运行的指标聚合

然后，气流作业将每小时针对视图数据库运行一次，以每小时存储桶中的指标填充聚合。每日气流工作还将每小时的时段聚合到每天的时段中。每个数据库都由客户分片，以最大程度地减少来自一个客户的大查询对其他客户的影响。

slim_views数据库存储了数据的完整副本，但仅包括度量和过滤器列（约占所有列的一半）。这极大地提高了针对尚未汇总的时间范围（即最近45分钟）的指标查询的性能。

在旧式体系结构中，必须进行汇总才能在超过几个小时的时间内对任何查询实现可接受的性能。这是因为Citus必须扫描与该客户和时间范围相匹配的所有行，应用任何过滤器并计算所涉及的平均值或分位数。虽然聚合使系统保持运行，但它有一些大限制。

执行聚合时，必须查询并存储我们要支持的任何可能的过滤器组合。然后，聚合的基数在我们希望支持的过滤器数量中成指数增长。例如，假设我们要支持对video_title，operating_system和country的过滤。计算和存储的聚合总数将为（＃个视频标题）*（＃个操作系统）*（＃个国家/地区）。对于某些具有成千上万个值的维，聚合的大小会很快失去控制。因此，我们必须将客户限制为最多三个过滤器。

计算所有这些聚合并非易事。气流作业一直在查询slim_views以创建每小时汇总。如果有太多同时落在同一Citus碎片上的作业同时运行，则群集可能会停顿下来。这就需要手动停止所有其他作业，并一次保姆直到所有作业都完成。在这些事件中，聚合将滞后，并且必须将来自slim_views的原始数据用于要查询的时间范围的较大部分。这可能会形成负面的反馈循环，并可能导致停机，而聚合又要追溯到最后一个小时。

在每天，每小时和行级别的聚合中组合数据的逻辑在大约12,000行存储过程中进行了编码。这些存储的proc负责从分片到过滤到回落到更细粒度聚合的所有事务。为了进行简单的更改（例如添加新的维，通常是单个ALTER TABLE语句），必须对存储的proc进行推理并进行仔细编辑以使其向后兼容。我们都为更改这些存储的过程而感到恐惧。

在重访此体系结构时，我们很清楚，我们应该尽一切努力避免聚合的需要，而这正是通过切换到ClickHouse所实现的。

您可能想知道＆＃34; ClickHouse到底是什么？＆＃34; ClickHouse是由Yandex设计的开源分析数据库，它的运行速度非常快。 ClickHouse通过两种主要方式提高速度

面向列的压缩。在ClickHouse中，数据是按列分离，压缩和存储的。由于同一列中的连续值可能具有重复模式，因此与基于行的存储系统相比，这压缩得非常好。

稀疏索引。将数据部分写入磁盘时，该部分中的行按主键排序，并且每第n行的主键以及该行部分的偏移量都被写入稀疏索引。默认情况下，n = 8192，因此索引比主键列小数千倍，并且可以保存在内存中。 ClickHouse文档对稀疏索引以及如何搜索和维护稀疏索引进行了很好的解释。

考虑到所有这些，我们从保持体系结构尽可能简单开始：将所有数据转储到一个称为视图的表中。无气流。无聚集。没有存储过程。任何存储逻辑都将在读取时写入SQL查询中，并即时进行计算。

令我们惊讶的是，这非常有效。我们可以查询较大的数据时间范围，并指定任意数量的过滤器。当数据具有非常宽的行（例如我们的视图架构）时，ClickHouse的列式存储将带来巨大的好处。即使有200列，也只需要从磁盘读取与正在使用的过滤器和要聚合的指标相对应的列。

当我们着手重新实现指标后端时，我们的目标是使所有首页查询的p95延迟小于一秒。这包括侧栏上的时间序列图，细分和迷你图。重新执行这些查询时，每个过滤器，维度或指标选择都会阻止。有了用于探究数据的产品，优化这些查询对于提供交互式感觉至关重要。

在开发过程中，我们发现ClickHouse是如此高效，以至于我们开始测量查询的p99延迟而不是p95，目的是使所有首页查询尽可能接近一秒钟。为了测试性能，我们将所有生产查询都镜像到Citus和ClickHouse，但进行了验证并丢弃了ClickHouse响应。这样，我们测量了各个查询之间的性能差异，以捕获任何潜在的回归。下面是两周反映所有生产负荷的图表。

即使在进行所有汇总时，ClickHouse在所有查询上的表现也至少要比Citus高出2倍。

复杂的查询，例如＆＃34; Insights＆＃34;发送到Citus时，客户端经常会超时，但是ClickHouse会在5秒内完成绝大多数操作。

尽管是面向列的并且使用稀疏索引，但ClickHouse在检索单行方面仍比Citus快。我将在稍后介绍如何优化此查询。

不用说，我们对新系统的性能感到非常满意。以我的经验，架构师系统将规模增加约10倍是最容易处理的。 ClickHouse对我们非常成功，如集群的稳态CPU使用率所示：

在此迁移过程中，我们获得了许多博客文章-有价值的知识，在这里我将分享一些值得注意的经验。

ClickHouse的列式索引和稀疏索引方面使查询单行数据的效率降低，尤其是当查询的内容不在主键中时（这决定了稀疏索引的排序顺序）。

在我们的案例中，我们既要支持通过单个视图ID进行查询，又要列出给定用户ID的所有视图。但是，我们的主键的格式为（customer_id，view_time），因此对于给定客户的用户ID或视图ID的幼稚查询将不得不扫描该ID的所有view_times。

早期的测试表明，幼稚的查询非常昂贵，平均花费几秒钟。幸运的是，我偶然发现了Percona撰写的这篇很棒的博客文章，该文章解释了如何使用ClickHouse实例化视图作为索引，尽管我不建议您将ClickHouse用作您的“主要运营数据库”。刚刚呢

基本思想是创建另一个表作为索引，其主键等于要在其上建立索引的字段。除了索引列，您还将该行的主键存储在原始表中。要查找单行，请首先通过id搜索物化索引以在原始表中检索该行的主键。然后使用该主键前缀搜索原始表。

例如，在我们的例子中，主表的主键是（customer_id，view_time）。要在user_id上创建索引，我们创建一个带主键（customer_id，user_id）和附加列view_time的user_id_index表。 user_id_index表的实例化视图存储写入主视图表的每个视图的customer_id，user_id和view_time。然后，要搜索特定视图（customer_id，user_id）的所有视图，我们在user_id_index中搜索所有对应的view_times，然后使用这些view_times查询视图表。这可以全部包装成一个查询，例如

注意，由于view_time不是唯一值，因此在索引查询和主表查询中都必须重复任何其他WHERE子句。

使用物化索引非常有效，使我们的平均单行查询延迟从几秒钟降低到仅75ms。

Citus中另一个相对简单的操作是在数据更改时更新行。在我们的系统中，恢复视图时需要这样做。例如，用户暂停视频，五分钟后返回，然后观看其余视频。我们不想从该视图的开始就延迟数据，因此我们将视图的开始一直存储到数据库中的暂停为止，然后在发生恢复时更新该行。

但是，ClickHouse没有与SQL UPDATE语句相同的概念。数据部分被视为行的不可变组（按每列拆分）。为了更改单个值，ClickHouse必须重写整个数据部分以及相应的稀疏索引偏移量。数据部分很容易就是千兆字节的数据，因此对每个视图恢复执行此操作都将非常昂贵。

相反，ClickHouse实现了一些表引擎，这些表引擎允许对行的更新最终合并为一行。我们选择了CollapsingMergeTree，其中在插入时为每一行分配的符号为1或-1。合并数据部分时，具有相同主键和相反Sign的行会互相抵消，因此将被省略。

我们修改了汇总/插入管道，以在恢复视图时存储写入到ClickHouse的最后一个状态。当最终将更新的视图写入ClickHouse时，也会以-1的符号写入旧状态。

在视图的正向和负向行都合并到同一数据部分之前，它们将共存于ClickHouse中。这意味着查询将是重复计算的视图，除非调整查询，否则这些查询将继续进行，这可以使用“符号”列进行。为了计算COUNT（），我们改为计算SUM（Sign）。类似地，AVG（metric）改为计算为SUM（metric * Sign）/ SUM（Sign）。尽管这给我们的查询层增加了一些复杂性，但它使视图恢复的性能与普通插入一样。

最后，将我们的长期分析存储转换为ClickHouse非常值得。它确实确实感觉像是为21世纪设计的数据库，并支持Protobuf和Kafka等现代格式和集成。

我们已经在Kubernetes集群内部生产了ClickHouse，并完成了分片和复制以消除任何单点故障。我希望将来与您分享更多的运营和部署方面的经验。

下一步，我们一直在寻找进一步利用ClickHouse的方法，包括使用物化视图优化查询，用AggregatingMergeTrees代替汇总过程以及构建高效的实时查询层。

如果您对这些问题感兴趣，请绝对在mux.com/jobs上查看我们的空缺职位！ 参与的另一种好方法是参加Altinity的Robert Hodges（丰富的ClickHouse知识）组织的ClickHouse聚会。 希望您喜欢这个帖子！ 随着我们继续将Mux Data提升到新的水平，将来请我们寻找更多的ClickHouse故事，技巧和窍门。