Performance Agent for collecting performance statistics on RDS for MySQL - ApsaraDB RDS

Performance Agent is an AliSQL plugin that collects instance-level performance metrics at a fixed interval and stores them in a queryable in-memory table (information_schema.PERF_STATISTICS). Use it to run custom SQL queries against live performance data, build real-time dashboards in Grafana, or feed metrics into your own monitoring pipelines.

Prerequisites

Before you begin, make sure your instance meets the following version requirements:

MySQL 8.0 with minor engine version 20200229 or later
MySQL 5.7 with minor engine version 20200229 or later
MySQL 5.6 with minor engine version 20200630 or later

For instructions on updating the minor engine version, see Update the minor engine version of an ApsaraDB RDS for MySQL instance.

How it works

Performance Agent writes a snapshot of key performance metrics to information_schema.PERF_STATISTICS at a fixed interval (default: 1 second). The table is an in-memory structure — older entries are evicted as new ones arrive.

With the default settings (performance_agent_interval=1, performance_agent_perfstat_volume_size=3600), the table retains 3,600 rows, covering approximately the previous hour.

Data retention at a glance

Collection interval	Records kept	Data retained
1 second (default)	3,600 (default)	~1 hour
5 seconds	3,600 (default)	~5 hours
10 seconds	3,600 (default)	~10 hours
1 second	7,200	~2 hours

To retain more history, increase performance_agent_perfstat_volume_size. This uses additional memory, so weigh the trade-off against your instance's available resources.

Configure Performance Agent

The following parameters control Performance Agent behavior. These parameters are not shown in the ApsaraDB RDS console. To view the current value of any parameter, run:

SHOW VARIABLES LIKE '<parameter_name>';

Parameter	Description	Default
`performance_agent_enabled`	Enables or disables Performance Agent. Valid values: `ON`, `OFF`.	`ON`
`performance_agent_interval`	How often RDS collects a performance snapshot. Unit: seconds. A smaller interval means more frequent data and higher memory use.	`1`
`performance_agent_perfstat_volume_size`	Maximum number of rows in the `PERF_STATISTICS` table. When this limit is reached, the oldest rows are evicted.	`3600`
`performance_agent_file_size`	Size of the performance data file written to disk. Unit: MB.	—
`performance_agent_network_device`	Prefix of the physical network interface controller (NIC) name for the host.	—

Query performance data

All queries run against information_schema.PERF_STATISTICS. Use ORDER BY TIME DESC and LIMIT to retrieve the most recent N seconds of data.

Resource metrics (CPU, memory, I/O)

SELECT TIME, PROCS_MEM_USAGE, PROCS_CPU_RATIO
FROM information_schema.PERF_STATISTICS
ORDER BY TIME DESC
LIMIT 30;

Sample output:

+---------------------+-----------------+-----------------+
| 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 |
...
+---------------------+-----------------+-----------------+
30 rows in set (0.08 sec)

InnoDB metrics

InnoDB statistics are stored as a JSON object in the EXTRA column. Use the -> operator to extract individual fields.

SELECT TIME,
       EXTRA->'$.INNODB_ROWS_READ',
       EXTRA->'$.INNODB_ROWS_INSERTED'
FROM information_schema.PERF_STATISTICS
ORDER BY TIME DESC
LIMIT 30;

Sample output:

+---------------------+-----------------------------+---------------------------------+
| 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                               |
...
+---------------------+-----------------------------+---------------------------------+
30 rows in set (0.08 sec)

Values in the EXTRA column match those returned by SHOW STATUS.

Connect to a monitoring platform

Connect Performance Agent to a monitoring platform for real-time dashboards. For example, connect to Grafana. Grafana

PERF_STATISTICS schema reference

