前端之家

  1. 首页
  2. 问答

在“合并汇总数据”时，Clickhouse运行缓慢

• 问答

我正在观看Clickhouse的表现。我注意到在“合并聚合数据”阶段查询很慢。在日志中看起来像这样：

10:55:20.988391 [ 53 ] {} <Trace> HTTPHandler: Request URI: /?query_id=ef578bae-0aa1-11ea-8948-0242ac170006&database=some_db
10:55:20.993291 [ 53 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Debug> executeQuery: (from --,user: --)  --- QUERY ---
10:55:21.000491 [ 53 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Debug> some_db.od (SelectExecutor): Key condition: (column 0 in 552-element set)
10:55:21.001854 [ 53 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Debug> some_db.od (SelectExecutor): Minmax index condition: (column 0 in 552-element set)
10:55:21.018972 [ 53 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Debug> some_db.od (SelectExecutor): Selected 3 parts by date,3 parts by key,7195 marks to read from 7 ranges
10:55:21.019191 [ 53 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Trace> some_db.od (SelectExecutor): Reading approx. 58941440 rows with 4 streams
10:55:21.019396 [ 53 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Trace> InterpreterSelectQuery: FetchColumns -> Complete
10:55:21.020418 [ 53 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Debug> executeQuery: Query pipeline:
 Expression
  Expression
   ParallelAggregating
    Expression × 4
     Filter
      MergeTreeThread
10:55:21.020861 [ 53 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Trace> ParallelAggregatingBlockInputStream: Aggregating
10:55:21.027488 [ 62 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Trace> Aggregator: Aggregation method: keys128   
10:55:21.029127 [ 64 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Trace> Aggregator: Aggregation method: keys128   
10:55:21.038888 [ 56 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Trace> Aggregator: Aggregation method: keys128   
10:55:21.046746 [ 48 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Trace> Aggregator: Aggregation method: keys128   
10:55:21.116165 [ 48 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Trace> Aggregator: Converting aggregation data to two-level.
10:55:21.119995 [ 56 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Trace> Aggregator: Converting aggregation data to two-level.
10:55:21.124843 [ 64 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Trace> Aggregator: Converting aggregation data to two-level.
10:55:21.180181 [ 62 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Trace> Aggregator: Converting aggregation data to two-level.
10:55:26.468352 [ 48 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Debug> MemoryTracker: Current memory usage: 1.01 GiB.
10:55:27.356930 [ 53 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Trace> ParallelAggregatingBlockInputStream: Aggregated. 14485433 to 2196249 rows (from 221.030 MiB) in 6.336 sec. (2286233.713 rows/sec.,34.885 MiB/sec.)
10:55:27.356989 [ 53 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Trace> ParallelAggregatingBlockInputStream: Aggregated. 14929109 to 2225915 rows (from 227.800 MiB) in 6.336 sec. (2356259.030 rows/sec.,35.954 MiB/sec.)
10:55:27.357031 [ 53 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Trace> ParallelAggregatingBlockInputStream: Aggregated. 14148579 to 2173827 rows (from 215.890 MiB) in 6.336 sec. (2233068.097 rows/sec.,34.074 MiB/sec.)
10:55:27.357061 [ 53 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Trace> ParallelAggregatingBlockInputStream: Aggregated. 15344221 to 2260723 rows (from 234.134 MiB) in 6.336 sec. (2421776.094 rows/sec.,36.953 MiB/sec.)
10:55:27.357133 [ 53 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Trace> ParallelAggregatingBlockInputStream: Total aggregated. 58907342 rows (from 898.855 MiB) in 6.336 sec. (9297336.934 rows/sec.,141.866 MiB/sec.)
10:55:27.357158 [ 53 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Trace> Aggregator: Merging aggregated data       
10:55:56.117053 [ 53 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Information> executeQuery: Read 58933982 rows,1.10 GiB in 35.120 sec.,1678071 rows/sec.,32.01 MiB/sec.
10:55:56.117925 [ 53 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Trace> virtual DB::MergingAndConvertingBlockInputStream::~MergingAndConvertingBlockInputStream(): Waiting for threads to finish
10:55:56.170074 [ 53 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Debug> MemoryTracker: Peak memory usage (total): 1.64 GiB.
10:55:56.265958 [ 53 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Debug> MemoryTracker: Peak memory usage (for query): 1.64 GiB.
10:55:56.266001 [ 53 ] {ef578bae-0aa1-11ea-8948-0242ac170006} <Information> HTTPHandler: Done processing query

因此Merging aggregated data花费了35秒（83％）中的29秒。但是我找不到任何信息，这行甚至意味着什么。 Clickhouse在“合并汇总数据”时会做什么？

我检查了服务器性能，但是它没有耗尽内存或CPU时间。 CPU也无法在iowait模式下工作。因此，我只是不明白Clickhouse性能受到哪些限制。有人知道我该如何解决缓慢的merging aggregated data吗？

编辑 这是查询：

SELECT site_id_from as on,site_id_to as off,sum(cnt)/23 as cnt
FROM some_db.od
WHERE timestamp_start in ('2019-10-01 00:00:00','2019-10-01 01:00:00',...,'2019-10-31 23:00:00') -- 552 keys
GROUP BY site_id_from,site_id_to

这是表定义：

CREATE TABLE IF NOT EXISTS some_db.od (
    `timestamp_start` DateTime('Europe/Moscow'),`site_id_from` Int32,`site_id_to` Int32,`cnt` Float64
)
ENGINE = MergeTree() 
PARTITION BY toYYYYMM(timestamp_start) 
ORDER BY timestamp_start;
coolio0303 回答：在“合并汇总数据”时，Clickhouse运行缓慢

尝试将表定义更改为

CREATE TABLE IF NOT EXISTS some_db.od (
    `timestamp_start` DateTime('Europe/Moscow'),`site_id_from` Int32,`site_id_to` Int32,`cnt` Float64,INDEX ts (timestamp_start) TYPE minmax GRANULARITY 1

)
ENGINE = MergeTree() 
PARTITION BY toYYYYMM(timestamp_start) 
ORDER BY site_id_from,site_id_to;

更改排序键可以减少GROUP BY的时间 跳过索引可以减少WHERE IN搜索的时间 https://clickhouse.yandex/docs/en/operations/table_engines/mergetree/#table_engine-mergetree-data_skipping-indexes

clickhouse
本文链接：https://www.f2er.com/2972475.html

大家都在问