Core monitoring metrics and alerting practices

CPU usage

• Purpose: Monitor the computational load on CNs to prevent slow SQL execution or request backlogs caused by CPU resource exhaustion.

• Metric analysis: In the PolarDB-X console, view the metric in the Monitoring and Alerting > Compute Monitoring > Instance > CPU chart. A consistently high CPU usage (for example, above 70%) typically indicates a compute resource bottleneck.

• Recommended actions:

  • Scale out: When an overall increase in business queries per second (QPS) raises the total computational load, add more CNs to horizontally expand processing capacity.

  • Scale up: When a single complex query, such as a large report or a complex join, consumes significant CPU resources, upgrade the specifications of a single CN to enhance its computing power.

Memory and full GC

• Purpose: Monitor the health of the JVM heap memory on CNs. Frequent Full Garbage Collections (GCs) are a clear sign of insufficient memory or a memory leak and can severely degrade performance.

• Metric analysis:

  • Memory usage: In the PolarDB-X console, view the metric in the Monitoring and Alerting > Compute Monitoring > Cluster > Memory Capacity chart.

    CN memory is JVM heap memory, and its usage metric reflects the usage of the JVM's old generation. A regular sawtooth pattern in memory usage is a normal sign of Young GC. Due to the JVM's memory management, allocated heap memory is usually not immediately released to the operating system even when the workload decreases. Instead, it is retained for reuse. Therefore, it is normal for memory usage to remain at a high level (for example, 80% to 90%). The core metric for identifying a memory bottleneck is the Full GC time.

  • Full GC time: In the PolarDB-X console, view the metric in the Monitoring and Alerting > Compute Monitoring > Cluster > Full GC Time chart. If memory is sufficient, this metric should always be 0. Any non-zero value is a cause for concern. Frequent occurrences indicate a severe memory shortage.

• Recommended actions:

  1. Identify memory-intensive SQL: Use the SQL Explorer feature to analyze and optimize SQL statements that consume large amounts of memory. Common types include:

     • Large-scale Batch Insert operations.

     • Aggregate operations that generate many intermediate results, such as GROUP BY or DISTINCT.

     • Cross-shard JOIN operations.

  2. Upgrade CNs: If memory pressure persists after SQL optimization, upgrade the memory specifications of the CNs.

  3. Monitor the Full GC time instead of memory usage.

Logical QPS and physical QPS

• Purpose: Measure the database's distributed query efficiency and determine whether the partition key is used effectively.

• Metric analysis:

  • Logical QPS: In the PolarDB-X console, view the metric in the Monitoring and Alerting > Compute Monitoring > Node > Logical TPS/Logical QPS/Physical QPS chart. This metric represents the number of SQL requests received by the CN layer.

  • Physical QPS: In the PolarDB-X console, view the metric in the Monitoring and Alerting > Compute Monitoring > Node > Logical TPS/Logical QPS/Physical QPS chart. This metric represents the number of SQL executions pushed down from the CN layer to the DN layer.

• Recommended actions:

  • The ratio of physical QPS to logical QPS is a key indicator of database scalability. Ideally, this ratio should be close to 1:1, which indicates that most queries are precisely routed to a single data shard and that the partition key is used effectively.

  • If physical QPS is much higher than logical QPS (for example, if an instance has 16 shards and the ratio is close to 16:1), it indicates that many queries are executed without a partition key. This causes full-shard scans, which severely consume system resources and degrade performance.

  • Use the SQL Explorer feature to filter for SQL statements that have more than one physical execution. This helps you quickly locate queries that require optimization.

CPU usage

• Purpose: Monitor the computational load on DNs. High CPU usage on a DN is often related to a large number of complex computations being pushed down or I/O bottlenecks.

• Recommended actions:

  1. Optimize SQL: Check for slow queries or full table scans, and optimize your index design.

  2. Upgrade DNs: Upgrade the CPU and memory specifications of the DNs.

  3. Add DNs: Add more DNs to distribute the load. This operation triggers data redistribution and consumes resources; perform it during off-peak hours.

Memory

• Purpose: Monitor the memory health of DNs, especially the efficiency of the buffer pool.

• Metric analysis:

  • Memory usage: A DN's memory is primarily used for the buffer pool. This memory is allocated when the process starts. Therefore, it is normal and healthy for the memory usage to remain above 90% for extended periods.

  • Buffer pool read hit ratio: In the PolarDB-X console, view the metric in the Monitoring and Alerting > Storage Resource Monitoring > Node > Memory Buffer Pool chart. This metric is a core indicator of DN memory performance. All data reads and writes pass through the buffer pool. A higher hit ratio means fewer reads from disk. For online transaction processing (OLTP) workloads, a healthy level is above 98%. If the value falls below this level, performance degrades due to increased disk I/O.

IOPS usage

• Purpose: Monitor disk I/O pressure, which is a common performance bottleneck.

• Metric analysis: In the PolarDB-X console, view the metric in the Monitoring and Alerting > Storage Resource Monitoring > Node > IOPS Usage chart. IOPS usage is usually negatively correlated with the buffer pool read hit ratio. When the buffer pool cannot cache all hot data, disk I/O increases. If IOPS usage remains high, SQL latency increases significantly.

Recommended alert configurations