ClickHouse IOPS Troubleshooting: A Complete Guide to Diagnosing and Fixing Disk I/O Bottlenecks

ClickHouse IOPS troubleshooting is the discipline that separates a predictable analytical cluster from one that mysteriously stalls under peak load. Disk I/O is the most common hidden bottleneck in production ClickHouse, yet most teams instinctively blame CPU or memory first. In reality, MergeTree background merges, part mutations, high-frequency inserts, and wide SELECT scans all compete for the same finite pool of device operations per second. Consequently, p99 latency drifts upward for weeks before anyone notices a saturated block device.

Furthermore, ClickHouse hides I/O pressure remarkably well. It compresses aggressively, caches marks in memory, and parallelises reads across many threads, so moderate saturation simply looks like normal variance. However, once the device reaches its provisioned ceiling, everything degrades at once: inserts queue, merges fall behind, parts accumulate, and mutations never finish. Therefore, disciplined ClickHouse IOPS troubleshooting always begins with measurement rather than guesswork. This guide covers the system tables, operating-system tools, and configuration changes that actually resolve disk I/O bottlenecks.

Understanding How ClickHouse Consumes IOPS

Before tuning anything, build a mental model of where operations really go. ClickHouse is not a random-read database, because it strongly favours large sequential scans. Nevertheless, several internal subsystems generate substantial random I/O, and they rarely appear on default dashboards. Every ClickHouse IOPS troubleshooting exercise should therefore map these consumers first.

MergeTree Write Amplification

Every INSERT creates a new immutable part on disk. Afterwards, background threads repeatedly merge small parts into progressively larger ones. As a result, the same row is often rewritten five to ten times before it lands in its final part. This write amplification is the single largest consumer of write IOPS in most clusters, so ClickHouse IOPS troubleshooting almost always begins with the merge subsystem. The official MergeTree engine documentation describes the part lifecycle in depth.

Additionally, mutations behave far worse than merges. An ALTER TABLE … UPDATE or DELETE rewrites every affected part in full. A single careless mutation on a multi-terabyte table can therefore saturate a device for hours.

Read Paths That Quietly Burn IOPS

Primary key index reads look cheap, but they are never free. ClickHouse loads the sparse index and the mark files for every part it touches. When a query hits hundreds of parts, those small reads multiply fast. Moreover, a cold or undersized mark cache converts each lookup into a physical read instead of a memory hit.

Similarly, granule reads suffer when the uncompressed cache cannot hold repetitive point lookups. Skip-index evaluation then adds another layer of small reads. Together, these paths explain why apparently trivial dashboard queries sometimes dominate device utilisation. Effective ClickHouse IOPS troubleshooting therefore examines read paths, not only writes.

Detached Parts and Silent Bloat

Detached parts occupy space and complicate merges, although they serve no queries at all. Likewise, orphaned temporary directories from failed merges linger after a crash. Both inflate directory listings and slow server startup, which complicates ClickHouse IOPS troubleshooting after an incident. Consequently, you should audit them on a schedule. For a wider catalogue of avoidable anti-patterns, review our notes on common ClickHouse performance mistakes.

Diagnosing IOPS Bottlenecks with System Tables

ClickHouse instruments itself thoroughly. Productive ClickHouse IOPS troubleshooting therefore starts inside the database, not on the host. Run the queries below in order, because each one narrows the problem space further.

Step 1: Trend Asynchronous I/O Metrics

First, establish a baseline from system.asynchronous_metric_log. This table samples device counters every second, so it reveals when pressure started.

SELECT
    toStartOfMinute(event_time) AS minute,
    metric,
    round(avg(value), 2) AS avg_value,
    round(max(value), 2) AS max_value
FROM system.asynchronous_metric_log
WHERE event_date >= today() - 1
  AND event_time > now() - INTERVAL 6 HOUR
  AND metric IN (
      'OSIOWaitTime',
      'BlockReadOps_nvme0n1',
      'BlockWriteOps_nvme0n1',
      'BlockQueueTime_nvme0n1',
      'MaxPartCountForPartition'
  )
