View and analyze the data size of a cluster or table

View the data size of a cluster

In the Storage Overview section, you can view the following metrics of the current cluster: Total Data Size, Hot Data Size, Cold Data Size, and Data Growth (including Last 24 Hours and Daily Last Week).

1. Log on to the Alibaba Cloud AnalyticDB for MySQL console. In the upper-left corner, select your cluster's region. In the left-side navigation pane, click Clusters, and then click the ID of your cluster.

2. In the left-side navigation pane, choose Storage Analysis > Storage Overview.

3. In the Storage Overview section, view the Total Data Size, Hot Data Size, Cold Data Size, and Data Growth metrics.

   Category	Metric	Description
   Total Data Size	Total Data Size	The total data size of all tables in the AnalyticDB for MySQL cluster.
   	Hot Data Size	The hot data size of all tables in the AnalyticDB for MySQL cluster. Note For more information about hot data, see Tiered storage of hot and cold data.
   	Cold Data Size	The cold data size of all tables in the AnalyticDB for MySQL cluster. Note AnalyticDB for MySQL Data Warehouse Edition (V3.0) clusters in reserved mode do not support separation of hot and cold data storage. For these clusters, the cold data size is not displayed. For more information about cold data, see Tiered storage of hot and cold data.
   Hot Data Size Note Each table of an AnalyticDB for MySQL cluster contains multiple partitions, such as hot data partitions and cold data partitions. The Total Data Size parameter of each partition is divided into the following data types: Table Record, Regular Index, Primary Key Index, and Other.	Table Record	The data size of hot partitions in all tables of the AnalyticDB for MySQL cluster, excluding data of the Regular Index or Primary Key Index type.
   	Regular Index	The data size of regular indexes in hot partitions of all tables of the AnalyticDB for MySQL cluster. Note When you create a table in an AnalyticDB for MySQL cluster, a full-column index is created by default. If indexes contain large amounts of data, you can delete specific idle indexes to reduce the data size. For more information, see Schema optimization.
   	Primary Key Index	The data size of primary key indexes in hot partitions of all tables of the AnalyticDB for MySQL cluster. Note If specific primary key indexes contain large amounts of data, you can find the tables that contain the primary key indexes in the Table Storage Information section and optimize the indexes. For information about the Table Storage Information section, see the "View the data size of a table" section of this topic.
   	Other	The size of metadata and temporary data in hot partitions of all tables of the AnalyticDB for MySQL cluster.
   Cold Data Size Note Each table of an AnalyticDB for MySQL cluster contains multiple partitions, such as hot data partitions and cold data partitions. The Total Data Size parameter of each partition is divided into the following data types: Table Record, Regular Index, Primary Key Index, and Other.	Table Record	The data size of cold partitions in all tables of the AnalyticDB for MySQL cluster, excluding data of the Regular Index or Primary Key Index type.
   	Regular Index	The data size of regular indexes in cold partitions of all tables of the AnalyticDB for MySQL cluster. Note When you create a table in an AnalyticDB for MySQL cluster, a full-column index is created by default. If indexes contain large amounts of data, you can delete specific idle indexes to reduce the data size. For more information, see Schema optimization.
   	Primary Key Index	The data size of primary key indexes in cold partitions of all tables of the AnalyticDB for MySQL cluster. Note If specific primary key indexes contain large amounts of data, you can find the tables that contain the primary key indexes in the Table Storage Information section and optimize the indexes. For information about the Table Storage Information section, see the "View the data size of a table" section of this topic.
   	Other	The size of metadata and temporary data in cold partitions of all tables of the AnalyticDB for MySQL cluster.
   Data Growth	Last 24 Hours	The growth of the total data size of the AnalyticDB for MySQL cluster within the last 24 hours. Formula: Data growth within the last 24 hours = Data size collected at the current time - Data size collected 24 hours ago.
   	Daily Last Week	The daily growth of the total data size of the AnalyticDB for MySQL cluster within the last week. Formula: Daily growth within the last week = (Data size collected at the current time - Data size collected at the same time seven days ago)/7.

View the data size and disk usage of nodes

In the Storage Trend section, you can view the data size and disk usage of nodes within a specific period of time.

1. Log on to the Alibaba Cloud AnalyticDB for MySQL console. In the upper-left corner, select your cluster's region. In the left-side navigation pane, click Clusters, and then click the ID of your cluster.

