Skip to main content
Version: 6.2

Diagnosing ClickHouse Data Migration Issues

This article explains how to diagnose issues when migrating data from Smart Monitor Data Storage (hereinafter Data Storage) to ClickHouse with the Move to ClickHouse action. The memory formula, thresholds, and error examples are based on measurements with Smart Monitor 5.1.3 and ClickHouse 25.7.4.11: tables with 290, 590, 990, 1040, 1340, and 1590 columns, fetch_size 10000, and an 8 GiB max_memory_usage_for_user limit. The results apply to this configuration. Adjust the values for your installation if the memory limit, ClickHouse version, or document profile differs significantly.

Memory of a Single INSERT​

Memory consumption of a single ClickHouse INSERT during migration depends primarily on the number of columns in the target table (C), not on the number of rows in a batch or the pool size. For the measured tables with 290 to 1590 columns, the model deviates from measured memory consumption by no more than 1%:

INSERT ≈ 85 MiB + 4.5 MiB × C

The sm.ism.move_to_click.threadpool.size pool size (a static Data Storage node setting applied after a restart) does not affect memory consumption of a single INSERT. It determines how many INSERT operations run concurrently and therefore affects peak ClickHouse server memory consumption and the frequency of error 241. Check the configured pool size with GET _nodes/stats/thread_pool (the ism_data_migration pool).

note

Another major memory consumer is background part merges. By default, ClickHouse applies the vertical merge algorithm only to parts containing at least vertical_merge_algorithm_min_rows_to_activate rows (131072 by default) and vertical_merge_algorithm_min_columns_to_activate columns (11 by default). Smaller parts use the horizontal algorithm. For tables with approximately 300 or more columns, horizontal merges of small parts can consume several times more memory than a single INSERT. For details about the trade-off between memory consumption and merge time, see Horizontal and Vertical Merges.

Recommendations​

  1. Estimate memory consumption of a single INSERT using the formula above and set max_memory_usage_for_user in ClickHouse accordingly. With a pool of 16 or more threads, one INSERT should use no more than ≈ 60% of the limit (measured: migration succeeded at 58% and failed at 75%). With a pool of 1-2 threads, the limit is ≈ 88%; higher values were not tested
  2. For wide tables (approximately 1000 columns or more), gradually reduce sm.ism.move_to_click.threadpool.size until the migration stays within the memory limit. Restart Data Storage after each change. With an 8 GiB max_memory_usage_for_user limit, a 16-thread pool successfully migrated tables with 990 and 1040 columns but failed with 1340 columns; approximately 1000-1100 columns is a guideline. For a pool of 1-2 threads, only tables up to 1590 columns were tested (88% of the limit, successful migration). Wider tables were not tested with that pool size. According to the model, a single INSERT reaches 8 GiB at around 1800 columns; this is a theoretical boundary, not a measured result. For tables with up to approximately 590 columns, the risk of running out of memory is lower even with a large pool, and migration is faster
  3. Do not lower fetch_size to save memory. On a 590-column table, memory consumption of a single INSERT at fetch_size 2500 barely decreased compared with fetch_size 10000 (≈ 2.67 GiB fixed part plus ≈ 31 KiB per row versus 2.68 GiB). However, migration reaches the limit more often because INSERT operations run more frequently. On a different table with 290 columns, increasing fetch_size to 40000 noticeably increased memory consumption (1.88 GiB versus 1.36 GiB at fetch_size 10000). The recommended fetch_size is 10000 (the action default)
  4. Consider the overall ClickHouse max_server_memory_usage limit separately from max_memory_usage_for_user. Peak server memory consumption includes background load, concurrent INSERT operations, and merges and can exceed the per-user limit (measured with a 16-thread pool: ≈ 10.3-10.5 GiB with an 8 GiB max_memory_usage_for_user limit)
  5. Where possible, reduce the number of columns in the target table: remove unused fields from the mapping or template. Memory consumption grows linearly with the number of columns

Horizontal and Vertical Merges​