GROUP BY minute, metric
ORDER BY minute DESC, metric
LIMIT 500;

Note that block-device metric names embed your device name. Check SELECT DISTINCT metric FROM system.asynchronous_metrics WHERE metric LIKE 'Block%' to discover the exact identifiers on your host.

Step 2: Inspect Live Merge and Mutation Pressure

SELECT metric, value, description
FROM system.metrics
WHERE metric IN (
    'Merge',
    'PartMutation',
    'BackgroundMergesAndMutationsPoolTask',
    'BackgroundFetchesPoolTask',
    'BackgroundCommonPoolTask',
    'ReplicatedFetch',
    'ReplicatedSend',
    'OpenFileForRead',
    'OpenFileForWrite',
    'Read',
    'Write'
)
ORDER BY value DESC;

A persistently high BackgroundMergesAndMutationsPoolTask value signals that the merge pool cannot keep pace. In that case, the storage layer, not the CPU, is usually the limiting factor.

Step 3: Quantify Cumulative I/O from system.events

SELECT event, value, description
FROM system.events
WHERE event IN (
    'OSReadBytes',
    'OSWriteBytes',
    'OSReadChars',
    'OSWriteChars',
    'OSIOWaitMicroseconds',
    'ReadBufferFromFileDescriptorReadBytes',
    'WriteBufferFromFileDescriptorWriteBytes',
    'MergedRows',
    'MergedUncompressedBytes',
    'MergesTimeMilliseconds'
)
ORDER BY value DESC;

Next, collapse those counters into a single readable summary. OSReadBytes and OSWriteBytes measure real device traffic, whereas the Chars variants include page-cache hits. A large gap between them proves the cache is working.

SELECT
    formatReadableSize(sumIf(value, event = 'OSReadBytes'))  AS device_read,
    formatReadableSize(sumIf(value, event = 'OSReadChars'))  AS logical_read,
    formatReadableSize(sumIf(value, event = 'OSWriteBytes')) AS device_write,
    round(sumIf(value, event = 'OSIOWaitMicroseconds') / 1e6, 1) AS io_wait_seconds,
    round(100 * sumIf(value, event = 'OSReadBytes')
              / nullIf(sumIf(value, event = 'OSReadChars'), 0), 2) AS cache_miss_pct
FROM system.events;

Step 4: Hunt Part Explosion in system.parts

Excessive parts per partition is the classic root cause of ClickHouse disk I/O saturation. This query ranks the worst offenders immediately.

SELECT
    database,
    table,
    partition,
    count() AS parts,
    sum(rows) AS total_rows,
    formatReadableSize(sum(bytes_on_disk)) AS size_on_disk,
    round(avg(bytes_on_disk) / 1048576, 2) AS avg_part_mb,
    min(min_time) AS oldest_row,
    max(modification_time) AS last_write
FROM system.parts
WHERE active
GROUP BY database, table, partition
HAVING parts > 20
ORDER BY parts DESC
LIMIT 50;

During ClickHouse IOPS troubleshooting, small avg_part_mb values combined with high part counts confirm insert fragmentation. Additionally, check detached parts with SELECT database, table, count() FROM system.detached_parts GROUP BY 1, 2 ORDER BY 3 DESC.

Step 5: Watch Merges in Flight

SELECT
    database,
    table,
    round(elapsed, 1) AS elapsed_s,
    round(progress * 100, 1) AS pct_done,
    num_parts,
    is_mutation,
    formatReadableSize(total_size_bytes_compressed)  AS input_size,
    formatReadableSize(bytes_read_uncompressed)      AS read_uncompressed,
    formatReadableSize(bytes_written_uncompressed)   AS written_uncompressed,
    formatReadableSize(memory_usage)                 AS mem,
    result_part_name
FROM system.merges
ORDER BY elapsed DESC;

In ClickHouse IOPS troubleshooting, long-running merges that barely advance indicate device starvation. Meanwhile, many concurrent merges on one table point straight to a merge storm.

Step 6: Rank Queries by Bytes Read

