当我们把存储在clickhouse中的数据全部清除后,为何磁盘空间没有恢复最初状态呢?其实有一点,我们忽略了,当磁盘存储或查询,本身运行都会存在一些日志,而数据清除了,但是日志却还在,因为clickhosue日志文件默认是不会自动清理的,所以我们需要我们针对日志文件的存储做单独的策略配置,常用的方法就是给日志文件加TTL。
可以通过以下指令查看磁盘的使用清空:
select
partition,
database,
table,
sum(rows) as row,
formatReadableSize(sum(bytes_on_disk)) as used_disk,
formatReadableSize(sum(data_uncompressed_bytes)) as before_compress,
formatReadableSize(sum(data_compressed_bytes)) as after_compress,
round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes) * 100, 0) compress_rate
from system.parts
--and database = 'system'
group by table,database,partition
order by row desc limit 10;
不难发现,trace_log表占用了大量的磁盘空间
原因是:ClickHouse中的query_log、query_thread_log、trace_log等日志表,默认是不会自动清理的,因此这些日志表会随着时间推移占用越来越多的存储空间。下面看下看下这3个日志的具体d定义以及解决方案:
目录
system.query_log
查询执行的信息。已执行查询的信息,例如开始时间、处理持续时间、错误消息。不包含 INSERT 语句,可以在config.xml里设置query_log,可以通过setting log_queries = 0关闭,不会自动删除记录,可以设置ttl属性的表。通过SYSTEM FLUSH LOGS可以强制刷写。每个查询在 query_log 表中创建1~2行,具体取决于查询的状态:
1. 查询执行成功,则会创建 QueryStart 和 QueryFinish 类型的事件。
2. 查询期间发生错误,则会创建QueryStart 和 ExceptionWhileProcessing 类型的事件。
3. 查询之前发生错误,则会创建一个具有 ExceptionBeforeStart 类型的事件。
type (Enum8) -- 执行查询时发生的事件类型: 'QueryStart' = 1 -- 成功开始查询执行。 'QueryFinish' = 2 -- 查询执行成功结束。 'ExceptionBeforeStart' = 3 -- 查询执行开始前的异常。 'ExceptionWhileProcessing' = 4 -- 查询执行期间的异常。 event_date (Date) -- 查询开始日期。 event_time (DateTime) -- 查询开始时间。 event_time_microseconds (DateTime) -- 以微秒精度查询开始时间。 query_start_time (DateTime) -- 查询执行的开始时间。 query_start_time_microseconds (DateTime64) -- 以微秒精度开始执行查询的时间。 query_duration_ms (UInt64) -- 以毫秒为单位的查询执行持续时间。 read_rows (UInt64) -- 从参与查询的所有表和表函数中读取的总行数。它包括通常的子查询、IN 和 JOIN 的子查询。对于分布式查询 read_rows 包括在所有副本读取的总行数。每个副本发送它的 read_rows 值,查询的服务器发起者汇总所有接收到的和本地的值。 read_bytes (UInt64) -- 从参与查询的所有表和表函数中读取的总字节数。它包括通常的子查询、IN 和 JOIN 的子查询。对于分布式查询 read_bytes 包括在所有副本读取的总行数。每个副本发送它的 read_bytes 值,查询的服务器发起者汇总所有接收到的和本地的值。 write_rows (UInt64) -- 对于 INSERT 查询,写入的行数。对于其他查询,列值为 0。 write_bytes (UInt64) -- 对于 INSERT 查询,写入的字节数。对于其他查询,列值为 0。 result_rows (UInt64) -- SELECT 查询结果中的行数,或 INSERT 查询中的行数。 result_bytes (UInt64) -- 用于存储查询结果的 RAM 容量(以字节为单位)。 memory_usage (UInt64) -- 查询的内存消耗。 current_database (String) -- 当前数据库的名称。 query (String) -- 查询语句。 normalized_query_hash (UInt64) -- 哈希值。 query_kind (LowCardinality(String)) -- 查询的类型。 database (Array(LowCardinality(String))) -- 查询中存在的数据库的名称。 tables (Array(LowCardinality(String))) -- 查询中存在的表的名称。 columns (Array(LowCardinality(String))) -- 查询中存在的列的名称。 exception_code (Int32) -- 异常代码。 exception(字符串)----异常消息。 stack_trace (String) -- 堆栈跟踪。如果查询成功完成,则为空字符串。 is_initial_query (UInt8) -- 查询类型。可能的值: 1 -- 查询由客户端发起。 0 -- 作为分布式查询执行的一部分,查询由另一个查询启动。 user (String) -- 发起当前查询的用户名。 query_id (String) -- 查询 ID。 address (IPv6) -- 用于进行查询的 IP 地址。 port (UInt16) -- 用于进行查询的客户端端口。 initial_user (String) -- 运行初始查询的用户名(用于分布式查询执行)。 initial_query_id (String) -- 初始查询的 ID(用于分布式查询执行)。 initial_address (IPv6) -- 启动父查询的 IP 地址。 initial_port (UInt16) -- 用于进行父查询的客户端端口。 interface (UInt8) -- 发起查询的接口。可能的值: 1 -- TCP。 2 -- HTTP。 os_user (String) -- 运行 clickhouse-client 的操作系统用户名。 client_hostname (String) -- 运行 clickhouse-client 或其他 TCP 客户端的客户端机器的主机名。 client_name (String) -- clickhouse-client 或其他 TCP 客户端名称。 client_revision (UInt32) -- clickhouse-client 或另一个 TCP 客户端的修订版。 client_version_major (UInt32) -- clickhouse-client 或其他 TCP 客户端的主要版本。 client_version_minor (UInt32) -- clickhouse-client 或其他 TCP 客户端的次要版本。 client_version_patch (UInt32) -- clickhouse-client 或其他 TCP 客户端版本的补丁组件。 http_method (UInt8) -- 发起查询的 HTTP 方法。可能的值: 0 -- 查询是从 TCP 接口启动的。 1 -- 使用了 GET 方法。 2 -- 使用了 POST 方法。 http_user_agent (String) -- 在 HTTP 查询中传递的 HTTP 标头 UserAgent。 http_referer (String) -- 在 HTTP 查询中传递的 HTTP 标头 Referer(包含进行查询的页面的绝对或部分地址)。 forwarded_for (String) -- 在 HTTP 查询中传递的 HTTP 标头 X-Forwarded-For。 quota_key (String) -- 在配额设置中指定的配额键(见 keyed)。 revision (UInt32) -- ClickHouse 修订版。 log_comment (String) -- 记录备注。它可以设置为不超过 max_query_size 的任意字符串。如果未定义,则为空字符串。 thread_ids (Array(UInt64)) -- 参与查询执行的线程 ID。 ProfileEvents.Names (Array(String)) -- 衡量不同指标的计数器。它们的描述可以在表 system.events 中找到 ProfileEvents.Values (Array(UInt64)) -- ProfileEvents.Names 列中列出的指标值。 Settings.Names (Array(String)) -- 客户端运行查询时更改的设置名称。要启用对设置的日志更改,请将 log_query_settings 参数设置为 1。 Settings.Values (Array(String)) -- Settings.Names 列中列出的设置值。 used_aggregate_functions (Array(String)) -- 在查询执行期间使用的聚合函数的规范名称。 used_aggregate_function_combinators (Array(String)) -- 在查询执行期间使用的聚合函数组合器的规范名称。 used_database_engines (Array(String)) -- 在查询执行期间使用的数据库引擎的规范名称。 used_data_type_families (Array(String)) -- 在查询执行期间使用的数据类型系列的规范名称。 used_dictionaries (Array(String)) -- 字典的规范名称,在查询执行期间使用。 used_formats (Array(String)) -- 格式的规范名称,在查询执行期间使用。 used_functions (Array(String)) -- 在查询执行期间使用的函数的规范名称。 used_storages (Array(String)) -- 存储的规范名称,在查询执行期间使用。 used_table_functions (Array(String)) -- 在查询执行期间使用的表函数的规范名称
system.query_thread_log
该表记录关于每个查询执行线程的信息。
ClickHouse只在指定query_thread_log服务器参数的情况下创建这个表。此参数设置日志记录规则,如记录时间间隔或将登录查询的表的名称。
要启用查询日志记录,请将log_query_threads参数设置为1。
列名:
event_date (Date) -- 线程完成查询执行的日期。 event_time (DateTime) -- 线程完成查询执行的日期和时间。 query_start_time (DateTime) -- 查询执行的开始时间。 query_duration_ms (UInt64) -- 查询执行的持续时间。 read_rows (UInt64) -- 读取行数。 read_bytes (UInt64) -- 读取字节数。 written_rows (UInt64) -- 对于插入查询,写入的行数。对于其他查询列值为0。 written_bytes (UInt64) -- 对于插入查询,写入的字节数。对于其他查询,值为0。 memory_usage (Int64) -- 在此线程上下文中分配和释放的内存数量之间的差异。 peak_memory_usage (Int64) -- 在此线程上下文中分配和释放的内存数量之间的最大差异。 thread_name (String) -- 线程的名称。 thread_number (UInt32) -- 内部线程ID。 os_thread_id (Int32) -- 操作系统线程ID。 master_thread_id (UInt64) -- OS初始线程的初始ID。 query (String) -- 查询字符串。 is_initial_query (UInt8) -- 查询类型。可能的取值: 1 -- 查询由客户端发起。 0 -- 查询由另一个查询发起,用于分布式查询执行。 user (String) -- 发起当前查询的用户的名称。 query_id (String) -- 查询ID。 address (IPv6) -- 发起查询的IP地址。 port (UInt16) -- 发起行查询的客户端端口。 initial_user (String) -- 运行初始查询的用户名(用于分布式查询执行)。 initial_query_id (String) -- 初始查询的ID(用于分布式查询执行)。 initial_address (IPv6) -- 启动父查询的IP地址。 initial_port (UInt16) -- 发起父查询的客户端端口。 interface (UInt8) -- 发起查询的接口。可能的取值: 1 -- TCP. 2 -- HTTP. os_user (String) -- 运行clickhouse-client的操作系统的用户名。 client_hostname (String) -- 运行clickhouse-client或另一个TCP客户端的客户端机器的主机名。 client_name (String) -- clickhouse-client或另一个TCP客户端名称。 client_revision (UInt32) -- clickhouse-client或另一个TCP客户端的修订版。 client_version_major (UInt32) -- lickhouse-client或另一个TCP客户端的主要版本。 client_version_minor (UInt32) -- lickhouse-client或另一个TCP客户端的小版本。 client_version_patch (UInt32) -- clickhouse-client或另一个TCP客户端版本的补丁组件。 http_method (UInt8) -- 发起查询的HTTP方法。可能的取值: 0 -- 查询是从TCP接口启动的。 1 -- 使用GET方法。 2 -- 采用POST方法。 http_user_agent (String) --在HTTP请求中传递的UserAgent请求头。 quota_key (String) -- 配额设置中指定的配额键。 revision (UInt32) -- ClickHouse 修订版。 thread_numbers (Array(UInt32)) -- 参与查询执行的线程数。 ProfileEvents.Names (Array(String)) -- 测量不同指标的机器数。它们的描述可以在system.events表中找到 ProfileEvents.Values (Array(UInt64)) -- 在ProfileEvents.Names 列中列出的指标值。
默认情况下,每隔7.5秒向表添加一次日志。可以在query_thread_log服务器设置中设置这个时间间隔(参考flush_interval_milliseconds参数)。要将日志从内存缓冲区强制刷新到表中,可以使用系统刷新日志查询。
当手动删除表时,将动态地自动创建该表。注意,所有以前的日志都将被删除。
system.trace_log
记录由抽样查询分析器收集的堆栈调用信息。
ClickHouse只有在trace_log服务器配置配置部分设置时才会创建这个表。还应该设置query_profiler_real_time__ns和query_profiler_cpu_time__ns配置。
要分析日志,请使用addressToLine、addressToSymbol和demangle自检功能。
event_date (Date) -- 采样时刻的日期。 event_time (DateTime) -- 采样时刻的时间戳。 timestamp_ns (UInt64) -- 采样时刻的时间戳纳秒。 revision (UInt32) -- ClickHouse服务器构建修订版。 timer_type (Enum8) -- 时间类型: Real 表示时钟时间。 CPU 表示CPU时间。 thread_number (UInt32) -- 线程标识符。 query_id (String) -- 查询标识符,可用于获取从query_log系统表查询运行的详细信息。 trace (Array(UInt64)) -- 抽样时间的堆栈调用跟踪。 每个元素都是ClickHouse服务器进程中的一个虚拟内存地址。
解决方案:
1.在对应表上加上TTL
保存15天的数据
ALTER TABLE system.query_log ON CLUSTER 'server-ch' MODIFY TTL event_date + INTERVAL 15 DAY
ALTER TABLE system.query_thread_log ON CLUSTER 'server-ch' MODIFY TTL event_date + INTERVAL 15 DAY
ALTER TABLE system.trace_log ON CLUSTER 'server-ch' MODIFY TTL event_date + INTERVAL 15 DAY
2.在配置文件加上TTL配置
(配置文件位置 /etc/clickhouse-server/config.xml)
- database – 数据库名
- table – 日志存放的系统表名
- partition_by — 系统表分区键,如果定义了 engine 则不能使用
- engine -系统表 表引擎,如果定义了 partition_by 则不能使用
- flush_interval_milliseconds – 将数据从内存的缓冲区刷新到表的时间间隔。
<query_log>
<database>system</database>
<table>query_log</table>
<engine>Engine = MergeTree PARTITION BY event_date ORDER BY event_time TTL event_date + INTERVAL 30 day</engine>
<flush_interval_milliseconds>7500</flush_interval_milliseconds>
</query_log>
可能会报错,如下所示:
Code: 159. DB::Exception: Received from localhost:9000. DB::Exception: Watching task /clickhouse/task_queue/ddl/server-ch/query-0000009454 is executing longer than distributed_ddl_task_timeout (=15) seconds. There are 3 unfinished hosts (0 of them are currently active), they are going to execute the query in background. (TIMEOUT_EXCEEDED)
这种常见的问题为: 集群分布式 DDL 被阻塞,可通过 show processlist查看阻塞情况
直接根据query_id kill掉阻塞的语句
kill query where query_id='34214551-fb59-4120-8faf-7bebb0986c20'
再次查看磁盘大小,发现数据已经自动删除: