openGauss routine maintenance: check database performance

Check database performance
Inspection method
The performance statistics tool gs_checkperf provided by openGauss can check the hardware performance.

prerequisite

openGauss is running normally.
The business running on the database is running normally.
Steps

Log in to the active database node as the operating system user omm.

Run the following command to check the performance of the openGauss database.

gs_checkperf
For specific performance statistics items, see "Server Tools > gs_checkperf > Performance Check Items" in the "openGauss Tool Reference".

example

Displays performance statistics on screen in a concise format.

gs_checkperf -i pmk -U omm
Cluster statistics information:
Host CPU busy time ratio : 1.43 %
MPPDB CPU time % in busy time : 1.88 %
Shared Buffer Hit ratio : 99.96 %
In-memory sort ratio : 100.00 %
Physical Reads : 4
Physical Writes : 25
DB size : 70 MB
Total Physical writes : 25
Active SQL count : 2
Session count : 3
exception handling
After using the gs_checkperf tool to check the performance status of openGauss, if an abnormality is found in the check result, you can repair it according to the following content.

Table 2 Check openGauss level performance status

Abnormal state

Approach

The CPU usage of the host is high

1. Replace and increase high-performance CPU.

2. Use the top command to check which processes in the system have high CPU usage, and then use the kill command to close unused processes.

""top
openGauss high CPU occupancy

1. Replace and increase high-performance CPU.

2. Use the top command to check which processes in the database have high CPU usage, and then use the kill command to close unused processes.

""top

1. Use the gs_expand tool to expand the capacity and add a new host to balance the CPU occupancy.

Shared memory hit rate is low

1. Expand memory.

2. Use the following command to view the operating system configuration file /etc/sysctl.conf, and increase the shared memory kernel.shmmax value.

""vim /etc/sysctl.conf
Low in-memory sort ratio

Expand memory.

I/O, high disk usage

1. Replace the high-performance disk.

2. Adjust the data layout and try to allocate I/O requests to all physical disks reasonably.

3. Perform VACUUM FULL operation on the whole database.

""vacuum full;

1. Perform disk defragmentation, refer to the above to perform a vacuum full of the entire database or perform a single-table vacuum/vacuum full operation.

2. Reduce the number of concurrency.

transaction statistics

Query the pg_stat_activity system table and disconnect unnecessary connections. (query after logging in to the database: openGauss=# \d+ pg_stat_activity;)

Table 3 Check node level performance status

Abnormal state

Approach

High CPU occupancy

1. Replace and increase high-performance CPU.

2. Use the top command to check which processes in the system have high CPU usage, and then use the kill command to close unused processes.

""top
High memory usage

Expand or clear memory.

I/O high usage

1. Replace the high-performance disk.

2. Perform disk cleanup.

3. Replace direct disk I/O with memory reading and writing as much as possible, so that frequently accessed files or data can be put into memory for operation and processing.

Table 4 Session/process level performance status

Abnormal state

Approach

CPU, memory, and I/O usage are too high

Check which process occupies high CPU/memory or high I/O usage. If it is a useless process, kill it. Otherwise, check the specific reason. For example, SQL execution takes up a lot of memory, check whether the SQL statement needs to be optimized.

Table 5 SSD performance status

Abnormal state

Approach

SSD read and write performance failure

Run the following commands to check whether the SSD is faulty and troubleshoot the specific cause.

""gs_checkperf -i SSD -U omm