Load Two Million Rows and Look Inside the Column Files

목표

MergeTree 표에 200만 행을 넣고, 파트·그래뉼·열별 압축률을 system 표에서 읽는다. 같은 행 수를 읽어도 건드린 열과 정렬 키에 따라 비용이 달라지는 것을 rows_read·bytes_read 로 확인한다.

왜 중요한가

열 지향 DB 에서 쿼리 비용은 "몇 행을 골랐나" 가 아니라 "어느 열을 몇 그래뉼 읽었나" 로 정해진다. 이 감각이 없으면 정렬 키를 잘못 고르고, SELECT * 를 습관처럼 쓰고, 디스크 용량을 원본 크기로 잡는다. 이 실습의 채점기는 여러분이 적은 숫자를 믿지 않는다 — 서버의 system.parts·system.columns·system.query_log 를 직접 읽고, 여러분이 저장한 SELECT 를 읽기 전용으로 다시 돌려 읽은 행·바이트를 재서 대조한다. 채점기 자신의 쿼리는 query_log 에 남지 않는다.

단계

1. 데이터베이스 col 과 표 col.events 를 만드세요 — 열 ts DateTime, site LowCardinality(String), user_id UInt64, url String, dur_ms UInt32, country FixedString(2) (이 순서), 엔진 MergeTree, 정렬 키 ORDER BY (site, ts).
2. /opt/lab/fixtures/columnar/events.sql 을 한 번 실행해 200만 행을 넣으세요.
3. OPTIMIZE TABLE col.events FINAL 로 파트를 하나로 합친 뒤, 그 파트의 part(이름)·rows·marks·part_type 을 /root/ch/columnar/parts.json 에 적으세요.
4. system.columns 에서 비압축/압축 비율이 가장 큰 열(most_compressed)·가장 작은 열(least_compressed)·압축 후 바이트 합(total_compressed_bytes)을 /root/ch/columnar/columns.json 에 적으세요.
5. dur_ms 의 합만 구하는 /root/ch/columnar/q_one.sql 과 url 문자열의 내용을 읽는 /root/ch/columnar/q_url.sql(예: max(url))을 만들고, 두 쿼리의 bytes_read 를 /root/ch/columnar/bytes.json 에 one_col_bytes·url_bytes 로 적으세요.
6. site = 'docs.example' 인 행의 dur_ms 합을 내는 /root/ch/columnar/q_key.sql 과 country = 'KR' 인 행의 dur_ms 합을 내는 /root/ch/columnar/q_nokey.sql 을 만들고, 두 쿼리의 rows_read 를 /root/ch/columnar/rows.json 에 key_rows_read·nokey_rows_read 로 적으세요.
7. SELECT uniqExact(user_id) FROM col.events WHERE site = 'shop.example' 을 SETTINGS log_comment = 'chs-col-07' 을 붙여 돌린 뒤, system.query_log 에서 그 쿼리를 찾아 query_id·read_rows·read_bytes·result_rows 를 /root/ch/columnar/qlog.json 에 적으세요.
8. col.events 와 같은 열의 표 col.small 을 만들어 col.events 의 1000행을 INSERT 한 번으로 넣고, 두 표의 파트 형식을 /root/ch/columnar/part_types.json 에 events·small 로 적으세요.

참고

• 서버는 파드가 뜰 때 이미 떠 있습니다(127.0.0.1:9000). clickhouse-client 만 치면 붙습니다. 멈췄다면 ch-up.
• 파일로 쿼리 돌리기: clickhouse-client --queries-file 파일.sql. 결과 끝의 통계는 --format JSON 으로 받으면 statistics 에 있습니다(jq .statistics).
• 숫자를 JSON 에 따옴표 없이 받으려면 --output_format_json_quote_64bit_integers 0 을 줍니다.
• OPTIMIZE ... FINAL 은 실습에서 파트 수를 고정하려고 쓰는 것입니다. 운영에서는 병합을 서버에 맡기는 것이 원칙입니다.
• query_log 는 약 1초마다 비워집니다. 방금 돌린 쿼리가 안 보이면 SYSTEM FLUSH LOGS.
• 흔한 실수: 2단계를 두 번 돌려 400만 행이 되는 것 — MergeTree 는 중복을 막지 않습니다. 그때는 TRUNCATE TABLE col.events 뒤 다시 넣습니다.
• 공식 문서: Table parts · Primary indexes · MergeTree · system.parts · system.columns · system.query_log

정렬 키가 있는 MergeTree 표를 만든다

데이터베이스 col 과 표 col.events 를 만드세요. 열은 ts DateTime, site LowCardinality(String), user_id UInt64, url String, dur_ms UInt32, country FixedString(2) 순서, 엔진은 MergeTree, 정렬 키는 ORDER BY (site, ts) 입니다.

CREATE DATABASE 와 CREATE TABLE 두 문장입니다. 정렬 키를 적지 않은 MergeTree 는 만들어지지 않습니다 — 키가 곧 디스크의 행 순서이기 때문입니다. 열 이름과 타입을 한 글자라도 다르게 쓰면 뒤 단계의 원본 스크립트가 들어가지 않습니다.

