13. Tuning and operations

ClickHouse is unusually pleasant to operate, for one reason: it tells you everything about itself in SQL. There is no separate metrics protocol to learn, no special admin tool. You query system tables.

The system tables that matter

SHOW TABLES FROM system;

There are dozens. These are the ones you will actually use.

Table	Answers
system.query_log	what ran, how long, how much it read
system.parts	parts, rows, bytes, per table and partition
system.columns	per-column compressed and uncompressed sizes
system.merges	merges running right now
system.mutations	ALTER UPDATE/DELETE progress
system.replicas	replication health and lag
system.replication_queue	what a replica is stuck on
system.processes	queries running right now
system.metrics	current gauges (memory, connections, running merges)
system.events	cumulative counters since start
system.asynchronous_metrics	periodically sampled values (disk, OS, cache)
system.settings	current settings and whether they are changed
system.disks / system.storage_policies	tiering
system.dictionaries	dictionary status and last exception
system.errors	error counts by type — an excellent early warning
system.query_thread_log	per-thread detail (off by default)
system.trace_log	sampled stack traces for profiling
system.part_log	every part created, merged, or dropped
system.text_log	the server log, queryable in SQL

system.text_log deserves a mention: it turns log grepping into a WHERE clause, which during an incident is worth a great deal.

The first hour on an unfamiliar cluster

-- version, uptime, and how much is here
SELECT version(), uptime();

SELECT database, table, formatReadableSize(sum(bytes_on_disk)) AS size,
       sum(rows) AS rows, count() AS parts
FROM system.parts WHERE active
GROUP BY database, table ORDER BY sum(bytes_on_disk) DESC LIMIT 20;

-- part counts — the health number from chapter 6
SELECT database, table, count() AS parts
FROM system.parts WHERE active
GROUP BY database, table ORDER BY parts DESC LIMIT 10;

-- what has been slow in the last day
SELECT normalized_query_hash, count() AS runs,
       sum(query_duration_ms) AS total_ms,
       formatReadableQuantity(sum(read_rows)) AS rows_read,
       any(substring(query, 1, 90)) AS sample
FROM system.query_log
WHERE type = 'QueryFinish' AND event_time > now() - INTERVAL 1 DAY
GROUP BY normalized_query_hash ORDER BY total_ms DESC LIMIT 15;

-- errors
SELECT name, value, last_error_message
FROM system.errors WHERE value > 0 ORDER BY value DESC LIMIT 15;

-- replication, if applicable
SELECT database, table, absolute_delay, queue_size, is_readonly
FROM system.replicas ORDER BY absolute_delay DESC;

-- settings someone changed
SELECT name, value, default, description
FROM system.settings WHERE changed;

That last one is the most useful diagnostic on an inherited system. Someone changed something for a reason nobody remembers, and it is frequently the cause.

Memory — the setting that matters most

ClickHouse will use all the memory you let it. Limits exist at three levels, and configuring them is the highest-value operational work here.

   max_server_memory_usage            0 → 0.9 × physical RAM
   max_memory_usage                   10 GiB   per query, per server
   max_memory_usage_for_user          0        per user, across queries
   max_bytes_before_external_group_by 0        spill threshold
   max_bytes_before_external_sort     0
   max_concurrent_queries             100

One caveat before the numbers: max_server_memory_usage_to_ram_ratio reads the host’s RAM, not a container limit. A cgroup ceiling does not change /proc/meminfo, so 0.9 of a 128 GB host inside a 16 GB container means ClickHouse plans for 115 GB and is OOM-killed rather than gracefully refusing a query. In a container, set max_server_memory_usage to an absolute byte value derived from the limit. The Docker guide’s chapter on what a container actually is covers why the process cannot see its own ceiling.

The practical configuration on, say, a 128 GB server:

<max_server_memory_usage_to_ram_ratio>0.9</max_server_memory_usage_to_ram_ratio>
<mark_cache_size>5368709120</mark_cache_size>              <!-- 5 GB -->
<uncompressed_cache_size>0</uncompressed_cache_size>       <!-- off unless measured -->

And per user, which is where the real protection lives:

<profiles>
  <analyst>
    <max_memory_usage>20000000000</max_memory_usage>          <!-- 20 GB -->
    <max_execution_time>300</max_execution_time>
    <max_rows_to_read>100000000000</max_rows_to_read>
    <max_bytes_before_external_group_by>10000000000</max_bytes_before_external_group_by>
    <readonly>1</readonly>
  </analyst>
  <ingest>
    <max_memory_usage>8000000000</max_memory_usage>
    <async_insert>1</async_insert>
    <wait_for_async_insert>1</wait_for_async_insert>
  </ingest>
</profiles>

Profiles per user are the single best operational habit

Give analysts a profile with a memory cap, an execution time limit, and readonly. Give ingestion its own profile. Give dashboards a third with a short timeout.

Without this, one person’s SELECT * with an accidental cross join takes down analytics for everyone. With it, that query fails in thirty seconds and only affects them.

Quotas add rate limiting over an interval:

<quotas>
  <analyst>
    <interval>
      <duration>3600</duration>
      <queries>1000</queries>
      <read_rows>1000000000000</read_rows>
      <execution_time>3600</execution_time>
    </interval>
  </analyst>
</quotas>

Settings worth changing (and the many that are not)

ClickHouse has well over a thousand settings. The defaults are good. These are the few that commonly need attention:

max_threads                        cores    lower it for concurrency,
                                            raise for single-query speed
max_concurrent_queries             100
max_execution_time                 0        SET ONE. per profile.
max_memory_usage                   10 GiB   per query
max_bytes_before_external_group_by 0        ≈ half max_memory_usage
async_insert                       0        1 when clients cannot batch
background_pool_size               16       merge parallelism
background_schedule_pool_size      128
max_partitions_per_insert_block    100      a useful guard
input_format_parallel_parsing      1
optimize_move_to_prewhere          1        leave on

max_threads is a concurrency dial, not a speed dial. Default (all cores) makes a single query as fast as possible and lets one query monopolise the machine. On a server serving many concurrent dashboard queries, setting max_threads to a quarter of the cores per query gives far better overall throughput and latency.

background_pool_size controls merge parallelism. Raise it if merges are falling behind and the disk has headroom; it will not help if you are I/O-bound, and it competes with queries.

Beyond these, resist. Most ClickHouse performance problems are schema problems (chapters 2, 5, 11), and settings tuning is where people go instead of fixing them.

Monitoring

The numbers worth alerting on, in priority order. ClickHouse can expose these directly to the Prometheus guide via its built-in Prometheus endpoint — no exporter needed:

-- 1. PART COUNT — the most important number in ClickHouse
SELECT database, table, count() AS parts
FROM system.parts WHERE active GROUP BY database, table
HAVING parts > 100 ORDER BY parts DESC;

-- 2. replication lag
SELECT database, table, absolute_delay, queue_size
FROM system.replicas WHERE absolute_delay > 30 OR queue_size > 100;

-- 3. failed queries
SELECT count(), any(exception)
FROM system.query_log
WHERE type = 'ExceptionWhileProcessing' AND event_time > now() - INTERVAL 5 MINUTE;

-- 4. memory
SELECT formatReadableSize(value) FROM system.metrics WHERE metric = 'MemoryTracking';

-- 5. disk
SELECT name, formatReadableSize(free_space), formatReadableSize(total_space),
       round(100 * (1 - free_space / total_space), 1) AS pct_used
FROM system.disks;

-- 6. stuck mutations
SELECT database, table, mutation_id, parts_to_do, latest_fail_reason
FROM system.mutations WHERE NOT is_done AND latest_fail_reason != '';

-- 7. dictionaries that stopped refreshing
SELECT name, status, last_exception FROM system.dictionaries WHERE status != 'LOADED';

ClickHouse also exposes Prometheus metrics natively:

<prometheus>
  <endpoint>/metrics</endpoint>
  <port>9363</port>
  <metrics>true</metrics>
  <events>true</events>
  <asynchronous_metrics>true</asynchronous_metrics>
</prometheus>

Configure the log tables’ TTL, or they grow forever:

<query_log>
  <database>system</database>
  <table>query_log</table>
  <ttl>event_date + INTERVAL 30 DAY DELETE</ttl>
  <flush_interval_milliseconds>7500</flush_interval_milliseconds>
</query_log>

A system.query_log nobody bounded is a classic cause of a full disk on a ClickHouse server.

Backups

Three approaches, with honest properties.

BACKUP / RESTORE — the built-in commands, and the right default:

BACKUP TABLE default.page_views
TO Disk('backups', 'page_views_2026_08_20.zip');

