526互联

clickhouse系统日志

发布时间 2023-12-15 14:45:13作者: 秋来叶黄

在操作clickhouse的时候，会有一些日志被记录下来，日志占用的空间也不少。我们可以设置一下

查询日志 query_log

调用查询语句时，会记录日志，记录sql语句，使用的数据库和表，占用的内存等。
https://clickhouse.com/docs/en/operations/system-tables/query_log

设置定期删除

在config.xml中有配置查询日志的位置

<!-- Query log. Used only for queries with setting log_queries = 1. -->
    <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.com/docs/en/table_engines/mergetree-family/custom_partitioning_key/
            Example:
                event_date
                toMonday(event_date)
                toYYYYMM(event_date)
                toStartOfHour(event_time)
        -->
        <partition_by>toYYYYMM(event_date)</partition_by>
        <!--
            Table TTL specification: https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/#mergetree-table-ttl
            Example:
                event_date + INTERVAL 1 WEEK
                event_date + INTERVAL 7 DAY DELETE
                event_date + INTERVAL 2 WEEK TO DISK 'bbb'

        <ttl>event_date + INTERVAL 30 DAY DELETE</ttl>
        -->

        <!--
            ORDER BY expr: https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#order_by
            Example:
                event_date, event_time
                event_date, type, query_id
                event_date, event_time, initial_query_id

        <order_by>event_date, event_time, initial_query_id</order_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>
        <!-- Maximal size in lines for the logs. When non-flushed logs amount reaches max_size, logs dumped to the disk. -->
        <max_size_rows>1048576</max_size_rows>
        <!-- Pre-allocated size in lines for the logs. -->
        <reserved_size_rows>8192</reserved_size_rows>
        <!-- Lines amount threshold, reaching it launches flushing logs to the disk in background. -->
        <buffer_size_rows_flush_threshold>524288</buffer_size_rows_flush_threshold>
        <!-- Indication whether logs should be dumped to the disk in case of a crash -->
        <flush_on_crash>false</flush_on_crash>

        <!-- example of using a different storage policy for a system table -->
        <!-- storage_policy>local_ssd</storage_policy -->
    </query_log>

关闭查询日志

在users.xml中，配置策略，为你的用户指定策略<log_queries>0</log_queries>。0表示关闭查询日志，1表示打开查询日志。

    <!-- Profiles of settings. -->
    <profiles>
        <!-- Default settings. -->
        <default>
                        <log_queries>0</log_queries>
        </default>
    </profiles>

https://clickhouse.com/docs/en/operations/settings/settings#log-queries

part_log 分区日志

我们知道clickhouse可以对数据存储进行聚合分区，比如设定一天，可以把一天的数据合并到一个数据块。这样可以提高效率，也方便管理（主要就是定时删除旧的数据）。
https://clickhouse.com/docs/en/operations/system-tables/part_log
https://clickhouse.com/docs/en/operations/server-configuration-parameters/settings#part-log

设置

同样在config.xml中有设置

    <part_log>
        <database>system</database>
        <table>part_log</table>
        <partition_by>toYYYYMM(event_date)</partition_by>
        <flush_interval_milliseconds>7500</flush_interval_milliseconds>
        <max_size_rows>1048576</max_size_rows>
        <reserved_size_rows>8192</reserved_size_rows>
        <buffer_size_rows_flush_threshold>524288</buffer_size_rows_flush_threshold>
        <flush_on_crash>false</flush_on_crash>
    </part_log>

trace_log 跟踪日志

抽样分析的日志，分析当前clickhouse的运行状态

asynchronous_metric_log

记录操作asynchronous_metric表的日志。asynchronous_metric表记录了对内存等使用的信息

query_views_log

对view的查询日志
<log_query_views>0</log_query_views>关闭查询视图日志

设置ttl

除了查询日志，很多日志都无法关闭，可以设置ttl，定义超过多久的数据删除

<ttl>event_date + INTERVAL 30 DAY DELETE</ttl>

如果定义log中有<engine></engine>字段，就不可以用<ttl></ttl>，需要在<engine></engine>中定义ttl，不然启动clickhouse会报错

[ 1591898 ] {} <Error> Application: Code: 36. DB::Exception: If 'engine' is specified for system table, TTL parameters should be specified directly inside 'engine' and 'ttl' setting doesn't make sense. (BAD_ARGUMENTS), Stack trace (when copying this message, always include the lines below):