ClickHouse 7 — Tối ưu hiệu năng & vận hành

Loạt bài trước đã dựng nên một hệ thống chạy được: MergeTree, nạp dữ liệu, truy vấn và Materialized View, rồi phân tán. Bài này trả lời một câu hỏi rất đời thường: dashboard đang chậm, làm sao điều tra và sửa?

Bối cảnh xuyên suốt: một dashboard giám sát giao dịch của NCB đột nhiên phản hồi 8–12 giây thay vì dưới 1 giây như trước. Ta sẽ đi theo đúng thứ tự một kỹ sư nên làm: đo trước, đoán sau — dùng system.query_log và EXPLAIN để biết query đang đọc bao nhiêu dữ liệu, rồi mới quyết định sửa ORDER BY, thêm skipping index, dùng PREWHERE, hay xử lý vấn đề part/merge/mutation ở tầng vận hành.

Lưu ý: mọi block SQL dưới đây là cú pháp ClickHouse, dùng để minh hoạ. Sandbox của app là PostgreSQL read-only nên không block nào được đánh dấu "chạy được".

Nguyên lý số một: giảm dữ liệu phải đọc

ClickHouse nhanh vì nó quét cột theo lô cực nhanh, không phải vì có phép màu index như OLTP. Do đó tối ưu hiệu năng gần như luôn quy về một câu: làm sao đọc ít byte hơn từ đĩa. Có bốn đòn bẩy, xếp theo độ tác động:

1. Loại bớt part/granule nhờ primary key — bỏ qua cả khối dữ liệu không liên quan (tác động lớn nhất).
2. Data skipping index — bỏ thêm granule dựa trên cột phụ.
3. PREWHERE — lọc sớm trước khi đọc các cột khác.
4. Chọn đúng cột (tránh SELECT *) — đọc ít cột hơn.

Ba đòn đầu giảm số dòng phải đọc; đòn cuối giảm số cột. Vì ClickHouse lưu theo cột, đọc thừa một cột nặng (ví dụ cột JSON, cột mô tả dài) có thể đắt gấp nhiều lần cột số.

Thiết kế ORDER BY / primary key khớp mẫu truy vấn

Đây là yếu tố quan trọng nhất và cũng khó sửa nhất (đổi ORDER BY phải tạo lại bảng). Nhắc lại từ bài MergeTree: trong ClickHouse, primary key mặc định chính là ORDER BY của bảng. Nó không đảm bảo tính duy nhất; nó là sparse index — cứ mỗi index_granularity dòng (mặc định 8192) ghi lại một mốc, tạo thành các granule. Khi query lọc theo tiền tố của khóa sắp xếp, ClickHouse chỉ đọc những granule có thể chứa dữ liệu.

Quy tắc thiết kế:

• Đặt cột dùng để lọc/bằng ở đầu ORDER BY. Nếu dashboard luôn lọc theo kind rồi theo thời gian, đặt ORDER BY (kind, created_at) — chứ không phải (created_at, kind).
• Tiền tố (prefix) mới được dùng cho cắt tỉa. ORDER BY (a, b, c) giúp lọc a, a AND b, a AND b AND c. Lọc riêng b hoặc c gần như không được lợi từ primary key.
• Cột lực lượng thấp (low cardinality) nên đứng trước cột lực lượng cao, vì nó gom dữ liệu thành khối lớn liền mạch.
• Có thể tách riêng PRIMARY KEY (ngắn hơn, ít cột hơn) khỏi ORDER BY (dài hơn) để index nhẹ mà vẫn giữ thứ tự lưu trữ mong muốn.

