DENIC 通过 ClickHouse 将查询时间缩短了 10 倍

我们欢迎 DENIC 的数据科学产品负责人 Christian Petrasch 和业务数据分析师 Robin Gontermann 作为嘉宾来到我们的博客。 继续阅读以了解 DENIC 为什么选择 ClickHouse 来支持他们的数据科学平台，从而能够分析来自各种来源的数据。

DENIC eG (德国网络信息中心) 管理和运营互联网上的德国命名空间 .de。 凭借超过 1720 万个域名，它是世界上最大的顶级域名注册局之一。 DENIC 以非营利为基础运营，并提供支持对 .de 顶级域名下的网站和网络服务进行快速、安全和可靠访问的服务。 DENIC 运营着一个全球分布式名称服务器网络，并负责注册管理，包括域名数据库、注册系统和 .de 域名的信息服务。

为了不断改善互联网社区的用户体验，DENIC 越来越关注数据分析。 数据科学团队创建了一个特殊的数据分析集群，能够整合和分析来自各种信息来源的数据。

我们分析相关的数据目前分布在关系数据库、服务器日志数据和各种其他来源中。 这些来源已用于监控和系统改进。 这些工具的分析功能有限，跨各种来源的交叉评估成本高昂或根本不可行，因为它们通常必须手动执行和评估。

在开发数据科学平台的最初步骤中，使用了基于关系型数据库管理系统 (DBMS) 的数据库。 来自不同数据源的数据由 Kubernetes 上的容器中的 Python 代理进行整合，结果被写入数据库中的目标表，这些表充当 Grafana 仪表板的数据源。

这种方法导致了相当多的目标表和容器，一方面难以管理，另一方面也变得有些过于复杂。

关系数据库也仅在有限程度上适用于更大量的数据，因为查询的处理时间可能需要几分钟到几小时。 这对于仪表板来说显然太长了，并且也会以可能导致生产限制的方式给注册数据库带来压力。

由于数据科学平台在 DENIC 仍处于早期阶段，并且团队规模很小，当时只有三个人，因此必须考虑管理工作量以及解决方案的性能。 使用基于文件的数据库（如 Hadoop）和分布式数据处理系统（如 Spark）的初步测试显示出可靠的性能，但管理工作量太大。 云解决方案减少了管理工作量，但由于数据隐私保护而无法考虑。

因此，必须创建一个新的平台，该平台高效、产生的管理工作量少，同时运营成本效益高。

互联网研究表明，面向列的数据库可能会成功。 这些类型的数据库专为对大量数据进行快速查询而设计，这对于我们的目的来说似乎非常有前景。 通过一些案例研究和会议演示，数据科学团队了解了 ClickHouse。

初步测试和 PoC 表明，ClickHouse 非常好地满足了 DENIC 在集群运营方面的要求，并且只需要很小的服务器占用空间，这使其具有成本效益。

Datascience@DENIC 的设置如下：

我们的用例之一是 ClickHouse 表的设计，该表由 DENIC 注册数据库的多个实体提供数据。 数据（每天 <1,000,000 条记录）由注册数据库的 REST 接口以时间序列事件的形式提供，并由 Python 代理每天获取、处理并写入 ClickHouse 集群。

根据注册数据库的数据结构，例如，域和关联的持有人数据由 REST 接口单独提供。 但是，为了更好地利用 ClickHouse 集群的优势，域和持有人信息以非规范化的方式写入表中。 每个域最多有 5 个持有人，任何数量的域都属于一个持有人。 由于事件的域相关信息将映射在一行中，因此关联的持有人信息必须存储在数组中。 仅当域或其持有人属性发生更改时，才会写入域的新行条目。

这意味着每个域都有自己的单独时间戳，从而形成自己的时间序列。 与常见的时间序列问题（具有少量不同的时间序列但有许多时间戳）相比，DENIC 的时间序列建模如下：

大量不同的域 (>20,000,000)，每个域的时间戳相对较少 (<1000)。 总的来说，Cickhouse 集群中表的数据结构如下：

