Skip to content
Product
DBaaS
Service-Specific
ClickHouse
How-To
Check data distribution between storage devices in Managed ClickHouse®'s tiered storage

Check data distribution between storage devices in Managed ClickHouse®'s tiered storage

Monitor how your data is distributed between the two layers of your tiered storage: Network-attached block storage and object storage.

If you have the tiered storage feature enabled, your data in Managed ClickHouse is distributed between two storage devices (tiers). You can check on what storage devices your databases and tables are stored. You can also preview their total sizes as well as part counts, minimum part sizes, median part sizes, and maximum part sizes.

Prerequisites

Run a data distribution check with the ClickHouse client

  1. Connect to your Managed ClickHouse service using, for example, the ClickHouse client.

  2. Run the following query:

    SELECT
    database,
    table,
    disk_name,
    formatReadableSize(sum(data_compressed_bytes)) AS total_size,
    count(*) AS parts_count,
    formatReadableSize(min(data_compressed_bytes)) AS min_part_size,
    formatReadableSize(median(data_compressed_bytes)) AS median_part_size,
    formatReadableSize(max(data_compressed_bytes)) AS max_part_size
FROM system.parts
GROUP BY
    database,
    table,
    disk_name
ORDER BY
    database ASC,
    table ASC,
    disk_name ASC

    You can expect output similar to the following, here for the trips table from the quick start with its oldest partitions moved to object storage:

    ┌─database─┬─table─┬─disk_name─┬─total_size─┬─parts_count─┬─min_part_size─┬─median_part_size─┬─max_part_size─┐
│ default  │ trips │ default   │ 39.52 MiB  │           2 │ 18.09 MiB     │ 19.76 MiB        │ 21.43 MiB     │
│ default  │ trips │ remote    │ 117.53 MiB │           3 │ 30.29 MiB     │ 40.29 MiB        │ 46.94 MiB     │
└──────────┴───────┴───────────┴────────────┴─────────────┴───────────────┴──────────────────┴───────────────┘

The query returns a table with data distribution details for all databases and tables that belong to your service: the storage device they use, their total sizes as well as parts counts and sizing.

What’s next

Last updated on
Use query cache in Managed ClickHouse®