Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog PolarDB-X Best Practices (4): Core Monitoring Metrics

PolarDB-X Best Practices (4): Core Monitoring Metrics

This article describes the core monitoring metrics in PolarDB-X, with some insights applicable to other databases within the MySQL ecosystem.

By Mengshi

PolarDB-X is a distributed database that consists of components such as CN (Compute Node), DN (Data Node), GMS (Global Metadata Service), and CDC (Change Data Capture). There are many monitoring metrics on its monitoring page. In fact, most databases provide massive monitoring metrics. It is easy to get confused if you do not have experience using the metrics. Most metrics are only useful in rare scenarios, such as some extreme troubleshooting scenarios. Usually, focusing on just a few key metrics is enough to gauge the database's operational status.

This article describes the core monitoring metrics in PolarDB-X, with some insights applicable to other databases within the MySQL ecosystem.

Computing Resources (CN)

CPU

For CNs, the core resource is the CPU, and the most important metric to monitor is CPU usage (Computing Resource Monitoring -> Instance -> CPU):

1

If CPU resources are insufficient, it is recommended to upgrade the CN specifications or increase the CN number.

Memory

The CN of PolarDB-X is a Java-implemented program, whose memory utilization shows the usage of the OLD region in the JVM heap memory. Since Java uses a garbage collection (GC) mechanism to reclaim memory, its memory monitoring (Computing Resource Monitoring -> Cluster -> Memory) graph usually shows a zigzag pattern as follows. It is a normal situation and you do not need to worry.

2

Monitoring only the memory usage cannot determine if the CN has sufficient memory resources. Generally, we recommend that you combine it with the Full GC Time (Computing Resource Monitoring -> Cluster -> Full GC Time).

3

If you are unfamiliar with Java, Full GC is a GC behavior that occurs when the memory is insufficient. In other words, the Full GC time should always be 0 when there is sufficient memory. If this metric frequently shows non-zero values within a short period, it usually indicates that the memory is insufficient.

Here are some suggestions:

  1. Upgrade the CN specifications.
  2. Optimize SQL statements. The following types of SQL statements may consume a large amount of memory. You can use SQL Explorer and other features for further analysis:
  • Extremely large batch insert operations.
  • Aggregate operations that generate a large number of intermediate results, such as GROUP BY and DISTINCT operations.
  • Operations such as joins across shards.

Logical QPS and physical QPS

Logical QPS (Computing Resource Monitoring -> Node -> Logical QPS) refers to the QPS at the CN layer. Physical QPS (Computing Resource Monitoring -> Node -> Physical QPS) refers to the QPS at the DN layer.

For example, if an SQL statement needs to query 10 shards, it is recorded as 1 query at the CN layer and 10 queries at the DN layer. In most cases, the physical QPS is greater than or equal to the logical QPS. (In rare cases, it is the other way around. For example, some SQL statements without table names can be processed by the CN.)

QPS includes query operations and DML operations.

These two metrics are useful. Please remember the best practices in one sentence:

The performance and scalability of PolarDB-X largely depend on whether the ratio of physical QPS : logical QPS is close to 1:1.

When the physical QPS is much higher than the logical QPS, for example:

  1. The table is divided into 16 shards.
  2. Physical QPS : logical QPS = 16:1.

This means that most queries may not have partition keys and thus scan the full shard. If the ratio is close to 1:1, most SQL statements have an equivalent condition on the partition key.

This ratio is not necessarily equal to 1:1. Operations such as global index writes and batch inserts will increase this ratio, which is normal.

Note: In the PolarDB-X QPS, the following statements are excluded to provide data that more closely matches the user's actual experience:
1. SELECT 1, which is usually sent by the connection pool to check the liveness of connections.
2. SELLECT @@xxxxxx, @@xxxxxx, @@xxxxx, which is usually generated by the mysql-java-connector when creating a connection.
3. Transaction control statements such as commit, rollback, begin, and set autocommit=0.
4. The prepare section in the Prepare protocol.
5. Other statements that are not usually generated by the business, including but not limited to some SHOW commands.

Storage Resources (DN)

CPU

CPU is a crucial resource, so it is recommended to set up an alert item for it. When CPU resources become insufficient:

  1. Optimize SQL statements.
  2. Upgrade the DN specifications.
  3. Increase the number of DNs.

