默认账号default

文件位置 /etc/clickhouse-server/users.d/default-password.xml
密码采用sha256加密替换即改密

慢查询定位

慢sql记录

select left(query, 100) as sql,count() as queryNum,sum(query_duration_ms) as totalTime,totalTime / queryNum as avgTime from system.query_log ql where event_time > toDateTime('2024-08-06 14:00:00') and event_time < toDateTime('2024-08-06 00:00:00') group by sql order by queryNum desc limit 10

当前正在运行的查询

SELECT initial_query_id, elapsed, formatReadableSize(memory_usage), formatReadableSize(peak_memory_usage), query FROM system.processes ORDER BY peak_memory_usage DESC LIMIT 10;

日志

tail -n 200 /var/log/clickhouse-server/clickhouse-server.log

ttl日志设置

查询表大小语句

SELECT   database,   table,   formatReadableSize(sum(bytes_on_disk)) AS total_size FROM system.parts where  active = 1 GROUP BY database, table;

查询partion

SELECT
    partition,
    name
FROM system.parts
WHERE (database = 'system') AND (table = 'trace_log'  )

删除partion

alter table system.trace_log    drop partition 202311

查询mutations

SELECT
    database,
    table,
    mutation_id,
    command,
    is_done
FROM system.mutations
ORDER BY create_time DESC

查看表结构

show create table system.asynchronous_metric_log

设置ttl

https://clickhouse.com/docs/zh/operations/system-tables
eg:

alter table system.trace_log  modify ttl event_date + toIntervalDay(7);
toDate(_timestamp)

第一种

ALTER table `system`.asynchronous_metric_log MODIFY TTL event_date + toIntervalDay(10);
ALTER table `system`.metric_log MODIFY TTL event_date + toIntervalDay(10);
ALTER table `system`.part_log MODIFY TTL event_date + toIntervalDay(10);
ALTER table `system`.query_log MODIFY TTL event_date + toIntervalDay(10);
ALTER table `system`.trace_log MODIFY TTL event_date + toIntervalDay(10);

自己表

ALTER table `idmesh`.user_app_log MODIFY TTL toDate(_timestamp) + toIntervalDay(14);
ALTER table `idmesh`.sys_audit_log MODIFY TTL toDate(_timestamp) + toIntervalDay(14);
ALTER table `idmesh`.sys_app_log MODIFY TTL toDate(_timestamp) + toIntervalDay(14);

user_app_log

第二种

在/etc/clickhouse-server/config.xml中的表配置
event_date + INTERVAL 30 DAY DELETE

第三种

/etc/clickhouse-server/config.d中配置
如：

asynchronous_metric_log.xml

<clickhouse>
    <asynchronous_metric_log>
        <database>system</database>
        <table>asynchronous_metric_log</table>
        <flush_interval_milliseconds>7000</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>
        <ttl>event_date + INTERVAL 30 DAY DELETE</ttl>
        <flush_on_crash>false</flush_on_crash>
    </asynchronous_metric_log>
</clickhouse>

metric_log.xml

<clickhouse>
    <metric_log>
        <database>system</database>
        <table>metric_log</table>
        <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>
        <collect_interval_milliseconds>1000</collect_interval_milliseconds>
        <ttl>event_date + INTERVAL 30 DAY DELETE</ttl>
        <flush_on_crash>false</flush_on_crash>
    </metric_log>
</clickhouse>

part_log.xml

<clickhouse>
    <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>
        <ttl>event_date + INTERVAL 30 DAY DELETE</ttl>
        <flush_on_crash>false</flush_on_crash>
    </part_log>
</clickhouse>

query_log.xml

<clickhouse>
    <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>
        <ttl>event_date + INTERVAL 30 DAY DELETE</ttl>
        <!-- example of using a different storage policy for a system table -->
        <!-- storage_policy>local_ssd</storage_policy -->
    </query_log>
</clickhouse>

trace_log.xml

<clickhouse>
    <trace_log>
        <database>system</database>
        <table>trace_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>
        <ttl>event_date + INTERVAL 30 DAY DELETE</ttl>
        <!-- Indication whether logs should be dumped to the disk in case of a crash -->
        <flush_on_crash>false</flush_on_crash>
    </trace_log>
</clickhouse>
最后修改：2025 年 01 月 03 日
© 允许规范转载
如果觉得我的文章对你有用，请随意赞赏