Merges and mutations use too much memory and queries fail

Your ClickHouse server runs queries and background merges at the same time. Large merges or mutations can use a large amount of memory. Queries can fail when they compete with these operations for available memory.

You may see errors such as MEMORY_LIMIT_EXCEEDED. Query latency may also increase before failures begin. The server may remain available, but some queries cannot complete.

This problem often appears during heavy insert workloads. It can also appear when large mutations run on busy tables.

For example, your workload may look like this:

• Applications insert data into MergeTree tables.
• ClickHouse creates new data parts.
• Background merges combine smaller parts into larger parts.
• Mutations rewrite existing parts to update or delete data.
• Analytical queries run while these operations are active.

All these operations need memory. If several large merges run while queries use most of the available memory, the server can experience memory pressure.

You do not always need to reduce query concurrency or add RAM. First, check whether background merges and mutations use too much memory.

This article explains how to inspect their memory use and limit the memory available for new background operations.

2. Why it happens

ClickHouse uses background operations to maintain MergeTree tables.

Each insert can create one or more data parts. Background merges combine parts to reduce their number and maintain efficient query execution.

Mutations can rewrite existing parts. Examples include updates and deletes that use mutation operations on MergeTree tables.

Both operations can consume significant memory. The amount depends on factors such as part size, data types, row width, and the operation itself.

Queries also need memory for filtering, sorting, aggregation, joins, and other processing. Background operations and queries share the server's available memory.

ClickHouse has two server settings that help control this competition:

• merges_mutations_memory_usage_soft_limit
• merges_mutations_memory_usage_to_ram_ratio

The first setting defines the memory limit for background merges and mutations. The second setting determines the default limit as a fraction of physical memory.

The supplied default ratio is 0.5, or 50%. The default soft limit is 0, which selects the ratio-based limit.

When background merges and mutations reach the limit, ClickHouse stops scheduling new operations of these types. Operations that already started continue running. Memory use can therefore remain above the limit for some time.

This behavior can reduce additional background memory pressure. It does not guarantee that queries will never fail from memory exhaustion.

3. How to confirm

Start by checking the memory used by running merges and mutations.

The system.merges table reports information about active merges and mutations on MergeTree tables. Its memory_usage column reports the memory used by each running operation.

Run this query:

SELECT
    database,
    table,
    elapsed,
    progress,
    num_parts,
    formatReadableSize(memory_usage) AS memory_used
FROM system.merges
ORDER BY memory_usage DESC;

Review the largest values first.

Look for several operations that consume large amounts of memory at the same time. Check whether these operations overlap with query failures.

The system.merges table shows current operations. It does not provide a complete history of memory use. An operation may finish before you run the query.

If the result contains no rows, ClickHouse has no operations to show at that moment. Repeat the query during the period when failures occur.

Next, inspect the server settings.

SELECT
    name,
    value,
    changed,
    description
FROM system.server_settings
WHERE name IN
(
    'merges_mutations_memory_usage_soft_limit',
    'merges_mutations_memory_usage_to_ram_ratio'
)
ORDER BY name;

Check the reported values and whether an administrator changed them.

The system.server_settings table exposes server-wide settings. Its available columns can differ between versions. Verify this query on your target release.

You can also inspect the server's overall memory limit settings.

SELECT
    name,
    value,
    changed
FROM system.server_settings
WHERE name IN
(
    'max_server_memory_usage',
    'max_server_memory_usage_to_ram_ratio'
)
ORDER BY name;

These settings control the server's overall memory limit. They are different from the background merge and mutation limit.

If merges use little memory when you inspect them, investigate other causes. Check query memory limits, concurrent queries, server memory usage, and operating system memory pressure.

Do not change settings until you have evidence that background operations contribute to the problem.

4. The settings

The following defaults come from the supplied ClickHouse master commit, 9f79cc1, dated 30 October 2026. Verify them against your target release before deployment.

Both settings apply to the full server. They are not per-query settings.

merges_mutations_memory_usage_soft_limit

This setting controls the memory budget for background merges and mutations.

A value of 0 tells ClickHouse to calculate the limit from merges_mutations_memory_usage_to_ram_ratio.

You can also set an explicit limit in bytes. For example, 8589934592 represents 8 GiB.

When ClickHouse reaches the configured limit, it stops scheduling new background merges and mutations. Operations that already started continue running.

merges_mutations_memory_usage_to_ram_ratio

This setting controls the default background memory limit as a fraction of physical memory.

The supplied default is 0.5. ClickHouse uses this ratio to calculate the default limit.

For example, if the relevant physical memory amount is 64 GiB, a ratio of 0.5 produces a limit of approximately 32 GiB.

A lower ratio reduces the memory budget for background operations. This can leave more memory available for queries, but it can also reduce merge throughput.

Important: These settings require a server restart. Do not assume that changing their configuration files takes effect immediately.

