cacheDB: the local cache tier, which hosts caches for recently written and hot data and resides on local node disks.durableDB: the remote persistent tier, which hosts uploaded cold data and resides in COS.information_schema.TDSTORE_TIERED_STORAGE_USAGE is used to view the capacity usage of cold tables in cacheDB and durableDB. It displays the approximate data volume for each table/index/partition at the granularity of (TINDEX_ID, REPLICATION_GROUP_ID). Querying from any SQL node allows you to obtain the capacity distribution of cold tables across the entire cluster. This is commonly used for capacity planning, upload progress assessment, and resource optimization.Column Name | Data Type | Nullable or Not | Description |
TINDEX_ID | BIGINT UNSIGNED | No | TDStore internal data object ID, which uniquely identifies a table, index, or partition. |
REPLICATION_GROUP_ID | BIGINT UNSIGNED | No | ID of the RG where the data resides. A single object can be distributed across multiple RGs, with each RG displayed in a separate row. |
TABLE_SCHEMA | VARCHAR(64) | Yes | Name of the database to which the data belongs (schema). |
TABLE_NAME | VARCHAR(64) | Yes | Name of the primary table to which the data belongs. For a partition table, the primary table name is always displayed. |
PARTITION_NAME | VARCHAR(64) | Yes | Partition name. For a non-partition table, this column is NULL. |
INDEX_NAME | VARCHAR(64) | Yes | Index name. A primary key is uniformly displayed as PRIMARY. |
INDEX_TYPE | VARCHAR(64) | Yes | Index type. Valid values: PRIMARY, UNIQUE, MULTIPLE, FULLTEXT, SPATIAL. |
CACHE_DB_SIZE | BIGINT UNSIGNED | No | Approximate data size of the index/partition in cacheDB, in bytes. |
DURABLE_DB_SIZE | BIGINT UNSIGNED | No | Approximate data size of the index/partition in durableDB, in bytes. |
TOTAL_SIZE | BIGINT UNSIGNED | No | Total data size, which equals CACHE_DB_SIZE + DURABLE_DB_SIZE. |
tdstore_tiered_storage_usage_view_mode:Parameter Name | Scope | Default Value | Value Range | Description |
tdstore_tiered_storage_usage_view_mode | GLOBAL | 1 | 0,1,2 | Controls the data source mode for the view: 0: Disables the view feature and returns an error upon query.1: Aggregates heartbeat data through the MC (Meta Cluster), with low I/O overhead. Recommended for use in production environments.2: Initiates RPC queries to TDStore nodes in real time, providing more real-time data but with higher overhead. |
Limit | Description |
Data Range | Only displays cold table data that has entered tiered storage. Regular tables (hot tables) are not within the view's scope. |
Permission Requirements | To query this view, you must have the permission to access information_schema. |
Data Precision | All SIZE columns are approximate estimates based on metadata, primarily used for observing resource usage and are not suitable as a basis for precise billing.The arithmetic relationship CACHE_DB_SIZE + DURABLE_DB_SIZE = TOTAL_SIZE always holds precisely. |
DDL Concurrency | If a DROP TABLE or DROP INDEX operation is executed concurrently during a query, the deleted objects are skipped from the results, and the query does not fail. |
Query Frequency | The view is a low-frequency Ops interface. It is recommended to query it on demand in scenarios such as capacity assessment and inspection, and it is not advisable to continuously poll it in high-frequency business paths. |
Version Requirement | TDSQL Boundless version V21.6.3.0 or later. |
DESC information_schema.TDSTORE_TIERED_STORAGE_USAGE;
+----------------------+-----------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------------------+-----------------+------+-----+---------+-------+| TINDEX_ID | bigint unsigned | NO | | NULL | || REPLICATION_GROUP_ID | bigint unsigned | NO | | NULL | || TABLE_SCHEMA | varchar(64) | YES | | NULL | || TABLE_NAME | varchar(64) | YES | | NULL | || PARTITION_NAME | varchar(64) | YES | | NULL | || INDEX_NAME | varchar(64) | YES | | NULL | || INDEX_TYPE | varchar(64) | YES | | NULL | || CACHE_DB_SIZE | bigint unsigned | NO | | NULL | || DURABLE_DB_SIZE | bigint unsigned | NO | | NULL | || TOTAL_SIZE | bigint unsigned | NO | | NULL | |+----------------------+-----------------+------+-----+---------+-------+
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, INDEX_NAME, INDEX_TYPE,CACHE_DB_SIZE, DURABLE_DB_SIZE, TOTAL_SIZEFROM information_schema.TDSTORE_TIERED_STORAGE_USAGEWHERE TABLE_SCHEMA = 'cold_db' AND TABLE_NAME = 't_cold_data'ORDER BY INDEX_NAME;
+--------------+-------------+----------------+------------+------------+---------------+-----------------+------------+| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | INDEX_NAME | INDEX_TYPE | CACHE_DB_SIZE | DURABLE_DB_SIZE | TOTAL_SIZE |+--------------+-------------+----------------+------------+------------+---------------+-----------------+------------+| cold_db | t_cold_data | NULL | idx_val | MULTIPLE | 1024 | 8192 | 9216 || cold_db | t_cold_data | NULL | PRIMARY | PRIMARY | 4096 | 32768 | 36864 |+--------------+-------------+----------------+------------+------------+---------------+-----------------+------------+
SELECT TABLE_NAME, PARTITION_NAME, INDEX_NAME, INDEX_TYPE,CACHE_DB_SIZE, DURABLE_DB_SIZE, TOTAL_SIZEFROM information_schema.TDSTORE_TIERED_STORAGE_USAGEWHERE TABLE_SCHEMA = 'cold_db' AND TABLE_NAME = 't_cold_part'ORDER BY PARTITION_NAME, INDEX_NAME;
+-------------+----------------+------------+------------+---------------+-----------------+------------+| TABLE_NAME | PARTITION_NAME | INDEX_NAME | INDEX_TYPE | CACHE_DB_SIZE | DURABLE_DB_SIZE | TOTAL_SIZE |+-------------+----------------+------------+------------+---------------+-----------------+------------+| t_cold_part | p0 | PRIMARY | PRIMARY | 2048 | 16384 | 18432 || t_cold_part | p1 | PRIMARY | PRIMARY | 2048 | 16384 | 18432 || t_cold_part | p2 | PRIMARY | PRIMARY | 2048 | 16384 | 18432 |+-------------+----------------+------------+------------+---------------+-----------------+------------+
SELECT TABLE_SCHEMA,TABLE_NAME,SUM(CACHE_DB_SIZE) AS CACHE_TOTAL,SUM(DURABLE_DB_SIZE) AS DURABLE_TOTAL,SUM(TOTAL_SIZE) AS GRAND_TOTALFROM information_schema.TDSTORE_TIERED_STORAGE_USAGEGROUP BY TABLE_SCHEMA, TABLE_NAMEORDER BY GRAND_TOTAL DESC;
cacheDB is significantly higher than that in durableDB, it indicates that a large amount of cold data has not been uploaded. You can investigate this by checking the progress of the upload task.SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, INDEX_NAME,CACHE_DB_SIZE, DURABLE_DB_SIZE,ROUND(CACHE_DB_SIZE * 100.0 / NULLIF(TOTAL_SIZE, 0), 2) AS CACHE_PCTFROM information_schema.TDSTORE_TIERED_STORAGE_USAGEWHERE TOTAL_SIZE > 0ORDER BY CACHE_PCT DESCLIMIT 10;
1 aggregates data through MC with low I/O overhead. When more real-time data is required, you can temporarily switch to mode 2:-- Switch to real-time RPC modeSET GLOBAL tdstore_tiered_storage_usage_view_mode = 2;-- Execute the querySELECT * FROM information_schema.TDSTORE_TIERED_STORAGE_USAGE LIMIT 10;-- Switch back to the default mode after the query is completedSET GLOBAL tdstore_tiered_storage_usage_view_mode = 1;
tdstore_tiered_storage_usage_view_mode is set to 0, this view query will directly return an error. You must reset it to 1 or 2 for normal use.Was this page helpful?
You can also Contact sales or Submit a Ticket for help.
Help us improve! Rate your documentation experience in 5 mins.
Feedback