ApsaraDB RDS:View RDS for MySQL metrics

You can use the Memory OOM Diagnosis view to analyze out-of-memory (OOM) problems.

• Memory Usage:

  • If the InnoDB Buffer Pool usage remains unchanged while the memory usage slowly and continuously increases for a long period, such as more than seven days, a memory leak may have occurred.

  • If the memory usage suddenly increases while the InnoDB Buffer Pool usage remains unchanged, the increase may be caused by traffic spikes.

  • If both the memory and the InnoDB Buffer Pool usage increase, the InnoDB Buffer Pool is being gradually filled, which is normal.

• Resident Memory: The amount of physical memory used.

• Open files, Temp File Size, Temp Disk Tables, and Sort Rows are common metrics that indicate memory consumption.

Memory growth is related to business metrics. SQL statements that cause sudden memory spikes are often untraceable because of OOM. Therefore, we recommend that you:

• Check business logs to determine the cause of the sudden memory increase.

• Upgrade the memory specifications and enable SQL Explorer and Audit. If a sudden memory spike occurs, you can check the running time of SQL queries to determine the cause.

You can use the Read-only Instance Delay Diagnosis view to diagnose delays for read-only instances.

• Active Session: Check for blocking from metadata locks.

  Typically, queries on large amounts of data prevent DDL statements from obtaining metadata locks. In this case, the DDL statements block other sessions, which causes connections to accumulate.

• DML Rows Processed, Pages Requested, DML/DDL Operations, and Temp Disk Space Used: Displays common business metrics.

• Replication Delay: The latency metric.

You can view the types of files that occupy the storage space of the instance and their change trends. The following metrics are commonly associated with storage usage:

• Data files (user_data_size): You can use Space Analysis to view the space usage of each database and table, and then scale out or delete unnecessary data. For more information, see Solutions for a Full Instance Caused by Data Files.

• Temporary files (temp_file_size): Temporary tables may be generated when you execute SQL statements to sort and group data or associate tables. Binary log cache files are generated before large transactions are committed. These tables and files occupy storage space. For more information, see Resolve full instance storage caused by temporary files.

• Binary logs (binlog_size): Large transactions can quickly generate binary logs. These logs occupy storage space. For more information about how to manage binary logs, see Resolve full instance storage caused by MySQL binary log files.

  Note

  If your services subscribe to the binary logs of the database, the logs may not be cleared promptly and can occupy space.

• Undo logs (undo_log_size): In most cases, long-running queries prevent undo logs from being cleared. You can check for long-running queries that have not been completed.

  Note

  In MySQL 5.6 and earlier, undo logs do not have a separate tablespace.

• Slow log (slowlog_size): If the slow log uses too much space, you can use the truncate command to clear it during off-peak hours.

  Note

  Support for the truncate command was added in version 20210630 of MySQL 5.7 and version 20210930 of MySQL 8.0.

• General logs (general_log_size): The total size of an instance's error, Performance Agent, and recovery logs, which is typically stable and under 1 GB. If the size significantly exceeds this value, please submit a ticket to contact the product team. This metric represents data that is periodically generated by the MySQL kernel, not the size of the general_log file in MySQL.

You can use the CPU Jitter Diagnostics view to analyze CPU jitter issues. Relevant metrics include the following:

• Business metrics:

  • Page Request: Typically, Buffer Pool requests fluctuate in sync with CPU utilization.

  • Rows Processed: Examine the relationship between CPU utilization and the number of rows processed to determine if a spike in the number of rows corresponds to a change in CPU utilization.

  • Queries: View the main types of SQL statements that are executed when CPU utilization changes.

• Connections:

  Thread Running: High concurrency can cause high CPU utilization. MDL stacking or row locks can also cause a buildup of connections, which increases CPU utilization.

Common causes of CPU jitter:

• Changes in business metrics, such as Page Request or Rows Processed, can affect CPU utilization. If this occurs, you can select the time range of the change in CPU utilization and execute Diagnosis to obtain a detailed root cause analysis.

• An increase in active connections causes CPU consumption. In this case, you must investigate the issue from the business side.

You can use the Large Transaction Recognition Diagnosis view to analyze large transaction issues.

• Threads Connected, Temp File Size, and Binlog Space: These are the three core metrics that indicate a large transaction. A large transaction is present in the database if one of the following events occurs:

  • Active sessions accumulate.

  • The temporary space first increases and then decreases.

  • After the temporary space decreases, the Binlog space increases.

• Rows Processed, Logical Page Write, and Queries per Second: These metrics are used to determine the type of a large transaction.

  For example, if there are few queries but many rows are deleted, it indicates a large transaction that deletes data.

Large transactions can block binary log writes:

• When an instance has a large transaction, the temporary tablespace (binlog cache) first increases gradually and then stabilizes.

• When the temporary tablespace is stable, the Binlog space increases. Because binary log writing is globally serial, other transactions are blocked, which causes connections to accumulate.

• If the instance runs RDS High-availability Edition, the probe statements from the high-availability (HA) component on the primary and secondary instances are also blocked, and a primary/secondary failover occurs.