How to check memory space usage by clickhouse on S3 disk?

Question

I am currently using S3 as disk for clickhouse to store a few tables. How can you check the memory used by a clickhouse on the different disks with a simple sql query ?

I had a few ideas like this:

select name, (total_space - free_space)/pow(10, 9) as used_space_Gb from system.disks

gives 0 used space for S3 :/

also this query give the space usage for every table, but we cannot see on which disk

SELECT name, total_bytes/(pow(10, 9)) from system.tables where database = 'default' or database = 'test'

Answer 1

try this

SELECT
    disk_name,
    database,
    table,
    formatReadableSize(sum(data_compressed_bytes) AS size) AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes) AS usize) AS uncompressed,
    round(usize / size, 2) AS compr_rate,
    sum(rows) AS rows,
    count() AS part_count
FROM system.parts
WHERE (active = 1) AND (table LIKE '%') AND (database LIKE '%')
GROUP BY
    disk_name,
    database,
    table
ORDER BY size DESC;