乐趣区

关于数据库:clickhouse聚合之内存不足怎么办那就提升聚合性能

jiezi

大家好，我是奇想派，能够叫我奇奇。

当你遇到 clickhouse 内存溢出，内存不足报错，如蕴含 Exception: Memory limit (for query)、Exception: Memory limit (total) exceeded 等，这样的谬误时候，是不是不知所措，不知如何下手，那么你就应该认真看看这篇文章啦，本文教你如何解决 clickhouse 内存溢出问题。

上一篇文章《clickhouse 聚合之摸索聚合外部机制》里次要介绍了 clickhouse 聚合时的外部机制，在本篇文章中，次要是解说如何晋升聚合性能。次要步骤是：

  • 1、先带大家对 clickhouse 理论查问进行性能测试，这样咱们能够先充沛了解以后性能的耗时和资源应用状况。
  • 2、依据理论状况，咱们再进行一系列实用技巧，给聚合性能进步一个品位。让聚合更快、更有效益。

摸索聚合性能

让咱们具体看一下聚合，并应用示例查问测试性能。咱们将扭转三个不同的方面：GROUP BY 键、聚合函数的复杂性以及扫描中应用的线程数。通过放弃示例绝对简略，咱们能够更轻松地了解围绕聚合的衡量。

分组根据中值的影响

咱们最后的示例查问有几十个 GROUP BY 值。回过头来参考用于保留扫描后果的哈希表，这意味着此类哈希表具备大量键。如果咱们应用不同的 GROUP BY 运行雷同的查问，从而生成更多的值，该怎么办？较大的密钥数量是否会对聚合的资源或速度产生影响？

这很容易测试。首先，让咱们从新运行示例查问，而后从新运行另一个具备不同 GROUP BY 的查问。

SET max_untracked_memory = 1

-- #1
SELECT Carrier, avg(DepDelay) AS Delay
FROM ontime
GROUP BY Carrier
ORDER BY Delay DESC LIMIT 3
;

-- #2
SELECT Carrier, FlightDate, avg(DepDelay) AS Delay
FROM ontime
GROUP BY Carrier, FlightDate
ORDER BY Delay DESC LIMIT 314

如果咱们当初查看 system.query_log 查问速度和内存，咱们会看到以下内容。

┌──────────event_time─┬──secs─┬─memory────┬─threads─┬─query───────────────┐
│ 2022-03-15 11:09:34 │ 0.725 │ 33.96 MiB │ 4       │ -- #2SELECT Carrier │
│ 2022-03-15 11:09:33 │ 0.831 │ 10.46 MiB │ 4       │ -- #1SELECT Carrier │
└─────────────────────┴───────┴───────────┴─────────┴─────────────────────┘

第二个查问应用的内存量存在很大差别。领有更多密钥会占用更多 RAM，至多在这种状况下是这样。导致这种状况的密钥数量有什么区别？咱们能够应用以下查问找出答案。不便的 uniqExact() 聚合计算行中值的惟一呈现次数。

SELECT
    uniqExact(Carrier),
    uniqExact(Carrier, FlightDate)
FROM ontime
FORMAT Vertical

Row 1:
──────
uniqExact(Carrier):             35
uniqExact(Carrier, FlightDate): 169368

咱们的第一个查问有 35 个 GROUP BY 键。咱们的第二个查问必须解决 169，368 个密钥。

在来到此示例之前，值得注意的是，第二个查问始终比第一个查问快约 15%。这是令人诧异的，因为您可能会认为更多的 GROUP BY 键意味着更多的工作。正如咱们在其余中央所解释的那样，ClickHouse 依据 GROUP BY 键的数量和类型抉择不同的聚合办法以及不同的哈希表配置。反过来，它们的效率会有所不同。

如何晓得？