SELECT
    normalizedQueryHash(query) AS query_hash,
    any(normalizeQuery(query))  AS query_pattern,
    count() AS executions,
    formatReadableSize(sum(read_bytes)) AS total_read,
    formatReadableSize(avg(read_bytes)) AS avg_read,
    round(avg(query_duration_ms))       AS avg_ms,
    sum(ProfileEvents['OSReadBytes'])          AS device_read_bytes,
    round(sum(ProfileEvents['OSIOWaitMicroseconds']) / 1e6, 1) AS io_wait_s
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_date >= today() - 1
  AND read_bytes > 0
GROUP BY query_hash
ORDER BY sum(read_bytes) DESC
LIMIT 25;

This single query usually exposes the top three workloads worth optimising. For deeper metric-by-metric analysis, see our guide to the most useful ClickHouse matrices for query and IOPS performance.

OS-Level Verification for ClickHouse IOPS Troubleshooting

Database counters tell you what ClickHouse requested. The operating system tells you what the hardware delivered. Consequently, complete ClickHouse IOPS troubleshooting always confirms the hypothesis at the OS layer before anyone buys faster disks.

Read the Device with iostat

# Extended stats, 1-second samples, 20 iterations
iostat -x -m 1 20

# Narrow the output to the ClickHouse data device only
iostat -x -d nvme0n1 1 20

Focus on four columns. First, r/s plus w/s equals your delivered IOPS. Second, %util above 90 percent means the queue is rarely empty. Third, r_await and w_await reveal latency per request; NVMe should stay under one millisecond, while network storage often sits near ten. Finally, aqu-sz shows the average queue depth, so a value above the device concurrency confirms genuine saturation.

Importantly, high %util alone is not proof of a problem on modern NVMe. Rising await alongside flat throughput is the real signature of an exhausted device, and that pattern anchors every ClickHouse IOPS troubleshooting conclusion.

Attribute I/O to clickhouse-server

# Per-process read/write throughput for the server
pidstat -d -p $(pgrep -o clickhouse-server) 1 20

# Kernel-level cumulative counters for the same process
cat /proc/$(pgrep -o clickhouse-server)/io

# Which threads are blocked on disk right now
ps -L -o pid,tid,stat,wchan:24,comm -p $(pgrep -o clickhouse-server) | grep -E 'D|wait'

Then correlate the two data sources. If pidstat attributes nearly all device traffic to clickhouse-server, and system.events shows matching OSWriteBytes growth during a merge window, merges are your culprit. Conversely, when device traffic exceeds what ClickHouse reports, suspect a noisy neighbour, a backup agent, or log shipping on the same volume.

# Rank every process by I/O to catch non-database consumers
iotop -boqqt -d 5 -n 6 | head -40

Fixing Common IOPS Problems

Once you have identified the dominant consumer, the remediation path is usually straightforward. The five patterns below account for the vast majority of ClickHouse IOPS troubleshooting engagements we handle.

1. Too Many Small Inserts Causing Part Explosion

Thousands of tiny inserts per second create thousands of tiny parts, and the merge pool then spends its entire budget cleaning up. Ideally, clients should batch 10,000 to 100,000 rows and insert at most once per second per table. When you cannot change the client, enable server-side batching instead.

-- Session or user-profile level asynchronous inserts
SET async_insert = 1,
    wait_for_async_insert = 1,
    async_insert_busy_timeout_ms = 1000,
    async_insert_max_data_size = 10485760,
    async_insert_max_query_number = 450;

-- Verify that batching is actually happening
SELECT
    query_id,
    table,
    status,
    rows,
    data_kind,
    flush_time
FROM system.asynchronous_insert_log
WHERE event_date = today()
ORDER BY flush_time DESC
LIMIT 20;

Batching therefore remains the highest-leverage ClickHouse IOPS troubleshooting fix available. Additionally, reduce partition granularity. Monthly partitions instead of daily ones cut part counts dramatically on high-cardinality tables.

2. Merge Storms and Runaway Background Pools

Merge storms occur when the pool schedules many huge merges simultaneously. As a result, foreground queries starve. Cap both concurrency and merge size, and remember that these settings belong in server configuration rather than a session.