Memory

There are several metrics related to memory in the DN.

Memory usage

Different from the memory usage of a CN, the memory usage of a DN (Storage Resource Monitoring -> Node -> Memory Usage) refers to the ratio of the memory used by the DN process to the physical memory.

Memory in the DN is mainly used for the buffer pool (usually more than 75% of memory). Memory for the buffer pool is allocated in advance when the DN process starts and is not affected by traffic. Therefore, memory usage monitoring shows that memory usage is maintained at more than 90% for a long time. This is normal and should not be a concern (idle memory is a waste for DN).

Therefore, for the memory of DN, we directly observe the relevant monitoring of the buffer pool.

Memory buffer pool

The buffer pool (Storage Resource Monitoring -> Node -> Memory Buffer Pool) monitoring graph includes three key metrics:

  1. The read hit ratio of the buffer pool.
  2. The number of dirty blocks in the buffer pool.
  3. The usage of the buffer pool.

Among them, the most important metric is the read hit ratio of the buffer pool.

If you don't know much about the principle of the database, you just remember that SQL statements read and write all data through the buffer pool. For data that is not in the buffer pool, it needs to be read from the disk to the buffer pool before any read or write operation.

Compared with memory read and write, disk I/O is an expensive and inefficient resource. Most database optimizations ultimately aim to reduce disk I/O.

Therefore, the hit ratio of the buffer pool is an important metric that affects database performance.

Normally, the hit ratio higher than 98% is a relatively proper level. Below this value, a large number of disk I/O operations may occur.

Why does the hit ratio of the buffer pool need to be so high?  
A commonly used point query SQL statement for an online business usually involves reading and writing dozens of pages (different levels of the tree, access to different indexes, and index lookup). 
For example, 10,000 QPS requires hundreds of thousands of page accesses. Even if 1% of the pages generate disk I/O, thousands of disk I/Os will be generated.

However, it is not necessary to set up an alert item for buffer pool monitoring. When the buffer pool has a bottleneck, it is usually reflected in IOPS, so setting up an alert item for IOPS is sufficient.

IOPS

IOPS is a core metric of disk resources. The IOPS usage (Storage Resource Monitoring -> Node -> IOPS Usage) is usually interconnected with the usage of the buffer pool.

When IOPS resources are insufficient, metrics such as CPU and active threads also increase, finally leading to slower SQL queries. SQL optimization is mostly about optimizing I/O, which will not be elaborated.

It is recommended to set up an alert item for IOPS.

Disk capacity

There's not much to say about disk capacity. It's also recommended to set up an alert item.

Change Data Capture (CDC)

The CDC is independent of the Logger node in Paxos and Raft. It is a component used to generate global binlogs. Therefore, you need to monitor this component only if you use global binlogs. For example:

  1. Subscribe to PolarDB-X binlogs through DTS.
  2. Subscribe to binlogs through Canal or Flink CDC.

The recommended monitoring items for alert setting are CPU and latency.

Alert Setting Suggestions

The following figure summarizes the monitoring items recommended for setting alerts.

Component Metric name in CloudMonitor Recommended alert threshold
CN (Computing Resources) CPU utilization of PolarDB-X compute nodes 70%
DN (Storage Resources) CPU utilization of PolarDB-X data nodes 70%
IOPS utilization of PolarDB-X data nodes 70%
Disk utilization of PolarDB-X data nodes 85%
CDC (Change Data Capture) CPU utilization of PolarDB-X CDC Dumper 70%
Latency of PolarDB-X CDC Dumper 10s 
Note: 
Metrics that are not mentioned in this article typically do not require setting up alert rules, but it does not mean that these metrics are useless. 
In scenarios such as troubleshooting, these metrics may still be of reference significance.
MySQL Database Developers Best practices PolarDB-X Computing Resources PolarDB for Xscale
0 1 0
Share on

Read previous post:

PolarDB-X Best Practice Series (3): Use Qwen and Stored Procedures to Quickly Generate Test Data

Read next post:

Compaction Service: Intelligent Scheduling and Scaling within Seconds

ApsaraDB

493 posts | 145 followers

You may also like

Comments

ApsaraDB

493 posts | 145 followers

Related Products

More Posts by ApsaraDB

See All