CREATE TABLE IF NOT EXISTS database.domain_table ON CLUSTER ds_cluster
(
  domain String,
  reg_acc_id Nullable(Int32),
  previous_reg_acc_id Nullable(Int32),
  contact_code Array(String),
  denic_code Array(String),
  holder_country_code Array(String),
  holder_city Array(String),
  holder_address Array(String),
  holder_postal_code Array(String),
  holder_name Array(String),
  holder_person_type Array(String),
  holder_person_type_was_incorrect Array(UInt8),
  holder_company_type Array(String),
  addresscheck_correction_code Array(String),
  addresscheck_address Array(String),
  addresscheck_postal_code Array(String),
  addresscheck_city Array(String),
  domain_ace String,
  domain_category Array(String),
  is_idn_domain UInt8,
  is_locked UInt8,
  is_vchecked UInt8,
  verified_by Int32,
  registration_status LowCardinality(String),
  event LowCardinality(String),
  business_operation Array(String),
  rr_dnskey_algorithm Array(Int16),
  rr_dnskey_flags Array(Int16),
  rr_dnskey_protocol Array(Int16),
  rr_mx_owner Array(String),
  rr_mx_dname Array(String),
  rr_mx_preference Array(Int16),
  rr_a_owner Array(String),
  rr_a_address Array(String),
  rr_aaaa_owner Array(String),
  rr_aaaa_address Array(String),
  rr_ns_dname Array(String),
  rr_ns_address Array(String),
  rr_ns_ip_type Array(Int16),
  timestamp DateTime64(6,'Europe/Berlin')
)
 ENGINE ReplicatedMergeTree('/ch/database/tables/{layer}-{shard}/event_data', '{replica}')
 PARTITION BY substring(domain, 1, 1)
 ORDER BY (domain, timestamp);

为了创建这种数据结构，Python 代理必须链接两个实体：域和持有人。 如果持有人信息发生更改，则必须从 ClickHouse 集群中选择相应的有效域状态，并作为具有更新后的持有人信息的新条目写入数据库。 此查询非常耗时，占用了代理总运行时间的大部分。

SELECT *
FROM database.domain_table_distributed
WHERE hasAny(denic_code, ['id1','id2',...]) AND ((domain, timestamp) GLOBAL IN (
    SELECT
        domain,
        max(timestamp)
    FROM database.domain_table_distributed
    GROUP BY domain
))

hasAny 过滤器中的“id1”,… 涵盖了当天更新的持有人的 ID。 每天最多更新数千名持有人。

为了不对注册数据库造成如此大的负载，ClickHouse 集群的初始填充必须以逐日迁移的方式进行。

在保存了数百万条数据记录后，首先出现了明显的​​问题。 REST 接口传递的数据处理速度明显变慢。 选择与持有人更新关联的域状态的查询大约需要 5 分钟。 在调查该行为后，很明显，随着数据量的增加，这将花费越来越长的时间，从而使未来无法持续。

寻找对策使我们找到了各种选项，例如使用不同的 ClickHouse 引擎的物化视图来支持我们的用例。 这种学习曲线相当陡峭且困难，因为物化视图有一些需要考虑的特性，最终导致我们放弃了这个选项。

我们想到了创建一个物化视图，通过它查询可以提高性能。

对于第一次测试，创建了一个使用 AggregatedMergeTree 引擎的物化视图，并结合了对所需列的 argMax。 对减少的列数进行的第一次测试带来了显着的性能提升 (2 倍)。

CREATE MATERIALIZED VIEW database.aggregated_mv_test on cluster ds_cluster
ENGINE = ReplicatedAggregatingMergeTree('/ch/database/tables/{layer}-{shard}/aggregated_mv_test', '{replica}')
PARTITION BY substring(domain, 1, 1)
ORDER BY (domain)
POPULATE
AS
SELECT
  argMaxState(reg_acc_id, toFloat64(timestamp)) as reg_acc_id,
  argMaxState(contact_code,toFloat64(timestamp)) as contact_code,
  argMaxState(denic_code,toFloat64(timestamp)) as denic_code,
  argMaxState(holder_country_code,toFloat64(timestamp)) as holder_country_code,
  domain,
  argMaxState(domain_ace,toFloat64(timestamp)) as domain_ace,
  maxState(toFloat64(timestamp)) as latest_timestamp
 from
