大家好，我是奇想派，能够叫我奇奇。
当你遇到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-- #1SELECT Carrier, avg(DepDelay) AS DelayFROM ontimeGROUP BY CarrierORDER BY Delay DESC LIMIT 3;-- #2SELECT Carrier, FlightDate, avg(DepDelay) AS DelayFROM ontimeGROUP BY Carrier, FlightDateORDER 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 ontimeFORMAT VerticalRow 1:──────uniqExact(Carrier): 35uniqExact(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 = 1SELECT Carrier, avg(DepDelay) AS Delay, uniqExact(TailNum) AS AircraftFROM ontimeGROUP BY Carrier ORDER BY Delay DESCLIMIT 3-- #2SELECT Carrier, FlightDate, avg(DepDelay) AS Delay, uniqExact(TailNum) AS AircraftFROM ontimeGROUP BY Carrier, FlightDate ORDER BY Delay DESCLIMIT 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 TailArrayLenFROM ontimeGROUP BY Carrier, FlightDate ORDER BY Delay DESCLIMIT 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 = 1SET max_threads = 1SELECT Origin, FlightDate, avg(DepDelay) AS Delay, uniqExact(TailNum) AS AircraftFROM ontimeWHERE Carrier='WN'GROUP BY Origin, FlightDate ORDER BY Delay DESCLIMIT 3SET 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 DelayFROM ( 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聚合就像一辆高性能的赛车。它十分快，但你须要训练能力博得较量。做对了，你会在几分之一秒内失去后果；但做错了，你的查问会很慢或内存不足。
在这个由两局部组成的系列文章中，咱们介绍了聚合的工作原理，展现了用于查看性能的简略工具，并探讨了聚合性能。
更多内容请关注微信公众号【编程达人】，分享优质好文章，编程黑科技，助你成为编程达人！