<clickhouse>
    <!-- Total background threads for merges and mutations -->
    <background_pool_size>16</background_pool_size>
    <background_merges_mutations_concurrency_ratio>2</background_merges_mutations_concurrency_ratio>
    <background_schedule_pool_size>32</background_schedule_pool_size>

    <merge_tree>
        <!-- Never merge into parts larger than 50 GiB -->
        <max_bytes_to_merge_at_max_space_in_pool>53687091200</max_bytes_to_merge_at_max_space_in_pool>
        <max_bytes_to_merge_at_min_space_in_pool>1048576</max_bytes_to_merge_at_min_space_in_pool>
        <number_of_free_entries_in_pool_to_lower_max_size_of_merge>8</number_of_free_entries_in_pool_to_lower_max_size_of_merge>
        <max_replicated_merges_in_queue>8</max_replicated_merges_in_queue>
        <merge_max_block_size>8192</merge_max_block_size>
        <parts_to_delay_insert>300</parts_to_delay_insert>
        <parts_to_throw_insert>600</parts_to_throw_insert>
    </merge_tree>
</clickhouse>

Capping merge concurrency resolves a large share of ClickHouse IOPS troubleshooting cases outright. Furthermore, schedule mutations deliberately. Batch several ALTER statements together, run them off-peak, and monitor system.mutations until is_done = 1.

3. Undersized Mark Cache and Uncompressed Cache

Mark cache misses translate directly into random reads. Therefore, measure the hit ratio before resizing anything.

SELECT
    round(100 * sumIf(value, event = 'MarkCacheHits')
              / nullIf(sumIf(value, event IN ('MarkCacheHits', 'MarkCacheMisses')), 0), 2) AS mark_cache_hit_pct,
    round(100 * sumIf(value, event = 'UncompressedCacheHits')
              / nullIf(sumIf(value, event IN ('UncompressedCacheHits', 'UncompressedCacheMisses')), 0), 2) AS uncompressed_hit_pct
FROM system.events;

Aim for a mark cache hit ratio above 99 percent. If yours falls short, raise the limits and keep the uncompressed cache off unless your workload repeats point lookups.

<clickhouse>
    <mark_cache_size>10737418240</mark_cache_size>        <!-- 10 GiB -->
    <uncompressed_cache_size>8589934592</uncompressed_cache_size> <!-- 8 GiB -->
</clickhouse>

-- Enable the uncompressed cache only for repetitive lookup workloads
SET use_uncompressed_cache = 1;

4. Storage Tiering with Hot and Cold Volumes

Most analytical workloads read recent data constantly and historical data rarely. Accordingly, move cold partitions to cheaper devices and reserve fast NVMe for the hot window. The multiple volumes and storage policy documentation covers the full syntax.

<clickhouse>
  <storage_configuration>
    <disks>
      <hot_nvme>
        <path>/var/lib/clickhouse/hot/</path>
        <keep_free_space_bytes>10737418240</keep_free_space_bytes>
      </hot_nvme>
      <cold_hdd>
        <path>/mnt/cold/clickhouse/</path>
      </cold_hdd>
    </disks>
    <policies>
      <hot_cold>
        <volumes>
          <hot>
            <disk>hot_nvme</disk>
            <max_data_part_size_bytes>107374182400</max_data_part_size_bytes>
          </hot>
          <cold>
            <disk>cold_hdd</disk>
            <prefer_not_to_merge>false</prefer_not_to_merge>
          </cold>
        </volumes>
        <move_factor>0.2</move_factor>
      </hot_cold>
    </policies>
  </storage_configuration>
</clickhouse>

-- Apply the policy and age data out automatically
ALTER TABLE events MODIFY SETTING storage_policy = 'hot_cold';

ALTER TABLE events
    MODIFY TTL event_time + INTERVAL 30 DAY TO VOLUME 'cold',
              event_time + INTERVAL 365 DAY DELETE;

-- Confirm placement
SELECT disk_name, count() AS parts, formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE active AND table = 'events'
GROUP BY disk_name;

5. Choosing Storage Hardware and Provisioning Cloud IOPS

