Optimizing for S3 Insert and Read Performance

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/s3/performance.md)

This section focuses on optimizing performance when reading and inserting data from S3 using the [s3 table functions](/docs/sql-reference/table-functions/s3).

References

**The lesson described in this guide can be applied to other object storage implementations with their own dedicated table functions such as [GCS](/docs/sql-reference/table-functions/gcs) and [Azure Blob storage](/docs/sql-reference/table-functions/azureBlobStorage).**

Before tuning threads and block sizes to improve insert performance, we recommend users understand the mechanics of S3 inserts. If you're familiar with the insert mechanics, or just want some quick tips, skip to our example [below](/docs/integrations/s3/performance#example-dataset).

## Insert Mechanics (single node)[​](#insert-mechanics-single-node "Direct link to Insert Mechanics (single node)")

Two main factors, in addition to hardware size, influence the performance and resource usage of ClickHouse's data insert mechanics (for a single node): **insert block size** and **insert parallelism**.

### Insert block size[​](#insert-block-size "Direct link to Insert block size")

When performing an `INSERT INTO SELECT`, ClickHouse receives some data portion, and ① forms (at least) one in-memory insert block (per [partitioning key](/docs/engines/table-engines/mergetree-family/custom-partitioning-key)) from the received data. The block's data is sorted, and table engine-specific optimizations are applied. The data is then compressed and ② written to the database storage in the form of a new data part.

The insert block size impacts both the [disk file I/O usage](https://en.wikipedia.org/wiki/Category:Disk_file_systems) and memory usage of a ClickHouse server. Larger insert blocks use more memory but generate larger and fewer initial parts. The fewer parts ClickHouse needs to create for loading a large amount of data, the less disk file I/O and automatic [background merges required](https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part1#more-parts--more-background-part-merges).

When using an `INSERT INTO SELECT` query in combination with an integration table engine or a table function, the data is pulled by the ClickHouse server:

Until the data is completely loaded, the server executes a loop:

```
① Pull and parse the next portion of data and form an in-memory data block (one per partitioning key) from it.

② Write the block into a new part on storage.

Go to ①
```

In ①, the size depends on the insert block size, which can be controlled with two settings:

- [`min_insert_block_size_rows`](/docs/operations/settings/settings#min_insert_block_size_rows) (default: `1048545` rows)
- [`min_insert_block_size_bytes`](/docs/operations/settings/settings#min_insert_block_size_bytes) (default: `256 MiB`)

When either the specified number of rows is collected in the insert block, or the configured amount of data is reached (whichever happens first), then this will trigger the block being written into a new part. The insert loop continues at step ①.

Note that the `min_insert_block_size_bytes` value denotes the uncompressed in-memory block size (and not the compressed on-disk part size). Also, note that the created blocks and parts rarely precisely contain the configured number of rows or bytes because ClickHouse streams and [processes](https://clickhouse.com/company/events/query-performance-introspection) data row-[block](/docs/operations/settings/settings#max_block_size)-wise. Therefore, these settings specify minimum thresholds.

#### Be aware of merges[​](#be-aware-of-merges "Direct link to Be aware of merges")

The smaller the configured insert block size is, the more initial parts get created for a large data load, and the more background part merges are executed concurrently with the data ingestion. This can cause resource contention (CPU and memory) and require additional time (for reaching a [healthy](/docs/operations/settings/merge-tree-settings#parts_to_throw_insert) (3000) number of parts) after the ingestion is finished.

References

ClickHouse query performance will be negatively impacted if the part count exceeds the [recommended limits](/docs/operations/settings/merge-tree-settings#parts_to_throw_insert).

ClickHouse will continuously [merge parts](https://clickhouse.com/blog/asynchronous-data-inserts-in-clickhouse#data-needs-to-be-batched-for-optimal-performance) into larger parts until they [reach](/docs/operations/settings/merge-tree-settings#max_bytes_to_merge_at_max_space_in_pool) a compressed size of ~150 GiB. This diagram shows how a ClickHouse server merges parts:

A single ClickHouse server utilizes several [background merge threads](/docs/operations/server-configuration-parameters/settings#background_pool_size) to execute concurrent [part merges](https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part1#more-parts--more-background-part-merges:~:text=to%20execute%20concurrent-,part%20merges,-.%20Each%20thread%20executes). Each thread executes a loop:

```
① Decide which parts to merge next, and load these parts as blocks into memory.

② Merge the loaded blocks in memory into a larger block.

③ Write the merged block into a new part on disk.

Go to ①
```

Note that [increasing](https://clickhouse.com/blog/supercharge-your-clickhouse-data-loads-part1#hardware-size) the number of CPU cores and the size of RAM increases the background merge throughput.

Parts that were merged into larger parts are marked as [inactive](/docs/operations/system-tables/parts) and finally deleted after a [configurable](/docs/operations/settings/merge-tree-settings#old_parts_lifetime) number of minutes. Over time, this creates a tree of merged parts (hence the name [`MergeTree`](/docs/engines/table-engines/mergetree-family) table).

### Insert parallelism[​](#insert-parallelism "Direct link to Insert parallelism")

A ClickHouse server can process and insert data in parallel. The level of insert parallelism impacts the ingest throughput and memory usage of a ClickHouse server. Loading and processing data in parallel requires more main memory but increases the ingest throughput as data is processed faster.

Table functions like s3 allow specifying sets of to-be-loaded-file names via glob patterns. When a glob pattern matches multiple existing files, ClickHouse can parallelize reads across and within these files and insert the data in parallel into a table by utilizing parallel running insert threads (per server):

Until all data from all files is processed, each insert thread executes a loop:

```
① Get the next portion of unprocessed file data (portion size is based on the configured block size) and create an in-memory data block from it.

② Write the block into a new part on storage.

Go to ①.
```

The number of such parallel insert threads can be configured with the [`max_insert_threads`](/docs/operations/settings/settings#max_insert_threads) setting. The default value is `1` for open-source ClickHouse and 4 for [ClickHouse Cloud](https://clickhouse.com/cloud).

With a large number of files, the parallel processing by multiple insert threads works well. It can fully saturate both the available CPU cores and the network bandwidth (for parallel file downloads). In scenarios where just a few large files will be loaded into a table, ClickHouse automatically establishes a high level of data processing parallelism and optimizes network bandwidth usage by spawning additional reader threads per insert thread for reading (downloading) more distinct ranges within large files in parallel.