Tiered Storage Observability

This document describes how to monitor Infrequent Access (IA) storage, including storage class transition progress, IA read metrics at the SQL level, and IA cache performance at the cluster level.

Monitor storage class transitions

This section describes how to track the progress of a storage class conversion and how to review past conversions.

Conversion progress is tracked the same way whichever form you use to change the storage class:

• The STORAGE_CLASS syntactic sugar, for example ALTER TABLE t1 STORAGE_CLASS='IA'
• The ENGINE_ATTRIBUTE form, for example ALTER TABLE t1 ENGINE_ATTRIBUTE='{"storage_class":"IA"}'
• A partition-level change made with ENGINE_ATTRIBUTE

Partitioned tables support only ENGINE_ATTRIBUTE, so partition conversions appear in the same views as table-level conversions. A table or partition that is created directly in the IA storage class has no data to migrate, so it does not appear in these views.

The ALTER TABLE statement itself updates the schema metadata within seconds. The region-level data migration then runs asynchronously in TiKV and is decoupled from the DDL lifecycle, so ADMIN SHOW DDL JOBS does not report its progress. Use SHOW STORAGE_CLASS TRANSITIONS to track a conversion in progress, and query mysql.tidb_storage_class_transition_history to review past and ongoing conversions. A record is inserted into the history table when a conversion starts, with state = 'RUNNING', and is updated in place when the conversion reaches a final state.

View in-progress transitions

SHOW STORAGE_CLASS TRANSITIONS;
SHOW STORAGE_CLASS TRANSITIONS LIKE 'table_name';
SHOW STORAGE_CLASS TRANSITIONS WHERE DIRECTION = 'TO_STANDARD';

SHOW STORAGE_CLASS TRANSITIONS is equivalent to SELECT * FROM INFORMATION_SCHEMA.TIKV_STORAGE_CLASS_TRANSITIONS.

The INFORMATION_SCHEMA.TIKV_STORAGE_CLASS_TRANSITIONS table lists the conversions that are currently in progress. It has no state column, because every row in it is an in-progress conversion. The table has the following columns:

To check the progress and elapsed time of a specific conversion:

SELECT TABLE_NAME, DIRECTION, COMPLETED_REPLICAS, TOTAL_REPLICAS,
       ROUND(PROGRESS * 100, 1) AS PROGRESS_PCT,
       DURATION, LAST_UPDATE_TIME
FROM INFORMATION_SCHEMA.TIKV_STORAGE_CLASS_TRANSITIONS
WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'table_name';

A conversion goes through one in-progress state and ends in one of two final states. Every state is also recorded in mysql.tidb_storage_class_transition_history:

Determine whether a conversion is stuck

Watch LAST_UPDATE_TIME, COMPLETED_REPLICAS, and DURATION together:

• COMPLETED_REPLICAS increases and LAST_UPDATE_TIME keeps advancing: the conversion is progressing normally and the data volume is simply large.
• DURATION keeps growing but COMPLETED_REPLICAS does not increase for a long time: the conversion might be stuck because of a system exception, such as a TiKV rolling restart, temporarily insufficient resources, or short-term object storage unavailability.
• TOTAL_REPLICAS, COMPLETED_REPLICAS, PROGRESS, and LAST_UPDATE_TIME all stay NULL: no successful observation has been made yet. These columns are populated and cleared together, so LAST_UPDATE_TIME cannot tell whether observations are still being attempted. Check DURATION instead: it always increases while the conversion is being tracked. If these columns stay NULL while DURATION keeps growing, polling is still running but has not returned a valid observation.

You cannot resolve a stuck conversion yourself. Contact TiDB Cloud Support for help. After the issue is resolved, COMPLETED_REPLICAS continues to increase until the conversion reaches COMPLETED, and no additional action is required from you.

Reverse a conversion that is still in progress

If you issue a reverse conversion for a table or partition while the previous conversion is still in progress, the previous conversion is voided:

