This topic describes how to use the Performance Insight feature to monitor workloads, perform association analysis, and optimize performance of an ApsaraDB 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 one of the following MySQL versions:
- MySQL 8.0
- MySQL 5.7
- The minor engine version of your RDS instance is 20190915 or later. Note 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 of an ApsaraDB RDS for MySQL instance.
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 | +---------------------------------------+-------+Note If 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.