-- Dashboard luôn: WHERE kind = ? AND created_at >= ?
-- => đặt kind trước created_at
CREATE TABLE transactions
(
    id          UInt64,
    account_id  UInt64,
    amount      Float64,
    kind        LowCardinality(String),
    created_at  DateTime
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(created_at)
ORDER BY (kind, created_at, account_id);

Cách kiểm chứng nhanh xem primary key có được dùng không: xem trường read_rows trong system.query_log (mục dưới). Nếu query lọc một kind mà read_rows gần bằng tổng số dòng bảng, primary key không đang cắt tỉa như mong đợi — thường do lọc bằng biểu thức bọc quanh cột (toDate(created_at) = ... thay vì created_at >= ... AND created_at < ...), làm mất khả năng dùng index.

Data skipping index cho cột phụ

Primary key chỉ cắt tỉa theo tiền tố của ORDER BY. Với các cột không nằm trong khóa sắp xếp nhưng hay bị lọc, ClickHouse cho phép tạo data skipping index — index này không trỏ tới dòng, mà lưu thông tin tóm tắt cho mỗi khối granule để quyết định "khối này chắc chắn không chứa giá trị cần tìm → bỏ qua".

-- Lọc theo mã chi nhánh (không nằm trong ORDER BY)
ALTER TABLE transactions
    ADD INDEX idx_branch branch_code TYPE set(100) GRANULARITY 4;

-- Lọc theo mã tham chiếu lực lượng cao
ALTER TABLE transactions
    ADD INDEX idx_ref ref_no TYPE bloom_filter(0.01) GRANULARITY 1;

-- Với dữ liệu đã có, phải materialize index mới có hiệu lực
ALTER TABLE transactions MATERIALIZE INDEX idx_branch;

Vài điểm quan trọng:

• GRANULARITY n ở đây là số granule của primary key mà một khối index bao phủ, không phải số dòng. GRANULARITY 4 nghĩa là mỗi mục index tóm tắt 4 × 8192 dòng.
• Skipping index chỉ có lợi khi giá trị phân bố cục bộ (các dòng cùng giá trị nằm gần nhau trong file). Nếu giá trị rải đều khắp bảng, mọi khối đều "có thể chứa" → không bỏ qua được khối nào, index thành gánh nặng vô ích.
• Index chỉ tự áp cho dữ liệu ghi sau khi tạo; dữ liệu cũ cần MATERIALIZE INDEX.
• Kiểm chứng bằng EXPLAIN indexes = 1 — dòng của index sẽ cho biết đã lọc bao nhiêu granule (Granules: 120/4096).

PREWHERE: lọc trước khi đọc cột nặng

PREWHERE là tối ưu đặc trưng của ClickHouse. Với WHERE thường, engine đọc mọi cột cần cho query rồi mới lọc. PREWHERE đọc trước chỉ các cột trong điều kiện, loại bớt granule/dòng, rồi mới đọc các cột còn lại chỉ cho những dòng sống sót.

-- Đọc trước cột amount (nhẹ) để lọc,
-- rồi mới đọc cột metadata (JSON, nặng) cho phần còn lại
SELECT account_id, metadata
FROM transactions
PREWHERE amount > 500000000
WHERE kind = 'debit';

Trong đa số trường hợp, đặt optimize_move_to_prewhere = 1 (mặc định bật) đã tự đẩy điều kiện phù hợp từ WHERE sang PREWHERE. Bạn chỉ nên viết tay PREWHERE khi biết rõ có một điều kiện dùng cột nhẹ, lọc mạnh, mà query lại phải trả về vài cột rất nặng — khi đó ép lọc bằng cột nhẹ trước giúp tránh đọc cột nặng cho phần lớn dữ liệu.

Tránh SELECT * và giảm dữ liệu đọc

Vì lưu theo cột, SELECT * buộc ClickHouse đọc mọi cột, kể cả cột JSON/text nặng mà dashboard không hề dùng. Vài thói quen giảm byte đọc:

• Chỉ chọn cột cần dùng. Trên bảng giao dịch rộng, bỏ vài cột nặng có thể giảm dữ liệu đọc hàng chục lần.
• Lọc theo thời gian bằng khoảng nửa mở (created_at >= a AND created_at < b) thay vì bọc hàm quanh cột, để primary key và partition cùng cắt tỉa.
• Đọc từ bảng rollup thay vì bảng raw khi có thể (mẫu Materialized View ở bài 5).
• Dùng LIMIT khi chỉ cần mẫu; dùng SAMPLE nếu bảng khai báo SAMPLE BY và chấp nhận xấp xỉ.

Mutation — thao tác nặng, dùng dè dặt

Đây là cạm bẫy lớn nhất với người quen OLTP. Trong ClickHouse, ALTER TABLE ... UPDATE và ALTER TABLE ... DELETE được gọi là mutation và không phải là cập nhật tại chỗ. Mỗi mutation viết lại toàn bộ các part bị ảnh hưởng: ClickHouse đọc part cũ, áp thay đổi, ghi ra part mới, rồi thay thế. Với một part 50 GB, sửa một dòng cũng có thể viết lại gần cả part.

-- MỖI câu dưới đây là thao tác nặng, chạy nền, viết lại part
ALTER TABLE transactions UPDATE status = 'reversed' WHERE id = 12345;
ALTER TABLE transactions DELETE WHERE created_at < '2020-01-01';

Hệ quả và cách xử lý:

• Mutation chạy bất đồng bộ ở nền; theo dõi tiến độ trong system.mutations (is_done, parts_to_do). Câu ALTER trả về ngay nhưng công việc thực sự vẫn đang chạy.
• Đừng thiết kế cho update/delete lẻ tẻ. Hãy thiết kế append-only: mọi thay đổi là một dòng mới. Cần "trạng thái mới nhất" thì dùng ReplacingMergeTree + argMax, hoặc SELECT ... argMax(status, version).
• Xóa dữ liệu cũ theo thời gian: dùng TTL (TTL created_at + INTERVAL 90 DAY) hoặc ALTER TABLE ... DROP PARTITION — cả hai bỏ nguyên part/partition, rẻ hơn nhiều so với DELETE WHERE.
• Từ bản mới, có DELETE FROM với lightweight delete (đánh dấu dòng xóa qua mask thay vì viết lại ngay), nhẹ hơn cho xóa lượng nhỏ, nhưng vẫn cần merge dọn sau. Đừng lạm dụng.

Quản lý part: tránh "too many parts"

Mỗi lệnh INSERT tạo ra ít nhất một part mới trên đĩa. ClickHouse gộp part ở nền (merge), nhưng nếu tốc độ tạo part vượt tốc độ merge, số part phình lên và bạn gặp lỗi kinh điển:

DB::Exception: Too many parts (N). Merges are processing significantly slower than inserts.

Nguyên nhân số một: insert nhiều lô nhỏ, tần suất cao (ví dụ mỗi giao dịch một INSERT, hoặc mỗi message Kafka một lô vài dòng). Cách phòng và chữa:

• Gộp lô (batch) khi ghi. Đây là nguyên tắc vàng: mỗi INSERT nên chứa hàng chục nghìn đến hàng trăm nghìn dòng, tần suất cỡ giây chứ không mili-giây.
• Nếu buộc phải ghi nhỏ, bật async_insert = 1 — ClickHouse gom nhiều insert nhỏ vào buffer server-side rồi flush thành part lớn.
• Với nguồn stream, dùng Kafka table engine hoặc Buffer table làm lớp đệm gộp lô.
• PARTITION BY quá mịn cũng gây phình part (mỗi partition có part riêng). Phân vùng theo tháng (toYYYYMM) là hợp lý; theo ngày/giờ với nhiều kind dễ sinh hàng nghìn partition nhỏ.
• Kiểm tra hiện trạng: đếm part active mỗi partition trong system.parts.

Quản lý merge

Merge là quá trình nền gộp nhiều part nhỏ thành part lớn hơn (đồng thời áp ReplacingMergeTree/SummingMergeTree, xóa TTL...). Nó tiêu tốn I/O và CPU, nên đôi khi cần điều phối:

• Xem merge đang chạy trong system.merges (part nguồn, tiến độ, RAM dùng).
• Đừng lạm dụng OPTIMIZE TABLE ... FINAL. Nó ép merge toàn bộ về một part — rất nặng, chỉ nên dùng thủ công cho bảo trì, không dùng thường xuyên hay trong đường query.
• FINAL trong SELECT ... FROM t FINAL buộc gộp part lúc đọc để thấy trạng thái sau khi ReplacingMergeTree khử trùng — tiện nhưng chậm; ưu tiên argMax/GROUP BY thay thế khi có thể.
• Nếu merge "không đuổi kịp" insert, hướng đúng là giảm tốc độ tạo part (batch lớn hơn) chứ không phải ép merge mạnh hơn.

Quan sát bằng system tables và EXPLAIN

Không đo thì không tối ưu. ClickHouse phơi bày gần như mọi thứ qua các bảng system.* — đọc chúng bằng chính SQL.

system.query_log — nhật ký từng truy vấn

Bảng quan trọng nhất để điều tra query chậm. Mỗi query hoàn tất ghi một dòng với thời gian chạy, số dòng/byte đã đọc, RAM dùng:

SELECT
    query_duration_ms,
    read_rows,
    formatReadableSize(read_bytes)   AS read_bytes,
    formatReadableSize(memory_usage) AS mem,
    substring(query, 1, 80)          AS q
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time >= now() - INTERVAL 1 HOUR
ORDER BY query_duration_ms DESC
LIMIT 20;

Tín hiệu chẩn đoán: nếu read_rows rất lớn so với số dòng query thực sự cần → primary key/skipping index không cắt tỉa được, quay lại phần thiết kế ORDER BY. Nếu read_bytes lớn mà read_rows bình thường → đang đọc quá nhiều cột (SELECT * hoặc cột nặng).

system.parts, system.merges, system.metrics

-- Số part active mỗi partition — soi vấn đề "too many parts"
SELECT partition, count() AS parts, sum(rows) AS rows,
       formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE table = 'transactions' AND active
GROUP BY partition
ORDER BY partition;

• system.merges — merge đang chạy và tiến độ.
• system.mutations — mutation đang chạy, xem is_done.
• system.metrics (giá trị tức thời) và system.events (bộ đếm tích lũy) — số query đang chạy, merge đang chạy, kết nối...
• system.asynchronous_metrics — chỉ số hệ thống (RAM, cache, replica lag).

EXPLAIN — hiểu kế hoạch trước khi chạy

-- Xem index cắt tỉa được bao nhiêu granule
EXPLAIN indexes = 1
SELECT count() FROM transactions
WHERE kind = 'debit' AND created_at >= today();

-- Xem pipeline thực thi
EXPLAIN PIPELINE
SELECT kind, count() FROM transactions GROUP BY kind;

EXPLAIN indexes = 1 là công cụ đắt giá nhất: nó in ra với mỗi index (primary key và skipping index) đã chọn bao nhiêu granule trên tổng số. Con số Granules: 30/4096 cho biết ngay tối ưu có ăn hay không, mà không cần chạy query nặng.

Quản lý bộ nhớ, quota và settings profile

ClickHouse xử lý GROUP BY/JOIN/ORDER BY lớn trong RAM, nên một query xấu có thể ngốn hết bộ nhớ và làm sập truy vấn khác. Các cơ chế kiểm soát:

• max_memory_usage — trần RAM cho một query; vượt là query bị hủy với lỗi rõ ràng, thay vì OOM cả server.
• max_memory_usage_for_user — trần cho tất cả query của một người dùng.
• Spill xuống đĩa khi vượt ngưỡng, để query chậm lại thay vì thất bại: max_bytes_before_external_group_by, max_bytes_before_external_sort (đặt cỡ nửa max_memory_usage).
• max_execution_time — cắt query chạy quá lâu.

-- Đặt trần cho phiên hiện tại (minh hoạ)
SET max_memory_usage = 10000000000;              -- 10 GB / query
SET max_bytes_before_external_group_by = 5000000000;
SET max_execution_time = 30;                      -- giây

Settings profile gom sẵn các giới hạn thành hồ sơ gán cho user (ví dụ profile dashboard cho user BI với trần RAM thấp và readonly = 1); quota giới hạn theo thời gian (số query, số dòng đọc, thời gian CPU trong mỗi giờ/ngày). Nhờ đó tách người dùng BI khỏi job ETL nặng, tránh một truy vấn tham lam làm nghẽn cụm.

Backup và vận hành cơ bản

• Backup: dùng lệnh tích hợp BACKUP TABLE ... TO Disk(...) / RESTORE (ghi ra đĩa cục bộ hoặc S3). Với thao tác thủ công nhẹ, ALTER TABLE ... FREEZE PARTITION tạo hardlink snapshot bất biến của part trong thư mục shadow/ để chép đi. Có thể chép nguyên part giữa các bảng cùng cấu trúc bằng ATTACH PARTITION FROM.
• Dọn dữ liệu cũ: TTL tự động, hoặc DROP PARTITION — luôn ưu tiên hơn DELETE WHERE.
• Nạp/hủy nhanh cả partition: DETACH/ATTACH PARTITION để tách part ra khỏi bảng mà không xóa dữ liệu.
• Cấu hình quan trọng theo dõi: kích thước lô insert, số part active, replica lag (nếu dùng ReplicatedMergeTree ở bài 6), và top query chậm trong query_log.

Luồng chẩn đoán query dashboard chậm

Use case thực tế

Bối cảnh. Dashboard giám sát giao dịch của NCB (đọc trực tiếp bảng transactions raw, ~700 triệu dòng/ngày) đột nhiên chậm từ ~0,8 giây lên 8–12 giây, đúng vào đợt triển khai tính năng "lọc theo chi nhánh". Trung tâm vận hành mở điều tra.

Đo trước. Kỹ sư truy system.query_log một giờ gần nhất, lọc query chậm nhất. Query dashboard cho read_rows ≈ 620 triệu (gần cả bảng ngày) trong khi kết quả chỉ vài trăm dòng — dấu hiệu rõ ràng primary key không cắt tỉa. read_bytes ≈ 9 GB.

Chẩn đoán bằng EXPLAIN. EXPLAIN indexes = 1 cho thấy primary key (created_at, kind) chỉ lọc theo thời gian (Granules: 76000/76000 cho phần branch_code), còn điều kiện mới branch_code = 'CN-HN-01' không có index nào hỗ trợ → quét toàn bộ granule trong khung ngày. Ngoài ra query dùng SELECT *, kéo theo cả cột metadata (JSON) nặng.

Sửa theo ba bước, đo lại sau mỗi bước.

1. Bỏ SELECT *, chỉ chọn 6 cột dashboard cần. read_bytes rớt từ 9 GB xuống ~1,4 GB, thời gian còn ~4 giây.
2. Thêm skipping index idx_branch branch_code TYPE set(200) GRANULARITY 4 rồi MATERIALIZE INDEX. Vì dữ liệu chi nhánh phân bố cục bộ (Kafka nạp theo luồng chi nhánh), EXPLAIN giờ cho Granules: 340/76000 — cắt tỉa hơn 99%. Thời gian còn ~600 ms.
3. Phát hiện phụ: system.parts cho thấy partition tháng hiện tại có 3.100 part active do một job phụ đang INSERT từng dòng test. Bật async_insert cho job đó và gộp lô; sau khi merge dọn, part về ~40. Không ảnh hưởng trực tiếp query nhưng giải tỏa nguy cơ "too many parts".

Kết quả. Dashboard về ~0,5 giây, thấp hơn cả trước sự cố. Bài học rút ra được ghi thành checklist nội bộ: mọi cột lọc mới trên bảng lớn phải kèm skipping index và cấm SELECT * trong query dashboard; job ghi phải batch hoặc bật async_insert. Team cũng dựng một truy vấn query_log chạy định kỳ để cảnh báo top query có read_rows bất thường trước khi người dùng kịp phàn nàn.

Ghi nhớ

• Tối ưu ClickHouse = giảm byte phải đọc. Thứ tự đòn bẩy: primary key cắt tỉa granule > skipping index > PREWHERE > tránh SELECT *.
• ORDER BY khớp mẫu truy vấn là yếu tố số một. Đặt cột hay lọc/bằng ở đầu; chỉ tiền tố của khóa mới cắt tỉa được; tránh bọc hàm quanh cột lọc.
• Data skipping index (minmax, set, bloom_filter, tokenbf) cho cột phụ ngoài ORDER BY — chỉ hiệu quả khi giá trị phân bố cục bộ; nhớ MATERIALIZE INDEX cho dữ liệu cũ.
• PREWHERE đọc trước cột trong điều kiện, lọc dòng rồi mới đọc cột nặng; thường tự động, chỉ viết tay khi biết rõ có lợi.
• Mutation (ALTER UPDATE/DELETE) viết lại cả part — nặng, chạy nền, dùng dè dặt. Thiết kế append-only; xóa cũ bằng TTL / DROP PARTITION thay vì DELETE WHERE.
• "Too many parts" đến từ insert lô nhỏ tần suất cao và PARTITION BY quá mịn. Chữa: batch lớn, async_insert, Buffer/Kafka engine, phân vùng theo tháng.
• Đừng lạm dụng OPTIMIZE ... FINAL hay SELECT ... FINAL; merge không đuổi kịp thì giảm tốc tạo part, đừng ép merge.
• Quan sát bằng system tables: query_log (điều tra query chậm), parts (too many parts), merges/mutations (thao tác nền), metrics. Dùng EXPLAIN indexes = 1 để thấy cắt tỉa granule mà không cần chạy.
• Kiểm soát tài nguyên: max_memory_usage + spill ra đĩa (max_bytes_before_external_group_by/sort), settings profile và quota để tách BI khỏi ETL nặng.
• Backup bằng BACKUP/RESTORE hoặc FREEZE PARTITION; vận hành dữ liệu theo partition (DETACH/ATTACH/DROP) luôn rẻ hơn thao tác theo dòng.