ClickHouse/clickhouse-docs-embeddings huggingface dataset:
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/settings/query-complexity.md)

## Overview[​](#overview "Direct link to Overview")

As part of the [settings](/docs/operations/settings/overview), ClickHouse offers
the ability to place restrictions on query complexity. This helps protect against
potentially resource-intensive queries, ensuring safer and more predictable
execution, particularly when using the user interface.

Almost all the restrictions only apply to `SELECT` queries, and for distributed
query processing, restrictions are applied on each server separately.

ClickHouse generally checks the restrictions only after data parts have been
fully processed, rather than checking the restrictions for each row. This can
result in a situation where restrictions are violated while the part is being
processed.

## `overflow_mode` settings[​](#overflow_mode_setting "Direct link to overflow_mode_setting")

Most restrictions also have an `overflow_mode` setting, which defines what happens
when the limit is exceeded, and can take one of two values:

- `throw`: throw an exception (default).
- `break`: stop executing the query and return the partial result, as if the
  source data ran out.

## `group_by_overflow_mode` settings[​](#group_by_overflow_mode_settings "Direct link to group_by_overflow_mode_settings")

The `group_by_overflow_mode` setting also has
the value `any`:

- `any` : continue aggregation for the keys that got into the set, but do not
  add new keys to the set.

## List of settings[​](#relevant-settings "Direct link to List of settings")

The following settings are used for applying restrictions on query complexity.

Note

Restrictions on the "maximum amount of something" can take a value of `0`,
which means that it is "unrestricted".
title_wip

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
title_wip

