ClickHouse查看数据库容量、表的指标、表分区、数据大小等。
业务表空间
通过 system.parts 查询数据库容量大小。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SELECT
formatReadableSize(sum(bytes)) AS bytes_size,
formatReadableSize(sum(primary_key_bytes_in_memory)) AS primary_keys_size,
formatReadableSize(sum(data_uncompressed_bytes)) AS `原始大小`,
formatReadableSize(sum(data_compressed_bytes)) AS `压缩大小`,
round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100, 0) AS `压缩率`
FROM system.parts
where database = 'default'
order by bytes_size desc
limit 10;
┌─bytes_size─┬─primary_keys_size─┬─原始大小──┬─压缩大小──┬─压缩率──┐
│ 11.49 GiB │ 1.07 MiB │ 24.01 GiB │ 11.48 GiB │ 48 │
└────────────┴───────────────────┴───────────┴───────────┴────────┘
|
查看表分区情况
1
2
3
4
5
6
7
8
9
10
11
12
|
SELECT
partition
FROM system.parts
WHERE active
AND database = 'default'
AND table = 'user'
┌─partition─┐
│ 210602 │
│ 210602 │
└───────────┘
|
系统日志表
ClickHouse有许多系统表System Table，
其中一些用来存储日志，比如：query_log, query_thread_log, trace_log, part_log, metric_log 等等。
系统日志表默认是不会清理，会随着时间增长，最终可能导致磁盘空间不足。所以需要定期清理这些日志。
通过 system.parts 查看日志表大小
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SELECT sum(rows) AS `总行数`,
formatReadableSize(sum(data_uncompressed_bytes)) AS `原始大小`,
formatReadableSize(sum(data_compressed_bytes)) AS `压缩大小`,
round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100, 0) AS `压缩率`,
`table` AS `表名`
FROM system.parts
where database = 'system'
group by `table`
order by table asc;
┌─────总行数─┬─原始大小──┬─压缩大小───┬─压缩率─┬─表名──────────────────────┐
│ 2513009176 │ 35.12 GiB │ 1.69 GiB │ 5 │ asynchronous_metric_log │
│ 13941652 │ 49.52 GiB │ 1.33 GiB │ 3 │ metric_log │
│ 68050999 │ 15.60 GiB │ 2.75 GiB │ 18 │ part_log │
│ 41 │ 57.71 KiB │ 15.30 KiB │ 27 │ query_log │
│ 4457997 │ 6.08 GiB │ 178.01 MiB │ 3 │ query_views_log │
│ 230288475 │ 68.45 GiB │ 4.40 GiB │ 6 │ trace_log │
└────────────┴───────────┴────────────┴────────┴─────────────────────────┘
|
清理日志表
系统日志表默认配置按月分区，query_log为例，其配置信息如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
<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>
-->
<!-- 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>
|
查询query_log表的分区信息
1
2
3
4
5
6
7
8
9
10
11
12
|
> select partition,name from system.parts where database='system' and table='query_log';
┌─partition─┬─name───────────┐
│ 202306 │ 202306_1_14_3 │
│ 202306 │ 202306_1_19_4 │
│ 202306 │ 202306_15_15_0 │
│ 202306 │ 202306_16_16_0 │
│ 202306 │ 202306_17_17_0 │
│ 202306 │ 202306_18_18_0 │
│ 202306 │ 202306_19_19_0 │
└───────────┴────────────────┘
|
有两种方法可以删除query_log表中数据。
1
|
alter table system.query_log delete where event_date < '2023-06-01';
|
当表数据量大时，执行效率低。
1
|
alter table system.query_log drop partition '202306';
|
当删除分区过大时（默认为50GB），会删除失败。
解决办法参考max_partition_size_to_drop
定期清理日志表
依赖人定期清理效率低，且容易出错失败，可以通过设置日志表TTL，给日志设置自动过期时间，自动清理。
1
|
alter table system.query_log modify ttl event_date + toIntervalDay(15);
|
设置query_log表日志过期时间为15天。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
<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 15 DAY DELETE</ttl>
<!-- 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>
|
设置ttl为15天过期。
或者在engine参数中设置
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
<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 15 DAY DELETE</ttl>
<!-- 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>
-->
<engine>Engine = MergeTree PARTITION BY event_date ORDER BY event_time TTL event_date + INTERVAL 15 day</engine>
<!-- Interval of flushing data. -->
<flush_interval_milliseconds>7500</flush_interval_milliseconds>
</query_log>
|
修改配置后重启clickhouse-server
1
|
sudo /etc/init.d/clickhouse-server restart
|
重启成功后，系统日志表可能会分表，如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
┌─────总行数─┬─原始大小───┬─压缩大小──┬─压缩率─┬─表名────────────────────────┐
│ 36447928 │ 522.15 MiB │ 28.36 MiB │ 5 │ asynchronous_metric_log │
│ 2055180396 │ 28.72 GiB │ 1.31 GiB │ 5 │ asynchronous_metric_log_0 │
│ 39861 │ 153.50 MiB │ 11.49 MiB │ 7 │ metric_log │
│ 10888256 │ 40.95 GiB │ 1.61 GiB │ 4 │ metric_log_0 │
│ 150279 │ 36.24 MiB │ 6.82 MiB │ 19 │ part_log │
│ 399724812 │ 94.02 GiB │ 15.58 GiB │ 17 │ part_log_0 │
│ 238332 │ 292.44 MiB │ 29.52 MiB │ 10 │ query_log │
│ 387395848 │ 448.59 GiB │ 43.70 GiB │ 10 │ query_log_0 │
│ 46233632 │ 59.73 GiB │ 1.87 GiB │ 3 │ query_views_log │
│ 255595 │ 76.37 MiB │ 7.44 MiB │ 10 │ trace_log │
│ 539787879 │ 161.39 GiB │ 13.79 GiB │ 9 │ trace_log_0 │
└────────────┴────────────┴───────────┴────────┴───────────────────────────┘
|
可以选择删除或清理旧的日志表来释放空间，比如：
1
2
3
4
5
|
alter table system.trace_log_0 modify ttl event_date + toIntervalDay(30);
alter table system.query_log_0 modify ttl event_date + toIntervalDay(30);
alter table system.part_log_0 modify ttl event_date + toIntervalDay(30);
alter table system.metric_log_0 modify ttl event_date + toIntervalDay(30);
alter table system.asynchronous_metric_log_0 modify ttl event_date + toIntervalDay(30);
|
参考