2. In the left-side navigation pane, choose Storage Analysis > Storage Overview.

3. View the data size and disk usage of nodes within a specific period of time. You can select a time range from the drop-down list in the Storage Trend section. You can also drag the pointer in the Total Data Size or Node Disk Usage chart to select a time range.

   Important

   • You can view storage information within the last 30 days. You can select a time range of up to seven days to view the storage information.

   • Compared with AnalyticDB for MySQL Data Warehouse Edition (V3.0) in elastic mode for Cluster Edition and AnalyticDB for MySQL Data Lakehouse Edition (V3.0), the metrics displayed in the Total Data Size chart for AnalyticDB for MySQL Data Warehouse Edition (V3.0) in reserved mode include only Total Data Size, Average Data Size of Storage Nodes, and Maximum Data Size of Storage Nodes. This is because AnalyticDB for MySQL Data Warehouse Edition (V3.0) in reserved mode does not support separation of hot and cold data storage.Total Data Size

   Elastic mode for Cluster Edition and Data Lakehouse Edition (V3.0)

   Category	Metric	Description
   Total Data Size	Average Hot Data Size of Storage Nodes	The average hot data size of all storage nodes in the AnalyticDB for MySQL cluster.
   	Maximum Hot Data Size of Storage Nodes	The maximum hot data size of all storage nodes in the AnalyticDB for MySQL cluster.
   	Hot Data Size	The hot data size of all tables in the AnalyticDB for MySQL cluster.
   	Cold Data Size	The cold data size of all tables in the AnalyticDB for MySQL cluster.
   Node Disk Usage	Average Node Disk Usage	The average disk usage of all storage nodes in the AnalyticDB for MySQL cluster.
   	Maximum Disk Usage of Node	The maximum disk usage of all storage nodes in the AnalyticDB for MySQL cluster. Formula: Maximum disk usage = Maximum node storage/(Maximum hot data size of an EIU/0.9) × 100%. Important The maximum node storage is measured in GB. The data storage can be automatically scaled in AnalyticDB for MySQL Data Warehouse Edition (V3.0) in elastic mode for Cluster Edition and AnalyticDB for MySQL Data Lakehouse Edition (V3.0), but the hot data size of an elastic I/O unit (EIU) is limited to 8 TB. When you calculate the maximum disk usage, you must use a value of 8000 GB. For example, if the maximum hot data size of nodes is 8000 GB, the maximum disk usage is calculated by using the following formula: 8000/(8000/0.9) × 100% = 90%.
   	Disk Usage Threshold (90%)	If the maximum disk usage of the AnalyticDB for MySQL cluster exceeds the disk usage threshold (90%), the cluster is locked. After the cluster is locked, you can query data from the cluster and cannot write data to the cluster. We recommend that you log on to the CloudMonitor console and set the maximum disk usage threshold to 80%. This allows you to handle issues in a timely manner and prevent the cluster from being locked. For more information, see Create an alert rule.

   Reserved mode

   Category	Metric	Description
   Total Data Size	Total Data Size	The total data size of all tables in the AnalyticDB for MySQL cluster.
   	Average Data Size of Storage Nodes	The average data size of all storage nodes in the AnalyticDB for MySQL cluster.
   	Maximum Data Size of Storage Nodes	The maximum data size of all storage nodes in the AnalyticDB for MySQL cluster.
   Node Disk Usage	Average Node Disk Usage	The average disk usage of all storage nodes in the AnalyticDB for MySQL cluster.
   	Maximum Disk Usage of Node	The maximum disk usage of all storage nodes in the AnalyticDB for MySQL cluster. Formula: Maximum disk usage = (Maximum storage of node groups/Storage of each node group) × 100%. Note The maximum storage of node groups and the storage of each node group are measured in GB. For example, you purchase two node groups. The storage of each node group is 200 GB. The total data size of all tables in the cluster is 140 GB, including 60 GB in one node group and 80 GB in the other node group. In this case, the maximum disk usage is calculated by using the following formula: (80/200) × 100% = 40%.
   	Disk Usage Threshold (90%)	If the maximum disk usage of the AnalyticDB for MySQL cluster exceeds the disk usage threshold (90%), the cluster is locked. After the cluster is locked, you can query data from the cluster and cannot write data to the cluster. We recommend that you log on to the CloudMonitor console and set the maximum disk usage threshold to 80%. This allows you to handle issues in a timely manner and prevent the cluster from being locked. For more information, see Create an alert rule.

