Pile up parts, merge them, and hit the limit

Goal

You confirm with system.parts, system.part_log and system.query_log how many parts an INSERT creates, how merging reduces them, and what gets blocked when there are too many parts. You gather several INSERTs into one part with asynchronous INSERT.

Why it matters

The regular culprit in ClickHouse outages is "Too many parts", and the cause is how data is inserted. To fix the inserting code, you must be able to count "how many parts did this INSERT create". Merges happen in the background at their own will, so this lab starts by stopping merges. The grader does not trust the numbers you wrote — it filters and counts the events of part_log by the uuid of the table that exists now, and confirms TOO_MANY_PARTS by the error code in query_log.

Steps

1. Create the database parts and the table parts.events — columns id UInt64, grp UInt8, v UInt32, engine MergeTree, ORDER BY id. Then stop this table's merges with SYSTEM STOP MERGES parts.events.
2. Run /opt/lab/fixtures/parts/batches.sql (10 INSERT statements) once, then write the number of active parts and their names to /root/ch/parts/parts.json as active_parts and names.
3. Create a new table parts.big with the same columns and insert 1 million rows from numbers(1000000) with one INSERT, attaching SETTINGS min_insert_block_size_rows = 250000. From part_log, write the query_id, the count and the list of row counts of the new parts that INSERT created to /root/ch/parts/blocks.json as query_id, parts and rows.
4. After SYSTEM START MERGES parts.events, merge the parts into one with OPTIMIZE TABLE parts.events FINAL, and write that part's name and level and the number of MergeParts events for this table in part_log to /root/ch/parts/merge.json as part, level and merge_events.
5. Create a table parts.guarded with the same columns and SETTINGS parts_to_throw_insert = 5, stop its merges, and insert id 1–6 one row at a time with six INSERTs. Collect the errors that come out (stderr) into /root/ch/parts/toomany.txt.
6. Turn merges on again for parts.guarded, merge with OPTIMIZE ... FINAL, and insert the rejected id 6 again so that the table ends up with six rows, id 1–6.
7. Create a new table parts.async_ev with the same columns and send a two-row INSERT ... VALUES 5 times separately, attaching SETTINGS async_insert = 1, wait_for_async_insert = 0, async_insert_use_adaptive_busy_timeout = 0, async_insert_busy_timeout_max_ms = 600000. After emptying the buffer with SYSTEM FLUSH ASYNC INSERT QUEUE, write the number of INSERTs in query_log since the table was created, the number of new parts in part_log, and the query_id that created those parts to /root/ch/parts/async.json as insert_queries, new_parts and flush_query_id.
8. For each table that currently exists in the parts database, count the NewPart events in part_log and write them to /root/ch/parts/summary.json as {"표이름": 수, ...} (table name mapped to the count), including events, big, guarded and async_ev.

Notes

• Viewing parts: SELECT name, rows, level FROM system.parts WHERE database = 'parts' AND table = '...' AND active.
• part_log is flushed every second. If what you just did is not visible, SYSTEM FLUSH LOGS. To see only the records of the current table, table_uuid = (SELECT uuid FROM system.tables WHERE database = 'parts' AND name = '...').
• SYSTEM STOP MERGES is released when the server restarts. If you run OPTIMIZE on a table with merges stopped, it is rejected with Cancelled merging parts — first SYSTEM START MERGES.
• Common mistakes: running step 2 twice. Even if you TRUNCATE, the part_log records remain, so to redo it, DROP TABLE parts.events and start from step 1. In step 7, putting 10 rows in one statement, or sending them one at a time in waiting mode (the default), does not gather the parts into one.
• The non-waiting mode of step 7 is not recommended in production because errors are not returned to the client (the documentation recommends wait_for_async_insert = 1). Here it is used to gather without relying on time.
• Official docs: Table parts · Part merges · system.part_log · parts_to_* settings · Asynchronous inserts · Selecting an insert strategy · OPTIMIZE

Create the table and stop merges

Create the database parts and the table parts.events. The columns are, in order, id UInt64, grp UInt8, v UInt32, the engine is MergeTree, and it is ORDER BY id. Then stop this table's background merges with SYSTEM STOP MERGES parts.events.

If you do not stop merges, the server combines the small parts you create in the next step within a few seconds, and you cannot see how the parts arose. SYSTEM STOP MERGES stops only that table if you give it a table name.

