This topic describes the Performance Agent feature that is provided by AliSQL. This feature allows you to collect statistics about the performance of an ApsaraDB RDS for MySQL instance.

Background information

A memory table named PERF_STATISTICS is added for Performance Agent. This table is stored in the information_schema system database. This table stores the performance data that is generated over the most recent period of time. You can query performance data from this table.

Prerequisites

Your RDS instance runs one of the following MySQL versions and RDS editions:

  • MySQL 8.0 with a minor engine version of 20200229 or later
  • MySQL 5.7 with a minor engine version of 20200229 or later
  • MySQL 5.6 with a minor engine version of 20200630 or later
Note For more information about how to update the minor engine version of an RDS instance, see Update the minor engine version of an ApsaraDB RDS for MySQL instance.

Parameters

The following table describes the parameters that you must configure for the Performance Agent feature.

Parameter Description
performance_agent_enabled Specifies whether to enable the Performance Agent feature. Valid values: ON and OFF. Default value: ON.
performance_agent_interval Specifies the interval at which ApsaraDB RDS collects performance data. Unit: seconds. Default value: 1.
performance_agent_perfstat_volume_size Specifies the maximum number of data records that are allowed in the PERF_STATISTICS memory table. Default value: 3600. If you set the performance_agent_interval parameter to 1, ApsaraDB RDS retains the performance data that is generated over the previous hour.
Note The parameters that are described in the preceding table are not displayed in the ApsaraDB RDS console. You can execute the SHOW VARIABLES LIKE '<Parameter name>'; statement to view the value of each of these parameters.

Schema

The PERF_STATISTICS memory table uses the following schema:

CREATE TEMPORARY TABLE `PERF_STATISTICS` (
  `TIME` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `PROCS_MEM_USAGE` double NOT NULL DEFAULT '0',
  `PROCS_CPU_RATIO` double NOT NULL DEFAULT '0',
  `PROCS_IOPS` double NOT NULL DEFAULT '0',
  `PROCS_IO_READ_BYTES` bigint(21) NOT NULL DEFAULT '0',
  `PROCS_IO_WRITE_BYTES` bigint(21) NOT NULL DEFAULT '0',
  `MYSQL_CONN_ABORT` int(11) NOT NULL DEFAULT '0',
  `MYSQL_CONN_CREATED` int(11) NOT NULL DEFAULT '0',
  `MYSQL_USER_CONN_COUNT` int(11) NOT NULL DEFAULT '0',
  `MYSQL_CONN_RUNNING` int(11) NOT NULL DEFAULT '0',
  `MYSQL_LOCK_IMMEDIATE` int(11) NOT NULL DEFAULT '0',
  `MYSQL_LOCK_WAITED` int(11) NOT NULL DEFAULT '0',
  `MYSQL_COM_INSERT` int(11) NOT NULL DEFAULT '0',
  `MYSQL_COM_UPDATE` int(11) NOT NULL DEFAULT '0',
  `MYSQL_COM_DELETE` int(11) NOT NULL DEFAULT '0',
  `MYSQL_COM_SELECT` int(11) NOT NULL DEFAULT '0',
  `MYSQL_COM_COMMIT` int(11) NOT NULL DEFAULT '0',
  `MYSQL_COM_ROLLBACK` int(11) NOT NULL DEFAULT '0',
  `MYSQL_COM_PREPARE` int(11) NOT NULL DEFAULT '0',
  `MYSQL_LONG_QUERY` int(11) NOT NULL DEFAULT '0',
  `MYSQL_TCACHE_GET` bigint(21) NOT NULL DEFAULT '0',
  `MYSQL_TCACHE_MISS` bigint(21) NOT NULL DEFAULT '0',
  `MYSQL_TMPFILE_CREATED` int(11) NOT NULL DEFAULT '0',
  `MYSQL_TMP_TABLES` int(11) NOT NULL DEFAULT '0',
  `MYSQL_TMP_DISKTABLES` int(11) NOT NULL DEFAULT '0',
  `MYSQL_SORT_MERGE` int(11) NOT NULL DEFAULT '0',
  `MYSQL_SORT_ROWS` int(11) NOT NULL DEFAULT '0',
  `MYSQL_BYTES_RECEIVED` bigint(21) NOT NULL DEFAULT '0',
  `MYSQL_BYTES_SENT` bigint(21) NOT NULL DEFAULT '0',
  `MYSQL_BINLOG_OFFSET` int(11) NOT NULL DEFAULT '0',
  `MYSQL_IOLOG_OFFSET` int(11) NOT NULL DEFAULT '0',
  `MYSQL_RELAYLOG_OFFSET` int(11) NOT NULL DEFAULT '0',
  `EXTRA` json NOT NULL DEFAULT 'null'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Column Description
TIME The time when the performance data of the RDS instance is collected. The time is in the yyyy-MM-dd HH:mm:ss format.
PROCS_MEM_USAGE The size of physical memory that is occupied by the RDS instance. Unit: bytes.
PROCS_CPU_RATIO The CPU utilization of the RDS instance.
PROCS_IOPS The number of I/O operations that are performed.
PROCS_IO_READ_BYTES The amount of data that is read by I/O operations. Unit: bytes.
PROCS_IO_WRITE_BYTES The amount of data that is written by I/O operations. Unit: bytes.
MYSQL_CONN_ABORT The number of closed connections.
MYSQL_CONN_CREATED The number of new connections.
MYSQL_USER_CONN_COUNT The total number of connections.
MYSQL_CONN_RUNNING The number of active connections.
MYSQL_LOCK_IMMEDIATE The number of locks that are held.
MYSQL_LOCK_WAITED The number of lock wait events.
MYSQL_COM_INSERT The number of statements that are executed to insert data.
MYSQL_COM_UPDATE The number of statements that are executed to update data.
MYSQL_COM_DELETE The number of statements that are executed to delete data.
MYSQL_COM_SELECT The number of statements that are executed to query data.
MYSQL_COM_COMMIT The number of transactions that are explicitly committed.
MYSQL_COM_ROLLBACK The number of transactions that are rolled back.
MYSQL_COM_PREPARE The number of statements that are pre-processed.
MYSQL_LONG_QUERY The number of slow queries.
MYSQL_TCACHE_GET The number of cache hits.
MYSQL_TCACHE_MISS The number of cache misses.
MYSQL_TMPFILE_CREATED The number of temporary files that are created.
MYSQL_TMP_TABLES The number of temporary tables that are created.
MYSQL_TMP_DISKTABLES The number of temporary disk tables that are created.
MYSQL_SORT_MERGE The number of times that data is merged and sorted.
MYSQL_SORT_ROWS The number of rows that are sorted.
MYSQL_BYTES_RECEIVED The amount of data that is received. Unit: bytes.
MYSQL_BYTES_SENT The amount of data that is sent. Unit: bytes.
MYSQL_BINLOG_OFFSET The size of the binary log file that is generated. Unit: bytes.
MYSQL_IOLOG_OFFSET The size of the binary log file that is sent by the RDS instance to its secondary RDS instance. Unit: bytes.
MYSQL_RELAYLOG_OFFSET The size of the binary log file that is applied by the secondary RDS instance. Unit: bytes.
EXTRA The statistics about InnoDB. The value of the EXTRA parameter consists of multiple fields and is in the JSON format. For more information, see the "Table 1" section of this topic.
Note The values of the metrics in the InnoDB statistics are the same as the values that are obtained by executing the SHOW STATUS statement.
Table 1. Fields in the value of the EXTRA parameter
Field Description
INNODB_TRX_CNT The number of transactions.
INNODB_DATA_READ The amount of data that is read. Unit: bytes.
INNODB_IBUF_SIZE The number of pages that are merged.
INNODB_LOG_WAITS The number of times that InnoDB waits to write log data.
INNODB_MAX_PURGE The number of transactions that are deleted.
INNODB_N_WAITING The number of locks for which InnoDB waits.
INNODB_ROWS_READ The number of rows that are read.
INNODB_LOG_WRITES The number of times that log data is written by InnoDB.
INNODB_IBUF_MERGES The number of times that data is merged by InnoDB.
INNODB_DATA_WRITTEN The amount of data that is written. Unit: bytes.
INNODB_DBLWR_WRITES The number of doublewrite operations.
INNODB_IBUF_SEGSIZE The size of data that is inserted into the buffer.
INNODB_ROWS_DELETED The number of rows that are deleted.
INNODB_ROWS_UPDATED The number of rows that are updated.
INNODB_COMMIT_TRXCNT The number of transactions that are committed.
INNODB_IBUF_FREELIST The length of the idle list.
INNODB_MYSQL_TRX_CNT The number of MySQL transactions.
INNODB_ROWS_INSERTED The number of rows that are inserted.
INNODB_ACTIVE_TRX_CNT The number of active transactions.
INNODB_OS_LOG_WRITTEN The amount of log data that is written. Unit: bytes.
INNODB_ACTIVE_VIEW_CNT The number of active views.
INNODB_RSEG_HISTORY_LEN The length of the TRX_RSEG_HISTORY table.
INNODB_AVG_COMMIT_TRXTIME The average amount of time that is taken to commit a transaction.
INNODB_MAX_COMMIT_TRXTIME The maximum amount of time that is taken to commit a transaction.
INNODB_DBLWR_PAGES_WRITTEN The number of writes that are completed by doublewrite operations.

Use Performance Agent

  • Query performance data from the system table.
    • Query the CPU utilization and memory usage over the previous 30 seconds. Example:
      MySQL> select TIME, PROCS_MEM_USAGE, PROCS_CPU_RATIO from information_schema.PERF_STATISTICS order by time DESC limit 30;
      +---------------------+-----------------+-----------------+
      | TIME                | PROCS_MEM_USAGE | PROCS_CPU_RATIO |
      +---------------------+-----------------+-----------------+
      | 2020-02-27 11:15:36 |       857812992 |           18.55 |
      | 2020-02-27 11:15:35 |       857808896 |           18.54 |
      | 2020-02-27 11:15:34 |       857268224 |           19.64 |
      | 2020-02-27 11:15:33 |       857268224 |           21.06 |
      | 2020-02-27 11:15:32 |       857264128 |           20.39 |
      | 2020-02-27 11:15:31 |       857272320 |           20.32 |
      | 2020-02-27 11:15:30 |       857272320 |           21.35 |
      | 2020-02-27 11:15:29 |       857272320 |            28.8 |
      | 2020-02-27 11:15:28 |       857268224 |           29.08 |
      | 2020-02-27 11:15:27 |       857268224 |           26.92 |
      | 2020-02-27 11:15:26 |       857268224 |           23.84 |
      | 2020-02-27 11:15:25 |       857264128 |           13.76 |
      | 2020-02-27 11:15:24 |       857264128 |           15.12 |
      | 2020-02-27 11:15:23 |       857264128 |           14.76 |
      | 2020-02-27 11:15:22 |       857264128 |           15.38 |
      | 2020-02-27 11:15:21 |       857260032 |           13.23 |
      | 2020-02-27 11:15:20 |       857260032 |           12.75 |
      | 2020-02-27 11:15:19 |       857260032 |           12.17 |
      | 2020-02-27 11:15:18 |       857255936 |           13.22 |
      | 2020-02-27 11:15:17 |       857255936 |           20.51 |
      | 2020-02-27 11:15:16 |       857255936 |           28.74 |
      | 2020-02-27 11:15:15 |       857251840 |           29.85 |
      | 2020-02-27 11:15:14 |       857251840 |           29.31 |
      | 2020-02-27 11:15:13 |       856981504 |           28.85 |
      | 2020-02-27 11:15:12 |       856981504 |           29.19 |
      | 2020-02-27 11:15:11 |       856977408 |           29.12 |
      | 2020-02-27 11:15:10 |       856977408 |           29.32 |
      | 2020-02-27 11:15:09 |       856977408 |            29.2 |
      | 2020-02-27 11:15:08 |       856973312 |           29.36 |
      | 2020-02-27 11:15:07 |       856973312 |           28.79 |
      +---------------------+-----------------+-----------------+
      30 rows in set (0.08 sec)
    • Query the rows that are read and written by InnoDB over the previous 30 seconds. Example:
      MySQL> select TIME, EXTRA->'$.INNODB_ROWS_READ', EXTRA->'$.INNODB_ROWS_INSERTED' from information_schema.PERF_STATISTICS order by time DESC limit 30;
      +---------------------+-----------------------------+---------------------------------+
      | TIME                | EXTRA->'$.INNODB_ROWS_READ' | EXTRA->'$.INNODB_ROWS_INSERTED' |
      +---------------------+-----------------------------+---------------------------------+
      | 2020-02-27 11:22:17 | 39209                       | 0                               |
      | 2020-02-27 11:22:16 | 36098                       | 0                               |
      | 2020-02-27 11:22:15 | 38035                       | 0                               |
      | 2020-02-27 11:22:14 | 37384                       | 0                               |
      | 2020-02-27 11:22:13 | 38336                       | 0                               |
      | 2020-02-27 11:22:12 | 33946                       | 0                               |
      | 2020-02-27 11:22:11 | 36301                       | 0                               |
      | 2020-02-27 11:22:10 | 36835                       | 0                               |
      | 2020-02-27 11:22:09 | 36900                       | 0                               |
      | 2020-02-27 11:22:08 | 36402                       | 0                               |
      | 2020-02-27 11:22:07 | 39672                       | 0                               |
      | 2020-02-27 11:22:06 | 39316                       | 0                               |
      | 2020-02-27 11:22:05 | 37830                       | 0                               |
      | 2020-02-27 11:22:04 | 36396                       | 0                               |
      | 2020-02-27 11:22:03 | 34820                       | 0                               |
      | 2020-02-27 11:22:02 | 37350                       | 0                               |
      | 2020-02-27 11:22:01 | 39463                       | 0                               |
      | 2020-02-27 11:22:00 | 38419                       | 0                               |
      | 2020-02-27 11:21:59 | 37673                       | 0                               |
      | 2020-02-27 11:21:58 | 35117                       | 0                               |
      | 2020-02-27 11:21:57 | 36140                       | 0                               |
      | 2020-02-27 11:21:56 | 37592                       | 0                               |
      | 2020-02-27 11:21:55 | 39765                       | 0                               |
      | 2020-02-27 11:21:54 | 35553                       | 0                               |
      | 2020-02-27 11:21:53 | 35882                       | 0                               |
      | 2020-02-27 11:21:52 | 37061                       | 0                               |
      | 2020-02-27 11:21:51 | 40699                       | 0                               |
      | 2020-02-27 11:21:50 | 39608                       | 0                               |
      | 2020-02-27 11:21:49 | 39317                       | 0                               |
      | 2020-02-27 11:21:48 | 37413                       | 0                               |
      +---------------------+-----------------------------+---------------------------------+
      30 rows in set (0.08 sec)
  • Connect to a monitoring platform to monitor your database performance in real time. For example, connect to Grafana. Grafana