원본 200만 행을 넣는다

/opt/lab/fixtures/columnar/events.sql 을 한 번 실행해 col.events 에 2,000,000행을 넣으세요.

clickhouse-client 에 --queries-file 로 넘기면 됩니다. 이 스크립트는 numbers() 로 행 번호를 만들고 해시로 값을 뽑아서, 몇 번을 돌려도 같은 행이 나옵니다. 행 수가 400만이면 두 번 넣은 것입니다.

파트를 하나로 합치고 이름을 읽는다

OPTIMIZE TABLE col.events FINAL 로 파트를 하나로 합친 뒤, system.parts 에서 그 활성 파트의 part(이름)·rows·marks·part_type 을 /root/ch/columnar/parts.json 에 적으세요.

INSERT 한 번이 블록 크기에 따라 파트를 여러 개 만들 수 있습니다. 합친 뒤에는 이름이 바뀝니다(병합 수준이 올라갑니다). active = 1 인 행만 보세요 — 합쳐진 옛 파트도 잠시 목록에 남아 있습니다. system.parts 의 name 열을 part 로 별칭 붙여 JSONEachRow 로 받으면 그대로 저장할 수 있습니다.

열마다 압축률이 얼마나 다른가

system.columns 에서 col.events 의 열마다 data_uncompressed_bytes / data_compressed_bytes 를 구해, 가장 큰 열을 most_compressed, 가장 작은 열을 least_compressed, 압축 후 바이트 합을 total_compressed_bytes 로 /root/ch/columnar/columns.json 에 적으세요.

argMax(name, 비율) 과 argMin(name, 비율) 을 쓰면 한 쿼리로 끝납니다. 가장 잘 줄어드는 열은 대개 정렬 키 앞쪽이거나 값 종류가 몇 개 없는 열이고, 가장 덜 줄어드는 열은 계속 커지는 값이나 무작위에 가까운 값입니다.

같은 행, 다른 열 — 읽은 바이트

dur_ms 의 합만 구하는 /root/ch/columnar/q_one.sql 과 url 문자열의 내용을 읽는 /root/ch/columnar/q_url.sql 을 만들고, 두 쿼리를 --format JSON 으로 돌려 statistics.bytes_read 를 /root/ch/columnar/bytes.json 에 one_col_bytes·url_bytes 로 적으세요.

두 쿼리 모두 200만 행 전부를 읽습니다. 다른 것은 건드린 열의 크기입니다. UInt32 한 열이면 행당 4바이트입니다. url 쪽은 length(url) 을 쓰면 안 됩니다 — 그 함수는 길이만 담은 부분열을 읽도록 바뀌어 내용을 읽지 않습니다. max(url) 처럼 내용을 비교하는 함수를 쓰세요.

정렬 키로 거를 때만 건너뛴다

site = 'docs.example' 인 행의 dur_ms 합을 내는 /root/ch/columnar/q_key.sql 과 country = 'KR' 인 행의 dur_ms 합을 내는 /root/ch/columnar/q_nokey.sql 을 만들고, 두 쿼리의 statistics.rows_read 를 /root/ch/columnar/rows.json 에 key_rows_read·nokey_rows_read 로 적으세요.

site 는 정렬 키의 첫 열이라 인덱스가 그 값이 있을 수 없는 그래뉼을 건너뜁니다. country 는 키에 없어서 건너뛸 근거가 없습니다. 읽은 행 수가 8192의 배수 근처로 나오는 이유를 생각해 보세요 — 건너뛰기의 단위는 행이 아니라 그래뉼입니다.

query_log 에서 자기 쿼리를 찾는다

SELECT uniqExact(user_id) FROM col.events WHERE site = 'shop.example' SETTINGS log_comment = 'chs-col-07' 을 돌리고, system.query_log 에서 그 쿼리의 끝(type = 'QueryFinish') 기록을 찾아 query_id·read_rows·read_bytes·result_rows 를 /root/ch/columnar/qlog.json 에 적으세요.

query_log 는 약 1초마다 디스크로 비워집니다. 바로 찾으려면 SYSTEM FLUSH LOGS 를 먼저 칩니다. 한 쿼리는 시작(QueryStart)과 끝(QueryFinish) 두 줄을 남기는데 읽은 행 수는 끝 줄에만 있습니다. log_comment 로 거르면 수많은 쿼리 사이에서 자기 것만 남습니다.

작은 파트는 Compact 가 된다

col.events 와 같은 열·엔진의 표 col.small 을 만들어 col.events 의 1000행을 INSERT 한 번으로 넣고, 두 표의 활성 파트 형식(part_type)을 /root/ch/columnar/part_types.json 에 events·small 로 적으세요.

CREATE TABLE ... AS 다른표 는 열과 엔진을 그대로 복사합니다. 파트 형식은 파트의 바이트·행 수가 표 설정 min_bytes_for_wide_part·min_rows_for_wide_part 보다 작으면 Compact, 크면 Wide 입니다. 표 설정은 바꾸지 마세요.