Defines what action ClickHouse performs when any of the following join limits is reached.|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [`max_partitions_per_insert_block`](/docs/operations/settings/settings#max_partitions_per_insert_block) Limits the maximum number of partitions in a single inserted block and an exception is thrown if the block contains too many partitions.|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | [`throw_on_max_partitions_per_insert_block`](/docs/operations/settings/settings#throw_on_max_partitions_per_insert_block) Allows you to control the behaviour when `max_partitions_per_insert_block` is reached.| [`max_temporary_data_on_disk_size_for_user`](/docs/operations/settings/settings#throw_on_max_partitions_per_insert_block) The maximum amount of data consumed by temporary files on disk in bytes for all concurrently running user queries.|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | [`max_temporary_data_on_disk_size_for_query`](/docs/operations/settings/settings#max_temporary_data_on_disk_size_for_query) The maximum amount of data consumed by temporary files on disk in bytes for all concurrently running queries.|  |  |  |  | | --- | --- | --- | --- | | [`max_sessions_for_user`](/docs/operations/settings/settings#max_sessions_for_user) Maximum number of simultaneous sessions per authenticated user to the ClickHouse server.|  |  | | --- | --- | | [`max_partitions_to_read`](/docs/operations/settings/settings#max_partitions_to_read) Limits the maximum number of partitions that can be accessed in a single query. | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
title_wip

## Obsolete settings[​](#obsolete-settings "Direct link to Obsolete settings")

Note

The following settings are obsolete

### max\_pipeline\_depth[​](#max-pipeline-depth "Direct link to max_pipeline_depth")

Maximum pipeline depth. It Corresponds to the number of transformations that each
data block goes through during query processing. Counted within the limits of a
single server. If the pipeline depth is greater, an exception is thrown.

- [Overview](#overview)- [`overflow_mode` settings](#overflow_mode_setting)- [`group_by_overflow_mode` settings](#group_by_overflow_mode_settings)- [List of settings](#relevant-settings)- [Obsolete settings](#obsolete-settings)
          - [max\_pipeline\_depth](#max-pipeline-depth)

Was this page helpful?
Query-level Session Settings

### Assign a setting to a session[​](#assign-a-setting-to-a-session "Direct link to Assign a setting to a session")

```
SET async_insert =1;
SELECT value FROM system.settings where name='async_insert';
```

```
┌─value──┐
│ 1      │
└────────┘
```

### Assign a setting during a query[​](#assign-a-setting-during-a-query "Direct link to Assign a setting during a query")

```
INSERT INTO YourTable
-- highlight-next-line
SETTINGS async_insert=1
VALUES (...)
```

## See also[​](#see-also "Direct link to See also")

- View the [Settings](/docs/operations/settings/settings) page for a description of the ClickHouse settings.
- [Global server settings](/docs/operations/server-configuration-parameters/settings)

- [Overview](#overview)- [Order of priority](#order-of-priority)- [Converting a setting to its default value](#converting-a-setting-to-its-default-value)- [Custom settings](#custom_settings)- [Examples](#examples)
          - [Using SQL to apply a setting to a user directly](#using-sql-to-apply-a-setting-to-a-user-directly)- [Using SQL to create a settings profile and assign to a user](#using-sql-to-create-a-settings-profile-and-assign-to-a-user)- [Using XML to create a settings profile and user](#using-xml-to-create-a-settings-profile-and-user)- [Assign a setting to a session](#assign-a-setting-to-a-session)- [Assign a setting during a query](#assign-a-setting-during-a-query)- [See also](#see-also)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/settings/server-overload.md)

## Overview[​](#overview "Direct link to Overview")

Sometimes server can become overloaded due to different reasons. In order to determine the current CPU overload,
ClickHouse server calculates the ratio of CPU wait time (`OSCPUWaitMicroseconds` metric) to busy time
(`OSCPUVirtualTimeMicroseconds` metric). When the server is overloaded above certain ratio,
it makes sense to discard some queries or even drop connection requests to not increase the load even more.

There's a server setting `os_cpu_busy_time_threshold` which controls the minimum busy time to consider CPU
doing some useful work. If the current value of `OSCPUVirtualTimeMicroseconds` metric is below this value,
CPU overload is assumed to be 0.

## Rejecting queries[​](#rejecting-queries "Direct link to Rejecting queries")

The behavior of rejecting queries is controlled by query-level settings `min_os_cpu_wait_time_ratio_to_throw` and
`max_os_cpu_wait_time_ratio_to_throw`. If those settings are set and `min_os_cpu_wait_time_ratio_to_throw` is less
than `max_os_cpu_wait_time_ratio_to_throw`, then the query is rejected and `SERVER_OVERLOADED` error is thrown
with some probability is the overload ratio is at least `min_os_cpu_wait_time_ratio_to_throw`. The probability
is determined as a linear interpolation between min and max ratios. For example, if `min_os_cpu_wait_time_ratio_to_throw = 2`,
`max_os_cpu_wait_time_ratio_to_throw = 6`, and `cpu_overload = 4`, then the query will be rejected with a probability of `0.5`.

## Dropping connections[​](#dropping-connections "Direct link to Dropping connections")

Dropping connections is controlled by server-level settings `min_os_cpu_wait_time_ratio_to_drop_connection` and
`max_os_cpu_wait_time_ratio_to_drop_connection`. Those settings can be changed without server restart. The idea behind
those settings is similar to the one with rejecting queries. The only difference in this case is if the server is overloaded,
the connection attempt will be rejected from the server side.

## Resource overload warnings[​](#resource-overload-warnings "Direct link to Resource overload warnings")

ClickHouse also logs CPU and Memory overload warnings to `system.warnings` table when the server is overloaded. You can
customize these thresholds through server configuration.

**Example**

```
<resource_overload_warnings>
    <cpu_overload_warn_ratio>0.9</cpu_overload_warn_ratio>
    <cpu_overload_clear_ratio>0.8</cpu_overload_clear_ratio>
    <cpu_overload_duration_seconds>600</cpu_overload_duration_seconds>
    <memory_overload_warn_ratio>0.9</memory_overload_warn_ratio>
    <memory_overload_clear_ratio>0.8</memory_overload_clear_ratio>
    <memory_overload_duration_seconds>600</memory_overload_duration_seconds>
</resource_overload_warnings>
```

- [Overview](#overview)- [Rejecting queries](#rejecting-queries)- [Dropping connections](#dropping-connections)- [Resource overload warnings](#resource-overload-warnings)

Was this page helpful?
Optional secured communication between ClickHouse and Zookeeper

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/ssl-zookeeper.md)

Not supported in ClickHouse Cloud

Note

This page isn't applicable to [ClickHouse Cloud](https://clickhouse.com/cloud). The procedure documented here is automated in ClickHouse Cloud services.

You should specify `ssl.keyStore.location`, `ssl.keyStore.password` and `ssl.trustStore.location`, `ssl.trustStore.password` for communication with ClickHouse client over SSL. These options are available from Zookeeper version 3.5.2.

You can add `zookeeper.crt` to trusted certificates.

```
sudo cp zookeeper.crt /usr/local/share/ca-certificates/zookeeper.crt
sudo update-ca-certificates
```

Client section in `config.xml` will look like:

```
<client>
    <certificateFile>/etc/clickhouse-server/client.crt</certificateFile>
    <privateKeyFile>/etc/clickhouse-server/client.key</privateKeyFile>
    <loadDefaultCAFile>true</loadDefaultCAFile>
    <cacheSessions>true</cacheSessions>
    <disableProtocols>sslv2,sslv3</disableProtocols>
    <preferServerCiphers>true</preferServerCiphers>
    <invalidCertificateHandler>
        <name>RejectCertificateHandler</name>
    </invalidCertificateHandler>
</client>
```

Add Zookeeper to ClickHouse config with some cluster and macros:

```
<clickhouse>
    <zookeeper>
        <node>
            <host>localhost</host>
            <port>2281</port>
            <secure>1</secure>
        </node>
    </zookeeper>
</clickhouse>
```

Start `clickhouse-server`. In logs you should see:

```
<Trace> ZooKeeper: initialized, hosts: secure://localhost:2281
```

Prefix `secure://` indicates that connection is secured by SSL.

To ensure traffic is encrypted run `tcpdump` on secured port:

```
tcpdump -i any dst port 2281 -nnXS
```

And query in `clickhouse-client`:

```
SELECT * FROM system.zookeeper WHERE path = '/';
```

On unencrypted connection you will see in `tcpdump` output something like this:

```
..../zookeeper/quota.
```

On encrypted connection you should not see this.

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/startup-scripts.md)

ClickHouse can run arbitrary SQL queries from the server configuration during startup. This can be useful for migrations or automatic schema creation.

```
<clickhouse>
    <startup_scripts>
        <throw_on_error>false</throw_on_error>
        <scripts>
            <query>CREATE ROLE OR REPLACE test_role</query>
        </scripts>
        <scripts>
            <query>CREATE TABLE TestTable (id UInt64) ENGINE=TinyLog</query>
            <condition>SELECT 1;</condition>
        </scripts>
        <scripts>
            <query>CREATE DICTIONARY test_dict (...) SOURCE(CLICKHOUSE(...))</query>
            <user>default</user>
        </scripts>
    </startup_scripts>
</clickhouse>
```

ClickHouse executes all queries from the `startup_scripts` sequentially in the specified order. If any of the queries fail, the execution of the following queries won't be interrupted. However, if `throw_on_error` is set to true,
the server will not start if an error occurs during script execution.

You can specify a conditional query in the config. In that case, the corresponding query executes only when the condition query returns the value `1` or `true`.

Note

If the condition query returns any other value than `1` or `true`, the result will be interpreted as `false`, and the corresponding won't be executed.

Was this page helpful?
system.aggregated_zookeeper_log

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/system-tables/aggregated_zookeeper_log.md)

This table contains aggregated statistics of ZooKeeper operations (e.g. number of operations, average latency, errors) grouped by `(session_id, parent_path, operation, component, is_subrequest)` and periodically flushed to disk.

Unlike [system.zookeeper\_log](/docs/operations/system-tables/zookeeper_log) which logs every individual request and response, this table aggregates operations into groups, making it much more lightweight and therefore more suitable for production workloads.

Operations that are part of a `Multi` or `MultiRead` batch are tracked separately via the `is_subrequest` column. Subrequests have zero latency because the total latency is attributed to the enclosing `Multi`/`MultiRead` operation.

Columns:

- `hostname` ([LowCardinality(String)](/docs/sql-reference/data-types/string)) — Hostname of the server.
- `event_date` ([Date](/docs/sql-reference/data-types/date)) — Date the group was flushed.
- `event_time` ([DateTime](/docs/sql-reference/data-types/datetime)) — Time the group was flushed.
- `session_id` ([Int64](/docs/sql-reference/data-types/int-uint)) — Session id.
- `parent_path` ([String](/docs/sql-reference/data-types/string)) — Prefix of the path.
- `operation` ([Enum](/docs/sql-reference/data-types/enum)) — Type of ZooKeeper operation.
- `is_subrequest` ([UInt8](/docs/sql-reference/data-types/int-uint)) — Whether this operation was a subrequest inside a `Multi` or `MultiRead` operation.
- `count` ([UInt32](/docs/sql-reference/data-types/int-uint)) — Number of operations in the group.
- `errors` ([Map(Enum, UInt32)](/docs/sql-reference/data-types/map)) — Errors in the group, mapping error code to count.
- `average_latency` ([Float64](/docs/sql-reference/data-types/float)) — Average latency across all operations in the group, in microseconds. Subrequests have zero latency because the latency is attributed to the enclosing `Multi` or `MultiRead` operation.
- `component` ([LowCardinality(String)](/docs/sql-reference/data-types/string)) — Component that caused the event.

**See Also**

- [system.zookeeper\_log](/docs/operations/system-tables/zookeeper_log) — Detailed per-request ZooKeeper log.
- [ZooKeeper](/docs/operations/tips#zookeeper)

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/system-tables/asynchronous_inserts.md)

Querying in ClickHouse Cloud

The data in this system table is held locally on each node in ClickHouse Cloud. Obtaining a complete view of all data, therefore, requires the `clusterAllReplicas` function. See [here](/docs/operations/system-tables/overview#system-tables-in-clickhouse-cloud) for further details.

Contains information about pending asynchronous inserts in queue.

Columns:

- `query` ([String](/docs/sql-reference/data-types)) — Query text.
- `database` ([String](/docs/sql-reference/data-types)) — Database name.
- `table` ([String](/docs/sql-reference/data-types)) — Table name.
- `format` ([String](/docs/sql-reference/data-types)) — Format name.
- `first_update` ([DateTime64(6)](/docs/sql-reference/data-types)) — First insert time with microseconds resolution.
- `total_bytes` ([UInt64](/docs/sql-reference/data-types)) — Total number of bytes waiting in the queue.
- `entries.query_id` ([Array(String)](/docs/sql-reference/data-types)) — Array of query ids of the inserts waiting in the queue.
- `entries.bytes` ([Array(UInt64)](/docs/sql-reference/data-types)) — Array of bytes of each insert query waiting in the queue.

**Example**

Query:

```
SELECT * FROM system.asynchronous_inserts LIMIT 1 \G;
```

Result:

```
Row 1:
──────
query:            INSERT INTO public.data_guess (user_id, datasource_id, timestamp, path, type, num, str) FORMAT CSV
database:         public
table:            data_guess
format:           CSV
first_update:     2023-06-08 10:08:54.199606
total_bytes:      133223
entries.query_id: ['b46cd4c4-0269-4d0b-99f5-d27668c6102e']
entries.bytes:    [133223]
```

**See Also**

- [system.query\_log](/docs/operations/system-tables/query_log) — Description of the `query_log` system table which contains common information about queries execution.
- [system.asynchronous\_insert\_log](/docs/operations/system-tables/asynchronous_insert_log) — This table contains information about async inserts performed.

Was this page helpful?
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/system-tables/asynchronous_metric_log.md)

Querying in ClickHouse Cloud

The data in this system table is held locally on each node in ClickHouse Cloud. Obtaining a complete view of all data, therefore, requires the `clusterAllReplicas` function. See [here](/docs/operations/system-tables/overview#system-tables-in-clickhouse-cloud) for further details.

Contains the historical values for `system.asynchronous_metrics`, which are saved once per time interval (one second by default). Enabled by default.

Columns:

- `hostname` ([LowCardinality(String)](/docs/sql-reference/data-types/string)) — Hostname of the server executing the query.
- `event_date` ([Date](/docs/sql-reference/data-types/date)) — Event date.
- `event_time` ([DateTime](/docs/sql-reference/data-types/datetime)) — Event time.
- `metric` ([String](/docs/sql-reference/data-types/string)) — Metric name.
- `value` ([Float64](/docs/sql-reference/data-types/float)) — Metric value.

**Example**

```
SELECT * FROM system.asynchronous_metric_log LIMIT 3 \G
```

```
Row 1:
──────
hostname:   clickhouse.eu-central1.internal
event_date: 2023-11-14
event_time: 2023-11-14 14:39:07
metric:     AsynchronousHeavyMetricsCalculationTimeSpent
value:      0.001

Row 2:
──────
hostname:   clickhouse.eu-central1.internal
event_date: 2023-11-14
event_time: 2023-11-14 14:39:08
metric:     AsynchronousHeavyMetricsCalculationTimeSpent
value:      0

Row 3:
──────
hostname:   clickhouse.eu-central1.internal
event_date: 2023-11-14
event_time: 2023-11-14 14:39:09
metric:     AsynchronousHeavyMetricsCalculationTimeSpent
value:      0
```

**See Also**

- [asynchronous\_metric\_log setting](/docs/operations/server-configuration-parameters/settings#asynchronous_metric_log) — Enabling and disabling the setting.
- [system.asynchronous\_metrics](/docs/operations/system-tables/asynchronous_metrics) — Contains metrics, calculated periodically in the background.
- [system.metric\_log](/docs/operations/system-tables/metric_log) — Contains history of metrics values from tables `system.metrics` and `system.events`, periodically flushed to disk.

Was this page helpful?
title_wip

An internal metric of the low-level memory allocator (jemalloc). See <https://jemalloc.net/jemalloc.3.html>

### jemalloc.background\_thread.run\_intervals[​](#jemallocbackground_threadrun_intervals "Direct link to jemalloc.background_thread.run_intervals")

An internal metric of the low-level memory allocator (jemalloc). See <https://jemalloc.net/jemalloc.3.html>

### jemalloc.epoch[​](#jemallocepoch "Direct link to jemalloc.epoch")

An internal incremental update number of the statistics of jemalloc (Jason Evans' memory allocator), used in all other `jemalloc` metrics.

### jemalloc.mapped[​](#jemallocmapped "Direct link to jemalloc.mapped")

An internal metric of the low-level memory allocator (jemalloc). See <https://jemalloc.net/jemalloc.3.html>

### jemalloc.metadata[​](#jemallocmetadata "Direct link to jemalloc.metadata")

An internal metric of the low-level memory allocator (jemalloc). See <https://jemalloc.net/jemalloc.3.html>

### jemalloc.metadata\_thp[​](#jemallocmetadata_thp "Direct link to jemalloc.metadata_thp")

An internal metric of the low-level memory allocator (jemalloc). See <https://jemalloc.net/jemalloc.3.html>

### jemalloc.resident[​](#jemallocresident "Direct link to jemalloc.resident")

An internal metric of the low-level memory allocator (jemalloc). See <https://jemalloc.net/jemalloc.3.html>

### jemalloc.retained[​](#jemallocretained "Direct link to jemalloc.retained")

An internal metric of the low-level memory allocator (jemalloc). See <https://jemalloc.net/jemalloc.3.html>

### jemalloc.prof.active[​](#jemallocprofactive "Direct link to jemalloc.prof.active")

An internal metric of the low-level memory allocator (jemalloc). See <https://jemalloc.net/jemalloc.3.html>

**See Also**

- [Monitoring](/docs/operations/monitoring) — Base concepts of ClickHouse monitoring.
- [system.metrics](/docs/operations/system-tables/metrics) — Contains instantly calculated metrics.
- [system.events](/docs/operations/system-tables/events) — Contains a number of events that have occurred.
- [system.metric\_log](/docs/operations/system-tables/metric_log) — Contains a history of metrics values from tables `system.metrics` and `system.events`.
title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/operations/system-tables/azure_queue_settings.md)

Contains information about settings of [AzureQueue](/docs/engines/table-engines/integrations/azure-queue) tables.
Available from `24.10` server version.

Columns:

- `database` ([String](/docs/sql-reference/data-types)) — Database of the table with S3Queue Engine.
- `table` ([String](/docs/sql-reference/data-types)) — Name of the table with S3Queue Engine.
- `name` ([String](/docs/sql-reference/data-types)) — Setting name.
- `value` ([String](/docs/sql-reference/data-types)) — Setting value.
- `type` ([String](/docs/sql-reference/data-types)) — Setting type (implementation specific string value).
- `changed` ([UInt8](/docs/sql-reference/data-types)) — 1 if the setting was explicitly defined in the config or explicitly changed.
- `description` ([String](/docs/sql-reference/data-types)) — Setting description.
- `alterable` ([UInt8](/docs/sql-reference/data-types)) — Shows whether the current user can change the setting via ALTER TABLE MODIFY SETTING: 0 — Current user can change the setting, 1 — Current user can't change the setting.

Was this page helpful?