ソートキー外の条件 — インデックスとプロジェクションでスキップする

한국어 원문으로 표시합니다.

목표

정렬 키가 ts 인 로그 표에서 키 밖의 열로 거르는 쿼리를 건너뛰기 인덱스와 프로젝션으로 줄이고, 각각이 언제 통하고 언제 통하지 않는지, 무엇을 대가로 치르는지를 EXPLAIN·rows_read·system 표로 확인한다.

왜 중요한가

건너뛰기 인덱스는 이름과 달리 B-트리가 아니라 그래뉼 묶음의 요약입니다. 자료의 분포가 맞지 않으면 한 그래뉼도 건너뛰지 못하고, 기존 파트에는 MATERIALIZE 하기 전까지 아무 효과가 없습니다. 프로젝션은 쿼리를 고치지 않고 다른 정렬 순서를 쓰게 해 주지만 디스크를 크게 씁니다. 이 실습의 채점기는 여러분이 적은 숫자를 믿지 않습니다 — 저장한 SELECT 를 읽기 전용으로 다시 돌리되, 조건 캐시를 끄고, 인덱스를 끈 채로도 돌려 줄어든 행이 정말 그 인덱스·프로젝션 덕분인지 확인합니다.

단계

데이터베이스 skp 와 표 skp.logs 를 만드세요 — 열 ts DateTime, seq UInt64, service LowCardinality(String), user_id UInt32, trace_id UInt64, latency_ms UInt32, path String (이 순서), MergeTree, ORDER BY ts. /opt/lab/fixtures/skip/logs.sql 로 100만 행을 한 번 넣고 OPTIMIZE TABLE skp.logs FINAL 로 파트를 하나로 만드세요.
ALTER TABLE skp.logs ADD INDEX idx_trace trace_id TYPE bloom_filter GRANULARITY 1 을 실행하고 (MATERIALIZE 는 아직 하지 말고), EXPLAIN indexes = 1 SELECT count(), sum(latency_ms) FROM skp.logs WHERE trace_id = 12249378055674284861 의 출력을 /root/ch/skip/explain_before.txt 에 저장하세요.
MATERIALIZE INDEX idx_trace 를 mutations_sync = 2 로 실행하고, 위의 SELECT 를 /root/ch/skip/q_trace.sql 로 저장한 뒤, 조건 캐시를 끄고 잰 rows_read 와 그 뮤테이션의 mutation_id 를 /root/ch/skip/trace.json 에 적으세요.
seq 에 idx_seq, user_id 에 idx_user 라는 minmax 인덱스(GRANULARITY 1)를 추가하고 MATERIALIZE 하세요. seq BETWEEN 1001500000 AND 1001520000 인 행의 sum(latency_ms) 를 구하는 /root/ch/skip/q_seq.sql 과 user_id BETWEEN 100 AND 120 인 행의 sum(latency_ms) 를 구하는 /root/ch/skip/q_user_range.sql 을 만들고, 두 쿼리의 rows_read 를 /root/ch/skip/minmax.json 에 seq_rows_read·user_rows_read 로 적으세요.
프로젝션 p_user (SELECT * ORDER BY user_id) 를 추가하고 MATERIALIZE 하세요. user_id = 4242 인 행의 count(), sum(latency_ms) 를 구하는 /root/ch/skip/q_user.sql 을 만들고, 프로젝션을 쓴 rows_read 와 --optimize_use_projections 0 으로 끈 rows_read 를 /root/ch/skip/proj.json 에 with_projection·without_projection 으로 적으세요.
5단계의 쿼리를 SETTINGS log_comment = 'chs-skip-06' 을 붙여 돌린 뒤, system.query_log 의 끝(QueryFinish) 기록에서 query_id·read_rows·projections 를 /root/ch/skip/qlog.json 에 적으세요.
집계 프로젝션 p_svc_hour (SELECT service, toStartOfHour(ts), count(), avg(latency_ms) GROUP BY service, toStartOfHour(ts)) 를 추가하고 MATERIALIZE 하세요. 서비스별 service, count(), round(avg(latency_ms), 3) 을 서비스순으로 내는 /root/ch/skip/q_svc.sql 을 만들고, 그 rows_read 와 system.projection_parts 의 p_svc_hour rows 를 /root/ch/skip/agg.json 에 rows_read·projection_rows 로 적으세요.
활성 파트의 bytes_on_disk(part_bytes), 두 프로젝션 파트의 bytes_on_disk(p_user_bytes·p_svc_hour_bytes), 두 인덱스의 data_compressed_bytes(idx_trace_bytes·idx_seq_bytes)를 /root/ch/skip/cost.json 에 적으세요.