咱们能够应用 clickhouse-client –send_logs_message=’trace’ 选项启用跟踪日志记录，而后认真查看音讯。(无关如何执行此操作的阐明，请参阅之前文章《clickhouse 聚合之摸索聚合外部机制》。它显示扫描线程实现的工夫，还批示聚合办法。上面是第二个查问中来自其中一个线程的几个典型音讯。

[chi-clickhouse101-clickhouse101-0-0-0] 2022.03.28 14:23:15.311284 [1887] {92d8506c-ae7c-4d01-84b6-3c927ed1d6a5} <Trace> Aggregator: Aggregation method: keys32

…
[chi-clickhouse101-clickhouse101-0-0-0] 2022.03.28 14:23:16.007341 [1887] {92d8506c-ae7c-4d01-84b6-3c927ed1d6a5} <Debug> AggregatingTransform: Aggregated. 48020348 to 42056 rows (from 366.37 MiB) in 0.698374248 sec. (68760192.887 rows/sec., 524.61 MiB/sec.)

在第二种状况下，扫描线程的运行速度更快，这可能意味着所选的聚合办法和 / 或哈希表实现更快。为了找出为什么咱们须要从 src/Interpreters/Aggregator.h 开始深入研究代码。在那里，咱们能够理解 keys32 和其余聚合办法。这项考察将不得不期待另一篇博客文章。

不同聚合函数的影响

avg() 函数非常简单，应用的内存绝对较少 – 基本上它创立的每个局部聚合都有几个整数。如果咱们应用更简单的函数会产生什么？

uniqExact() 函数是一个很好的尝试。此聚合存储一个哈希表，其中蕴含它在块中看到的值，而后合并它们以取得最终答案。咱们能够假如哈希表将须要更多的 RAM 来存储，并且速度也会更慢。让咱们来看看！咱们将应用大量和大量 GROUP BY 键运行以下查问。

SET max_untracked_memory = 1

SELECT Carrier, avg(DepDelay) AS Delay, uniqExact(TailNum) AS Aircraft
FROM ontime
GROUP BY Carrier ORDER BY Delay DESC
LIMIT 3

-- #2
SELECT Carrier, FlightDate, avg(DepDelay) AS Delay, 
  uniqExact(TailNum) AS Aircraft
FROM ontime
GROUP BY Carrier, FlightDate ORDER BY Delay DESC
LIMIT 3

当咱们查看 system.query_log 中的查问性能时，咱们会看到以下后果。

┌──────────event_time─┬──secs─┬─memory────┬─threads─┬─query────────────────┐
│ 2022-03-15 12:19:08 │ 3.324 │ 2.41 GiB  │ 4       │ -- #2SELECT Carrier  │
│ 2022-03-15 12:19:04 │ 2.657 │ 21.57 MiB │ 4       │ -- #1SELECT Carrier  │
. . .

增加此新聚合的执行工夫必定存在差别，但这并不奇怪。更乏味的是 RAM 的减少。对于大量的密钥，它不是很多：21.57MiB，而咱们原始示例查问中的 10.46Mib。

然而，RAM 使用量会随着更多的 GROUP BY 键而爆炸式增长。如果您思考查问，这是有情理的，该查问要求在每个组中进行航班的航空公司尾号 (即飞机) 的确切计数。将 FlightDate 增加到 GROUP BY 意味着同一架飞机被计数更屡次，这意味着它们必须保留在哈希表中，直到 ClickHouse 能够合并并进行最终计数。

因为咱们看到的是爆炸式增长，让咱们再尝试一个查问。groupArray() 函数是 ClickHouse 独有的弱小聚合，它将组中的列值收集到数组中。咱们假如它将应用比 uniqExact 更多的内存。那是因为它将蕴含所有值，而不仅仅是咱们计算的值。

上面是查问，后跟查问日志统计信息。

SELECT Carrier, FlightDate, avg(DepDelay) AS Delay,
  length(groupArray(TailNum)) AS TailArrayLen
FROM ontime
GROUP BY Carrier, FlightDate ORDER BY Delay DESC
LIMIT 3

┌──────────event_time─┬──secs─┬─memory───┬─threads─┬─query───────────────┐
│ 2022-03-15 21:28:51 │ 2.957 │ 5.66 GiB │ 4       │ -- #3SELECT Carrier │
└─────────────────────┴───────┴──────────┴─────────┴─────────────────────┘

正如预期的那样，这比 uniqExact 占用更多的内存：5.66GiB。默认状况下，ClickHouse 将终止应用超过 10GiB 的查问，因而这靠近一个有问题的级别。然而，请留神，鉴于咱们正在扫描近 200M 行，所有这些依然十分快。与以前一样，因为 ClickHouse 在每种状况下抉择不同的聚合办法和哈希表实现，因而性能也存在差别。

执行线程数的影响

对于咱们的最初一项考察，咱们将钻研线程的影响。咱们能够应用 max_threads 来管制扫描阶段的线程数。咱们将运行雷同的查问三次，如下所示：

SET max_untracked_memory = 1

SET max_threads = 1

SELECT Origin, FlightDate, avg(DepDelay) AS Delay,
  uniqExact(TailNum) AS Aircraft
FROM ontime
WHERE Carrier='WN'
GROUP BY Origin, FlightDate ORDER BY Delay DESC
LIMIT 3

SET max_threads = 2
(same query) 

SET max_threads = 4
(same query)

好吧，让咱们看一下生成的内存应用状况。我增加了正文，以便更容易分辨哪个查问是哪个查问。

┌──────────event_time─┬──secs─┬─memory───┬─threads─┬─query───────────────┐
│ 2022-03-15 22:12:02 │  0.86 │ 1.71 GiB │ 8       │ -- #8SELECT Origin, │
│ 2022-03-15 22:12:01 │ 1.285 │ 1.72 GiB │ 4       │ -- #4SELECT Origin, │
│ 2022-03-15 22:11:59 │ 2.325 │ 1.75 GiB │ 2       │ -- #2SELECT Origin, │
│ 2022-03-15 22:11:56 │ 4.408 │ 1.91 GiB │ 1       │ -- #1SELECT Origin, │
. . .

如果咱们把它们放在一个图表中，就更容易了解后果。

增加线程可显著进步聚合查问速度，最高可达一个点，在本例中为 4 个线程。在某些时候，扫描会随着线程的减少而进行减速 – 这里的状况是 4 到 8 个线程之间。乏味的是，ClickHouse 在扫描运行后并行化合并操作。如果仔细检查调试级别的音讯，则会看到这部分查问执行速度也会放慢。

增加更多线程会影响内存使用量，但可能不会像您设想的那样。在这种状况下，当线程较多时，咱们应用较少的 RAM 来解决查问。

进步聚合性能的实用技巧

咱们曾经具体钻研了聚合响应工夫和内存应用状况，这是生产 ClickHous 实现中的两大思考因素。在最初一节中，咱们将总结改良两者的实用办法。

放慢聚合速度

有多种办法能够使具备聚合的查问速度更快。以下是次要倡议。

删除或替换简单的聚合函数

应用 uniqExact 来计算产生次数？请尝试 uniq。它的速度更快，但代价是答案的精度较低。ClickHouse 还具备特定的性能，能够用更具体的实现来取代低廉的聚合。如果您正在解决网站访问者的数据以计算转化率，请尝试应用 windowFunnel。

缩小“分组根据”中的值

解决大量 GROUP BY 键可能会减少聚合工夫。如果可能，按较少的我的项目分组。

应用 max_threads 进步并行度

减少 max_threads 能够导致靠近线性响应工夫的改良，只管最终增加新线程不再减少吞吐量。此时，您正在增加的内核将被节约。

推延连贯

如果要针对数据进行连贯，则最好将关联查问推延到聚合实现后，这样能缩小初始扫描中的解决。上面是一个示例。ClickHouse 将首先运行子查问，而后将后果与表左连贯。

SELECT Dest, airports.Name Name, c Flights, ad Delay
FROM 
(SELECT Dest, count(*) c, avg(ArrDelayMinutes) ad
  FROM ontime
   GROUP BY Dest HAVING c > 100000
     ORDER BY ad DESC LIMIT 10
) a 
LEFT JOIN airports ON IATA = Dest

如果咱们间接连贯表，ClickHouse 将在主扫描期间连贯行，这须要额定的 CPU。相同，咱们只是在子查问实现后的开端退出 10 行。它大大减少了所需的解决工夫。

这种办法还能够节俭内存，但除非您向后果中增加大量列，否则成果可能不显著。该示例为每个 GROUP BY 键增加一个值，这在事物的雄伟计划中基本不是很大。

过滤不必要的数据

继上一点之后，如果解决的数据较少，则任何查问的运行速度都会更快。过滤掉不须要的行，齐全删除不须要的列，以及所有这些好货色。

应用实例化视图

具体化视图事后聚合数据以服务于常见用例。在最好的状况下，他们只需缩小须要扫描的数据量，就能够将查问响应工夫缩短 1000 倍或更多。

更高效地应用内存

应用实例化视图(再次)

咱们提到过它们是否节俭了内存？例如，将基于秒的测量值事后聚合为小时的具体化视图能够大大减少 ClickHouse 在初始扫描期间拖动的局部聚合的大小和数量。实例化视图通常也会在内部存储中应用更少的空间。

删除或替换占用大量内存的聚合函数

像 max、min 和 avg 这样的函数对内存十分简洁。相比之下，像 uniqExact 这样的函数是记忆猪。要理解跨函数的内存应用状况以计算惟一产生次数，请查看此计数惟一产生次数的函数的表，如下表。

缩小“分组”里的字段数

GROUP BY 的聚合可能会呈现一个状况，那就是随着字段数的减少，内存会爆炸式增长。如果无奈缩小 GROUP BY 中的字段数，则另一个形式是应用实例化视图对特定的 GROUP BY 组合进行预聚合。

调整内存参数

如果查问内存占用必不可免，那就批改参数，进步限度，让查问能够失常运行。ClickHouse 内存限度由以下参数管制，您能够在零碎级别或在用户配置文件中更改这些参数(具体取决于设置)。

常见谬误

User class threw exception: ru.yandex.clickhouse.except.ClickHouseException: ClickHouse exception, code: 241, host: 10.121.8.8, port: 8123; Code: 241, e.displayText() = DB::Exception: Memory limit (for query) exceeded: would use 9.31 GiB (attempt to allocate chunk of 1048591 bytes), maximum: 9.31 GiB (version 19.9.5.36)

解决波及的参数

  • max_memory_usage – 单个查问的最大内存字节数。默认值为 10GiB，这对于大多数目标来说曾经足够了，但可能不是你的。
  • max_memory_usage_for_user – 单个用户在单个工夫点的所有查问的最大字节数。默认值为无限度。
  • max_server_memory_usage – 整个 ClickHouse 服务器的最大内存。默认值为可用 RAM 的 90%。

解决方案
配置文件个别批改用户列表文件 /etc/clickhouse-server/users.xml (默认门路，具体依据本人文件目录来定)

# 这里假如 clickhouse 所在服务器最大 RAM 内存：128G
<yandex>
    <profiles>
        <default>
            <max_memory_usage>123000000000</max_memory_usage>
            <max_server_memory_usage>123000000000</max_server_memory_usage>
            ...
        </default>
    </profiles>
    <users>
    ...
    </users>
</yandex>

调整聚合参数

如果所有其余办法都失败，则能够将局部聚合转储到内部存储。应用 max_bytes_before_external_group_by 设置。规范倡议将其设置为 max_memory_usage 的 50%。这能够确保您不会在合并阶段用完。

配置文件个别批改用户列表文件 /etc/clickhouse-server/users.xml (默认门路，具体依据本人文件目录来定)

# 这里假如 clickhouse 所在服务器最大 RAM 内存：128G
<yandex>
    <profiles>
        <default>
            <max_memory_usage>123000000000</max_memory_usage>
            <max_server_memory_usage>123000000000</max_server_memory_usage>
            <max_bytes_before_external_group_by>61500000000</max_bytes_before_external_group_by>
            ...
        </default>
    </profiles>
    <users>
    ...
    </users>
</yandex>

论断

聚合是数据仓库中从大数据中提取意义的基本操作。ClickHouse 聚合就像一辆高性能的赛车。它十分快，但你须要训练能力博得较量。做对了，你会在几分之一秒内失去后果；但做错了，你的查问会很慢或内存不足。

在这个由两局部组成的系列文章中，咱们介绍了聚合的工作原理，展现了用于查看性能的简略工具，并探讨了聚合性能。

更多内容请关注微信公众号【编程达人】，分享优质好文章，编程黑科技，助你成为编程达人！

退出移动版