Checking Database Performance

Check Method

Use the gs_checkperf tool provided by MogDB to check hardware performance.

Prerequisites

MogDB is running properly.
Services are running properly on the database.

Procedure

Log in as the OS user omm to the primary node of the database.
Run the following command to check the MogDB database performance:
```
gs_checkperf
```

For details about performance statistical items, see "Table 1 Performance check items" in "Tool Reference > Server Tools > gs_checkperf".

Examples

Simple performance statistical result is displayed on the screen as follows:

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 you use the gs_checkperf tool to check the cluster performance, if the performance is abnormal, troubleshoot the issue by following instructions in this section.

Table 1 Cluster-level performance status

Abnormal Status	Solution
High CPU usage of hosts	1. Add high-performance CPUs, or replace current CPUs with them.2. Run the top command to check which system processes cause high CPU usage, and run the kill command to stop unused processes. `top`
High CPU usage of MogDB Kernel	1. Add high-performance CPUs, or replace current CPUs with them. 2. Run the top command to check which database processes cause high CPU usage, and run the kill command to stop unused processes. `top` 3. Use the gs_expand tool to add new hosts to lower the CPU usage.
Low hit ratio of the shared memory	1. Expand the memory. 2. Run the following command to check the OS configuration file /etc/sysctl.conf and increase the value of kernel.shmmax. `vim /etc/sysctl.conf`
Low in-memory sort ratio	Expand the memory.
High I/O and disk usage	1. Replace current disks with high-performance ones. 2. Adjust the data layout to evenly distribute I/O requests to all the physical disks. 3. Run VACUUM FULL for the entire database. `vacuum full;` 4. Clean up the disk space. 5. Reduce the number of concurrent connections.
Transaction statistics	Query the pg_stat_activity system catalog and disconnect unnecessary connections. (Log in to the database and run the mogdb=# \d+ pg_stat_activity; command.)

Table 2 Node-level performance status

Abnormal Status	Solution
High CPU usage	1. Add high-performance CPUs, or replace current CPUs with them. 2. Run the top command to check which system processes cause high CPU usage, and run the kill command to stop unused processes. `top`
High memory usage	Expand or clean up the memory.
High I/O usage	1. Replace current disks with high-performance ones. 2. Clean up the disk space. 3. Use memory read/write to replace as much disk I/O as possible, putting frequently accessed files or data in the memory.

Table 3 Session/process-level performance status

Abnormal Status	Solution
High CPU, memory, and I/O usage	Check which processes cause high CPU, memory, or I/O usage. If they are unnecessary processes, kill them; otherwise, analyze the specific cause of high usage. For example, if SQL statement execution occupies much memory, check whether the SQL statements need optimization.

Table 4 SSD performance status

Abnormal Status	Solution
SSD read/write fault	Run the following command to check whether SSD is faulty. If yes, analyze the specific cause. `gs_checkperf -i SSD -U omm`