-
-
Save sanchezzzhak/511fd140e8809857f8f1d84ddb937015 to your computer and use it in GitHub Desktop.
SELECT table, | |
formatReadableSize(sum(bytes)) as size, | |
min(min_date) as min_date, | |
max(max_date) as max_date | |
FROM system.parts | |
WHERE active | |
GROUP BY table |
awesome scripts!
I add a compression percentage as find them easier than ratios at-a-glance
select parts.*,
columns.compressed_size,
columns.uncompressed_size,
columns.compression_ratio,
columns.compression_percentage
from (
select table,
formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size,
formatReadableSize(sum(data_compressed_bytes)) AS compressed_size,
round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes), 3) AS compression_ratio,
round((100 - (sum(data_compressed_bytes) * 100) / sum(data_uncompressed_bytes)), 3) AS compression_percentage
from system.columns
group by table
) columns
right join (
select table,
sum(rows) as rows,
max(modification_time) as latest_modification,
formatReadableSize(sum(bytes)) as disk_size,
formatReadableSize(sum(primary_key_bytes_in_memory)) as primary_keys_size,
any(engine) as engine,
sum(bytes) as bytes_size
from system.parts
where active
group by database, table
) parts on columns.table = parts.table
order by parts.bytes_size desc;
Thanks a lot. Very helpful scripts.
Thx you guys and gals! Real useful sqls! You've just inspired me to code a little exporter to view the presented data in prometheus or grafana.
Thx you guys and gals! Real useful sqls! You've just inspired me to code a little exporter to view the presented data in prometheus or grafana.
Mmm... Smth like this https://github.com/alexeyantropov/clickhouse_table_exporter/blob/main/src/exporter.py
@alexeyantropov do you mind to add some custom queries? Have you finished to code a little exporter?
@alexeyantropov do you mind to add some custom queries? Have you finished to code a little exporter?
I've finished and use it in my production environment. But sure I can add some extra queries and metrics. What have you to suggest?
Hey all, awesome scripts! Many thanks for this contributions ❤️