• In INFORMATION_SCHEMA.TIKV_STORAGE_CLASS_TRANSITIONS, the row for that table or partition is replaced by the new conversion. DIRECTION shows the new direction, START_TIME is the start time of the new conversion, and the progress counts from the beginning again. Only one row remains for that table or partition.
• The history record of the voided conversion in mysql.tidb_storage_class_transition_history is updated to state = 'SUPERSEDED'. Its finish_time is the start time of the new conversion, and its total_replicas and completed_replicas are the last values observed before it was voided. If nothing had been observed yet, both columns are NULL.

To find out how far a voided conversion had progressed, query the history table for records with state = 'SUPERSEDED'.

Query transition history

Every conversion is recorded in the mysql.tidb_storage_class_transition_history table when it starts, and its record is updated as the conversion progresses. The table has the following columns:

Use this table to estimate how long a similar conversion takes on your own cluster, which is more reliable than any reference figure from a test environment:

-- Average duration of completed conversions, grouped by direction.
-- Filter on state = 'COMPLETED': the duration of a SUPERSEDED record
-- does not represent a full conversion.
SELECT direction,
       COUNT(*) AS total_conversions,
       ROUND(AVG(duration), 0) AS avg_duration_sec,
       MIN(duration) AS min_duration_sec,
       MAX(duration) AS max_duration_sec
FROM mysql.tidb_storage_class_transition_history
WHERE state = 'COMPLETED'
GROUP BY direction;

-- The most recent finished conversion of a specific table
SELECT table_name, direction, state, duration AS total_duration_sec,
       total_replicas, completed_replicas, start_time, finish_time
FROM mysql.tidb_storage_class_transition_history
WHERE table_schema = 'db_name' AND table_name = 'table_name'
    AND state <> 'RUNNING'
ORDER BY finish_time DESC
LIMIT 1;

-- Conversions that were voided by a reverse conversion
SELECT table_schema, table_name, partition_name, direction,
       completed_replicas, total_replicas, start_time, finish_time, duration
FROM mysql.tidb_storage_class_transition_history
WHERE state = 'SUPERSEDED'
ORDER BY finish_time DESC;

Retention of history records

The maximum number of records retained in mysql.tidb_storage_class_transition_history is controlled by the system variable tidb_storage_class_transition_history_size. When the number of records exceeds this limit, the oldest records, ordered by finish_time, are removed first. Retention is enforced at most once per minute, so the row count can temporarily exceed the limit.

-- View the current retention limit
SELECT @@tidb_storage_class_transition_history_size;

-- Retain up to 500 records
SET GLOBAL tidb_storage_class_transition_history_size = 500;

tidb_storage_class_transition_history_size

• Scope: GLOBAL
• Persists to cluster: Yes
• Applies to hint SET_VAR: No
• Type: Integer
• Default value: 1000
• Range: [100, 100000]
• This variable is used to set the maximum number of storage class transition records retained in the mysql.tidb_storage_class_transition_history table. A larger value retains history for longer and uses more space in the mysql database.

Monitor IA reads at the SQL level

This section describes the IA metrics available in EXPLAIN ANALYZE, statement summary tables, slow query logs, and the TiDB Cloud console.

EXPLAIN ANALYZE

When a query involves remote data loading, the scan_detail includes the following fields:

EXPLAIN ANALYZE SELECT * FROM t_ia WHERE id BETWEEN 1 AND 50000;
-- The output includes:
-- ia_remote_read_segment_size: 2320453     -- Total bytes loaded remotely
-- ia_remote_read_segment_count: 3           -- Number of remote loading events
-- ia_remote_read_segment_wait_time: 0.008   -- Remote wait time (seconds)

Statement summary

STATEMENTS_SUMMARY, STATEMENTS_SUMMARY_HISTORY, and their CLUSTER_ counterparts include the following IA columns:

The AVG_ and MAX_ columns answer how much data each execution read remotely, while IA_EXEC_COUNT answers how many executions read remotely at all. Use both dimensions together: a statement with a small AVG_IA_REMOTE_READ_SEGMENT_SIZE but a high IA_EXEC_COUNT / EXEC_COUNT ratio accesses cold data frequently in small amounts.

For queries that do not involve IA tables, these columns are 0 or NULL.

To find the statements with the highest proportion of cold-read executions:

