TT Lab
はじめる
学ぶ 学習パス コース

ClickHouse — 列指向分析 DB を中身から

同じ値を型とコーデックだけ変えて格納し、サイズを測る

TT Labで続きを見る

目標

デフォルトの型・デフォルトのコーデックのテーブルと、同じ値を型やコーデックだけ変えて入れた比較用テーブルを作成し、system.columnsの圧縮後バイト数で、どの選択が勝つかを確認します。最後に、本番で稼働中のテーブルのコーデックを変えると、サイズがいつ変わるかを見ます。

なぜ重要なのか

分析DBのディスク・メモリ・読み取りのコストは、結局バイト数です。同じデータでも、型とコーデックによってサイズが何倍も変わりますが、どのコーデックが勝つかは、データの形が決めます。このラボでも、専門コーデックの1つは、かえって損をします。そのため比較は常に、同じデータ・同じソートで、1つだけを変えて行う必要があります。このラボの比較用テーブルが、その方法です。採点ツールは、書き込まれた数値を信用せずにsystem.columnsを直接読んで突き合わせ、判定は速度ではなくバイト数で行います。

ステップ

  1. データベースcodecsとテーブルcodecs.plainを作成してください。列はts DateTime, host String, status String, cpu Float64, bytes_total UInt64, latency_ms UInt32, err_code Nullable(UInt16)(この順序、コーデックなし)、エンジンはMergeTree、ORDER BY (host, ts)です。
  2. /opt/lab/fixtures/codecs/metrics.sqlを1回だけ実行して100万行を入れ、OPTIMIZE TABLE codecs.plain FINALでパートを1つにしてください。
  3. テーブルcodecs.status_bench (host String, ts DateTime, s_str String, s_lc LowCardinality(String), s_enum Enum8('ok' = 1, 'warn' = 2, 'error' = 3, 'timeout' = 4))をORDER BY (host, ts)で作成し、3つの列すべてにplainのstatusを入れ、パートを1つにマージしたあと、3つの列の圧縮後バイト数と最も小さい列の名前(smallest)を書き込んでください(保存先: /root/ch/codecs/types.json)。
  4. テーブルcodecs.ts_bench (host String, ts DateTime, ts_zstd DateTime CODEC(ZSTD(1)), ts_delta DateTime CODEC(Delta, ZSTD(1)), ts_dd DateTime CODEC(DoubleDelta, ZSTD(1)))をORDER BY (host, ts)で作成し、4つの時刻の列すべてにplainのtsを入れ、パートを1つにマージしたあと、4つの列の圧縮後バイト数と最も小さい列(best)を書き込んでください(保存先: /root/ch/codecs/time.json)。
  5. テーブルcodecs.num_bench (host String, ts DateTime, cnt UInt64 CODEC(ZSTD(1)), cnt_delta UInt64 CODEC(Delta, ZSTD(1)), lat UInt32 CODEC(ZSTD(1)), lat_t64 UInt32 CODEC(T64, ZSTD(1)), cpu Float64 CODEC(ZSTD(1)), cpu_gorilla Float64 CODEC(Gorilla, ZSTD(1)))をORDER BY (host, ts)で作成し、ペアごとにplainのbytes_total・latency_ms・cpuを入れ、パートを1つにマージしたあと、6つの列の圧縮後バイト数と、ペアの相手よりかえって大きくなった専門コーデックの列名の配列(codec_lost)を書き込んでください(保存先: /root/ch/codecs/numbers.json)。
  6. テーブルcodecs.null_bench (host String, ts DateTime, err_null Nullable(UInt16), err_zero UInt16)をORDER BY (host, ts)で作成し、err_nullにはplainのerr_codeを、err_zeroにはNULLを0で埋めた値を入れて、パートを1つにマージしてください。NULLの行を数えるクエリ(/root/ch/codecs/q_isnull.sql)を作成し、null_uncompressed(err_nullの圧縮前バイト数)・isnull_bytes_read(q_isnull.sqlのbytes_read)・avg_null・avg_zero(2つの列のavg)を書き込んでください(保存先: /root/ch/codecs/nullable.json)。
  7. テーブルcodecs.tuned (ts DateTime CODEC(Delta, ZSTD(1)), host LowCardinality(String), status LowCardinality(String), cpu Float64 CODEC(ZSTD(1)), bytes_total UInt64 CODEC(Delta, ZSTD(1)), latency_ms UInt16 CODEC(T64, ZSTD(1)), err_code UInt16)をORDER BY (host, ts)で作成し、plainの行を移して(NULLは0)パートを1つにマージしたあと、2つのテーブルの圧縮後バイト数の合計を、plain_total・tuned_totalとして書き込んでください(保存先: /root/ch/codecs/tuned.json)。
  8. codecs.legacyをplainと同じ列・エンジンで作成し、plainの行を入れてパートを1つにマージしたあと、tsの圧縮後バイト数を測ります(before)。ALTER TABLE codecs.legacy MODIFY COLUMN ts CODEC(Delta, ZSTD(1))の直後にもう一度測り(after_alter)、OPTIMIZE TABLE codecs.legacy FINALのあとにまた測って(after_optimize)、3つの値を書き込んでください(保存先: /root/ch/codecs/alter.json)。