BACKUP DATABASE default TO S3('https://bucket/backups/', 'key', 'secret');

RESTORE TABLE default.page_views FROM Disk('backups', 'page_views_2026_08_20.zip');

Incremental backups work via SETTINGS base_backup = …, which makes daily backups cheap.

FREEZE PARTITION — creates hard links to the part files under shadow/. Instant, near-zero disk cost at the moment of creation, and then you copy the links elsewhere at your leisure:

ALTER TABLE page_views FREEZE PARTITION '202608';
-- then copy /var/lib/clickhouse/shadow/… to durable storage
ALTER TABLE page_views UNFREEZE PARTITION '202608' WITH NAME 'backup_name';

Replication is not a backup. It protects against a machine dying. It faithfully replicates DROP TABLE.

The argument specific to ClickHouse: much of the time, your real backup is the source data. If everything came from Kafka or S3 and your transformations are in materialised views, you can rebuild by replaying. That is a legitimate strategy and it must be tested, because “we can just replay” takes on a different character when it means three days of reprocessing during an outage.

As always: test the restore, and time it. That time is your actual recovery objective.

Upgrades

ClickHouse releases roughly monthly, with LTS releases supported longer. Most teams should track LTS and upgrade a few times a year rather than chasing every release.

The procedure on a replicated cluster:

1. Read the changelog for every version you are skipping, particularly the backward-incompatible section. ClickHouse moves fast, and behaviour changes do occur.
2. Test in staging with real queries and real data volume.
3. Upgrade one replica per shard at a time, verify, continue.
4. Mixed versions within a cluster work for the duration of an upgrade but are not a place to live.

The most common upgrade surprise is a setting whose default changed, altering a query plan or a resource limit. SELECT * FROM system.settings WHERE changed before and after is a cheap way to catch it.

Downgrades are not generally supported — new versions may write data or metadata formats older versions cannot read. Snapshot before you upgrade.

On Kubernetes, the Altinity operator performs exactly this sequence — one replica per shard, in order, waiting for each to be healthy — from a change to a single object. It is a good fit, with one caveat worth stating: ClickHouse will consume every byte of I/O throughput you give it, so network-attached storage is a real constraint rather than a detail. Chapter 10 of that guide has the argument.

Security

• Set passwords. The default user with no password is the ClickHouse equivalent of an open MongoDB, and it has been exploited in the wild.
• readonly = 1 or 2 for analyst profiles. 2 allows setting changes; 1 does not.
• Row-level security with CREATE ROW POLICY, for multi-tenant tables:

CREATE ROW POLICY tenant_filter ON page_views
FOR SELECT USING tenant_id = toUInt32(currentUser())
TO analysts;

• TLS for client and inter-server connections.
• Bind to private interfaces; ports 8123 (HTTP) and 9000 (native) should not be public.
• Column-level grants — GRANT SELECT(col1, col2) ON db.table TO user — for hiding sensitive columns.

What goes wrong

No max_execution_time. One query monopolises the server indefinitely.

No per-user profiles. One analyst’s mistake is everyone’s outage.

Unbounded system log tables. Disk fills with query_log.

Part count unmonitored. The problem is invisible until inserts are rejected.

Uncompressed cache enabled by default reasoning. It evicts page cache and usually makes scans slower.

Merges starved because background_pool_size is too small for the ingest rate — or too large, so merges out-compete queries.

Replication assumed to be a backup.

Upgrading across many versions at once without reading the backward-incompatible notes.

What to take from this chapter

• Everything about ClickHouse is queryable in SQL. system.query_log, system.parts, system.replicas, system.errors, system.settings WHERE changed.
• Part count per table is the number to alert on, followed by replication lag, failed queries, memory, and disk.
• Per-user profiles and quotas — memory cap, execution timeout, readonly — are the single best operational habit available.
• max_threads is a concurrency dial: lower it for many concurrent queries, raise it for one fast one.
• The defaults are good. Most performance problems are schema problems; resist settings tuning as a substitute.
• Bound the system log tables’ TTL or they fill the disk.
• Use BACKUP/RESTORE (incremental supported) or FREEZE PARTITION; replication is not a backup; replayable source data is a legitimate strategy that must be timed.
• Track LTS releases, read the backward-incompatible notes, upgrade one replica at a time, and snapshot first — downgrades are not supported.
• Set a password on default.

Next: the specific ways it falls over.