View the data size of a table

In the Table Storage Information section, you can view the storage information of each table, such as the Total Data Size, Hot Data Size, Cold Data Size, and Regular Index metrics.

1. Log on to the Alibaba Cloud AnalyticDB for MySQL console. In the upper-left corner, select your cluster's region. In the left-side navigation pane, click Clusters, and then click the ID of your cluster.

2. In the left-side navigation pane, choose Storage Analysis > Storage Overview.

3. In the Table Storage Information section, view table metrics such as Total Data Size, Hot Data Size, Cold Data Size, Table Rows, and Partitions.

   Metric	Description
   Database	The name of the database.
   Table Name	The name of the table.
   Storage Percentage	The percentage of the total data size of the table to the total data size of the cluster. Formula: Storage percentage = (Total data size of the table/Total data size of the cluster) × 100%.
   Total Data Size	The total data size of the table. The total data size can be calculated by using one of the following formulas: Formula 1: Total data size = Hot data size + Cold data size. Formula 2: Total data size = Data size of table records + Data size of regular indexes + Data size of primary key indexes + Data size of other data.
   Hot Data Size	The data size of all hot partitions in the table, including data of the Table Record, Regular Index, Primary Key Index, and Other types.
   Cold Data Size	The data size of all cold partitions in the table, including data of the Table Record, Regular Index, Primary Key Index, and Other types.
   Table Record	The data size of all partitions in the table, excluding data of the Regular Index or Primary Key Index type.
   Regular Index	The data size of regular indexes in all partitions of the table.
   Primary Key Index	The data size of the primary key index in all partitions of the table.
   Other	The size of metadata and temporary data in all partitions of the table.
   Table Rows	The number of rows in the table.
   Partitions	The number of partitions in the table.

Query storage usage by using SQL

You can also query the information_schema.kepler_partitions and information_schema.kepler_meta_tables system tables by using SQL statements to query the cluster-level storage usage and the hot and cold storage usage details of each table in the cluster. Perform the following steps:

1. Obtain the number of shards in the cluster.

   select count(1) from information_schema.kepler_meta_shards;

2. Replace ${shardCount} in the following SQL statement with the number of shards obtained in Step 1, and then execute the statement. This SQL statement is used to query the cluster-level storage usage summary.

   select sum(totalSize)     as totalSize,      -- Total data size of the cluster
          sum(hotTotalSize)  as hotTotalSize,    -- Total hot data size
          sum(hotDataSize)   as hotDataSize,     -- Hot data: table records
          sum(hotIndexSize)  as hotIndexSize,    -- Hot data: secondary indexes
          sum(hotPkSize)     as hotPkSize,       -- Hot data: primary key indexes
          sum(hotOtherSize)  as hotOtherSize,    -- Hot data: other (metadata, etc.)
          sum(coldTotalSize) as coldTotalSize,   -- Total cold data size
          sum(coldDataSize)  as coldDataSize,    -- Cold data: table records
          sum(coldIndexSize) as coldIndexSize,   -- Cold data: secondary indexes
          sum(coldPkSize)    as coldPkSize,      -- Cold data: primary key indexes
          sum(coldOtherSize) as coldOtherSize    -- Cold data: other (metadata, etc.)
   from (select lower(p.schema_name) as schemaName, lower(p.table_name) as tableName,
                (sum(LOCAL_DATA_SIZE) + sum(REMOTE_DATA_SIZE)) * if(t.distribute_type = 'broadcast', ${shardCount}, 1) as totalSize,
                sum(LOCAL_DATA_SIZE) * if(t.distribute_type = 'broadcast', ${shardCount}, 1) as hotTotalSize,
                sum(if(LOCAL_DATA_SIZE > 0, DETAIL_SIZE, 0)) * if(t.distribute_type = 'broadcast', ${shardCount}, 1) as hotDataSize,
                sum(if(LOCAL_DATA_SIZE > 0, INDEX_SIZE, 0)) * if(t.distribute_type = 'broadcast', ${shardCount}, 1) as hotIndexSize,
                sum(if(LOCAL_DATA_SIZE > 0, PK_SIZE, 0)) * if(t.distribute_type = 'broadcast', ${shardCount}, 1) as hotPkSize,
                sum(if(LOCAL_DATA_SIZE > 0, LOCAL_DATA_SIZE - DETAIL_SIZE - INDEX_SIZE - PK_SIZE, 0)) *
                if(t.distribute_type = 'broadcast', ${shardCount}, 1) as hotOtherSize,
                sum(REMOTE_DATA_SIZE) * if(t.distribute_type = 'broadcast', ${shardCount}, 1) as coldTotalSize,
                sum(if(REMOTE_DATA_SIZE > 0, DETAIL_SIZE, 0)) * if(t.distribute_type = 'broadcast', ${shardCount}, 1) as coldDataSize,
                sum(if(REMOTE_DATA_SIZE > 0, INDEX_SIZE, 0)) * if(t.distribute_type = 'broadcast', ${shardCount}, 1) as coldIndexSize,
                sum(if(REMOTE_DATA_SIZE > 0, PK_SIZE, 0)) * if(t.distribute_type = 'broadcast', ${shardCount}, 1) as coldPkSize,
                sum(if(REMOTE_DATA_SIZE > 0, REMOTE_DATA_SIZE - DETAIL_SIZE - INDEX_SIZE - PK_SIZE, 0)) *
                if(t.distribute_type = 'broadcast', ${shardCount}, 1) as coldOtherSize
         from information_schema.kepler_partitions p
                  join information_schema.kepler_meta_tables t
                       on lower(p.schema_name) = lower(t.table_schema) and
                          lower(p.table_name) = lower(t.table_name)
         where p.schema_name not in ('analyticdb', 'INFORMATION_SCHEMA', 'MYSQL')
         group by schemaName, tableName,t.distribute_type) pp;

