ClickHouseのquery_logとquery_thread_logを無効化する方法

# はじめに

ClickHouse の query_log と query_thread_logが肥大化してディスクを圧迫することがあり、無効化できるのか調べてみた備忘録。

# 結論

`/etc/clickhouse-server/users.d/log_queries.xml`を以下のように設定する。

## query_logは残して、query_thread_logのみ無効化したい場合

```xml:log_queries.xml
<yandex>
    <profiles>
        <default>
            <!--<log_queries>0</log_queries>-->
            <log_query_threads>0</log_query_threads>
        </default>
    </profiles>
</yandex>
```
## query_log と query_thread_log の両方を無効化したい場合
```xml:log_queries.xml
<yandex>
    <profiles>
        <default>
            <log_queries>0</log_queries>
            <!--<log_query_threads>0</log_query_threads>-->
        </default>
    </profiles>
</yandex>
```
# 参考

- [query_log](https://clickhouse.com/docs/en/operations/server-configuration-parameters/settings#server_configuration_parameters-query-log)
- [query_thread_log](https://clickhouse.com/docs/en/operations/server-configuration-parameters/settings#server_configuration_parameters-query_thread_log)
- [Calming down Clickhouse](https://theorangeone.net/posts/calming-down-clickhouse/)

# 検証

## ソース

Docker で ClickHouseを構築して検証します。ClickHouseのバージョンは`22.3.12.19`を利用しました。
```
.
├── docker-compose.yaml
└── users.d
    └── log_queries.xml
```

```yaml:docker-compose.yaml
version: "3"
services:
  clickhouse:
    image: clickhouse/clickhouse-server:22.3.12.19
    container_name: clickhouse
    hostname: clickhouse
    volumes:
    - ./users.d:/etc/clickhouse-server/users.d
```

## query_log と query_thread_log の仕様
まず、[query_log](https://clickhouse.com/docs/en/operations/server-configuration-parameters/settings#server_configuration_parameters-query-log) と [query_thread_log](https://clickhouse.com/docs/en/operations/server-configuration-parameters/settings#server_configuration_parameters-query_thread_log) の仕様を公式ドキュメントで確認すると、

- **query_log**
query_log は `log_queries`というパラメータで制御できる。

>Setting for logging queries received with the log_queries=1 setting.

- **query_thread_log**

query_thread_log は `log_query_threads` というパラメータで制御できる。

>Setting for logging threads of queries received with the log_query_threads=1 setting.

さらに、[log_query_threads](https://clickhouse.com/docs/en/operations/settings/settings#settings-log-query-threads) を確認すると、log_query_threads は log_queries が有効化されているときに機能するようです。つまり、`log_query_threads=1`にしても`log_queries=0`であれば、query_thread_log は無効化されそうということとが分かります。

>Setting up query threads logging.
>Query threads log into the system.query_thread_log table. **This setting has effect only when log_queries is true.** Queries’ threads run by ClickHouse with this setup are logged according to the rules in the query_thread_log server configuration parameter.
>Possible values:
>0 — Disabled.
1 — Enabled.
Default value: 1.


## query_logは残して、query_thread_logのみ無効化したい場合

ClickHouseコンテナに入って、設定を反映する前の状態を確認すると、query_log と query_thread_log は両方ともあります。`log_queries` と `log_query_threads` は両方とも`1`になっています。

```bash
# コンテナに入る
docker exec -it clickhouse bash
```

```bash
root@clickhouse:/# clickhouse-client
ClickHouse client version 22.3.12.19 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 22.3.12 revision 54455.

clickhouse :) USE system;

USE system

Query id: a47c9fd4-31fe-48a5-b351-2dc7f590d03a

Ok.

0 rows in set. Elapsed: 0.001 sec. 

clickhouse :) SHOW tables;

SHOW TABLES

Query id: a20b9dd0-6c68-4fca-aa63-ab145912ede1

┌─name───────────────────────────┐
│ aggregate_function_combinators │
│ asynchronous_inserts           │
│ asynchronous_metric_log        │
│ asynchronous_metrics           │
│ build_options                  │
│ clusters                       │
│ collations                     │
│ columns                        │
│ contributors                   │
│ current_roles                  │
│ data_skipping_indices          │
│ data_type_families             │
│ databases                      │
│ detached_parts                 │
│ dictionaries                   │
│ disks                          │
│ distributed_ddl_queue          │
│ distribution_queue             │
│ enabled_roles                  │
│ errors                         │
│ events                         │
│ formats                        │
│ functions                      │
│ grants                         │
│ graphite_retentions            │
│ licenses                       │
│ macros                         │
│ merge_tree_settings            │
│ merges                         │
│ metric_log                     │
│ metrics                        │
│ models                         │
│ mutations                      │
│ numbers                        │
│ numbers_mt                     │
│ one                            │
│ part_moves_between_shards      │
│ parts                          │
│ parts_columns                  │
│ privileges                     │
│ processes                      │
│ projection_parts               │
│ projection_parts_columns       │
│ query_log                      │
│ query_thread_log               │
│ quota_limits                   │
│ quota_usage                    │
│ quotas                         │
│ quotas_usage                   │
│ replicas                       │
│ replicated_fetches             │
│ replicated_merge_tree_settings │
│ replication_queue              │
│ rocksdb                        │
│ role_grants                    │
│ roles                          │
│ row_policies                   │
│ session_log                    │
│ settings                       │
│ settings_profile_elements      │
│ settings_profiles              │
│ stack_trace                    │
│ storage_policies               │
│ table_engines                  │
│ table_functions                │
│ tables                         │
│ time_zones                     │
│ trace_log                      │
│ user_directories               │
│ users                          │
│ warnings                       │
│ zeros                          │
│ zeros_mt                       │
└────────────────────────────────┘

73 rows in set. Elapsed: 0.008 sec. 

clickhouse :) SELECT *
              FROM system.settings
              WHERE name LIKE '%log%'

SELECT *
FROM system.settings
WHERE name LIKE '%log%'

Query id: 8348574f-097b-40be-815c-ef5f3c5e4912

┌─name──────────────────────────────┬─value───────┬─changed─┬─description────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─min──┬─max──┬─readonly─┬─type───────────┐
│ log_queries                       │ 1           │       0 │ Log requests and write the log to the system table.                                                                                                                │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Bool           │
│ log_formatted_queries             │ 0           │       0 │ Log formatted queries and write the log to the system table.                                                                                                       │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Bool           │
│ log_queries_min_type              │ QUERY_START │       0 │ Minimal type in query_log to log, possible values (from low to high): QUERY_START, QUERY_FINISH, EXCEPTION_BEFORE_START, EXCEPTION_WHILE_PROCESSING.               │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ LogQueriesType │
│ log_queries_min_query_duration_ms │ 0           │       0 │ Minimal time for the query to run, to get to the query_log/query_thread_log/query_views_log.                                                                       │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Milliseconds   │
│ log_queries_cut_to_length         │ 100000      │       0 │ If query length is greater than specified threshold (in bytes), then cut query when writing to query log. Also limit length of printed query in ordinary text log. │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ UInt64         │
│ log_queries_probability           │ 1           │       0 │ Log queries with the specified probabality.                                                                                                                        │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Float          │
│ log_profile_events                │ 1           │       0 │ Log query performance statistics into the query_log, query_thread_log and query_views_log.                                                                         │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Bool           │
│ log_query_settings                │ 1           │       0 │ Log query settings into the query_log.                                                                                                                             │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Bool           │
│ log_query_threads                 │ 1           │       0 │ Log query threads into system.query_thread_log table. This setting have effect only when 'log_queries' is true.                                                    │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Bool           │
│ log_query_views                   │ 1           │       0 │ Log query dependent views into system.query_views_log table. This setting have effect only when 'log_queries' is true.                                             │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Bool           │
│ log_comment                       │             │       0 │ Log comment into system.query_log table and server log. It can be set to arbitrary string no longer than max_query_size.                                           │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ String         │
│ send_logs_level                   │ fatal       │       0 │ Send server text logs with specified minimum level to client. Valid values: 'trace', 'debug', 'information', 'warning', 'error', 'fatal', 'none'                   │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ LogsLevel      │
└───────────────────────────────────┴─────────────┴─────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────┴──────┴──────────┴────────────────┘

12 rows in set. Elapsed: 0.003 sec. 

clickhouse :)  
```

では、`log_query_threads=0`に設定して再起動します。

```xml:log_queries.xml
<yandex>
    <profiles>
        <default>
            <!--<log_queries>0</log_queries>-->
            <log_query_threads>0</log_query_threads>
        </default>
    </profiles>
</yandex>
```

ClickHouseコンテナに入って、設定後の状態を確認すると、query_log は存在しますが、query_thread_log は存在していません。`log_queries=1` と `log_query_threads=0` になっています。`log_queries` のデフォルトは`1`なので、query_log は有効になっています。

```bash
root@clickhouse:/# clickhouse-client
ClickHouse client version 22.3.12.19 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 22.3.12 revision 54455.

clickhouse :) USE system;

USE system

Query id: 25170370-5a0d-4877-a1c7-90ee5ea3c89e

Ok.

0 rows in set. Elapsed: 0.001 sec. 

clickhouse :) SHOW tables;

SHOW TABLES

Query id: 9dc0577f-7ba1-4d06-a2d0-15cba86c754c

┌─name───────────────────────────┐
│ aggregate_function_combinators │
│ asynchronous_inserts           │
│ asynchronous_metric_log        │
│ asynchronous_metrics           │
│ build_options                  │
│ clusters                       │
│ collations                     │
│ columns                        │
│ contributors                   │
│ current_roles                  │
│ data_skipping_indices          │
│ data_type_families             │
│ databases                      │
│ detached_parts                 │
│ dictionaries                   │
│ disks                          │
│ distributed_ddl_queue          │
│ distribution_queue             │
│ enabled_roles                  │
│ errors                         │
│ events                         │
│ formats                        │
│ functions                      │
│ grants                         │
│ graphite_retentions            │
│ licenses                       │
│ macros                         │
│ merge_tree_settings            │
│ merges                         │
│ metric_log                     │
│ metrics                        │
│ models                         │
│ mutations                      │
│ numbers                        │
│ numbers_mt                     │
│ one                            │
│ part_moves_between_shards      │
│ parts                          │
│ parts_columns                  │
│ privileges                     │
│ processes                      │
│ projection_parts               │
│ projection_parts_columns       │
│ query_log                      │
│ quota_limits                   │
│ quota_usage                    │
│ quotas                         │
│ quotas_usage                   │
│ replicas                       │
│ replicated_fetches             │
│ replicated_merge_tree_settings │
│ replication_queue              │
│ rocksdb                        │
│ role_grants                    │
│ roles                          │
│ row_policies                   │
│ session_log                    │
│ settings                       │
│ settings_profile_elements      │
│ settings_profiles              │
│ stack_trace                    │
│ storage_policies               │
│ table_engines                  │
│ table_functions                │
│ tables                         │
│ time_zones                     │
│ trace_log                      │
│ user_directories               │
│ users                          │
│ warnings                       │
│ zeros                          │
│ zeros_mt                       │
└────────────────────────────────┘

72 rows in set. Elapsed: 0.003 sec. 

clickhouse :) SELECT *
              FROM system.settings
              WHERE name LIKE '%log%'

SELECT *
FROM system.settings
WHERE name LIKE '%log%'

Query id: ae0be479-7196-40ca-b1d7-8e104e5b658d

┌─name──────────────────────────────┬─value───────┬─changed─┬─description────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─min──┬─max──┬─readonly─┬─type───────────┐
│ log_queries                       │ 1           │       0 │ Log requests and write the log to the system table.                                                                                                                │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Bool           │
│ log_formatted_queries             │ 0           │       0 │ Log formatted queries and write the log to the system table.                                                                                                       │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Bool           │
│ log_queries_min_type              │ QUERY_START │       0 │ Minimal type in query_log to log, possible values (from low to high): QUERY_START, QUERY_FINISH, EXCEPTION_BEFORE_START, EXCEPTION_WHILE_PROCESSING.               │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ LogQueriesType │
│ log_queries_min_query_duration_ms │ 0           │       0 │ Minimal time for the query to run, to get to the query_log/query_thread_log/query_views_log.                                                                       │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Milliseconds   │
│ log_queries_cut_to_length         │ 100000      │       0 │ If query length is greater than specified threshold (in bytes), then cut query when writing to query log. Also limit length of printed query in ordinary text log. │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ UInt64         │
│ log_queries_probability           │ 1           │       0 │ Log queries with the specified probabality.                                                                                                                        │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Float          │
│ log_profile_events                │ 1           │       0 │ Log query performance statistics into the query_log, query_thread_log and query_views_log.                                                                         │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Bool           │
│ log_query_settings                │ 1           │       0 │ Log query settings into the query_log.                                                                                                                             │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Bool           │
│ log_query_threads                 │ 0           │       1 │ Log query threads into system.query_thread_log table. This setting have effect only when 'log_queries' is true.                                                    │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Bool           │
│ log_query_views                   │ 1           │       0 │ Log query dependent views into system.query_views_log table. This setting have effect only when 'log_queries' is true.                                             │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Bool           │
│ log_comment                       │             │       0 │ Log comment into system.query_log table and server log. It can be set to arbitrary string no longer than max_query_size.                                           │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ String         │
│ send_logs_level                   │ fatal       │       0 │ Send server text logs with specified minimum level to client. Valid values: 'trace', 'debug', 'information', 'warning', 'error', 'fatal', 'none'                   │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ LogsLevel      │
└───────────────────────────────────┴─────────────┴─────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────┴──────┴──────────┴────────────────┘

12 rows in set. Elapsed: 0.003 sec. 

clickhouse :) 
```

## query_log と query_thread_log の両方を無効化したい場合

以下の設定でClickHouseを再起動します。

```xml:log_queries.xml
<yandex>
    <profiles>
        <default>
            <log_queries>0</log_queries>
            <!--<log_query_threads>0</log_query_threads>-->
        </default>
    </profiles>
</yandex>
```

ClickHouseコンテナに入って、設定後の状態を確認すると、query_log と query_thread_log が存在していません。`log_queries=0` と `log_query_threads=1` になっています。`log_query_threads` は `log_queries=1` の時有効化されるので、`log_queries=0` かつ `log_query_threads=1`では、query_threads_log は有効化されないことが分かります。

```
root@clickhouse:/# clickhouse-client 
ClickHouse client version 22.3.12.19 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 22.3.12 revision 54455.

clickhouse :) USE system;

USE system

Query id: f3cb744b-c569-4de9-b798-51084a5a4461

Ok.

0 rows in set. Elapsed: 0.002 sec. 

clickhouse :) SHOW tables;

SHOW TABLES

Query id: 9e414c6b-2220-4708-99a9-151a97787c2e

┌─name───────────────────────────┐
│ aggregate_function_combinators │
│ asynchronous_inserts           │
│ asynchronous_metric_log        │
│ asynchronous_metrics           │
│ build_options                  │
│ clusters                       │
│ collations                     │
│ columns                        │
│ contributors                   │
│ current_roles                  │
│ data_skipping_indices          │
│ data_type_families             │
│ databases                      │
│ detached_parts                 │
│ dictionaries                   │
│ disks                          │
│ distributed_ddl_queue          │
│ distribution_queue             │
│ enabled_roles                  │
│ errors                         │
│ events                         │
│ formats                        │
│ functions                      │
│ grants                         │
│ graphite_retentions            │
│ licenses                       │
│ macros                         │
│ merge_tree_settings            │
│ merges                         │
│ metric_log                     │
│ metrics                        │
│ models                         │
│ mutations                      │
│ numbers                        │
│ numbers_mt                     │
│ one                            │
│ part_moves_between_shards      │
│ parts                          │
│ parts_columns                  │
│ privileges                     │
│ processes                      │
│ projection_parts               │
│ projection_parts_columns       │
│ quota_limits                   │
│ quota_usage                    │
│ quotas                         │
│ quotas_usage                   │
│ replicas                       │
│ replicated_fetches             │
│ replicated_merge_tree_settings │
│ replication_queue              │
│ rocksdb                        │
│ role_grants                    │
│ roles                          │
│ row_policies                   │
│ session_log                    │
│ settings                       │
│ settings_profile_elements      │
│ settings_profiles              │
│ stack_trace                    │
│ storage_policies               │
│ table_engines                  │
│ table_functions                │
│ tables                         │
│ time_zones                     │
│ trace_log                      │
│ user_directories               │
│ users                          │
│ warnings                       │
│ zeros                          │
│ zeros_mt                       │
└────────────────────────────────┘

71 rows in set. Elapsed: 0.003 sec. 

clickhouse :) SELECT *
              FROM system.settings
              WHERE name LIKE '%log%'

SELECT *
FROM system.settings
WHERE name LIKE '%log%'

Query id: 11c2f45d-5e8c-46d9-a248-3cad5697fd72

┌─name──────────────────────────────┬─value───────┬─changed─┬─description────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─min──┬─max──┬─readonly─┬─type───────────┐
│ log_queries                       │ 0           │       1 │ Log requests and write the log to the system table.                                                                                                                │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Bool           │
│ log_formatted_queries             │ 0           │       0 │ Log formatted queries and write the log to the system table.                                                                                                       │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Bool           │
│ log_queries_min_type              │ QUERY_START │       0 │ Minimal type in query_log to log, possible values (from low to high): QUERY_START, QUERY_FINISH, EXCEPTION_BEFORE_START, EXCEPTION_WHILE_PROCESSING.               │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ LogQueriesType │
│ log_queries_min_query_duration_ms │ 0           │       0 │ Minimal time for the query to run, to get to the query_log/query_thread_log/query_views_log.                                                                       │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Milliseconds   │
│ log_queries_cut_to_length         │ 100000      │       0 │ If query length is greater than specified threshold (in bytes), then cut query when writing to query log. Also limit length of printed query in ordinary text log. │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ UInt64         │
│ log_queries_probability           │ 1           │       0 │ Log queries with the specified probabality.                                                                                                                        │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Float          │
│ log_profile_events                │ 1           │       0 │ Log query performance statistics into the query_log, query_thread_log and query_views_log.                                                                         │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Bool           │
│ log_query_settings                │ 1           │       0 │ Log query settings into the query_log.                                                                                                                             │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Bool           │
│ log_query_threads                 │ 1           │       0 │ Log query threads into system.query_thread_log table. This setting have effect only when 'log_queries' is true.                                                    │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Bool           │
│ log_query_views                   │ 1           │       0 │ Log query dependent views into system.query_views_log table. This setting have effect only when 'log_queries' is true.                                             │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ Bool           │
│ log_comment                       │             │       0 │ Log comment into system.query_log table and server log. It can be set to arbitrary string no longer than max_query_size.                                           │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ String         │
│ send_logs_level                   │ fatal       │       0 │ Send server text logs with specified minimum level to client. Valid values: 'trace', 'debug', 'information', 'warning', 'error', 'fatal', 'none'                   │ ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ │        0 │ LogsLevel      │
└───────────────────────────────────┴─────────────┴─────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────┴──────┴──────────┴────────────────┘

12 rows in set. Elapsed: 0.004 sec. 

clickhouse :) 
```

# まとめ

query_log と query_thread_log の制御方法について調査しました。
理解できれば簡単ですが、少々ややこしい仕様なので設定を変更するときには注意が必要です。