MogDB
Ecological Tools
Doc Menu

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

  1. Log in as the OS user omm to the primary node of the database.
  2. 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