Table definition

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_MEM_RATIO` double NOT NULL DEFAULT '0',
  `PROCS_CPU_RATIO` double NOT NULL DEFAULT '0',
  `PROCS_IOPS` double NOT NULL DEFAULT '0',
  `PROCS_IOPS_READ` double NOT NULL DEFAULT '0',
  `PROCS_IOPS_WRITE` double NOT NULL DEFAULT '0',
  `PROCS_IO_READ_BYTES` bigint NOT NULL DEFAULT '0',
  `PROCS_IO_WRITE_BYTES` bigint NOT NULL DEFAULT '0',
  `MYSQL_CONN_ABORT` int NOT NULL DEFAULT '0',
  `MYSQL_CONN_CREATED` int NOT NULL DEFAULT '0',
  `MYSQL_USER_CONN_COUNT` int NOT NULL DEFAULT '0',
  `MYSQL_CONN_COUNT` int NOT NULL DEFAULT '0',
  `MYSQL_CONN_RUNNING` int NOT NULL DEFAULT '0',
  `MYSQL_CONNECTIONS` int NOT NULL DEFAULT '0',
  `MYSQL_CONN_CACHED` int NOT NULL DEFAULT '0',
  `MYSQL_LOCK_IMMEDIATE` int NOT NULL DEFAULT '0',
  `MYSQL_LOCK_WAITED` int NOT NULL DEFAULT '0',
  `MYSQL_QUESTIONS` int NOT NULL DEFAULT '0',
  `MYSQL_COM_INSERT` int NOT NULL DEFAULT '0',
  `MYSQL_COM_INSERT_SELECT` int NOT NULL DEFAULT '0',
  `MYSQL_COM_REPLACE` int NOT NULL DEFAULT '0',
  `MYSQL_COM_REPLACE_SELECT` int NOT NULL DEFAULT '0',
  `MYSQL_COM_UPDATE` int NOT NULL DEFAULT '0',
  `MYSQL_COM_UPDATE_MULTI` int NOT NULL DEFAULT '0',
  `MYSQL_COM_DELETE` int NOT NULL DEFAULT '0',
  `MYSQL_COM_DELETE_MULTI` int NOT NULL DEFAULT '0',
  `MYSQL_COM_SELECT` int NOT NULL DEFAULT '0',
  `MYSQL_COM_COMMIT` int NOT NULL DEFAULT '0',
  `MYSQL_COM_ROLLBACK` int NOT NULL DEFAULT '0',
  `MYSQL_COM_PREPARE` int NOT NULL DEFAULT '0',
  `MYSQL_HANDLER_COMMIT` int NOT NULL DEFAULT '0',
  `MYSQL_HANDELR_ROLLBACK` int NOT NULL DEFAULT '0',
  `MYSQL_LONG_QUERY` int NOT NULL DEFAULT '0',
  `MYSQL_TCACHE_GET` bigint NOT NULL DEFAULT '0',
  `MYSQL_TCACHE_MISS` bigint NOT NULL DEFAULT '0',
  `MYSQL_TMPFILE_CREATED` int NOT NULL DEFAULT '0',
  `MYSQL_TMP_TABLES` int NOT NULL DEFAULT '0',
  `MYSQL_TMP_DISKTABLES` int NOT NULL DEFAULT '0',
  `MYSQL_SORT_MERGE` int NOT NULL DEFAULT '0',
  `MYSQL_SORT_ROWS` int NOT NULL DEFAULT '0',
  `MYSQL_BYTES_RECEIVED` bigint NOT NULL DEFAULT '0',
  `MYSQL_BYTES_SENT` bigint NOT NULL DEFAULT '0',
  `MYSQL_BINLOG_OFFSET` int NOT NULL DEFAULT '0',
  `MYSQL_IOLOG_OFFSET` int NOT NULL DEFAULT '0',
  `MYSQL_RELAYLOG_OFFSET` int NOT NULL DEFAULT '0',
  `EXTRA` json NOT NULL DEFAULT 'null'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Column reference

Resource metrics

Column	Description
`TIME`	Timestamp of the snapshot. Format: `yyyy-MM-dd HH:mm:ss`.
`PROCS_MEM_USAGE`	Physical memory used by the RDS instance. Unit: bytes.
`PROCS_MEM_RATIO`	Memory usage of the MySQL process, as a ratio.
`PROCS_CPU_RATIO`	CPU utilization of the RDS instance.
`PROCS_IOPS`	Total number of I/O operations.
`PROCS_IOPS_READ`	Read IOPS of the process in the container.
`PROCS_IOPS_WRITE`	Write IOPS of the process in the container.
`PROCS_IO_READ_BYTES`	Data read by I/O operations. Unit: bytes.
`PROCS_IO_WRITE_BYTES`	Data written by I/O operations. Unit: bytes.

Connection metrics

Column	Description
`MYSQL_CONN_ABORT`	Number of closed connections.
`MYSQL_CONN_CREATED`	Number of new connections created.
`MYSQL_USER_CONN_COUNT`	Total number of connections.
`MYSQL_CONN_COUNT`	Total number of connections.
`MYSQL_CONN_RUNNING`	Number of active connections.
`MYSQL_CONNECTIONS`	Number of MySQL connections.
`MYSQL_CONN_CACHED`	Number of blocked threads.
`MYSQL_LOCK_IMMEDIATE`	Number of locks held.
`MYSQL_LOCK_WAITED`	Number of lock wait events.

SQL execution metrics

Column	Description
`MYSQL_QUESTIONS`	Total SQL statements sent by the client.
`MYSQL_COM_INSERT`	Number of INSERT statements.
`MYSQL_COM_INSERT_SELECT`	Number of INSERT ... SELECT statements.
`MYSQL_COM_REPLACE`	Number of REPLACE statements.
`MYSQL_COM_REPLACE_SELECT`	Number of REPLACE ... SELECT statements.
`MYSQL_COM_UPDATE`	Number of UPDATE statements.
`MYSQL_COM_UPDATE_MULTI`	Number of multi-table UPDATE statements.
`MYSQL_COM_DELETE`	Number of DELETE statements.
`MYSQL_COM_DELETE_MULTI`	Number of multi-table DELETE statements.
`MYSQL_COM_SELECT`	Number of SELECT statements.
`MYSQL_COM_COMMIT`	Number of explicitly committed transactions.
`MYSQL_COM_ROLLBACK`	Number of rolled-back transactions.
`MYSQL_COM_PREPARE`	Number of prepared statements.
`MYSQL_HANDLER_COMMIT`	Number of times the COMMIT statement executed.
`MYSQL_HANDELR_ROLLBACK`	Number of times the storage engine performed a rollback.
`MYSQL_LONG_QUERY`	Number of slow queries.
`MYSQL_TCACHE_GET`	Number of cache hits.
`MYSQL_TCACHE_MISS`	Number of cache misses.
`MYSQL_TMPFILE_CREATED`	Number of temporary files created.
`MYSQL_TMP_TABLES`	Number of temporary tables created.
`MYSQL_TMP_DISKTABLES`	Number of temporary disk tables created.
`MYSQL_SORT_MERGE`	Number of sort-merge operations.
`MYSQL_SORT_ROWS`	Number of rows sorted.
`MYSQL_BYTES_RECEIVED`	Data received. Unit: bytes.
`MYSQL_BYTES_SENT`	Data sent. Unit: bytes.

Replication metrics

Column	Description
`MYSQL_BINLOG_OFFSET`	Size of the binary log file generated. Unit: bytes.
`MYSQL_IOLOG_OFFSET`	Size of the binary log file sent by the RDS instance to its secondary RDS instance. Unit: bytes.
`MYSQL_RELAYLOG_OFFSET`	Size of the relay log applied by the secondary RDS instance. Unit: bytes.

InnoDB metrics (EXTRA column)

The EXTRA column contains a JSON object with InnoDB statistics. All values match those returned by SHOW STATUS.

Field	Description
`INNODB_TRX_CNT`	Number of transactions.
`INNODB_DATA_READ`	Data read. Unit: bytes.
`INNODB_IBUF_SIZE`	Number of pages merged in the insert buffer.
`INNODB_LOG_WAITS`	Number of times InnoDB waited to write log data.
`INNODB_MAX_PURGE`	Number of transactions purged.
`INNODB_N_WAITING`	Number of locks InnoDB is waiting for.
`INNODB_ROWS_READ`	Number of rows read.
`INNODB_LOG_WRITES`	Number of times InnoDB wrote log data.
`INNODB_IBUF_MERGES`	Number of insert buffer merge operations.
`INNODB_DATA_WRITTEN`	Data written. Unit: bytes.
`INNODB_DBLWR_WRITES`	Number of doublewrite operations.
`INNODB_IBUF_SEGSIZE`	Size of data inserted into the insert buffer.
`INNODB_ROWS_DELETED`	Number of rows deleted.
`INNODB_ROWS_UPDATED`	Number of rows updated.
`INNODB_COMMIT_TRXCNT`	Number of committed transactions.
`INNODB_IBUF_FREELIST`	Length of the idle list.
`INNODB_MYSQL_TRX_CNT`	Number of MySQL transactions.
`INNODB_ROWS_INSERTED`	Number of rows inserted.
`INNODB_ACTIVE_TRX_CNT`	Number of active transactions.
`INNODB_OS_LOG_WRITTEN`	Log data written to disk. Unit: bytes.
`INNODB_ACTIVE_VIEW_CNT`	Number of active views.
`INNODB_RSEG_HISTORY_LEN`	Length of the `TRX_RSEG_HISTORY` list.
`INNODB_AVG_COMMIT_TRXTIME`	Average time to commit a transaction.
`INNODB_MAX_COMMIT_TRXTIME`	Maximum time to commit a transaction.
`INNODB_DBLWR_PAGES_WRITTEN`	Number of pages written by doublewrite operations.