参考

何も考えずに作ったテーブル

データベースcodecsとテーブルcodecs.plainを作成してください。列はts DateTime, host String, status String, cpu Float64, bytes_total UInt64, latency_ms UInt32, err_code Nullable(UInt16)の順序(コーデックなし)、エンジンはMergeTree、ソートキーはORDER BY (host, ts)です。

元のDBのスキーマをそのまま移した形を、わざと作ります。文字列はString、空の値はNullable、コーデックは書かないのでデフォルト(LZ4)です。このテーブルが、あとのステップの比較の基準になります。

100万行を入れてパートを1つにする

/opt/lab/fixtures/codecs/metrics.sqlを1回だけ実行してcodecs.plainに1,000,000行を入れ、OPTIMIZE TABLE codecs.plain FINALでアクティブなパートを1つにしてください。

clickhouse-clientに--queries-fileで渡せば実行できます。スクリプトは、サーバー50台が10秒ごとに報告した指標をハッシュで作るので、何回実行しても同じ行ができます。サイズを比較するには、パートが1つでないと数値がぶれます。

String・LowCardinality・Enum8

codecs.status_bench (host String, ts DateTime, s_str String, s_lc LowCardinality(String), s_enum Enum8('ok' = 1, 'warn' = 2, 'error' = 3, 'timeout' = 4))をORDER BY (host, ts)で作成し、3つの列すべてにplainのstatusを入れてパートを1つにマージしたあと、3つの列の圧縮後バイト数(s_str・s_lc・s_enum)と最も小さい列の名前(smallest)を書き込んでください(保存先: /root/ch/codecs/types.json)。

INSERT ... SELECT host, ts, status, status, statusのように同じ値を3回入れれば、差は型だけから生まれます。Stringは行ごとに文字を、LowCardinalityは辞書と番号を、Enum8は1バイトの番号を保存します。system.columnsのdata_compressed_bytesを使ってください。data_uncompressed_bytesではありません。

時刻の列: LZ4・ZSTD・Delta・DoubleDelta

codecs.ts_bench (host String, ts DateTime, ts_zstd DateTime CODEC(ZSTD(1)), ts_delta DateTime CODEC(Delta, ZSTD(1)), ts_dd DateTime CODEC(DoubleDelta, ZSTD(1)))をORDER BY (host, ts)で作成し、4つの時刻の列すべてにplainのtsを入れてパートを1つにマージしたあと、4つの列の圧縮後バイト数(ts・ts_zstd・ts_delta・ts_dd)と最も小さい列の名前(best)を書き込んでください(保存先: /root/ch/codecs/time.json)。

ソートキーが(host, ts)なので、1台のサーバーの中では時刻が10秒ずつ一定に増えます。汎用コーデックは、増え続ける整数の中に繰り返しを見つけられませんが、前処理コーデックが値を隣との差に変えておくと、事情が変わります。コーデックのチェーンは左から適用されるので、前処理を先に、汎用圧縮をあとに書きます。

専門コーデックがいつも勝つわけではない

