Troubleshooting Guide

OOM (Out of Memory)

Diagnosis: system.querylog WHERE memoryusage is high. system.metrics WHERE metric = 'MemoryTracking'.

Solutions:

• maxmemoryusage per query (e.g., 10GB), maxmemoryusageforuser per user
• maxbytesbeforeexternalgroupby / maxbytesbeforeexternal_sort for spill-to-disk
• Reduce JOIN sizes with pre-filtering; use SAMPLE for approximate aggregations

Slow Merges

Diagnosis: system.merges — check elapsed, progress, totalsizebytescompressed. system.partlog WHERE eventtype = 'MergeParts' for throughput. Too many parts: maxpartscountforpartition in system.asynchronousmetrics.

Solutions:

• Increase backgroundpoolsize (default: 16)
• Check disk I/O via system.asynchronous_metrics (ReadBufferFromFileDescriptorReadBytes)
• Batch larger inserts to reduce frequency; avoid excessive partitioning
• OPTIMIZE TABLE ... FINAL to force merge (expensive, off-peak only)

Stuck Mutations

Diagnosis: system.mutations WHERE isdone = 0 — check partstodo, latestfail_reason. Mutations block new merges on affected parts.

Solutions:

• KILL MUTATION WHERE mutation_id = '...' to cancel
• Fix underlying issue (schema mismatch, disk space), then re-submit
• Prefer INSERT + ReplacingMergeTree over UPDATE mutations

Query Failures

Diagnosis: system.query_log WHERE type = 'ExceptionWhileProcessing'. Use error clustering to find patterns:

SELECT exception_code, count(), topK(10)(exception)
FROM system.query_log
WHERE type = 'ExceptionWhileProcessing'
GROUP BY exception_code ORDER BY count() DESC

For persistent errors, check system.error_log (requires error logging enabled).

Common error codes:

• 60: table not found — verify table exists, check database name
• 47: unknown column — use gettableschema to check column names
• 241: memory limit — reduce scope, add LIMIT, use SAMPLE
• 159: timeout — add time filters, use LIMIT
• 252: too many parts — wait for merges or OPTIMIZE

Network Connectivity

Diagnosis: Check inter-server connectivity for replication or distributed queries. Verify interserverhttpport is reachable between nodes. Test with curl http://<peer>:9009. Check firewall rules and DNS resolution.

Cross-references

• Load the replication-guide skill for replication lag diagnosis and recovery.
• Load the storage-optimization skill for disk recovery and tiered storage management.