This topic describes how to use the Performance Insight feature to monitor workloads, perform association analysis, and optimize performance of an ApsaraDB RDS instance. This feature helps you quickly evaluate the workloads of your RDS instance and locate performance issues to ensure service stability.
Prerequisites
Your RDS instance runs MySQL 8.0 or MySQL 5.7 and runs a minor engine version of 20190915 or later.
To view the minor engine version of your RDS instance, you can log on to the ApsaraDB RDS console and go to the Basic Information page. In the Configuration Information section of the page, you can check whether the Upgrade Kernel Version button is displayed. If the button is displayed, you can click the button to view and update the minor engine version of your RDS instance. If the button is not displayed, your RDS instance runs the latest minor engine version. For more information, see Update the minor engine version.
Overview
The Performance Insight feature consists of the following two parts:
Object statistics
Object statistics queries statistics from indexes and the following tables:
TABLE_STATISTICS: records the rows with read and modified data.
INDEX_STATISTICS: records the rows with data read from indexes.
Performance point
Performance point collects performance details of your RDS instance. You can use these details to quantify the overheads of SQL statements quickly and accurately. Performance point measures database performance from the following three dimensions:
CPU: includes but is not limited to the total time spent executing an SQL statement and the time spent by CPU executing an SQL statement.
Lock: includes the time occupied by locks such as metadata locks on the server, storage transaction locks, mutual exclusions in debugging mode only, and readers-writer locks.
I/O: includes the time taken to perform operations such as reading and writing data files, writing log files, reading binary logs, reading redo logs, and asynchronously reading redo logs.
Use object statistics
Make sure that the values of the OPT_TABLESTAT and OPT_INDEXSTAT parameters are ON. Example:
mysql> show variables like "opt_%_stat"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | opt_indexstat | ON | | opt_tablestat | ON | +---------------+-------+Query the TABLE_STATISTICS or INDEX_STATISTICS table in the information_schema database to obtain table or index statistics. Examples:
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 | +--------------+--------------+-----------+--------------+------------------------+---------------+--------------+--------------+ 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 | +--------------+--------------+------------+-----------+The following table describes the parameters in the preceding examples.
Parameter
Description
TABLE_SCHEMA
The name of the database.
TABLE_NAME
The name of the table.
ROWS_READ
The number of rows read from the table.
ROWS_CHANGED
The number of rows modified in the table.
ROWS_CHANGED_X_INDEXES
The number of rows modified by using indexes in the table.
ROWS_INSERTED
The number of rows inserted into the table.
ROWS_DELETED
The number of rows deleted from the table.
ROWS_UPDATED
The number of rows updated in the table.
INDEX_NAME
The name of the index.
Use performance point
View the global variable settings of your RDS instance.
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 | +---------------------------------------+-------+NoteIf these variables cannot be found, check whether your RDS instance meets the version requirements that are described in the Prerequisites section.
Query the events_statements_summary_by_digest_supplement table in the performance_schema database to obtain the top 10 SQL statements in various dimensions. Example:
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 | +--------------------+----------------------------------+-------------------------------------------+--------------+The following table describes the parameters in the preceding example.
Parameter
Description
SCHEMA_NAME
The name of the database.
DIGEST
The 64-byte hash string obtained from the DIGEST_TEXT parameter.
DIGEST_TEXT
The digest of the SQL statement.
ELAPSED_TIME
The total time spent executing the SQL statement. Unit: microseconds.
CPU_TIME
The time spent by CPU executing the SQL statement. Unit: microseconds.
SERVER_LOCK_TIME
The time occupied by metadata locks on the server during the execution of the SQL statement. Unit: microseconds.
TRANSACTION_LOCK_TIME
The time occupied by storage transaction locks during the execution of the SQL statement. Unit: microseconds.
MUTEX_SPINS
The number of mutex spins triggered during the execution of the SQL statement.
MUTEX_WAITS
The number of spin waits triggered by mutexes during the execution of the SQL statement.
RWLOCK_SPIN_WAITS
The number of spin waits triggered by readers-write locks during the execution of the SQL statement.
RWLOCK_SPIN_ROUNDS
The number of rounds in which the background thread looped in the spin-wait cycles triggered by readers-write locks during the execution of the SQL statement.
RWLOCK_OS_WAITS
The number of operating system waits triggered by readers-write locks during the execution of the SQL statement.
DATA_READS
The number of times the system read data from data files during the execution of the SQL statement.
DATA_READ_TIME
The time spent reading data from data files during the execution of the SQL statement. Unit: microseconds.
DATA_WRITES
The number of times the system wrote data into data files during the execution of the SQL statement.
DATA_WRITE_TIME
The time spent writing data into data files during the execution of the SQL statement. Unit: microseconds.
REDO_WRITES
The number of times the system wrote data into log files during the execution of the SQL statement.
REDO_WRITE_TIME
The time spent writing data into log files during the execution of the SQL statement. Unit: microseconds.
LOGICAL_READS
The number of times the system read logical pages during the execution of the SQL statement.
PHYSICAL_READS
The number of times the system read physical pages during the execution of the SQL statement.
PHYSICAL_ASYNC_READS
The number of times system read physical asynchronous pages during the execution of the SQL statement.
Query the IO_STATISTICS table in the information_schema database to obtain information about recent data read and write operations: Example:
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 | +---------------------+-----------+----------------+The following table describes the parameters in the preceding example.
Parameter
Description
TIME
The date.
DATA_READ
The number of times the system read data.
DATA_READ_TIME
The total time spent reading data. Unit: microseconds.
DATA_READ_MAX_TIME
The maximum time spent reading data. Unit: microseconds.
DATA_READ_BYTES
The total amount of data read. Unit: bytes.
DATA_WRITE
The number of times the system wrote data.
DATA_WRITE_TIME
The total time spent writing data. Unit: microseconds.
DATA_WRITE_MAX_TIME
The maximum time spent writing data. Unit: microseconds.
DATA_WRITE_BYTES
The total amount of data written. Unit: bytes.