DENIC 使用 ClickHouse 将查询时间缩短 10 倍

Christian Petrasch 和 Robin Gontermann

2022年5月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。

初步测试和概念验证表明，ClickHouse 非常符合 DENIC 的集群操作要求，并且只需要很小的服务器占用空间，从而使其具有成本效益。

Datascience@DENIC 的设置如下

我们的一个使用案例是设计一个 ClickHouse 表，该表使用 DENIC 注册数据库的多个实体进行填充。数据（<1,000,000 条/天）由注册数据库的 REST 接口作为时间序列事件提供，并由 Python 代理每天获取、处理并写入 ClickHouse 集群。

例如，基于注册数据库的数据结构，域名和关联的持有者数据由 REST 接口分别提供。但是，为了更好地利用 ClickHouse 集群的优势，域名和持有者信息在表中被非规范化写入。每个域名最多有 5 个持有者，并且任意数量的域名属于一个持有者。由于事件的域名相关信息需要映射到一行，因此关联的持有者信息必须存储在数组中。仅当域名或其持有者属性发生更改时，才会为域名写入新的行条目。

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

大量不同的域名（>20,000,000），每个域名具有相对较少的时间戳（<1000）。总体而言，Clickhouse 集群中表的结构如下

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 的建议是在子查询中过滤掉完整的时间序列而不是单个时间序列事件，并只选择至少有一次受到更新影响的持有者的那些时间序列。此外，可以使用 LIMIT 1 BY domain 而不是 max(timestamp) 来进一步节省查询时间。

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,...])