codecs.num_bench (host String, ts DateTime, cnt UInt64 CODEC(ZSTD(1)), cnt_delta UInt64 CODEC(Delta, ZSTD(1)), lat UInt32 CODEC(ZSTD(1)), lat_t64 UInt32 CODEC(T64, ZSTD(1)), cpu Float64 CODEC(ZSTD(1)), cpu_gorilla Float64 CODEC(Gorilla, ZSTD(1)))をORDER BY (host, ts)で作成し、cntのペアにbytes_total、latのペアにlatency_ms、cpuのペアにcpuを入れてパートを1つにマージしたあと、6つの列の圧縮後バイト数と、ペアの相手(ZSTDだけを使った列)よりかえって大きくなった専門コーデックの列名の配列codec_lostを書き込んでください(保存先: /root/ch/codecs/numbers.json)。

ペアの片方はZSTDだけ、もう片方は専門コーデック+ZSTDなので、差は専門コーデックが加えた分です。累積カウンターはいつも増え続け、レイテンシは範囲が狭く、CPUは小数第2位までの浮動小数点です。どれが勝つかを先に予想して書いておき、数値と突き合わせてみてください。大きくなった列がなければ、空の配列です。

Nullableはファイルをもう1つ持つ

codecs.null_bench (host String, ts DateTime, err_null Nullable(UInt16), err_zero UInt16)をORDER BY (host, ts)で作成し、err_nullにplainのerr_codeを、err_zeroにNULLを0で埋めた値を入れて、パートを1つにマージしてください。err_nullがNULLの行を数えるクエリ(/root/ch/codecs/q_isnull.sql)を作成し、null_uncompressed(err_nullのdata_uncompressed_bytes)・isnull_bytes_read(q_isnull.sqlのstatistics.bytes_read)・avg_null・avg_zero(2つの列のavg)を書き込んでください(保存先: /root/ch/codecs/nullable.json)。

Nullableの列は、値ファイルと、行ごとに1バイトのnullマップを別に持ちます。NULLかどうかだけを尋ねる条件は、nullマップだけを読むので、読んだバイト数が行数と同じになるはずです。値を比較する条件を混ぜると、値ファイルまで読みます。平均はNULLを除いて出しますが、0は含めます。2つのavgがなぜ違うのかを、説明できるようにしてください。

勝った選択を集めたテーブル

codecs.tuned (ts DateTime CODEC(Delta, ZSTD(1)), host LowCardinality(String), status LowCardinality(String), cpu Float64 CODEC(ZSTD(1)), bytes_total UInt64 CODEC(Delta, ZSTD(1)), latency_ms UInt16 CODEC(T64, ZSTD(1)), err_code UInt16)をORDER BY (host, ts)で作成し、plainの行を移して(err_codeのNULLは0)パートを1つにマージしたあと、plainとtunedの圧縮後バイト数の合計を、plain_total・tuned_totalとして書き込んでください(保存先: /root/ch/codecs/tuned.json)。

前のステップの結果を集めたものです。型(LowCardinality、範囲に合ったUInt16、NULLの代わりに0)を先に選び、コーデックはそのあとに付けました。Gorillaは前のステップで負けたので、cpuにはZSTDだけを使います。2つのテーブルの合計は、system.columnsをtableでグループ化して足せば求められます。

本番のテーブルのコーデックを変えると、いつ小さくなるのか

codecs.legacyをplainと同じ列・エンジンで作成し、plainの行を入れてパートを1つにマージしたあと、tsの圧縮後バイト数を測ってください(before)。ALTER TABLE codecs.legacy MODIFY COLUMN ts CODEC(Delta, ZSTD(1))の直後にもう一度測り(after_alter)、OPTIMIZE TABLE codecs.legacy FINALのあとにまた測って(after_optimize)、3つの値を書き込んでください(保存先: /root/ch/codecs/alter.json)。

CREATE TABLE ... ASに別のテーブルを指定すると、列とエンジンをそのままコピーします。パートは一度書かれると変わらないので、ALTERは、これから書くパートの規則だけを変えます。すでにあるパートが新しいコーデックで書き直されるのはいつなのかを、考えてみてください。3回とも、同じsystem.columnsのクエリで測ります。