ClickHouse supports two part merge algorithms:

  • horizontal (the default for parts below the thresholds) - reads and merges all columns of a part at once; buffers are allocated for every column and part. On wide tables, this is the most memory-intensive option
  • vertical - first merges only the sort key columns (ORDER BY) and stores the resulting row permutation, then processes the remaining columns one at a time. It uses less memory than the horizontal algorithm but increases merge time

You can enable the vertical algorithm for small parts on a specific table:

ALTER TABLE <db>.<table> MODIFY SETTING vertical_merge_algorithm_min_rows_to_activate = 1;

Measurements show that applying this setting immediately after creating the target table reduces peak server memory consumption on a 290-column table from 7.2 to 4.5 GiB.

Important Note!

Lowering the threshold does not reduce the CPU and disk resources used for merging. It reduces peak memory consumption at the cost of longer merge times. If merges already finish between migrations, lowering the threshold may provide no benefit.

The Task Fails with a User Memory Limit Exceeded Error (Code 241)​

Memory consumption of one or more concurrent INSERT operations exceeded ClickHouse's max_memory_usage_for_user limit.

What to Do​

  1. Estimate memory consumption of a single INSERT using the formula from Memory of a Single INSERT and compare it with the limit
  2. Reduce sm.ism.move_to_click.threadpool.size and restart Data Storage. This reduces the number of concurrent INSERT operations, not the memory consumption of a single INSERT
  3. If the ClickHouse host has sufficient resources, increase max_memory_usage_for_user
  4. Reduce the number of columns in the target table

Example error from the task status:

Code: 241. DB::Exception: User memory limit exceeded: would use 8.02 GiB (attempt to allocate chunk of 4.16 MiB bytes), maximum: 8.00 GiB

The Task Fails with a (total) Memory Limit Exceeded Error (Code 241)​

The combined ClickHouse server load (background processes, INSERT operations, merges, and other tasks) exceeded the overall max_server_memory_usage limit. Code 210 (NETWORK_ERROR) may also appear: the connection was dropped after the query was interrupted, so this error does not require a separate fix.

What to Do​

  1. Enable vertical merges if they are not already enabled (see Horizontal and Vertical Merges)
  2. Lower the sm.ism.move_to_click.threadpool.size pool size
  3. Check whether ClickHouse shares the host with other resource-intensive processes
  4. Do not remove the max_memory_usage_for_user limit entirely. This will not help when the overall server limit is reached and may affect other processes on the host

Example error from the task status:

Code: 241. DB::Exception: (total) memory limit exceeded: would use 14.03 GiB, current RSS: 8.24 GiB, maximum: 14.03 GiB

The Task Log Has Many Code 241 Errors (ExceptionWhileProcessing), but the Final Status Is DONE​

Intermediate 241 errors in the log do not indicate a failure if the task's final status is DONE. Measurements show that these errors occur more often on wide tables with a large pool: 25-70 ExceptionWhileProcessing retries with code 241 were recorded on 990-1040-column tables with a 16-thread pool, versus occasional retries with a pool of 1-2 threads. These entries alone do not mean the task failed.

What to Do​

Follow the steps in The Task Fails with a User Memory Limit Exceeded Error before the task reaches FAILED.

Migration Slows Down: the ClickHouse Log Shows SOCKET_TIMEOUT or a Dropped Connection While Memory Usage Is Normal​

The client cannot send the data within the time specified by ClickHouse's http_receive_timeout (30 seconds by default).

What to Do​

Important Note!

Before changing the timeout, check the garbage collector settings in the Data Storage node's jvm.options. Measurements show that nonstandard GC options (for example, -XX:ConcGCThreads=1 -XX:ParallelGCThreads=1 -XX:+ShenandoahVerify -XX:ShenandoahRegionSize=256K) slowed migration and caused timeout-related connection drops.

  1. Check the applied options with GET _nodes/jvm?filter_path=nodes.*.jvm.input_arguments (the jvm.input_arguments field)
  2. If nonstandard GC options are found, remove them and restart the node
  3. If the JVM options match the expected configuration, increase http_receive_timeout for the ClickHouse user account used for migration

