ClickHouse 查詢優化詳細介紹
作者:oliverdding,騰訊 CSIG 測試開發工程師
ClickHouse 是 OLAP(Online analytical processing)數據庫,以速度見長 [1]。ClickHouse 爲什麼能這麼快?有兩點原因 [2]:
-
架構優越
-
列式存儲
-
索引
-
數據壓縮
-
向量化執行
-
資源利用
-
關注底層細節
但是,數據庫設計再優越也拯救不了錯誤的使用方式,本文以 MergeTree 引擎家族爲例講解如何對查詢優化。
ClickHouse 查詢執行過程
⚠️ 本節基於 ClickHouse 22.3 版本分析
clickhouser-server
啓動後會在 while 循環中等待請求,接收到查詢後會調用executeQueryImpl()
行數構建 AST、優化並生成執行計劃 pipeline,最後在executeImpl()
中多線程執行 DAG 獲取結果,這篇文章只關心 SQL 執行,省略掉網絡交互部分,查詢執行流程如下圖所示:
SQL 的解析優化和編譯原理息息相關,本節將包含大量編譯原理和代碼細節,屬擴展知識。
詞法解析和語法解析
ClickHouse 拿到需要執行的 SQL,首先需要將 String 格式的字符串解析爲它能理解的數據結構,也就是 AST 和執行計劃。構造 AST 部分代碼如下所示:
// src/Interpreters/executeQuery.cpp
static std::tuple<ASTPtr, BlockIO> executeQueryImpl()
{
// 構造Parser
ParserQuery parser(end, settings.allow_settings_after_format_in_insert);
// 將SQL轉爲抽象語法樹
ast = parseQuery(parser, begin, end, "", max_query_size, settings.max_parser_depth);
// 設置query的上下文,比如SETTINGS
...
if (async_insert)
{
...
} else {
// 生成interpreter實例
interpreter = InterpreterFactory::get(ast, context, SelectQueryOptions(stage).setInternal(internal));
// interpreter優化AST並返回執行計劃
res = interpreter->execute();
}
// 返回抽象語法樹和執行計劃
return std::make_tuple(ast, std::move(res));
}
值得一提的是,解析 SQL 生成語法樹這是編譯原理中詞法分析和語法分析部分覆蓋的事情。詞法分析只是簡單拆解數據流爲一個個 token,而語法分析分爲自頂向下和自底向上兩種方式,常見的語法分析方式也分爲手寫語法分析(往往是自頂向下的有限狀態機,遞歸下降分析)和語法分析工具(往往是自底向上,如 Flex、Yacc/Bison 等)。
手寫語法分析比起語法分析工具有幾個優勢(當然要寫得好的情況):
-
性能更好。可以優化熱點路徑等
-
診斷和錯誤恢復更清晰明瞭。手寫狀態機可以完全掌控系統狀態,錯誤處理更容易
-
簡單。不需要掌握新語法
ClickHouse 解析 SQL 的函數如下所示:
// src/Parsers/parseQuery.cpp
ASTPtr tryParseQuery()
{
// 將SQL拆分爲token流
Tokens tokens(query_begin, all_queries_end, max_query_size);
IParser::Pos token_iterator(tokens, max_parser_depth);
// 將token流解析爲語法樹
ASTPtr res;
const bool parse_res = parser.parse(token_iterator, res, expected);
return res;
}
可以看到先將 SQL 字符串拆解爲 token 流(詞法分析),再調用perser.parse()
函數進行語法分析,它的實現如下:
// src/Parsers/ParserQuery.cpp
bool ParserQuery::parseImpl(Pos & pos, ASTPtr & node, Expected & expected)
{
ParserQueryWithOutput query_with_output_p(end, allow_settings_after_format_in_insert);
ParserInsertQuery insert_p(end, allow_settings_after_format_in_insert);
ParserUseQuery use_p;
ParserSetQuery set_p;
ParserSystemQuery system_p;
ParserCreateUserQuery create_user_p;
ParserCreateRoleQuery create_role_p;
ParserCreateQuotaQuery create_quota_p;
ParserCreateRowPolicyQuery create_row_policy_p;
ParserCreateSettingsProfileQuery create_settings_profile_p;
ParserCreateFunctionQuery create_function_p;
ParserDropFunctionQuery drop_function_p;
ParserDropAccessEntityQuery drop_access_entity_p;
ParserGrantQuery grant_p;
ParserSetRoleQuery set_role_p;
ParserExternalDDLQuery external_ddl_p;
ParserTransactionControl transaction_control_p;
ParserBackupQuery backup_p;
bool res = query_with_output_p.parse(pos, node, expected)
|| insert_p.parse(pos, node, expected)
|| use_p.parse(pos, node, expected)
|| set_role_p.parse(pos, node, expected)
|| set_p.parse(pos, node, expected)
|| system_p.parse(pos, node, expected)
|| create_user_p.parse(pos, node, expected)
|| create_role_p.parse(pos, node, expected)
|| create_quota_p.parse(pos, node, expected)
|| create_row_policy_p.parse(pos, node, expected)
|| create_settings_profile_p.parse(pos, node, expected)
|| create_function_p.parse(pos, node, expected)
|| drop_function_p.parse(pos, node, expected)
|| drop_access_entity_p.parse(pos, node, expected)
|| grant_p.parse(pos, node, expected)
|| external_ddl_p.parse(pos, node, expected)
|| transaction_control_p.parse(pos, node, expected)
|| backup_p.parse(pos, node, expected);
return res;
}
可以發現 ClickHouse 將 Query 分爲了 18 種類型(截止 2022-11-12 日),每種 Query 都有自己的 Parser,通過關鍵詞匹配構造 AST 上的節點,最終生成語法樹。遞歸下降部分超綱了,這裏就不鋪開講。
優化器
經過語法分析後生成的 AST 並不是執行最優解,ClickHouse 包含大量基於規則的優化(rule based optimization),每個 Query 會遍歷一遍優化規則,將滿足的情況進行不改變查詢語義地重寫。
每一種 Query 類型都有對應的 Interpreter,後文都以 Select 查詢舉例,代碼如下:
// src/Interpreters/InterpreterFactory.cpp
std::unique_ptr<IInterpreter> InterpreterFactory::get()
{
...
if (query->as<ASTSelectQuery>())
{
return std::make_unique<InterpreterSelectQuery>(query, context, options);
}
...
}
在InterpreterSelectQuery
類的構造函數中將 AST 優化、重寫,代碼詳見src/Interpreters/InterpreterSelectQuery.cpp
,這裏只畫流程圖:
是否初始化 settings 優化 with 優化 joins 謂詞下推將 where 下推到 prewhere 是否要再次優化檢查 storage 權限生成 analysis_result 和 result_header
構造執行計劃
src/Interpreters/InterpreterSelectQuery.cpp
文件InterpreterSelectQuery::executeImpl()
方法將優化分析得到的中間數據輔助生成最終的執行計劃,代碼如下:
// src/Interpreters/InterpreterSelectQuery.cpp
void InterpreterSelectQuery::executeImpl()
{
...
// 個人理解針對EXPLAIN PLAN,只構建執行計劃不執行
if (options.only_analyze)
{
...
}
else
{
// 從磁盤讀取所需列,注意這一行,後文跳轉進去分析
executeFetchColumns(from_stage, query_plan);
}
if (options.to_stage > QueryProcessingStage::FetchColumns)
{
// 在分佈式執行Query時只在遠程節點執行
if (expressions.first_stage)
{
// 當storage不支持prewhere時添加FilterStep
if (!query_info.projection && expressions.filter_info)
{
...
}
if (expressions.before_array_join)
{
...
}
if (expressions.array_join)
{
...
}
if (expressions.before_join)
{
...
}
// 可選步驟:將join key轉爲一致的supertype
if (expressions.converting_join_columns)
{
...
}
// 添加Join
if (expressions.hasJoin())
{
...
}
// 添加where
if (!query_info.projection && expressions.hasWhere())
executeWhere(query_plan, expressions.before_where, expressions.remove_where_filter);
// 添加aggregation
if (expressions.need_aggregate)
{
executeAggregation(
query_plan, expressions.before_aggregation, aggregate_overflow_row, aggregate_final, query_info.input_order_info);
/// We need to reset input order info, so that executeOrder can't use it
query_info.input_order_info.reset();
if (query_info.projection)
query_info.projection->input_order_info.reset();
}
// 準備執行:
// 1. before windows函數
// 2. windows函數
// 3. after windows函數
// 4. 準備DISTINCT
if (expressions.need_aggregate)
{
// 存在聚合函數,在windows函數/ORDER BY之前不執行
}
else
{
// 不存在聚合函數
// 存在windows函數,應該在初始節點運行
// 並且,ORDER BY和DISTINCT依賴於windows函數,這裏也不能運行
if (query_analyzer->hasWindow())
{
executeExpression(query_plan, expressions.before_window, "Before window functions");
}
else
{
// 沒有windows函數,執行before ORDER BY、準備DISTINCT
assert(!expressions.before_window);
executeExpression(query_plan, expressions.before_order_by, "Before ORDER BY");
executeDistinct(query_plan, true, expressions.selected_columns, true);
}
}
// 如果查詢沒有GROUP、HAVING,有ORDER或LIMIT,會在遠程排序、LIMIT
preliminary_sort();
}
// 在分佈式執行Query時只在初始節點執行或optimize_distributed_group_by_sharding_key開啓時
if (expressions.second_stage || from_aggregation_stage)
{
if (from_aggregation_stage)
{
// 遠程節點聚合過,這裏啥也不幹
}
else if (expressions.need_aggregate)
{
// 從不同節點拉取數據合併
if (!expressions.first_stage)
executeMergeAggregated(query_plan, aggregate_overflow_row, aggregate_final);
if (!aggregate_final)
{
// 執行group by with totals/rollup/cube
...
}
// 添加Having
else if (expressions.hasHaving())
executeHaving(query_plan, expressions.before_having, expressions.remove_having_filter);
}
// 報個錯
else if (query.group_by_with_totals || query.group_by_with_rollup || query.group_by_with_cube)
throw Exception("WITH TOTALS, ROLLUP or CUBE are not supported without aggregation", ErrorCodes::NOT_IMPLEMENTED);
// 準備執行:
// 1. before windows函數
// 2. windows函數
// 3. after windows函數
// 4. 準備DISTINCT
if (from_aggregation_stage)
{
if (query_analyzer->hasWindow())
throw Exception(
"Window functions does not support processing from WithMergeableStateAfterAggregation",
ErrorCodes::NOT_IMPLEMENTED);
}
else if (expressions.need_aggregate)
{
executeExpression(query_plan, expressions.before_window,
"Before window functions");
executeWindow(query_plan);
executeExpression(query_plan, expressions.before_order_by, "Before ORDER BY");
executeDistinct(query_plan, true, expressions.selected_columns, true);
}
else
{
if (query_analyzer->hasWindow())
{
executeWindow(query_plan);
executeExpression(query_plan, expressions.before_order_by, "Before ORDER BY");
executeDistinct(query_plan, true, expressions.selected_columns, true);
}
else
{
// Neither aggregation nor windows, all expressions before
// ORDER BY executed on shards.
}
}
// 添加order by
if (expressions.has_order_by)
{
// 在分佈式查詢中,沒有聚合函數卻有order by,將會在遠端節點order by
...
}
// 多source order by優化
...
// 多條流時再次執行distinct
if (!from_aggregation_stage && query.distinct)
executeDistinct(query_plan, false, expressions.selected_columns, false);
// 處理limit
...
// 處理projection
...
// 處理offset
...
}
// 需要子查詢結果構建set
if (!subqueries_for_sets.empty())
executeSubqueriesInSetsAndJoins(query_plan, subqueries_for_sets);
}
}
其中InterpreterSelectQuery::executeFetchColumns()
函數是讀取所需列的階段。從代碼中可以看到它也做了很多的優化:
-
count()
優化 -
只有 LIMIT 情況的優化
-
quota
限制
可以看到:
-
limit 大部分情況下是計算完成後再執行,而 quota 是在讀取數據時執行的
-
加速的關鍵是減少讀入的數據量,也就是說善用索引
-
用
count()
、count(1)
和count(*)
,ClickHouse 都有優化,但不要count(any_field)
索引設計
索引是 ClickHouse 快速查詢最重要的一環,分爲主鍵索引(sparse indexes)和跳錶索引(data skipping indexes)。在執行查詢時,索引命中順序如下圖所示:
Partition Key MinMax IndexPartitionPrimary Key Sparse IndexData Skipping Indexes
詳見代碼:
// src/Processors/QueryPlan/ReadFromMergeTree.cpp
MergeTreeDataSelectAnalysisResultPtr ReadFromMergeTree::selectRangesToRead()
{
...
try
{
// 使用partition by選取需要parts
MergeTreeDataSelectExecutor::filterPartsByPartition(...);
// 處理抽樣
...
// 使用主鍵索引和跳錶索引
result.parts_with_ranges = MergeTreeDataSelectExecutor::filterPartsByPrimaryKeyAndSkipIndexes(...);
}
catch(...)
{
...
}
...
}
值得注意的是,主鍵的 sparse index 使用二分查找直接縮小範圍到所需要的 parts,而跳錶索引就需要在選出來的 parts 裏,每 n 個(用戶自定義)granules 就需要比較 n 次。
最佳實踐:
partition by 需要一個可以轉爲時間的列,比如 Datatime、Date 或者時間戳,而如果 primary key 中也有時間字段,可以使用同一個字段避免查詢時需要同時指定兩個時間字段。比如:指定爲數據處理時間。
Partition
首先要辨析 part 和 partition 的區別,ClickHouse 應用層面定義了 partition,用戶指定 partition by 關鍵詞設置不同的 partition,但是 partition 只是邏輯分區。真正存儲到磁盤時按 part 來存儲,每一個 part 一個文件夾,裏面存儲不同字段的.mrk
和.bin
文件,以及一個minmax_{PARTITION_KEY_COLUMN}.idx
文件,不同 part 的 minmax 作爲一個索引存儲於內存。
當查詢的 WHERE 帶有 partition key 時,首先會比較每一個 part 的 minmax 索引過濾不相關 parts。之後再根據 PARTITION BY 定義的規則過濾不相關 partition。
可是 partition 不是越小越好。
partitioning 並不會加速查詢(有主鍵存在),過小的 partition 反而會導致大量的 parts 無法合併(MergeTree 引擎家族會在後臺不斷合併 parts),因爲屬於不同 partition 的 parts 無法合併。[5]
最佳實踐 [6]:
-
一個 (Replicated)MergeTree 的 partition 大概 1 ~ 300GB
-
Summing/ReplacingMergeTree 的 partition 大概 400MB ~ 40GB
-
查詢時涉及儘量少 partition
-
插入時最好只有 1 ~ 2 個分區
-
一張表維持 100 個分區以內
Primary key index
主鍵是 ClickHouse 最重要的索引,沒有之一。好的主鍵應該能有效排除大量無關的數據 granules,減少磁盤讀取的字節數。
先講幾個主鍵的背景知識:
-
主鍵用於數據排序
-
ClickHouse 講數據按主鍵排序,再按
index_granularity
設置的大小(默認 8192)將數據分爲一個個 granules[7] -
每個 granules 的第一行作爲主鍵索引中的一個元素 [8]
-
查詢時在主鍵上使用二分查找跳過無關 granules[9]
-
主鍵只能通過前綴命中索引 [10]
-
每一個 part 內的
.bin
文件存儲了 n 個 granules,用.mrk
文件記錄每一個 granules 在.bin
文件的地址偏移 [11] -
ClickHouse 會在後臺不斷合併同一個 partition 的不同 parts,直到大小 / 分佈達到 “預期”
主鍵的選擇應該儘可能考慮周全,因爲主鍵是無法修改的,只能建新表後數據遷移。
最佳實踐 [12](針對 (Replicated)MergeTree 引擎):
-
選擇永遠會用於過濾條件的列
-
越重要的、基數越低的放左邊
-
主鍵中不要出現兩個高基數字段,一般最後一列可以爲總體增長的時間字段
-
將行的特徵字段加入,將相似的行放一起,提高壓縮率
-
若主鍵包含主從關係,主放左邊,從放右邊
Data skipping indexes
最後一步是跳錶索引,這個沒有太多可以講的地方,和其他數據庫相同,跳錶索引用於儘量減少讀取的行數。具體參看官方文檔。
配置優化
配置優化分爲兩部分,全局配置優化和 MergeTree 表配置優化。
全局配置優化
參看 Altinity 選擇性配置優化項。
這裏寫三個推薦的配置:
-
添加
force_index_by_date
和force_primary_key
避免全盤讀取 -
調整內存配置,參考 Altinity
-
系統表添加 TTL 和
ttl_only_drop_parts
表配置
表配置優化
除了全局配置,MergeTree 引擎家族每張表也有自己的配置項。[13]
推薦設置如下配置:
-
ttl_only_drop_parts=1
。只有 parts 中所有數據都過期了纔會 DROP,可以有效減少TTL_MERGE
發生的頻率,降低磁盤負載。 -
merge_with_ttl_timeout=86400
。配合上一項配置,將 TTL 檢查調整爲 1 天一次(默認 4 小時一次)。 -
use_minimalistic_part_header_in_zookeeper=1
。可以有效降低 Zookeeper 負載,避免 Zookeeeper 成爲性能瓶頸(插入)。
字段優化
除了索引、分區和配置外,還有表字段可以優化。接下來將講述 Schema 類型、CODEC 和緩存三個方面。
注意,儘量避免使用 Null,在 ClickHouse 中 Null 會用一個單獨 Null masks 文件存儲哪些行爲 Null[14],因此讀取某個普通字段只需要.bin
和.mrk
兩個文件,而讀取 Nullable 字段時需要.bin
、.mrk
和 masks 文件。社區查詢驗證,最高會有 2 倍性能損失。[15]
Schema 類型
使用 ClickHouse 存儲時,一般用戶都會創建大寬表,包含大量數值、字符串類型的字段。這裏提及兩種 Schema 類型 [16],沒有哪個更優越,由讀者執行評估業務適合哪一種。
平鋪字段
這是我們主表正在使用的類型,將可能用到的字段預留平鋪,除了一系列基礎字段外,增加大量metric1
, metric2
...metricN
和tag1
, tag2
...tagN
等等字段。
優點:
-
簡單
-
只讀取所需要的列,非常高效
-
每個指標、標記都可以有特殊類型
-
適合密集記錄(所有預留字段幾乎全用上)
缺點:
-
添加字段需要改變 schema
-
預留字段不能過多,最多 100 ~ 200 個
-
如果使用很稀疏,會創建大量 sparse file 字段
-
需要標識 “數據缺失” 的情況(Null 或者默認值)
-
讀取的列越多,需要讀取文件越多,IO 次數越多
arrays/nested/map 字段
這是我們 ctree 功能正在使用的類型。將業務字段塞入嵌套數據類型中,比如 array、nested struct 和 map。後文以 array 舉例:metric_array
、tag_array
。
優點:
-
動態擴展
-
ClickHouse 有大量高效的相關處理函數,甚至可以針對 Array、Map 設置索引
-
適合稀疏記錄(每行存儲少量值,儘管總基數很高)
缺點:
-
只需要其中一個 metric/tag 時,需要將整個 array 全部讀入內存
-
不通用,與其他系統交互時比較麻煩。比如 spark 使用 jdbc 時,嵌套類型無法支持比如 array(array(string))
-
不通意義的值存儲在相同字段,壓縮率變低
-
需要不同類型的預留字段時需要創建不同類型
總結
關於 Schema 設計這裏,讀者可以考慮 28 原則,理論上 80% 查詢只會用到 20% 的業務字段,因此可以將使用頻率高的業務字段平鋪,將使用頻率低的字段放入嵌套結構中。
CODEC
CODEC 分爲壓縮算法 CODEC、存儲格式 CODEC 和加密 CODEC,一般可以組合一起使用。在 ClickHouse 中,未顯示指定 CODEC 的字段都會被分配一個 DEFAULT 默認 CODEC LZ4(除非用戶修改 clickhouse 配置 compression 部分 [17])。
壓縮算法 CODEC 的選擇是一個平衡板問題,更高的壓縮度可以有更少的 IO 但是更高的 CPU,更低的壓縮度有更多的 IO 但是更少的 CPU。這需要讀者根據部署機器配置自行選擇合適的壓縮算法和壓縮等級。
這裏提供兩個判斷策略:
-
存在索引的字段可以設置更高的壓縮等級
-
用於 where 條件的字段應該設置更低壓縮等級
存儲格式 CODEC 主要是Delta
、DoubleDelta
、Gorilla
、FPC
和T64
幾種。
-
Delta
存儲行之間的變化值,適合變化較小且比較固定的列,比如時間戳。需要配合 ZSTD 使用 -
DoubleDelta
存儲Delta
的Delta
。適合變化很慢的序列 -
Gorilla
適合不怎麼變動的 integer、float 類型 [18] -
FPC
適合於 float 類型,由於我們未使用 float 字段這裏略過 -
T64
存儲編碼範圍內最大、最小值,以轉爲 64bit 存儲,適合較小的 integer 類型
擴展閱讀:
緩存
mark_cache_size
可以調整.mrk
文件的緩存大小,默認爲 5GB。適當調大可以減少查詢時 IO 次數,有效降低磁盤壓力。[19]
-
字段越多,
.mrk
文件越大 -
index_granularity
與.mrk
文件大小成負相關
可以通過如下 SQL 查詢當前所有表的 parts 信息:
SELECT
database,
table,
count() AS parts,
uniqExact(partition_id) AS partition_cnt,
sum(rows),
formatReadableSize(sum(data_compressed_bytes) AS comp_bytes) AS comp,
formatReadableSize(sum(data_uncompressed_bytes) AS uncomp_bytes) AS uncomp,
uncomp_bytes / comp_bytes AS ratio,
formatReadableSize(sum(marks_bytes) AS mark_sum) AS marks,
mark_sum / uncomp_bytes AS mark_ratio
FROM cluster(default_cluster, system.parts)
WHERE active
GROUP BY
database,
table
ORDER BY comp_bytes DESC
可以通過如下查詢獲取當天 mrk 緩存命中情況:
WITH (ProfileEvents.Values[indexOf(ProfileEvents.Names, 'MarkCacheHits')]) AS MARK_CACHE_HITS
SELECT
toHour(event_time) AS time,
countIf(MARK_CACHE_HITS != 0) AS hit_query_count,
count() AS total_query_count,
hit_query_count / total_query_count AS hit_percent,
avg(MARK_CACHE_HITS) AS average_hit_files,
min(MARK_CACHE_HITS) AS minimal_hit_files,
max(MARK_CACHE_HITS) AS maximal_hit_files,
quantile(0.5)(MARK_CACHE_HITS) AS "50",
quantile(0.9)(MARK_CACHE_HITS) AS "90",
quantile(0.99)(MARK_CACHE_HITS) AS "99"
FROM clusterAllReplicas('default_cluster', system.query_log)
WHERE event_date = toDate(now())
AND (type = 2 OR type = 4)
AND query_kind = 'Select'
GROUP BY time
ORDER BY time ASC
以及如下查詢獲取當前 mrk 緩存內存佔用情況:
SELECT formatReadableSize(value)
FROM asynchronous_metrics
WHERE metric = 'MarkCacheBytes'
以及 mrk 緩存具體緩存多少文件:
SELECT value
FROM asynchronous_metrics
WHERE metric = 'MarkCacheFiles'
除此之外,ClickHouse 還可以調整uncompressed_cache
緩存一定量原始數據於內存中。[20] 但是這個緩存只對大量短查詢有效,對於 OLAP 來說,查詢千奇百怪,不太建議調整這個配置。
業務優化
到了最難的部分,由於接下來的部分和不同業務息息相關,爲了講解我們業務上的優化,我先介紹下我們業務情況:
QAPM 主打應用性能監控,主要分爲指標、個例兩張表。個例表包含更多基礎字段,一般用戶展示;指標表主要用於聚合計算。
首先確定主鍵,毋庸置疑的前兩個一定是
-
app_id。放首位,因爲可能存在同一個產品不同功能聯動的情況,比如會話分析
-
category。放第二位,因爲功能之間獨立,大量查詢只涉及單功能
指標沒有特徵鍵值,因此只添加處理時間作爲第三個主鍵。
對於指標表,設置的主鍵爲:app_id, category, entrance_time
個例存在特徵 feature,由於:
-
大量查詢都包含 feature_md5
-
feature 是行的特徵,相同的特徵表明兩行相似,
將特徵的 md5 增加到主鍵中,用於加速查詢、提高壓縮率。但是這裏有兩個方向:
-
若 feature_md5 是高基數、大量長尾的字段
-
設置的主鍵爲:
app_id, category, intDiv(entrance_time, 3600000), feature_md5
-
若 feature_md5 基數可以降低到千、萬量級
-
設置的主鍵爲:
app_id, category, feature_md5, entrance_time
分區鍵設置爲`PARTITION BY intDiv(entrance_time, 2592000000)
鑑於SAMPLE BY
需要將 xxHash 字段放在主鍵中,主鍵都包含高基數字段,就不設置抽樣鍵,而是在需要的時候軟抽樣 [21]:
SELECT count() FROM table WHERE ... AND cityHash64(some_high_card_key) % 10 = 0; -- Deterministic
SELECT count() FROM table WHERE ... AND rand() % 10 = 0; -- Non-deterministic
插入優化
數據插入看起來和查詢性能沒什麼聯繫,但是有間接影響。不合理的插入會導致更多的寫盤、更多的數據 merge 甚至有可能插入失敗,影響讀盤性能。
聚合寫入
ClickHouse 作爲 OLAP 並不適合小批量、大併發寫入,相反而適合大批量、小併發寫入,官方建議插入數據每批次至少 1000 行,或者每秒鐘最多 1 次插入。[22]
這一小節我想強調原子(Atomic Insert)寫入的概念:一次插入創建一個數據 part。
前文提及,ClickHouse 一個 part 是一個文件夾,後臺有個 merge 線程池不斷 merge 不同的 part。原子插入可以減少 merge 次數,讓 ClickHouse 負載更低,性能更好。
原子寫入的充分條件 [23]:
-
數據直接插入
MergeTree
表(不能有 Buffer 表) -
數據只插入一個 partition(注意前文提到的 partition 和 part 的區別)
-
對於 INSERT FORMAT
-
插入行數少於
max_insert_block_size
(默認 1048545) -
關閉並行格式化
input_format_parallel_parsing=0
-
對於 INSERT SELECT
-
插入行數少於
max_block_size
-
小 block 被合併到合適的 block 大小
min_insert_block_size_rows
andmin_insert_block_size_bytes
-
MergeTree
表不包含物化視圖
這裏貼一下我們生產的配置(users.xml)。
經過統計,個例表每行大約 2KB,指標表每行大約 100B(未壓縮)。
設置min_insert_block_size_rows
爲 10000000,指標會先滿足這個條件,大概一個 block 原始大小 1GB。設置min_insert_block_size_bytes
爲 4096000000,個例會先滿足這個條件,大概一個 block 原始大小 1G,約 1024000 行。
這三個配置項是客戶端配置,需要在插入的 session 中設置,而不是在那幾個.xml
中配置。
max_insert_block_size: 16777216
input_format_parallel_parsing: 0
min_insert_block_size_rows: 10000000
min_insert_block_size_bytes: 1024000000
注意,min_insert_block_size_rows
和min_insert_block_size_bytes
是 “或” 的關係:
// src/Interpreters/SquashingTransform.cpp
bool SquashingTransform::isEnoughSize(size_t rows, size_t bytes) const
{
return (!min_block_size_rows && !min_block_size_bytes)
|| (min_block_size_rows && rows >= min_block_size_rows)
|| (min_block_size_bytes && bytes >= min_block_size_bytes);
}
讀寫分離
⚠️:本方案並沒有經過生產驗證,酌情考慮
ClickHouse 有 Shard 和 Replica 可以配置,作用如下圖所示:
所謂讀寫分離也就是將 Shard 分爲兩半,一半隻用於查詢,只要讓分佈式表查詢都導入到 Shard1 即可(在users.xml
中配置load_balancing
爲first_or_random
);一半用於寫入,插入的程序手動控制插入 Shard2 的節點,由 ClickHouse 的 ReplicatedMergeTree 不同 Shard 數據依靠 zookeeper 自動同步的策略將數據同步到 Shard1。[24]
這種策略有天然的缺陷:
-
寫的那半 Shard 持續有一定量(不會很高)的資源消耗用於寫入
-
讀的那半 Shard 會有資源消耗用於同步寫入(由於不用處理,會比直接寫入的情況資源消耗更低),但是讀請求會導致資源消耗突增
-
併發增加時性能不如混合情況,因爲讀寫分離相當於將讀資源砍半
🤔:或許可以配置兩邊 Shard 資源不一致來解決問題,比如寫入的 Shard 資源拉低,專用於處理數據插入;讀的 Shard 資源更高,專門用於處理突增併發流量。
BufferEngine
Buffer 並不推薦常規業務使用,只有在迫切需要查詢實時性 + 插入無法大批量預聚合時使用:
-
無法 atomic insert
-
即使使用 BufferEngine,數據插入也至少 1000 行每次,或者每秒鐘最多 1 次插入 [25]
KafkaEngine+MV
該部分待補充,想看的同學可以在評論區踢踢 😄
預聚合
預聚合有三種方法,ETL、物化視圖和投影,他們的區別如下 [26]:
在我們業務中,個例是不應該預聚合的,因爲數據需要被拉取展示而不用計算。指標需要聚合,數據量較大,每次實時計算對 ClickHouse 負載太大。
其實還有一種聚合方式,過期數據聚合。可以參考,同樣限制要求 group by 的鍵值爲主鍵前綴。
在我們業務使用時,什麼時候用哪一個呢?
-
需要針對某個功能加速時,可以考慮物化視圖 / 投影
-
全表預聚合加速查詢,需要使用 ETL
資源控制
最後,爲了避免集羣被某個查詢、插入弄垮,需要合理安排內存使用,需要給訪問賬戶分權限,在我們業務分爲:
-
default
:最高級賬號,不使用 -
root
:數據插入,配置聚合寫入部分的幾個配置項 -
monitor
:內部開發使用,權限較高 -
viewer
:web 使用,添加大量限制
viewer
賬戶配置如下所示:
<yandex>
<profiles>
<query>
<max_memory_usage>10000000000</max_memory_usage>
<max_memory_usage_for_all_queries>100000000000</max_memory_usage_for_all_queries>
<max_rows_to_read>1000000000</max_rows_to_read>
<max_bytes_to_read>100000000000</max_bytes_to_read>
<max_rows_to_group_by>1000000</max_rows_to_group_by>
<group_by_overflow_mode>any</group_by_overflow_mode>
<max_rows_to_sort>1000000</max_rows_to_sort>
<max_bytes_to_sort>1000000000</max_bytes_to_sort>
<max_result_rows>100000</max_result_rows>
<max_result_bytes>100000000</max_result_bytes>
<result_overflow_mode>break</result_overflow_mode>
<max_execution_time>60</max_execution_time>
<min_execution_speed>1000000</min_execution_speed>
<timeout_before_checking_execution_speed>15</timeout_before_checking_execution_speed>
<max_columns_to_read>25</max_columns_to_read>
<max_temporary_columns>100</max_temporary_columns>
<max_temporary_non_const_columns>50</max_temporary_non_const_columns>
<max_subquery_depth>2</max_subquery_depth>
<max_pipeline_depth>25</max_pipeline_depth>
<max_ast_depth>50</max_ast_depth>
<max_ast_elements>100</max_ast_elements>
<readonly>1</readonly>
</query>
</profiles>
</yandex>
同時建議設置 quota,減少大量讀盤計算、LIMIT 少量數據返回的情況發生。
我們是 CSIG 性能工程二組 QAPM 團隊,QAPM 時一款應用性能監控工具,覆蓋 android、ios、小程序、mac 和 win 多端,已有騰訊會議、優衣庫等大用戶接入,值得信賴,歡迎同事試用我們 QAPM 產品~跳轉鏈接
在 ClickHouse 優化過程遇到無數的問題,卡在 ClickHouse 自身監控無法覆蓋的角落時,全靠性能工程三組員工的 Drop(雨滴)工具的鼎力相助,高效直觀監控 CVM 各項指標,降低優化門檻,助力業務增效~跳轉鏈接
參考
[1]https://clickhouse.com/docs/en/faq/general/why-clickhouse-is-so-fast/
[2]https://benchmark.clickhouse.com
[3]https://gcc.gnu.org/wiki/New_C_Parser
[4]https://clang.llvm.org/features.html
[5]https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/#mergetree-data-storage
[6]https://kb.altinity.com/engines/mergetree-table-engine-family/pick-keys/#partition-by
[7]https://clickhouse.com/docs/en/guides/improving-query-performance/sparse-primary-indexes/sparse-primary-indexes-design/#data-is-organized-into-granules-for-parallel-data-processing
[8]https://clickhouse.com/docs/en/guides/improving-query-performance/sparse-primary-indexes/sparse-primary-indexes-design/#the-primary-index-has-one-entry-per-granule
[9]https://clickhouse.com/docs/en/guides/improving-query-performance/sparse-primary-indexes/sparse-primary-indexes-design/#the-primary-index-is-used-for-selecting-granules
[10]https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/#primary-keys-and-indexes-in-queries
[11]https://clickhouse.com/docs/en/guides/improving-query-performance/sparse-primary-indexes/sparse-primary-indexes-design/#mark-files-are-used-for-locating-granules
[12]https://kb.altinity.com/engines/mergetree-table-engine-family/pick-keys/#how-to-pick-an-order-by--primary-key
[13]https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/#settings
[14]https://clickhouse.com/docs/en/sql-reference/data-types/nullable/#storage-features
[15]https://groups.google.com/g/clickhouse/c/AP2FbQ-uoj8
[16]https://kb.altinity.com/altinity-kb-schema-design/best-schema-for-storing-many-metrics-registered-from-the-single-source/
[17]https://clickhouse.com/docs/en/operations/server-configuration-parameters/settings/#server-settings-compression
[18]http://www.vldb.org/pvldb/vol8/p1816-teller.pdf
[19]https://clickhouse.com/docs/en/operations/server-configuration-parameters/settings/#server-mark-cache-size
[20]https://clickhouse.com/docs/en/operations/caches/
[21]https://kb.altinity.com/altinity-kb-queries-and-syntax/altinity-kb-sample-by/#sample-emulation-via-where-condition
[22]https://clickhouse.com/docs/en/about-us/performance/#performance-when-inserting-data
[23]https://github.com/ClickHouse/ClickHouse/issues/9195#issuecomment-587500824
[24]https://www.jianshu.com/p/c3a4cc528ce8
[25]https://github.com/ClickHouse/ClickHouse/issues/11783#issuecomment-647778852
[26]https://kb.altinity.com/altinity-kb-schema-design/preaggregations/
本文由 Readfog 進行 AMP 轉碼,版權歸原作者所有。
來源:https://mp.weixin.qq.com/s/38RMVbw25P3iuE4IIuxdog