database.domain_table
group by domain;

但是，使用所有必需列的测试需要太多资源。 即使 128 GB 的 RAM 也不够，因此放弃了该选项。

对于第二次尝试，使用了 ReplicatedReplacingMergeTree 引擎。 这按域排序，并按时间戳进行版本控制。 较旧的、相同版本的数据记录（即非当前域条目）由引擎自动删除。 这会生成一个主要只包含当前数据的物化视图。 由于删除过程在后台异步运行，因此仍然需要查询中的状态确定。 此外，重要的是要确保数据不再由 Distributed 表引擎随机分布，而是将相同的域存储在同一个分片上，因为替换过程仅在同一个分片上有效。 结果是性能也得到了类似的良好提升 (2 倍)，这归因于条目数量的显着减少。 此外，总体使用的资源也少得多。

最后，我们得出的结论是，我们使用带有 ReplicatedReplacingMergeTree 引擎的普通表，该表通过插入来维护。 这也按域排序，并按时间戳进行版本控制。 相同域的较旧时间序列条目将被剔除。 如果导入数据时发生错误，Python 代理会删除错误插入的每日数据，并重新启动当天的集成过程。

这使得查询运行时间从 5 分钟缩短到 3 分钟。

已经有了很好的改进，但按照 ClickHouse 的标准仍然很长，尽管查询很复杂。 然而，随着持续填充，查询运行时间的增加变得比以前平缓得多。

我们通过 LinkedIn 直接与 ClickHouse 取得了联系，因为他们对各种用例感兴趣。 这就是我们与 ClickHouse 支持团队取得联系的方式，通过他们，我们能够缩小查询中耗时的部分。

特别是子查询与 GLOBAL IN 的结合非常耗时，因为它们扫描整个表而没有过滤器。

ClickHouse 的想法是过滤掉完整的时间序列，而不是子查询中的单个时间序列事件，并仅选择那些至少一次受到更新影响的持有人的时间序列。 此外，除了使用 max(timestamp) 之外，通过使用 LIMIT 1 BY domain，还可以节省更多查询时间。

WITH current_domains AS
    (
        SELECT *
        FROM database.current_domain_table_distributed
        WHERE domain GLOBAL IN (
            SELECT DISTINCT domain
            FROM database.current_domain_table_distributed
            WHERE hasAny(denic_code, [id1,...])
        )
        ORDER BY
            domain ASC,
            timestamp DESC
        LIMIT 1 BY domain
    )
SELECT *
FROM current_domains
WHERE hasAny(denic_code, [id1,...])

运行时显着提高到大约 1 分钟。

在进一步处理中，我们发现内置域过滤器现在是查询运行时间的原因。 特别是 denic_code 的数组过滤器对此做出了贡献。 我们最终与 ARRAY JOIN 一起解决了这个问题，以便可以使用更有效的 IN 操作来代替 hasAny。

ARRAY JOIN, so that a more effective IN operation could be used instead of hasAny.
WITH current_domains AS
    (
        SELECT *
        FROM database.current_domain_table_distributed
        WHERE domain GLOBAL IN (
            SELECT DISTINCT domain
            FROM database.current_domain_table_distributed
            ARRAY JOIN denic_code as id
            WHERE id in [id1,... ]
        )
        ORDER BY
            domain ASC,
            timestamp DESC
        LIMIT 1 BY domain
    )
SELECT *
FROM current_domains
WHERE hasAny(denic_code, [id1,...])

总而言之，查询运行时间从 5 分钟优化到大约 30 秒。 即使导入了数亿条数据记录，并且每天有数千次持有人更新，这种水平也可以保持得同样好。

ClickHouse 及其性能，即使在小型集群中，也为 DENIC 数据科学平台的开发提供了广泛的支持，由于其可扩展性，该平台在未来也将是可持续的。 我们想借此机会感谢 ClickHouse 及其支持团队的出色帮助。