참고

읽은 행은 clickhouse-client --queries-file 파일.sql --format JSON --use_query_condition_cache 0 | jq .statistics 로 잽니다. 조건 캐시를 켜 두면 같은 쿼리의 두 번째 실행이 0행을 읽었다고 나올 수 있습니다.
ADD INDEX·ADD PROJECTION 은 정의만 바꿉니다. 이미 있는 파트에는 MATERIALIZE INDEX·MATERIALIZE PROJECTION 이 필요하고, 이것은 뮤테이션이라 SETTINGS mutations_sync = 2 를 주면 끝날 때까지 기다립니다.
EXPLAIN 의 Skip 칸에서 Granules: 남은/전체 를 읽습니다. 26.8 은 기본으로 나무 모양(├──)으로 찍습니다.
흔한 실수: 쿼리에 ts 조건을 섞어 정렬 키가 대신 걸러 주는 것(채점기는 인덱스를 끄고도 다시 잽니다), 2단계 전에 MATERIALIZE 해 버리는 것.
공식 문서: Understanding data skipping indexes · Use data skipping indices where appropriate · Projections · Materialized views versus projections · EXPLAIN · system.data_skipping_indices · system.projection_parts

시각으로 정렬한 로그 표를 만든다

데이터베이스 skp 와 표 skp.logs 를 만드세요. 열 ts DateTime, seq UInt64, service LowCardinality(String), user_id UInt32, trace_id UInt64, latency_ms UInt32, path String 순서, MergeTree, ORDER BY ts. /opt/lab/fixtures/skip/logs.sql 로 100만 행을 한 번 넣고 OPTIMIZE TABLE skp.logs FINAL 로 파트를 하나로 만드세요.

파트를 하나로 합쳐 두면 그래뉼 수가 하나로 정해져 인덱스 전후를 같은 눈금으로 비교할 수 있습니다. 100만 행이면 8192행 그래뉼이 123개입니다. seq 는 시간과 함께 커지고, user_id 는 시간과 무관하게 고루 퍼져 있고, trace_id 는 행마다 다릅니다.

인덱스를 정의만 했을 때의 EXPLAIN

ALTER TABLE skp.logs ADD INDEX idx_trace trace_id TYPE bloom_filter GRANULARITY 1 을 실행하세요(MATERIALIZE 는 아직 하지 않습니다). 그리고 EXPLAIN indexes = 1 SELECT count(), sum(latency_ms) FROM skp.logs WHERE trace_id = 12249378055674284861 의 출력을 /root/ch/skip/explain_before.txt 에 저장하세요.

EXPLAIN 출력의 Indexes 아래에 PrimaryKey 칸과 Skip 칸이 있습니다. Skip 칸의 Granules 가 남은/전체입니다. 기존 파트에는 인덱스 파일이 아직 없어서 인덱스가 아무것도 걸러 내지 못합니다 — system.data_skipping_indices 의 크기도 확인해 보세요.

MATERIALIZE 한 뒤 읽은 행

ALTER TABLE skp.logs MATERIALIZE INDEX idx_trace SETTINGS mutations_sync = 2 를 실행하고, 2단계의 SELECT(EXPLAIN 없이)를 /root/ch/skip/q_trace.sql 로 저장하세요. 조건 캐시를 끄고 잰 statistics.rows_read 를 rows_read, system.mutations 에서 찾은 그 뮤테이션의 id 를 mutation_id 로 /root/ch/skip/trace.json 에 적으세요.

MATERIALIZE INDEX 는 모든 파트에 인덱스 파일을 새로 쓰는 뮤테이션입니다 — 파트 이름 끝에 뮤테이션 번호가 붙는 것을 보세요. 블룸 필터는 없다고 확답할 수 있는 그래뉼만 버리므로, 찾는 행이 한 줄이어도 거짓 양성 그래뉼 몇 개를 더 읽을 수 있습니다.

같은 minmax, 다른 효과

