はじめに

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

結論

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

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

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 の両方を無効化したい場合

log_queries.xml

<yandex>
    <profiles>
        <default>
            <log_queries>0</log_queries>
            <!--<log_query_threads>0</log_query_threads>-->
        </default>
    </profiles>
</yandex>

参考

検証

ソース

Docker で ClickHouseを構築して検証します。ClickHouseのバージョンは22.3.12.19を利用しました。

.
├── docker-compose.yaml
└── users.d
    └── log_queries.xml

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 と 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 を確認すると、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になっています。

# コンテナに入る
docker exec -it clickhouse 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に設定して再起動します。

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 は有効になっています。

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を再起動します。

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