5. How to fix it

Start with a small change. Do not set an aggressive limit without measuring the effect on merge progress.

Change 1: Lower the background memory ratio

Use this approach when background operations consume too much memory and queries need more headroom.

Create the file /etc/clickhouse-server/config.d/merges_mutations_memory.xml.

For example, start with a ratio of 0.3:

<clickhouse>
    <merges_mutations_memory_usage_to_ram_ratio>0.3</merges_mutations_memory_usage_to_ram_ratio>
</clickhouse>

This reduces the background memory budget from 50% to 30% of the relevant physical memory amount.

For a server with 64 GiB of relevant physical memory, the calculated limit would be approximately 19.2 GiB.

Use this value as a starting point, not a universal recommendation. Your workload may need a higher or lower ratio.

Restart required: Yes. Plan a controlled restart and monitor the workload after the server starts.

Change 2: Set an explicit background memory limit

Use an explicit limit when you know the maximum memory budget that your workload can tolerate.

For example, set a limit of 16 GiB:

<clickhouse>
    <merges_mutations_memory_usage_soft_limit>17179869184</merges_mutations_memory_usage_soft_limit>
</clickhouse>

This sets the limit to 16 GiB.

Choose the value based on available RAM, query memory requirements, and observed merge memory usage.

An explicit limit is useful when you need a fixed budget rather than a fraction of physical memory.

However, verify how your target version applies the explicit limit and the ratio-based limit. Do not assume that an explicit limit overrides the ratio in every case.

Restart required: Yes.

Which change should you use?

For most production incidents, start by lowering the ratio. It gives you a simple way to reduce the memory budget for background operations.

Use an explicit limit when you need a specific byte-based budget and have verified how your target version applies both settings.

Avoid changing both settings at once unless you understand their interaction. Otherwise, you may not know which change improved or worsened the workload.

After the restart, check the effective settings and observe memory use during a representative workload.

6. How to verify the fix

A successful change should reduce memory pressure without allowing the number of data parts to grow continuously.

First, inspect running merges again.

SELECT
    database,
    table,
    count() AS running_operations,
    formatReadableSize(sum(memory_usage)) AS total_memory_used,
    formatReadableSize(max(memory_usage)) AS largest_operation
FROM system.merges
GROUP BY
    database,
    table
ORDER BY sum(memory_usage) DESC;

Compare the results with measurements from before the change. Run the query during a similar workload.

Next, check whether queries still fail with memory errors. Review your application logs and ClickHouse query logs for the same failure pattern.

You should also check query performance. A successful fix should reduce memory-related failures without causing unacceptable query latency.

Finally, monitor the number of active data parts.

SELECT
    database,
    table,
    count() AS active_parts
FROM system.parts
WHERE active
GROUP BY
    database,
    table
ORDER BY active_parts DESC
LIMIT 20;

Compare the results over time. One snapshot cannot tell you whether the number of parts is growing.

If part counts remain stable and queries stop failing, the new limit may be working as intended.

If part counts continue to increase, merges may not be keeping up with inserts. Review the limit and your merge workload before making further changes.

7. Risks and trade-offs

The main risk is that a limit that is too low prevents ClickHouse from scheduling enough background merges and mutations.

New inserts can create parts faster than background operations can combine them. The number of active parts can then increase over time.

As part counts grow, queries may need to process more parts. This can increase query overhead and affect performance.

This problem can also lead to insert failures with TOO_MANY_PARTS. Lowering the background memory limit may therefore solve one incident while creating another.

Mutations can also take longer to finish. If your workload depends on timely updates or deletes, a lower limit may delay their completion.

Remember these points when tuning the settings:

• A lower ratio gives queries more memory headroom, but may reduce merge throughput.
• Running merges continue after ClickHouse reaches the soft limit.
• The soft limit does not guarantee that total server memory usage stays below a fixed value.
• Large mutations may still use substantial memory after the limit prevents new operations.
• Part counts and mutation progress need ongoing monitoring.

If queries still fail after you lower the ratio, investigate query memory limits and overall server memory usage. Do not keep reducing the ratio without checking merge progress.

8. Conclusion

• Background merges and mutations share server memory with queries.
• Use system.merges and its memory_usage column to inspect active operations.
• Lower merges_mutations_memory_usage_to_ram_ratio when background operations create excessive memory pressure.
• Restart ClickHouse after changing either setting.
• Monitor query failures and active part counts to ensure merges do not fall behind.

References

1. ClickHouse server settings source: ServerSettings.cpp. Verify the setting definitions and defaults against the target version.
2. ClickHouse documentation: System tables. Use this reference to check the available system tables and their columns.
3. ClickHouse documentation: system.merges. Check the fields available for running merge operations.
4. ClickHouse documentation: Server configuration parameters. Check server configuration and configuration file behavior.