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 a 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.

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.
Table 1. Fields in the EXTRA parameter
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)
  • 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.Grafana