SELECT DIGEST_TEXT, EXEC_COUNT, IA_EXEC_COUNT,
       ROUND(IA_EXEC_COUNT / EXEC_COUNT * 100, 2) AS ia_exec_pct,
       AVG_IA_REMOTE_READ_SEGMENT_SIZE
FROM INFORMATION_SCHEMA.CLUSTER_STATEMENTS_SUMMARY_HISTORY
WHERE IA_EXEC_COUNT > 0
ORDER BY ia_exec_pct DESC
LIMIT 10;

Slow queries

INFORMATION_SCHEMA.CLUSTER_SLOW_QUERY includes the following IA columns:

• IA_remote_read_segment_count
• IA_remote_read_segment_size
• IA_remote_read_segment_wait_time

The same fields are available in the ADMIN SHOW SLOW output:

ADMIN SHOW SLOW RECENT 10;
ADMIN SHOW SLOW TOP INTERNAL 10;
ADMIN SHOW SLOW TOP ALL 10;

The field semantics and units are the same as in the SLOW_QUERY table. For a query that does not involve IA tables, the values are NULL or 0. The corresponding fields are also visible in the slow query details in the TiDB Cloud console.

SQL statement list in the console

The IA_EXEC_COUNT column is also displayed in the SQL statement diagnosis list:

• Cloud Console: Monitoring > Diagnosis > SQL Statement
• Clinic: Diagnosis > SQL Statements

The column is named Exec Count of IA and is placed right after Executions Count so that you can compare the two values directly. It supports ascending and descending sorting like the other columns. For a statement that does not involve IA tables, the value is 0.

Monitor IA cache performance at the cluster level

This section describes the cluster-level panels for IA cache behavior in the TiDB Cloud console.

IA Cache Performance panels

Path: Monitoring > Metrics > Instance Overview > IA Cache Performance.

You can change the time window with the time picker to observe longer trends. If the cluster has no IA tables, the panels show No IA data instead of 0% or an error.

To monitor the cold data volume of a single statement, use the IA Remote Read Segment Size panel in Monitoring > Diagnosis > Slow Query > Coprocessor.

Interpret the panels

Cache hit rates depend on your actual access patterns. Concentrated access can exceed 95%, while scattered access might fall below 95%.

• Sudden drop in hit rate: check whether IA Cache Miss Rate rises at the same time. A simultaneous rise confirms a real increase in cold reads rather than a collection issue. Then use IA_EXEC_COUNT in the statement summary tables to identify which statements triggered the cold reads.
• Sustained low hit rate: the cache is under pressure from cold data. Consider raising the IA cache level, or reducing the amount of data set to IA. See Configure and Manage Tiered Storage.
• Evaluating whether a table suits IA: after setting a partition to IA, observe IA Cache Hit Rate for at least one full business day. A stable hit rate means the access pattern suits IA. Large fluctuations or a low average means the data is accessed too scatteredly for IA.

Diagnostic workflows

Estimate the conversion window before a change

1. Run SHOW STORAGE_CLASS TRANSITIONS to check whether other conversions are already in progress. The table lists only in-progress conversions.
2. Query mysql.tidb_storage_class_transition_history for the duration of similar past conversions on your cluster, filtered on state = 'COMPLETED'.
3. During the conversion, combine DURATION with PROGRESS to estimate the remaining time.

Investigate a sudden drop in cache hit rate

1. Confirm the drop in IA Cache Hit Rate and check whether IA Cache Miss Rate rises at the same time.
2. Identify the statements with a high IA_EXEC_COUNT / EXEC_COUNT ratio in the statement summary tables.
3. If many statements show the ratio rising at the same time, a batch of analytical queries is probably scanning IA tables and evicting hot data from the cache.
4. Decide whether to raise the IA cache level or to move the affected data back to Standard storage.

Decide whether to keep a table in IA

1. Aggregate IA_EXEC_COUNT / EXEC_COUNT for the statements that access the table.
2. If most statements keep a high cold-read ratio, the cache hit rate is too low for IA. Consider switching the table back to Standard.
3. If the cold-read ratio is low but a few statements read a large volume each time, optimize those statements instead of switching the whole table back.

See also

• Tiered Storage Overview
• Configure and Manage Tiered Storage
• Tiered Storage Limitations
• Tiered Storage FAQ