When using RDS-PostgreSQL, sustained high CPU usage on the instance can lead to a decrease in the read/write performance of the database, impacting business operations. It is essential to prioritize the causes and addressing them. This section describes a method to troubleshoot the causes of high CPU usage and solutions.
Analysis of Reasons
Viewing CPU Usage through the Console
The loading functions vary depending on the resource pools. Please refer to the actual interface of the Console. In the text below, the East China 1 resource pool is used as an example to illustrate related operation steps.
1. Log in to the eSurfing Cloud Portal.
2. Click Console and select the corresponding resource pool, such as East China 1.
3. Select RDS-PostgreSQL and click it to go to the Console Administration page.
4. Select Monitoring > Resource Monitoring in the left menu. After entering the Resource Monitoring page, select the corresponding node of the corresponding instance at the upper right to view the CPU usage of the relevant node.
Viewing Statements, Locks, and other information Through Console and Analyzing the Reasons for High CPU Usage
If CPU usage is too high, you can view slow statements, statement execution status, locks, and other information for the current instance through the Console. The operation steps (The loading functions vary depending on the resource pools. Please refer to the actual interface of the Console) are as follows:
• Log in to the eSurfing Cloud Portal.
• Click Console and select the corresponding resource pool, such as East China 1.
• Select RDS-PostgreSQL and click it to go to the Console Administration page.
• Select Monitoring > Engine Monitoring and select the target instance through the dropdown box. By selecting Statement Execution Status Query or Top Slow Statement Query, you can view the statement execution information, lock analysis status, the current slow statement of the instance, and other exceptional statement information.
• Select Monitoring > Log Monitoring, select the target instance through the dropdown box, and select Slow Log and Error Log to analyze the exceptional log information of the instance.
Suggested Solutions
1. It is recommended to add indexes when building tables, especially in the scenario of associated fields during multi-table associated queries.
2. Try to avoid using select * and reducing full-table scanning. During query, specify fields, where conditions, etc.