10 INSERTs → 10 parts

Run /opt/lab/fixtures/parts/batches.sql (10 INSERT statements, 1000 rows each) once, and write the number of active parts and the list of names to /root/ch/parts/parts.json as active_parts and names.

If you run it with --queries-file, each statement becomes a separate INSERT. The two middle numbers of the name are the first and last block numbers, and the last number is the merge level. If you gather the names into an array with groupArray and receive it as JSONEachRow, you can save it as it is.

When one INSERT becomes several parts

Create a new table parts.big with the same columns and insert 1 million rows from numbers(1000000) with one INSERT ... SELECT, attaching SETTINGS min_insert_block_size_rows = 250000. Read this table's NewPart events from part_log and write the query_id that created them, the number of new parts and the list of row counts per part to /root/ch/parts/blocks.json as query_id, parts and rows.

INSERT ... SELECT appends the blocks it read until min_insert_block_size_rows have gathered and then writes them as a part. The default is about 1 million rows, so if you leave it, it ends with one part. The query_id in part_log is the id of the INSERT you sent. If you recreated the table, you must filter by table_uuid so old records do not mix in.

Turn merges on and merge into one

After SYSTEM START MERGES parts.events, merge the parts into one with OPTIMIZE TABLE parts.events FINAL. Write the merged part's name and level (name and level in system.parts) and the number of MergeParts events for this table in part_log to /root/ch/parts/merge.json as part, level and merge_events.

If you run OPTIMIZE with merges stopped, it is rejected with Cancelled merging parts. The moment you turn merges on, the background may combine them first, and FINAL writes once more even when there is already one part — so the level can be 1 or 2. You can tell which path it was by looking at merged_from in part_log.

Lower the threshold to trigger TOO_MANY_PARTS

Create a table parts.guarded with the same columns and ORDER BY id and SETTINGS parts_to_throw_insert = 5, and stop its merges with SYSTEM STOP MERGES parts.guarded. Insert id 1–6 one row at a time with six INSERTs, and collect the error output (stderr) that comes out into /root/ch/parts/toomany.txt.

The threshold applies to the number of active parts in one partition. Even a one-row INSERT creates one part. clickhouse-client's errors come out on stderr, so collect them with 2>>. The grader does not trust only the file; it also checks whether an INSERT rejected with error code 252 actually exists in query_log.

Reduce parts to release it

Turn merges back on for parts.guarded with SYSTEM START MERGES, merge with OPTIMIZE TABLE parts.guarded FINAL, and then insert the rejected id 6 again. The table must have six rows, id 1–6 once each.

Raising the threshold only delays the symptom. Once the number of parts falls below the threshold, the same INSERT goes in. Be careful not to insert id 6 twice — MergeTree does not prevent duplicates.

5 asynchronous INSERTs → one part

Create a new table parts.async_ev with the same columns and send a two-row INSERT ... VALUES 5 times separately. Attach SETTINGS async_insert = 1, wait_for_async_insert = 0, async_insert_use_adaptive_busy_timeout = 0, async_insert_busy_timeout_max_ms = 600000 to each INSERT. After emptying the buffer with SYSTEM FLUSH ASYNC INSERT QUEUE, write the number of INSERTs that ended since this table was CREATEd in query_log (query_kind = 'Insert'), the number of new parts in part_log, and the query_id that created those parts to /root/ch/parts/async.json as insert_queries, new_parts and flush_query_id.

In non-waiting mode, the INSERT returns as soon as it goes into the buffer, so a SELECT still shows 0 rows. If you turn off the adaptive timer and set a large upper limit, it is not emptied by itself as time passes. The query that created the part is not your INSERT but the query that emptied the buffer (query_kind in query_log is AsyncInsertFlush).

Put the part counts by insert method in one table

For each table that currently exists in the parts database, count the NewPart events in part_log and write them to /root/ch/parts/summary.json in the shape {"표이름": 수, ...} (table name mapped to the count). The four tables events, big, guarded and async_ev must be included.

If you join system.tables and system.part_log by uuid, you count only the records of tables that exist now. Put the four numbers side by side — 10 statements, one statement cut into blocks, one row at a time, 5 statements gathered by the buffer. What decided the number of parts was not the row count but the way of inserting.