ApsaraDB RDS:Performance Insight

Performance insight

Performance Insight consists of two components:

• Object statistics

  Object statistics provides statistics about tables and indexes. It includes two tables:

  • TABLE_STATISTICS: Records rows read and modified.

  • INDEX_STATISTICS: Records rows read by indexes.

• Performance point

  Performance point provides detailed performance information for an instance to help you quantify the cost of SQL statements more quickly and accurately. It includes three dimensions:

  • CPU: Includes metrics such as total execution time (Elapsed time) and CPU time.

  • LOCK: Includes metrics such as server metadata lock (MDL) time, transaction lock time, mutex contention (debug mode only), and read-write lock contention.

  • IO: Includes metrics such as data file read and write time, log file write time, logical reads, physical reads, and physical asynchronous reads.

Using object statistics

1. Verify that the parameters OPT_TABLESTAT and OPT_INDEXSTAT are set to ON. For example:

   mysql> show variables like "opt_%_stat";
     +---------------+-------+
     | Variable_name | Value |
     +---------------+-------+
     | opt_indexstat | ON    |
     | opt_tablestat | ON    |
     +---------------+-------+

2. Query the TABLE_STATISTICS or INDEX_STATISTICS table in the information_schema database to view table and index statistics. For example:

   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     |      2091 |
     +--------------+--------------+------------+-----------+

   The following table describes the parameters.

   Parameter	Description
   TABLE_SCHEMA	The name of the database.
   TABLE_NAME	The name of the table.
   ROWS_READ	The number of rows read.
   ROWS_CHANGED	The number of rows modified.
   ROWS_CHANGED_X_INDEXES	The number of index updates resulting from row changes.
   ROWS_INSERTED	The number of rows inserted.
   ROWS_DELETED	The number of rows deleted.
   ROWS_UPDATED	The number of rows updated.
   INDEX_NAME	The name of the index.

Using performance point

1. Verify the Performance point parameters. The following output indicates the feature is enabled:

   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 are not found, ensure your instance meets the prerequisites.

2. Query the events_statements_summary_by_digest_supplement table in the performance_schema database to view statistics for SQL statements. For 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.

   Parameter	Description
   SCHEMA_NAME	The name of the database.
   DIGEST	A 64-byte hash string derived from the DIGEST_TEXT value.
   DIGEST_TEXT	The normalized form of the SQL statement.
   ELAPSED_TIME	The total execution time. Unit: microseconds (μs).
   CPU_TIME	The CPU time. Unit: microseconds (μs).
   SERVER_LOCK_TIME	The server lock time. Unit: microseconds (μs).
   TRANSACTION_LOCK_TIME	The transaction lock time. Unit: microseconds (μs).
   MUTEX_SPINS	The number of mutex spins.
   MUTEX_WAITS	The number of mutex waits.
   RWLOCK_SPIN_WAITS	The number of spin waits for read-write locks.
   RWLOCK_SPIN_ROUNDS	The number of spin rounds for read-write locks.
   RWLOCK_OS_WAITS	The number of operating system waits for read-write locks.
   DATA_READS	The number of reads from data files.
   DATA_READ_TIME	The time spent reading from data files. Unit: microseconds (μs).
   DATA_WRITES	The number of writes to data files.
   DATA_WRITE_TIME	The time spent writing to data files. Unit: microseconds (μs).
   REDO_WRITES	The number of writes to log files.
   REDO_WRITE_TIME	The time spent writing to log files. Unit: microseconds (μs).
   LOGICAL_READS	The number of logical page reads.
   PHYSICAL_READS	The number of physical page reads.
   PHYSICAL_ASYNC_READS	The number of physical asynchronous page reads.

3. Query the IO_STATISTICS table in the information_schema database to view recent data read and write activity. For 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.

   Parameter	Description
   TIME	The timestamp of the record.
   DATA_READ	The number of data read operations.
   DATA_READ_TIME	The total time spent reading data. Unit: microseconds (μs).
   DATA_READ_MAX_TIME	The maximum time spent on a single data read operation. Unit: microseconds (μs).
   DATA_READ_BYTES	The total size of data read. Unit: bytes.
   DATA_WRITE	The number of data write operations.
   DATA_WRITE_TIME	The total time spent writing data. Unit: microseconds (μs).
   DATA_WRITE_MAX_TIME	The maximum time spent on a single data write operation. Unit: microseconds (μs).
   DATA_WRITE_BYTES	The total size of data written. Unit: bytes.