パーツを書き直す更新、隠すだけの削除、マージ時に効く寿命

目標

不変のパートの上で、UPDATE・DELETE・TTLが実際に何をするのかを、system.mutations・system.part_log・system.partsで確認し、止まったミューテーションを見つけて終わらせます。

なぜ重要なのか

ClickHouseでは、UPDATE 1行がパート全体の書き直しになり、軽量DELETEは行を隠すだけで、TTLはマージが来るまで待ちます。この違いを知らないと、小さな訂正がディスクI/Oの暴走になり、削除したと信じた行がディスクに残り、失敗したミューテーション1つがテーブルのすべての変更を止めます。このラボの採点ツールは、書き込まれた数値をそのまま信用しません。元データの生成式から、残っているはずの行を再計算し、マスクを無効にしたまま(apply_deleted_mask = 0)でも数え、system.part_logの記録と突き合わせます。

ステップ

1. データベースmutとテーブルmut.eventsを作成してください。列はevent_date Date, ts DateTime, user_id UInt32, email String, amount UInt32, status LowCardinality(String), legal_hold UInt8(この順序)、MergeTree、ORDER BY (user_id, ts)です。/opt/lab/fixtures/mutation/events.sqlで40万行を1回だけ入れ、OPTIMIZE TABLE mut.events FINALでパートを1つにしてください。
2. ALTER TABLE mut.events UPDATE status = 'refunded' WHERE status = 'refund_req'をmutations_sync = 2で実行し、そのmutation_id、前後のアクティブなパート名(part_before・part_after)、system.part_logのそのMutatePartが書いた行数(rows_rewritten)を書き込んでください(保存先: /root/ch/mutation/mutation.json)。
3. user_id = 777の行をALTER TABLE ... DELETEで削除してください(mutations_sync = 2)。
4. user_id = 888の行を軽量DELETE(DELETE FROM)で削除してください。直後に、通常のSELECTで数えたそのユーザーの行(visible_rows)、SETTINGS apply_deleted_mask = 0で数えた行(masked_rows)、system.partsのアクティブなパートのrows(part_rows)を書き込んでから(保存先: /root/ch/mutation/lwd.json)、OPTIMIZE TABLE mut.events FINALを実行してください。
5. email列にTTL if(legal_hold = 1, toDate('2100-01-01'), event_date + INTERVAL 90 DAY)を付けて(MODIFY COLUMN email String TTL ...)、既存のパートに適用してください。
6. テーブルTTLevent_date + INTERVAL 180 DAY DELETE WHERE status = 'test'を付けて(MODIFY TTL)、既存のパートに適用してください。
7. テーブルmut.hits (ts DateTime, user_id UInt32, hits UInt32, max_hits UInt32 DEFAULT hits, sum_hits UInt64 DEFAULT hits)を、MergeTree、PRIMARY KEY (user_id, toStartOfDay(ts), ts)、TTL ts + INTERVAL 30 DAY GROUP BY user_id, toStartOfDay(ts) SET max_hits = max(max_hits), sum_hits = sum(sum_hits)で作成し、/opt/lab/fixtures/mutation/hits.sqlを1回だけ入れてから、OPTIMIZE TABLE mut.hits FINALで要約を適用してください。
8. ALTER TABLE mut.events UPDATE amount = toUInt32(email) WHERE legal_hold = 1を(待たずに)投入し、失敗が記録されたら、そのミューテーションのmutation_id・is_done・parts_to_do・latest_fail_error_code_name(キーerror_code_name)を書き込んでから(保存先: /root/ch/mutation/stuck.json)、KILL MUTATIONで終わらせてください。

参考

• ミューテーションの確認: SELECT mutation_id, command, is_done, parts_to_do, latest_fail_reason FROM system.mutations WHERE database = 'mut'。
• パートがどのように書き直されたかを見るには、SYSTEM FLUSH LOGSのあとにSELECT event_type, part_name, merged_from, rows FROM system.part_log WHERE database = 'mut' ORDER BY event_time_microsecondsを実行します。
• TTLはマージのときに適用されます。今すぐ適用するには、ALTER TABLE ... MATERIALIZE TTL SETTINGS mutations_sync = 2かOPTIMIZE TABLE ... FINALを使います。
• よくある間違い: ステップ4で先にOPTIMIZEを実行してマスクの状態を見逃すこと、ステップ6のWHEREを忘れて180日を過ぎたすべての行(=全部)を削除してしまうこと、ステップ8でKILLを忘れて後ろのミューテーションがすべて止まることです。
• フィクスチャは2024年のデータです。壊してしまった場合は、DROP DATABASE mutのあと、ステップ1からやり直すのが最も早い方法です。
• 公式ドキュメント: Avoid mutations・ALTER TABLE ... UPDATE・ALTER TABLE ... DELETE・Lightweight delete・Manage data with TTL・ALTER TABLE ... MODIFY TTL・system.mutations・system.part_log

パート1つのイベントテーブル

データベースmutとテーブルmut.eventsを作成してください。列はevent_date Date, ts DateTime, user_id UInt32, email String, amount UInt32, status LowCardinality(String), legal_hold UInt8の順序、MergeTree、ORDER BY (user_id, ts)です。/opt/lab/fixtures/mutation/events.sqlで40万行を1回だけ入れ、OPTIMIZE TABLE mut.events FINALでパートを1つにしてください。

パートを1つにしておくと、あとのミューテーションごとにパート名がどう変わるかを、1行で追えます。データはすべて2024年なので、あとで付けるTTLは、いつ採点しても同じ行を削除します。

2%を変えるためにパート全体を書く

ALTER TABLE mut.events UPDATE status = 'refunded' WHERE status = 'refund_req' SETTINGS mutations_sync = 2を実行してください。system.mutationsのそのmutation_id、実行前後のアクティブなパート名part_before・part_after、system.part_logでpart_afterを作ったMutatePartの記録のrowsをrows_rewrittenとして書き込んでください(保存先: /root/ch/mutation/mutation.json)。

パートは変更できないので、ミューテーションは新しいパートをまるごと書きます。新しいパート名の末尾に付いた数字が、ミューテーション番号です。part_logは1秒ごとにフラッシュされるので、SYSTEM FLUSH LOGSのあとに見て、merged_fromが元のパートを指しているかを確認してください。変わった行数と書き直した行数を比べてみてください。

重い削除: ALTER TABLE ... DELETE

user_id = 777の行を、ALTER TABLE mut.events DELETE WHERE user_id = 777 SETTINGS mutations_sync = 2で削除してください。

ALTER DELETEもミューテーションです。その行が入っているパートを、その行を除いて書き直します。終わったら、マスクを無効にしたクエリ(SETTINGS apply_deleted_mask = 0)でも、その行が見えないはずです。

軽量DELETEは隠すだけ

DELETE FROM mut.events WHERE user_id = 888を実行し、直後に、通常のSELECTで数えたそのユーザーの行をvisible_rows、SETTINGS apply_deleted_mask = 0で数えた行をmasked_rows、system.partsのアクティブなパートのrowsをpart_rowsとして書き込んでください(保存先: /root/ch/mutation/lwd.json)。そのあとOPTIMIZE TABLE mut.events FINALを実行してください。

軽量DELETEは、隠れた列_row_existsに0を書くミューテーションに変わります(system.mutationsのcommandを見てください)。SELECTはその印を見て行を隠しますが、パートには行がそのまま残っているので、rowsは減りません。実際に取り除かれるのは、マージのときです。

列TTL: 期限が過ぎた値だけを消す

ALTER TABLE mut.events MODIFY COLUMN email String TTL if(legal_hold = 1, toDate('2100-01-01'), event_date + INTERVAL 90 DAY)を実行し、既存のパートにも適用してください(MATERIALIZE TTL、mutations_sync = 2)。法的保存(legal_hold = 1)の行のemailは、残る必要があります。

列TTLは、期限が過ぎた値を、その型のデフォルト値(Stringなら空文字列)に置き換えます。保存対象の行には、遠い未来の日付を返す式で、例外を作ります。TTLを変えると、デフォルトでMATERIALIZE TTLミューテーションが一緒に作られるかを、system.mutationsで見てください。

行TTL: 条件に合う行だけを消す

ALTER TABLE mut.events MODIFY TTL event_date + INTERVAL 180 DAY DELETE WHERE status = 'test'を実行し、既存のパートにも適用してください。testではない行は、1つもなくなってはいけません。

データはすべて2024年なので、180日が過ぎています。WHEREがないと、すべての行が期限を超えて、テーブルがまるごと空になります。TTLはマージのときに適用されるので、MATERIALIZE TTLかOPTIMIZE FINALで今すぐ適用します。

GROUP BY TTL: 古い行を要約にする

テーブルmut.hits (ts DateTime, user_id UInt32, hits UInt32, max_hits UInt32 DEFAULT hits, sum_hits UInt64 DEFAULT hits)を、MergeTree、PRIMARY KEY (user_id, toStartOfDay(ts), ts)、TTL ts + INTERVAL 30 DAY GROUP BY user_id, toStartOfDay(ts) SET max_hits = max(max_hits), sum_hits = sum(sum_hits)で作成し、/opt/lab/fixtures/mutation/hits.sqlを1回だけ入れてから、OPTIMIZE TABLE mut.hits FINALを実行してください。

GROUP BYに使う列は、プライマリキーの先頭部分である必要があるので、PRIMARY KEYにtoStartOfDay(ts)を入れました。max_hits・sum_hitsのデフォルト値をhitsにしておかないと、要約前の1行が自分の値で始まりません。INSERT直後とOPTIMIZEのあとの行数を比べてみてください。

止まったミューテーションを見つけて終わらせる

ALTER TABLE mut.events UPDATE amount = toUInt32(email) WHERE legal_hold = 1を(mutations_syncなしで)投入してください。system.mutationsに失敗が記録されたら、そのミューテーションのmutation_id・is_done・parts_to_do・latest_fail_error_code_name(キー名はerror_code_name)を書き込んでから(保存先: /root/ch/mutation/stuck.json)、KILL MUTATIONで終わらせてください。

メールアドレスの文字列は数値として読めないので、ミューテーションはパートごとに失敗して、リトライを続けます。元に戻す(ロールバック)ことはなく、後ろに来るミューテーションは、すべてこれに阻まれます。latest_fail_reasonが埋まるまで数秒待ってから記録し、KILL MUTATION WHERE database = 'mut' AND mutation_id = '…'で終わらせます。