clickhouse为何删除完数据系统磁盘空间没有恢复

当我们把存储在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

system.query_thread_log

system.trace_log

解决方案：

 1.在对应表上加上TTL

2.在配置文件加上TTL配置

---

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'

再次查看磁盘大小，发现数据已经自动删除：

​