ClickHouse(system.query_log表)-开启慢sql查询_clickhouse 慢查询怎么看-CSDN博客

本文链接：https://blog.csdn.net/qq_40341628/article/details/108974611

ClickHouse(system.query_log表)开启慢sql查询

开启慢sql查询日志方法：

打开各个ClickHouse实例的users.xml文件，在默认配置最上层profile（如default）中加入：

<log_queries>1</log_queries>

注意：如添加该参数之后表不自动创建，重启clickhouse-server服务即可

检查每个节点ch中config.xml文件里默认的日志配置是否开启

<query_log>
        <!-- What table to insert data. If table is not exist, it will be created.
             When query log structure is changed after system update,
              then old table will be renamed and new table will be created automatically.
        -->
        <database>system</database>
        <table>query_log</table>
        <!--
            PARTITION BY expr https://clickhouse.yandex/docs/en/table_engines/custom_partitioning_key/
            Example:
                event_date
                toMonday(event_date)
                toYYYYMM(event_date)
                toStartOfHour(event_time)
        -->
        <partition_by>toYYYYMM(event_date)</partition_by>

        <!-- Instead of partition_by, you can provide full engine expression (starting with ENGINE = ) with parameters,
             Example: <engine>ENGINE = MergeTree PARTITION BY toYYYYMM(event_date) ORDER BY (event_date, event_time) SETTINGS index_granularity = 1024</engine>
          -->
        <!-- Interval of flushing data. -->
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    </query_log>
    <!-- Trace log. Stores stack traces collected by query profilers.
         See query_profiler_real_time_period_ns and query_profiler_cpu_time_period_ns settings. -->

query_log表列介绍：

type (Enum8) — Type of an event that occurred when executing the query. Values:
- ‘QueryStart’ = 1 — Successful start of query execution.
- ‘QueryFinish’ = 2 — Successful end of query execution.
- ‘ExceptionBeforeStart’ = 3 — Exception before the start of query execution.
- ‘ExceptionWhileProcessing’ = 4 — Exception during the query execution.
event_date (Date) — Query starting date.
event_time (DateTime) — Query starting time.
event_time_microseconds (DateTime) — Query starting time with microseconds precision.
query_start_time (DateTime) — Start time of query execution.
query_start_time_microseconds (DateTime64) — Start time of query execution with microsecond precision.
query_duration_ms (UInt64) — Duration of query execution in milliseconds.
read_rows (UInt64) — Total number or rows read from all tables and table functions participated in query. It includes usual subqueries, subqueries for IN and JOIN. For distributed queries read_rows includes the total number of rows read at all replicas. Each replica sends it’s read_rows value, and the server-initiator of the query summarize all received and local values. The cache volumes doesn’t affect this value.
read_bytes (UInt64) — Total number or bytes read from all tables and table functions participated in query. It includes usual subqueries, subqueries for IN and JOIN. For distributed queries read_bytes includes the total number of rows read at all replicas. Each replica sends it’s read_bytes value, and the server-initiator of the query summarize all received and local values. The cache volumes doesn’t affect this value.
written_rows (UInt64) — For INSERT queries, the number of written rows. For other queries, the column value is 0.
written_bytes (UInt64) — For INSERT queries, the number of written bytes. For other queries, the column value is 0.
result_rows (UInt64) — Number of rows in a result of the SELECT query, or a number of rows in the INSERT query.
result_bytes (UInt64) — RAM volume in bytes used to store a query result.
memory_usage (UInt64) — Memory consumption by the query.
query (String) — Query string.
exception (String) — Exception message.
exception_code (Int32) — Code of an exception.
stack_trace (String) — Stack trace. An empty string, if the query was completed successfully.
is_initial_query (UInt8) — Query type. Possible values:
- 1 — Query was initiated by the client.
- 0 — Query was initiated by another query as part of distributed query execution.
user (String) — Name of the user who initiated the current query.
query_id (String) — ID of the query.
address (IPv6) — IP address that was used to make the query.
port (UInt16) — The client port that was used to make the query.
initial_user (String) — Name of the user who ran the initial query (for distributed query execution).
initial_query_id (String) — ID of the initial query (for distributed query execution).
initial_address (IPv6) — IP address that the parent query was launched from.
initial_port (UInt16) — The client port that was used to make the parent query.
interface (UInt8) — Interface that the query was initiated from. Possible values:
- 1 — TCP.
- 2 — HTTP.
os_user (String) — Operating system username who runs clickhouse-client.
client_hostname (String) — Hostname of the client machine where the clickhouse-client or another TCP client is run.
client_name (String) — The clickhouse-client or another TCP client name.
client_revision (UInt32) — Revision of the clickhouse-client or another TCP client.
client_version_major (UInt32) — Major version of the clickhouse-client or another TCP client.
client_version_minor (UInt32) — Minor version of the clickhouse-client or another TCP client.
client_version_patch (UInt32) — Patch component of the clickhouse-client or another TCP client version.
http_method (UInt8) — HTTP method that initiated the query. Possible values:
- 0 — The query was launched from the TCP interface.
- 1 — GET method was used.
- 2 — POST method was used.
http_user_agent (String) — The UserAgent header passed in the HTTP request.
quota_key (String) — The “quota key” specified in the quotas setting (see keyed).
revision (UInt32) — ClickHouse revision.
thread_numbers (Array(UInt32)) — Number of threads that are participating in query execution.
ProfileEvents.Names (Array(String)) — Counters that measure different metrics. The description of them could be found in the table system.events
ProfileEvents.Values (Array(UInt64)) — Values of metrics that are listed in the ProfileEvents.Names column.
Settings.Names (Array(String)) — Names of settings that were changed when the client ran the query. To enable logging changes to settings, set the log_query_settings parameter to 1.
Settings.Values (Array(String)) — Values of settings that are listed in the Settings.Names column.

备注：partition_by表示查询日志表的分区列，语法与普通建表时相同，默认按月分区。flush_interval_milliseconds则表示日志刷入表中的周期，默认7.5秒，可以根据取数时效自己调节

注意事项：

需在每个节点users.xml配置文件添加该参数，并重启server服务

外部查询需要创建分布式表，以便统计所用节点的查询日志

CREATE TABLE IF NOT EXISTS system.query_log_all
ON CLUSTER sht_ck_cluster_pro
AS system.query_log
ENGINE = Distributed(sht_ck_cluster_pro,system,query_log,rand());