The move_to_click Task Does Not Start: No Status or Target Table​

An incorrect connection_id is a possible cause. In this case, the module does not create a task status or target table, and the error is recorded only in the Data Storage node log:

[ERROR][c.s.i.a.m.MoveToClickTransportAction] Move to click action ended exceptionally: Failed execution
...
Caused by: java.lang.Exception: Can't find connection with id '<connection_id>'

What to Do​

  1. Check the ClickHouse connection name in the Data Storage configuration, including its case and special characters
  2. View the configured connections in Main Menu - Settings - Management - Configuration List (see Search Anywhere Configuration Settings and ClickHouse)

parent.tripped Increases or 429 circuit_breaking_exception Errors Occur on Data Storage Requests During Migration​

Actual heap usage is approaching the parent Circuit Breaker's limit. This can occur with a large pool and wide tables (approximately 1000 or more columns): measurements showed heap usage around 95% of the limit with a 16-thread pool. An increase in parent.tripped alone did not cause migration to fail: the module automatically retries requests that return 429. Retry counts and delays are controlled by the Move to ClickHouse action's cb_retries_max_retries, cb_retries_initial_delay_millis, and cb_retries_max_delay_millis parameters (see the parameter table). The task fails after the retry limit is exhausted. If parent.tripped keeps increasing, reduce the pool size before the retry limit is reached.

What to Do​

If this interferes with other tasks in the cluster, reduce sm.ism.move_to_click.threadpool.size.

After a FAILED Status or Cancellation (cancel), the Target Table Has More Rows Than docsWritten Showed​

After cancellation or failure, the pool continues processing previously queued tasks for a short time (a few minutes in measurements): cancel does not clear the pool queue. During this period, additional rows may be written to the target table beyond the docsWritten value at the time of cancellation or failure.

What to Do​

Important Note!

The automatic cleanup in step 2 below applies only to tables created automatically by the Move to ClickHouse action: they are partitioned by index and shard. If a target table was created manually with a different partitioning key, automatic cleanup may find no data to delete. Before retrying the migration, clear that table manually or specify a new name with table_name_template. This behavior was verified for a plain local table without the action's replication and distributed parameters. It was not separately verified for replicated or distributed tables.

  1. Wait until the number of documents in the target table stops growing, then check again after a few minutes. A single check is not enough: the pool writes in batches, and a pause between batches may look like the queue has finished processing
  2. Then restart the migration into the same table. On each new attempt, the module deletes the previous run's partitions for the relevant index and shard (ALTER TABLE ... DROP PARTITION) before writing new data. A new table name and manual cleanup are not required for automatically created tables (see the warning above)
  3. If waiting is undesirable, restart the Data Storage node running the migration to stop all pool tasks immediately. Identify the node with GET _nodes/stats/thread_pool?filter_path=nodes.*.name,nodes.*.thread_pool.ism_data_migration: it has nonzero active or queue values for the ism_data_migration pool, and its name appears in the name field

docsWritten Does Not Increase After Restarting Migration of an Index Previously Migrated Fully or with an Error​

The docsWritten counter in the task status updates as new batches of data arrive. Index shards are processed sequentially. If the index was previously migrated fully or partially, the status shows the result of the previous run until the first new batch arrives. This may make it appear that docsWritten is not increasing.

What to Do​

  1. Wait for the first new batch of data: the status will update after it arrives

  2. Migration status is stored separately for each shard in documents with IDs in the form <index uuid>:<shard number>. First, get the index UUID:

    GET /_cat/indices/<index_name>?h=uuid

  3. Request the status of all index shards (the number of IDs matches the number of shards, numbered from 0):

    POST .sm_ism_data_migration_statuses/_mget
    {
      "ids": [
        "<index uuid>:0",
        "<index uuid>:1"
      ]
    }

    Add one ID for each index shard. Repeat the query after some time and compare docsWritten for each shard. If the value changes, migration is progressing

  4. If needed, check the data directly in ClickHouse's system.query_log or system.part_log