seq 에 idx_seq, user_id 에 idx_user 라는 minmax 인덱스(GRANULARITY 1)를 추가하고 둘 다 MATERIALIZE 하세요. seq BETWEEN 1001500000 AND 1001520000 인 행의 sum(latency_ms) 를 구하는 /root/ch/skip/q_seq.sql 과 user_id BETWEEN 100 AND 120 인 행의 sum(latency_ms) 를 구하는 /root/ch/skip/q_user_range.sql 을 만들고, 두 쿼리의 rows_read 를 /root/ch/skip/minmax.json 에 seq_rows_read·user_rows_read 로 적으세요.

minmax 는 그래뉼마다 최솟값과 최댓값만 적습니다. 정렬 키(ts)와 함께 커지는 열이면 그래뉼마다 범위가 좁아 조건 밖의 그래뉼이 대부분 빠지고, 시간과 무관하게 고루 퍼진 열이면 모든 그래뉼의 범위가 거의 전체라 하나도 빠지지 않습니다.

다른 정렬 순서의 숨은 사본

ALTER TABLE skp.logs ADD PROJECTION p_user (SELECT * ORDER BY user_id) 뒤 MATERIALIZE PROJECTION p_user 를 mutations_sync = 2 로 실행하세요. user_id = 4242 인 행의 count(), sum(latency_ms) 를 구하는 /root/ch/skip/q_user.sql 을 만들고, 조건 캐시를 끄고 잰 rows_read 를 with_projection, 여기에 --optimize_use_projections 0 을 더해 잰 값을 without_projection 으로 /root/ch/skip/proj.json 에 적으세요.

프로젝션은 파트마다 하위 디렉터리로 들어가는 사본입니다. 쿼리는 원래 표 이름 그대로 두고, 옵티마이저가 읽을 양이 가장 적은 쪽을 고릅니다. user_id 로 정렬된 사본에서는 user_id 가 정렬 키라 기본 인덱스가 그래뉼 하나로 좁혀 줍니다.

어느 프로젝션을 골랐는지 기록에서 본다

SELECT count(), sum(latency_ms) FROM skp.logs WHERE user_id = 4242 SETTINGS log_comment = 'chs-skip-06' 을 돌리고, system.query_log 의 끝(type = 'QueryFinish') 기록에서 query_id·read_rows·projections 를 /root/ch/skip/qlog.json 에 적으세요.

쿼리 문장에는 프로젝션 이름이 없으므로 무엇이 쓰였는지는 기록에서만 알 수 있습니다. query_log 의 projections 열은 쓰인 프로젝션을 데이터베이스.표.이름 꼴의 배열로 남깁니다. 로그는 약 1초마다 비워지니 먼저 SYSTEM FLUSH LOGS.

미리 집계한 프로젝션

프로젝션 p_svc_hour (SELECT service, toStartOfHour(ts), count(), avg(latency_ms) GROUP BY service, toStartOfHour(ts)) 를 추가하고 MATERIALIZE 하세요. 서비스별 service, count(), round(avg(latency_ms), 3) 을 서비스순으로 내는 /root/ch/skip/q_svc.sql 을 만들고, 조건 캐시를 끈 rows_read 와 system.projection_parts 의 p_svc_hour 활성 파트 rows 를 /root/ch/skip/agg.json 에 rows_read·projection_rows 로 적으세요.

GROUP BY 가 든 프로젝션은 숨은 AggregatingMergeTree 가 되어 시간·서비스마다 count 와 avg 의 중간 상태를 한 줄씩 가집니다. 서비스별 집계는 그 줄들을 다시 합치기만 하면 되므로 원본 100만 행 대신 사본의 몇천 행만 읽습니다.

빠르게 해 준 것들의 디스크 값

skp.logs 활성 파트의 bytes_on_disk 를 part_bytes, system.projection_parts 의 p_user·p_svc_hour 활성 파트 bytes_on_disk 를 p_user_bytes·p_svc_hour_bytes, system.data_skipping_indices 의 idx_trace·idx_seq data_compressed_bytes 를 idx_trace_bytes·idx_seq_bytes 로 /root/ch/skip/cost.json 에 적으세요.

파트의 bytes_on_disk 는 그 안에 든 프로젝션 하위 디렉터리와 인덱스 파일까지 포함합니다. 전부 다시 정렬한 사본, 몇천 행짜리 집계 사본, 블룸 필터, minmax 를 나란히 놓으면 무엇이 비싼지 한눈에 보입니다.