> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-fbfa8bee.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Lessons - debugging insights

> Find solutions to the most common ClickHouse problems including slow queries, memory errors, connection issues, and configuration problems.

*This guide is part of a collection of findings gained from community meetups. For more real world solutions and insights you can [browse by specific problem](/resources/support-center/tips-and-tricks/community-wisdom).*
*Suffering from high operational costs? Check out the [Cost Optimization](/resources/support-center/tips-and-tricks/cost-optimization) community insights guide.*

<h2 id="essential-system-tables">
  Essential system tables
</h2>

These system tables are fundamental for production debugging:

<h3 id="system-errors">
  system.errors
</h3>

Shows all active errors in your ClickHouse instance.

```sql theme={null}
SELECT name, value, changed 
FROM system.errors 
WHERE value > 0 
ORDER BY value DESC;
```

<h3 id="system-replicas">
  system.replicas
</h3>

Contains replication lag and status information for monitoring cluster health.

```sql theme={null}
SELECT database, table, replica_name, absolute_delay, queue_size, inserts_in_queue
FROM system.replicas 
WHERE absolute_delay > 60
ORDER BY absolute_delay DESC;
```

<h3 id="system-replication-queue">
  system.replication\_queue
</h3>

Provides detailed information for diagnosing replication problems.

```sql theme={null}
SELECT database, table, replica_name, position, type, create_time, last_exception
FROM system.replication_queue 
WHERE last_exception != ''
ORDER BY create_time DESC;
```

<h3 id="system-merges">
  system.merges
</h3>

Shows current merge operations and can identify stuck processes.

```sql theme={null}
SELECT database, table, elapsed, progress, is_mutation, total_size_bytes_compressed
FROM system.merges 
ORDER BY elapsed DESC;
```

<h3 id="system-parts">
  system.parts
</h3>

Essential for monitoring part counts and identifying fragmentation issues.

```sql theme={null}
SELECT database, table, count() as part_count
FROM system.parts 
WHERE active = 1
GROUP BY database, table
ORDER BY count() DESC;
```

<h2 id="common-production-issues">
  Common production issues
</h2>

<h3 id="disk-space-problems">
  Disk space problems
</h3>

Disk space exhaustion in replicated setups creates cascading problems. When one node runs out of space, other nodes continue trying to sync with it, causing network traffic spikes and confusing symptoms. One community member spent 4 hours debugging what was simply low disk space. Check out this [query](/resources/support-center/knowledge-base/queries-sql/useful-queries-for-troubleshooting#show-disk-storage-number-of-parts-number-of-rows-in-systemparts-and-marks-across-databases) to monitor your disk storage on a particular cluster.

If you're using AWS, you should be aware that default general purpose EBS volumes have a 16TB limit.

<h3 id="too-many-parts-error">
  Too many parts error
</h3>

Small frequent inserts create performance problems. The community has identified that insert rates above 10 per second often trigger "too many parts" errors because ClickHouse can't merge parts fast enough.

**Solutions:**

* Batch data using 30-second or 200MB thresholds
* Enable async\_insert for automatic batching
* Use buffer tables for server-side batching
* Configure Kafka for controlled batch sizes

[Official recommendation](/concepts/best-practices/selecting-an-insert-strategy#batch-inserts-if-synchronous): minimum 1,000 rows per insert, ideally 10,000 to 100,000.

<h3 id="data-quality-issues">
  Invalid timestamps issues
</h3>

Applications that send data with arbitrary timestamps create partition problems. This leads to partitions with data from unrealistic dates (like 1998 or 2050), causing unexpected storage behavior.

<h3 id="alter-operation-risks">
  `ALTER` operation risks
</h3>

Large `ALTER` operations on multi-terabyte tables can consume significant resources and potentially lock databases. One community example involved changing an Integer to a Float on 14TB of data, which locked the entire database and required rebuilding from backups.

**Monitor expensive mutations:**

```sql theme={null}
SELECT database, table, mutation_id, command, parts_to_do, is_done
FROM system.mutations 
WHERE is_done = 0;
```

Test schema changes on smaller datasets first.

<h2 id="memory-and-performance">
  Memory and performance
</h2>

<h3 id="external-aggregation">
  External aggregation
</h3>

Enable external aggregation for memory-intensive operations. It's slower but prevents out-of-memory crashes by spilling to disk. You can do this by using `max_bytes_before_external_group_by` which will help prevent out of memory crashes on large `GROUP BY` operations. You can learn more about this setting [here](/reference/settings/session-settings#max_bytes_before_external_group_by).

```sql theme={null}
SELECT 
    column1,
    column2,
    COUNT(*) as count,
    SUM(value) as total
FROM large_table
GROUP BY column1, column2
SETTINGS max_bytes_before_external_group_by = 1000000000; -- 1GB threshold
```

<h3 id="async-insert-details">
  Async insert details
</h3>

Async insert automatically batches small inserts server-side to improve performance. You can configure whether to wait for data to be written to disk before returning acknowledgment - immediate return is faster but less durable. Modern versions support deduplication to handle duplicate data within batches.

**Related docs**

* [Selecting an insert strategy](/concepts/best-practices/selecting-an-insert-strategy#asynchronous-inserts)

<h3 id="distributed-table-configuration">
  Distributed table configuration
</h3>

By default, distributed tables use single-threaded inserts. Enable `insert_distributed_sync` for parallel processing and immediate data sending to shards.

Monitor temporary data accumulation when using distributed tables.

<h3 id="performance-monitoring-thresholds">
  Performance monitoring thresholds
</h3>

Community-recommended monitoring thresholds:

* Parts per partition: preferably less than 100
* Delayed inserts: should stay at zero
* Insert rate: limit to about 1 per second for optimal performance

**Related docs**

* [Custom partitioning key](/reference/engines/table-engines/mergetree-family/custom-partitioning-key)

<h2 id="quick-reference">
  Quick reference
</h2>

| Issue           | Detection                        | Solution                            |
| --------------- | -------------------------------- | ----------------------------------- |
| Disk Space      | Check `system.parts` total bytes | Monitor usage, plan scaling         |
| Too Many Parts  | Count parts per table            | Batch inserts, enable async\_insert |
| Replication Lag | Check `system.replicas` delay    | Monitor network, restart replicas   |
| Bad Data        | Validate partition dates         | Implement timestamp validation      |
| Stuck Mutations | Check `system.mutations` status  | Test on small data first            |

<h3 id="video-sources">
  Video sources
</h3>

* [10 Lessons from Operating ClickHouse](https://www.youtube.com/watch?v=liTgGiTuhJE)
* [Fast, Concurrent, and Consistent Asynchronous INSERTS in ClickHouse](https://www.youtube.com/watch?v=AsMPEfN5QtM)