3. Replace ${shardCount} in the following SQL statement with the number of shards obtained in Step 1, and replace ${totalSize} with the totalSize value from the query result in Step 2, and then execute the statement. This SQL statement is used to query the storage usage of each table.

   select
       lower(p.schema_name) as schemaName,                                                              -- Database name
       lower(p.table_name) as tableName,                                                                -- Table name
       count_if(p.partition_id >= 0) as totalPartitionCount,                                         -- Total number of partitions
       sum(row_count) as totalRowCount,                                                                 -- Total number of rows
       sum(detail_size) * if(t.distribute_type='broadcast', ${shardCount}, 1) as totalDataSize,         -- Table record data size
       sum(index_size) * if(t.distribute_type='broadcast', ${shardCount}, 1) as totalIndexSize,         -- Secondary index data size
       sum(pk_size) * if(t.distribute_type='broadcast', ${shardCount}, 1) as totalPkSize,               -- Primary key index data size
       sum(remote_data_size) * if(t.distribute_type='broadcast', ${shardCount}, 1) as totalRemoteDataSize, -- Cold data size
       sum(local_data_size) * if(t.distribute_type='broadcast', ${shardCount}, 1) as totalLocalDataSize,-- Hot data size
       (sum(remote_data_size) + sum(local_data_size))
           * if(t.distribute_type='broadcast', ${shardCount}, 1) as totalSize,                          -- Total data size (hot + cold)
       (sum(remote_data_size) + sum(local_data_size) - sum(detail_size) - sum(index_size) - sum(pk_size))
           * if(t.distribute_type='broadcast', ${shardCount}, 1) as otherSize,                          -- Other data size (metadata, temporary data, etc.)
       round(100 * (sum(remote_data_size) + sum(local_data_size))
           * if(t.distribute_type='broadcast', ${shardCount}, 1) / ${totalSize}, 2) as spaceRatio       -- Space usage ratio (%)
   from information_schema.kepler_partitions p
       join information_schema.kepler_meta_tables t
           on lower(p.schema_name) = lower(t.table_schema)
           and lower(p.table_name) = lower(t.table_name)
   where p.schema_name not in ('analyticdb', 'INFORMATION_SCHEMA', 'MYSQL')
   -- (Optional) Filter by exact database name
   -- AND lower(p.schema_name) = 'your_db_name'
   -- (Optional) Filter by fuzzy table name match
   -- AND LOWER(p.table_name) LIKE '%keyword%'
   group by schemaName, tableName, t.distribute_type
   order by totalSize desc;

Related operations

FAQ

PURGE RECYCLE_BIN TABLE <table_name_in_ADB_RECYCLE_BIN>;