Hardware choices set the ceiling for every other optimisation, including ClickHouse IOPS troubleshooting wins you have already banked. Local NVMe delivers hundreds of thousands of IOPS at sub-millisecond latency, so it remains the gold standard for MergeTree. Network-attached storage adds latency to every mark read, which hurts high-concurrency dashboards more than large scans.

For RAID, prefer RAID 10 over RAID 5 or RAID 6. Parity RAID imposes a read-modify-write penalty that compounds merge write amplification badly. Set the stripe size to 1 MiB or larger, use the none or mq-deadline scheduler for NVMe, and mount with noatime.

In AWS, gp3 volumes provision throughput and IOPS independently, up to 16,000 IOPS and 1,000 MiB/s per volume. io2 Block Express scales to 256,000 IOPS with far tighter latency guarantees, so reserve it for write-heavy ingestion clusters. Above all, remember that instance-level throughput caps often bind before volume limits do, and burst credits mask problems until they run out. Our review of ClickHouse shard contention troubleshooting explains how these ceilings interact across a cluster.

Monitoring IOPS Continuously

One-off investigations fix incidents, whereas continuous monitoring prevents them. Accordingly, bake ClickHouse IOPS troubleshooting signals into your permanent dashboards. ClickHouse ships a native Prometheus endpoint, so no sidecar is strictly necessary.

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

Next, scrape the endpoint and visualise it in Grafana alongside node_exporter disk panels. Plot delivered IOPS, await, merge pool depth, and maximum parts per partition on one row, because engineers diagnose faster when they see cause and effect together.

groups:
  - name: clickhouse-iops
    rules:
      - alert: ClickHouseHighIOWait
        expr: rate(ClickHouseProfileEvents_OSIOWaitMicroseconds[5m]) / 1e6 > 0.5
        for: 10m
        labels:
          severity: warning
        annotations:
          summary: "ClickHouse I/O wait exceeds half a CPU core"

      - alert: ClickHousePartExplosion
        expr: ClickHouseAsyncMetrics_MaxPartCountForPartition > 200
        for: 15m
        labels:
          severity: critical
        annotations:
          summary: "Parts per partition too high - merges cannot keep up"

      - alert: ClickHouseMergeBacklog
        expr: ClickHouseMetrics_BackgroundMergesAndMutationsPoolTask > 30
        for: 20m
        labels:
          severity: warning

      - alert: ClickHouseDiskLatency
        expr: rate(node_disk_write_time_seconds_total[5m])
              / rate(node_disk_writes_completed_total[5m]) > 0.02
        for: 10m
        labels:
          severity: critical

For continuous ClickHouse IOPS troubleshooting, track four golden signals: delivered IOPS versus provisioned IOPS, average request latency, merge queue depth, and maximum parts per partition. Additionally, alert on failed mutations and on parts_to_delay_insert breaches, since both precede full ingestion outages. Our ClickHouse performance observability and monitoring whitepaper details dashboard layouts and retention strategies for these metrics.

Conclusion: Make ClickHouse IOPS Troubleshooting Routine

Disk I/O bottlenecks in ClickHouse are predictable once you know where to look. Start inside the database with system.events, system.parts, system.merges, and system.query_log. Then verify the hypothesis with iostat and pidstat at the OS layer. Afterwards, fix the root cause: batch your inserts, cap merge concurrency, size the mark cache correctly, tier cold data onto cheaper volumes, and provision storage that matches your ingestion rate. Finally, wire the same metrics into Prometheus so the next regression pages you before users complain.

Above all, treat ClickHouse IOPS troubleshooting as a recurring operational habit rather than an emergency response. Teams that review part counts and merge latency weekly rarely suffer ingestion outages at all.

Need help right now? ChistaDATA delivers in-depth ClickHouse performance audits, storage architecture reviews, and 24/7 enterprise support for mission-critical analytical platforms. Our engineers have resolved IOPS saturation on clusters ingesting millions of rows per second. Explore our ClickHouse consulting services, or contact us to schedule a performance audit and let our team turn your ClickHouse IOPS troubleshooting backlog into measurable throughput gains.