Question about usage of the data in the table

Off-Topic
1

Hi, there

Which meta-table should I refer to for memory usage and disk usage shown on the screen below?
Could you show me the query statement to see the usage of the data in the table?

image
image753Ă—240 9.29 KB

thanks in advance.
ChaeYoung.

2

I don’t have all the queries available. More than one query is used to generate that information. If you want to get creative, you can see the queries that Studio is running using queries like this:

select * from information_schema.mv_queries where query_text like '%size%';
select * from information_schema.mv_queries where query_text like '%memory%';

The queries aggregate information from information_schema.TABLE_STATISTICS and information_schema.COLUMNAR_SEGMENTS. Here are some examples:

SELECT DATABASE_NAME AS databaseName, SUM(COMPRESSED_SIZE) AS diskUsage 
FROM INFORMATION_SCHEMA.COLUMNAR_SEGMENTS 
GROUP BY DATABASE_NAME 
ORDER BY diskUsage DESC;

SELECT IFNULL(SUM(MEMORY_USE), 0) AS memoryUsage FROM INFORMATION_SCHEMA.TABLE_STATISTICS;
3

Feature request: an “advanced” button in each of the dashboard pages in Studio that shows the query that populated that view.

4

@robrich that’d nice but it’s more complex than that - Studio uses a multitude of tables and then runs some algorithms/joins on top of various queries to show nice and clean data to the user.

We should definitely document the queries that power all these tables though, and there’s an internal request for that open.

5

@chaeyoung.ko for that specific screenshot Studio uses:

    SELECT
        SCHEMA_NAME as databaseName
    FROM
        INFORMATION_SCHEMA.SCHEMATA
    ORDER BY
        SCHEMA_NAME ASC

    SELECT * FROM (
        SELECT
            'information_schema' AS databaseName,
            0 AS partitionCount,
            NULL AS syncRepl,
            "" AS remoteName
        UNION ALL
        SELECT
            DATABASE_NAME as databaseName,
            NUM_PARTITIONS AS partitionCount,
            IS_SYNC AS syncRepl,
            REMOTE_NAME AS remoteName
        FROM
            INFORMATION_SCHEMA.DISTRIBUTED_DATABASES
    ) dummy
    ORDER BY
        databaseName ASC;

(for each database)

    SHOW TABLES IN ? EXTENDED

And then to get memory_usage and disk_usage, we add up from information_schema.table_statistics and information_schema.columnar_segments, respectively.

1 Like