This topic describes the Performance Agent feature. Performance Agent provides an easy method for you to collect performance statistics. You can use Performance Agent as an PolarDB for MySQL plug-in to collect the performance statistics of the nodes in PolarDB for MySQL clusters.
Prerequisites
The PolarDB cluster version is PolarDB for MySQL 8.0 and the revision version is 8.0.2.1.0 or later. For how to confirm the cluster version, see Query the kernel version number .
Background information
Performance Agent adds a memory table named PERF_STATISTICS to the information_schema system database. This table stores the performance data that is generated over a recent period of time. You can query performance data from this table.
Parameters
The following table describes the parameters that you must configure for Performance Agent.
| Parameter | Description |
|---|---|
| performance_agent_enabled | Specifies whether to enable the Performance Agent feature. Valid values: ON and OFF. Default value: ON. |
| performance_agent_interval | The interval at which you want to collect performance data. Valid values: 1 to 60. Unit: seconds. Default value: 1. |
| performance_agent_perfstat_volume_size | 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, the system retains the performance data that is generated within the last hour. |
Schema
In the following example, the schema of the PERF_STATISTICS memory table is provided.
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 data record was generated. The time is in the yyyy-MM-dd HH:mm:ss format. |
| PROCS_MEM_USAGE | The amount of physical memory that was occupied by the data record. Unit: byte. |
| PROCS_CPU_RATIO | The CPU usage of the node. |
| PROCS_IOPS | The number of I/O operations that the system invoked. |
| PROCS_IO_READ_BYTES | The amount of data that was read by I/O operations. Unit: byte. |
| PROCS_IO_WRITE_BYTES | The amount of data that was written by I/O operations. Unit: byte. |
| MYSQL_CONN_ABORT | The number of the disconnected 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 held by the data record. |
| MYSQL_LOCK_WAITED | The number of lock wait transactions. |
| MYSQL_COM_INSERT | The number of statements that were executed to insert data. |
| MYSQL_COM_UPDATE | The number of statements that were executed to update data. |
| MYSQL_COM_DELETE | The number of statements that were executed to delete data. |
| MYSQL_COM_SELECT | The number of statements that were executed to query data. |
| MYSQL_COM_COMMIT | The number of transactions that were explicitly committed. |
| MYSQL_COM_ROLLBACK | The number of transactions that were rolled back. |
| MYSQL_COM_PREPARE | The number of statements that were 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 were created. |
| MYSQL_TMP_TABLES | The number of temporary tables that were created. |
| MYSQL_TMP_DISKTABLES | The number of temporary disk tables that were created. |
| MYSQL_SORT_MERGE | The number of times that data was merged and sorted. |
| MYSQL_SORT_ROWS | The number of rows that were sorted. |
| MYSQL_BYTES_RECEIVED | The amount of data that was received. Unit: byte. |
| MYSQL_BYTES_SENT | The amount of data that was sent. Unit: byte. |
| MYSQL_BINLOG_OFFSET | The size of the binary log file that was generated. Unit: byte. |
| MYSQL_IOLOG_OFFSET | The size of the binary log file that was sent from the primary node. Unit: byte. |
| MYSQL_RELAYLOG_OFFSET | The size of the binary log file that was sent from the read-only nodes. Unit: byte. |
| EXTRA | The statistics information about InnoDB. The EXTRA parameter consists of multiple
fields in the JSON format. For more information, see Table 1.
Note The values of the metrics in the InnoDB statistics information are the same as the
values that are obtained by executing the
SHOW STATUS statement.
|
| Field | Description |
|---|---|
| INNODB_TRX_CNT | The number of transactions. |
| INNODB_DATA_READ | The amount of data that was read. Unit: bytes. |
| INNODB_IBUF_SIZE | The number of pages that were merged. |
| INNODB_LOG_WAITS | The number of times that InnoDB waited to write logs. |
| INNODB_MAX_PURGE | The number of transactions that were deleted. |
| INNODB_N_WAITING | The number of locks for which InnoDB waited. |
| INNODB_ROWS_READ | The number of rows that were read. |
| INNODB_LOG_WRITES | The number of times that logs were written by InnoDB. |
| INNODB_IBUF_MERGES | The number of times that data was merged by InnoDB. |
| INNODB_DATA_WRITTEN | The amount of data that was written. Unit: byte. |
| INNODB_DBLWR_WRITES | The number of double write operations. |
| INNODB_IBUF_SEGSIZE | The size of data that was inserted into the buffer. |
| INNODB_ROWS_DELETED | The number of rows that were deleted. |
| INNODB_ROWS_UPDATED | The number of rows that were updated. |
| INNODB_COMMIT_TRXCNT | The number of transactions that were 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 were inserted. |
| INNODB_ACTIVE_TRX_CNT | The number of active transactions. |
| INNODB_OS_LOG_WRITTEN | The amount of log data that was written. Unit: byte. |
| 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 time taken to commit a transaction. |
| INNODB_MAX_COMMIT_TRXTIME | The maximum time taken to commit a transaction. |
| INNODB_DBLWR_PAGES_WRITTEN | The number of writes that were completed by double write operations. |
Examples
- The following examples of statements are provided. You can execute these statements
to query the system table to obtain performance data.
- Query the CPU usage and the memory usage within the last 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 within the last 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)
- Query the CPU usage and the memory usage within the last 30 seconds. Example:
- You can connect to a performance monitoring platform to monitor your database performance
in real time. For example, you can connect to Grafana to visualize monitoring data.
