Performance Insight helps you pinpoint which tables and indexes carry the heaviest load, which SQL statements consume the most CPU or lock time, and how your I/O behaves over time — so you can isolate the root cause of slow queries without guesswork.
The feature collects two types of data:
Object statistics — tracks read and write activity at the table and index level, stored in
TABLE_STATISTICSandINDEX_STATISTICSin theinformation_schemadatabase.Performance point — records per-statement timing across three dimensions (CPU, lock, I/O), stored in
events_statements_summary_by_digest_supplementin theperformance_schemadatabase.
A recommended diagnostic workflow:
Query object statistics to identify which tables or indexes have the highest activity.
Query performance point data to find the SQL statements driving that load, and compare
CPU_TIMEagainstELAPSED_TIMEto classify the bottleneck as CPU-bound, lock-bound, or I/O-bound.Query I/O statistics to correlate read and write spikes with specific time windows.
Prerequisites
Before you begin, make sure that:
Your RDS instance runs MySQL 8.0 or MySQL 5.7 with minor engine version 20190915 or later.
To check the minor engine version, go to the Basic Information page in the ApsaraDB RDS console. In the Configuration Information section, look for the Upgrade Kernel Version button. If the button appears, click it to view and update the minor engine version. If the button does not appear, the instance is already on the latest minor engine version. For details, see Update the minor engine version.
Use object statistics
Object statistics let you find hot tables and underused indexes — a starting point for deciding where to focus query optimization.
Step 1: Verify that object statistics are enabled
Run the following query to confirm that OPT_TABLESTAT and OPT_INDEXSTAT are both ON:
mysql> SHOW VARIABLES LIKE "opt_%_stat";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| opt_indexstat | ON |
| opt_tablestat | ON |
+---------------+-------+Step 2: Query table statistics
TABLE_STATISTICS records read and write activity per table. Use it to identify tables with unexpectedly high row reads or modifications — these are candidates for query optimization or index tuning.
mysql> SELECT * FROM TABLE_STATISTICS LIMIT 10;
+--------------+--------------+-----------+--------------+------------------------+---------------+--------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES | ROWS_INSERTED | ROWS_DELETED | ROWS_UPDATED |
+--------------+--------------+-----------+--------------+------------------------+---------------+--------------+--------------+
| mysql | db | 2 | 0 | 0 | 0 | 0 | 0 |
| mysql | engine_cost | 2 | 0 | 0 | 0 | 0 | 0 |
| mysql | proxies_priv | 1 | 0 | 0 | 0 | 0 | 0 |
| mysql | server_cost | 6 | 0 | 0 | 0 | 0 | 0 |
| mysql | tables_priv | 2 | 0 | 0 | 0 | 0 | 0 |
| mysql | user | 7 | 0 | 0 | 0 | 0 | 0 |
| test | sbtest1 | 1686 | 142 | 184 | 112 | 12 | 18 |
| test | sbtest10 | 1806 | 125 | 150 | 105 | 5 | 15 |
| test | sbtest100 | 1623 | 141 | 182 | 110 | 10 | 21 |
| test | sbtest11 | 1254 | 136 | 172 | 110 | 10 | 16 |
+--------------+--------------+-----------+--------------+------------------------+---------------+--------------+--------------+TABLE_STATISTICS columns
| Column | Description |
|---|---|
| TABLE_SCHEMA | Database name |
| TABLE_NAME | Table name |
| ROWS_READ | Number of rows read from the table |
| ROWS_CHANGED | Number of rows modified in the table |
| ROWS_CHANGED_X_INDEXES | Number of rows modified using indexes |
| ROWS_INSERTED | Number of rows inserted into the table |
| ROWS_DELETED | Number of rows deleted from the table |
| ROWS_UPDATED | Number of rows updated in the table |
Step 3: Query index statistics
INDEX_STATISTICS records how many rows are read through each index. An index with a low ROWS_READ count relative to its table's activity is a candidate for removal — dropping unused indexes reduces write overhead.
mysql> SELECT * FROM INDEX_STATISTICS LIMIT 10;
+--------------+--------------+------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ |
+--------------+--------------+------------+-----------+
| mysql | db | PRIMARY | 2 |
| mysql | engine_cost | PRIMARY | 2 |
| mysql | proxies_priv | PRIMARY | 1 |
| mysql | server_cost | PRIMARY | 6 |
| mysql | tables_priv | PRIMARY | 2 |
| mysql | user | PRIMARY | 7 |
| test | sbtest1 | PRIMARY | 2500 |
| test | sbtest10 | PRIMARY | 3007 |
| test | sbtest100 | PRIMARY | 2642 |
| test | sbtest11 | PRIMARY | 2091 |
+--------------+--------------+------------+-----------+INDEX_STATISTICS columns
| Column | Description |
|---|---|
| TABLE_SCHEMA | Database name |
| TABLE_NAME | Table name |
| INDEX_NAME | Index name |
| ROWS_READ | Number of rows read through this index |
Use performance point
Performance point records per-statement timing in events_statements_summary_by_digest_supplement (in the performance_schema database). Each row covers one statement digest and breaks down execution time across three dimensions:
CPU — total elapsed time and CPU time consumed by the statement
Lock — time spent waiting for metadata locks, transaction locks, mutexes, and readers-writer locks
I/O — time and volume of data file reads and writes, log file writes, binary log reads, redo log reads, and asynchronous redo log reads
Use these dimensions to classify bottlenecks: if CPU_TIME is close to ELAPSED_TIME, the statement is CPU-bound. If SERVER_LOCK_TIME or TRANSACTION_LOCK_TIME is high, the statement is lock-bound. If DATA_READ_TIME or DATA_WRITE_TIME dominates, the statement is I/O-bound.
Step 1: Verify that performance point is enabled
Run the following query to check the performance point variables:
mysql> SHOW VARIABLES LIKE "%performance_point%";
+---------------------------------------+-------+
| Variable_name | Value |
+---------------------------------------+-------+
| performance_point_dbug_enabled | OFF |
| performance_point_enabled | ON |
| performance_point_iostat_interval | 2 |
| performance_point_iostat_volume_size | 10000 |
| performance_point_lock_rwlock_enabled | ON |
+---------------------------------------+-------+If these variables do not appear, the instance does not meet the version requirements in Prerequisites.
Step 2: Query SQL statement performance data
Query events_statements_summary_by_digest_supplement to get per-statement performance data. Sort by ELAPSED_TIME to find the slowest statements, then inspect the breakdown columns to identify which dimension — CPU, lock, or I/O — accounts for most of the time.
mysql> SELECT * FROM events_statements_summary_by_digest_supplement LIMIT 10;
+--------------------+----------------------------------+-------------------------------------------+--------------+
| SCHEMA_NAME | DIGEST | DIGEST_TEXT | ELAPSED_TIME | ......
+--------------------+----------------------------------+-------------------------------------------+--------------+
| NULL | 6b787dd1f9c6f6c5033120760a1a82de | SELECT @@`version_comment` LIMIT ? | 932 |
| NULL | 2fb4341654df6995113d998c52e5abc9 | SHOW SCHEMAS | 2363 |
| NULL | 8a93e76a7846384621567fb4daa1bf95 | SHOW VARIABLES LIKE ? | 17933 |
| NULL | dd148234ac7a20cb5aee7720fb44b7ea | SELECT SCHEMA ( ) | 1006 |
| information_schema | 2fb4341654df6995113d998c52e5abc9 | SHOW SCHEMAS | 2156 |
| information_schema | 74af182f3a2bd265678d3dadb53e08da | SHOW TABLES | 3161 |
| information_schema | d3a66515192fcb100aaef6f8b6e45603 | SELECT * FROM `TABLE_STATISTICS` LIMIT ? | 2081 |
| information_schema | b3726b7c4c4db4b309de2dbc45ff52af | SELECT * FROM `INDEX_STATISTICS` LIMIT ? | 2384 |
| information_schema | dd148234ac7a20cb5aee7720fb44b7ea | SELECT SCHEMA ( ) | 129 |
| test | 2fb4341654df6995113d998c52e5abc9 | SHOW SCHEMAS | 342 |
+--------------------+----------------------------------+-------------------------------------------+--------------+events_statements_summary_by_digest_supplement columns
| Column | Description | Unit |
|---|---|---|
| SCHEMA_NAME | Database name | — |
| DIGEST | 64-byte hash string derived from DIGEST_TEXT | — |
| DIGEST_TEXT | Normalized form of the SQL statement | — |
| ELAPSED_TIME | Total execution time | Microseconds |
| CPU_TIME | CPU time consumed during execution | Microseconds |
| SERVER_LOCK_TIME | Time spent waiting for metadata locks on the server | Microseconds |
| TRANSACTION_LOCK_TIME | Time spent waiting for storage transaction locks | Microseconds |
| MUTEX_SPINS | Number of mutex spins triggered | — |
| MUTEX_WAITS | Number of spin waits triggered by mutexes | — |
| RWLOCK_SPIN_WAITS | Number of spin waits triggered by readers-writer locks | — |
| RWLOCK_SPIN_ROUNDS | Number of rounds the background thread looped in readers-writer lock spin-wait cycles | — |
| RWLOCK_OS_WAITS | Number of operating system waits triggered by readers-writer locks | — |
| DATA_READS | Number of reads from data files | — |
| DATA_READ_TIME | Time spent reading from data files | Microseconds |
| DATA_WRITES | Number of writes to data files | — |
| DATA_WRITE_TIME | Time spent writing to data files | Microseconds |
| REDO_WRITES | Number of writes to log files | — |
| REDO_WRITE_TIME | Time spent writing to log files | Microseconds |
| LOGICAL_READS | Number of logical page reads | — |
| PHYSICAL_READS | Number of physical page reads | — |
| PHYSICAL_ASYNC_READS | Number of asynchronous physical page reads | — |
Query I/O statistics
IO_STATISTICS (in the information_schema database) records read and write activity at regular intervals. Use it to correlate I/O spikes with time windows where you observed performance degradation — for example, matching a spike in DATA_READ with a period of slow query responses.
mysql> SELECT * FROM IO_STATISTICS LIMIT 10;
+---------------------+-----------+----------------+
| TIME | DATA_READ | DATA_READ_TIME | ......
+---------------------+-----------+----------------+
| 2019-08-08 09:56:53 | 73 | 983 |
| 2019-08-08 09:56:57 | 0 | 0 |
| 2019-08-08 09:59:17 | 0 | 0 |
| 2019-08-08 10:00:55 | 4072 | 40628 |
| 2019-08-08 10:00:59 | 0 | 0 |
| 2019-08-08 10:01:09 | 562 | 5800 |
| 2019-08-08 10:01:11 | 606 | 6910 |
| 2019-08-08 10:01:13 | 609 | 6875 |
| 2019-08-08 10:01:15 | 625 | 7077 |
| 2019-08-08 10:01:17 | 616 | 5800 |
+---------------------+-----------+----------------+IO_STATISTICS columns
| Column | Description | Unit |
|---|---|---|
| TIME | Timestamp of the interval | — |
| DATA_READ | Number of data read operations | — |
| DATA_READ_TIME | Total time spent reading data | Microseconds |
| DATA_READ_MAX_TIME | Maximum time for a single read operation | Microseconds |
| DATA_READ_BYTES | Total data read | Bytes |
| DATA_WRITE | Number of data write operations | — |
| DATA_WRITE_TIME | Total time spent writing data | Microseconds |
| DATA_WRITE_MAX_TIME | Maximum time for a single write operation | Microseconds |
| DATA_WRITE_BYTES | Total data written | Bytes |