gs_checkperf

Background

MogDB provides the gs_checkperf tool for you to routinely check the MogDB-level performance (such as host CPU, Gauss GPU, and I/O usage), host-level performance (CPU, memory, and I/O usage), session-/process-level performance (CPU, memory, and I/O usage), and SSD performance (write and read performance). This helps you learn the MogDB load and fine-tune database performance accordingly.

Prerequisites

MogDB running status is normal and is not the read-only mode.
Services are running properly on the database.

NOTE: For security purposes, the tool in the gaussdbToolPath directory will be automatically deleted after the pre-installation is complete in enterprise-edition installation mode.

Precautions

The monitoring information of gs_checkperf comes from tables in PMK mode. If the ANALYZE operation is not performed on such tables, gs_checkperf may fail to be executed. An example of the error information is as follows:
```
LOG:  Statistics in some tables or columns(pmk.pmk_snapshot.snapshot_id) are not collected.
HINT:  Do analyze for them in order to generate optimized plan.
```
To fix this error, log in to a node where a master database node is deployed, connect to the postgres database, and run the following SQL statements:
```
analyze pmk.pmk_configuration;
analyze pmk.pmk_meta_data;
analyze pmk.pmk_snapshot;
analyze pmk.pmk_snapshot_dbnode_stat;
analyze pmk.pmk_snapshot_datanode_stat;
```

Syntax

Check the SSD performance (as user root).

gs_checkperf -U USER [-o OUTPUT] -i SSD [-l LOGFILE]

Check the MogDB performance (as the user for installing MogDB).

gs_checkperf [-U USER] [-o OUTPUT] [-i PMK] [--detail] [-l LOGFILE]

Display help information.
```
gs_checkperf -? | --help
```
Display version information.
```
gs_checkperf -V | --version
```

Parameter Description

-U

Specifies the name of the user for running MogDB.

Value range: name of the user for running MogDB

Specify this parameter when you run the command as user root.
-o

Specifies the file for saving performance check reports.

Value range: the name of a specified file

If this parameter is not specified, OS check results are displayed on the screen.
-i

Specifies the number of a check item. This parameter is case-insensitive. Format: -i PMK and -i SSD

Value range: PMK and SSD

Check the PMK item as an MogDB user.

Check the SSD item as user root.

If this parameter is not specified, the PMK item is checked by default as the MogDB user, and SSD item is checked by default as user root.
-detail

Displays detailed PMK check results.
-l

Specifies the path for saving log files.

The default path is /var/log/gaussdb/omm/om/gs_checkperf-YYYY-MM-DD_hhmmss.log.
-?, -help

Displays help information.
-V, -version

Displays version information.

Table 1 Performance check items

Type	Performance Parameter	Description
MogDB level	Host CPU Usage	CPU usage of the host
Gauss CPU Usage	Usage of the Gauss CPU
Shared Memory Hit Rate	Hit rate of the shared memory
Memory Sorting Ratio	Ratio of completed sorts in memory
I/O Usage	Number and time of file reads and writes
Disk Usage	Number of file writes, average write duration, and maximum write duration
Transaction Statistics	Number of current SQL executions and sessions
Host level	CPU Usage	Host CPU usage, including CPU busy time and CPU idle time
Memory Usage	Host memory usage, including total physical memory and used memory
I/O Usage	Number and time of file reads and writes
Session/Process level	CPU Usage	Session CPU usage, including CPU busy time and CPU idle time
Memory Usage	Session memory usage, including total physical memory and used memory
I/O Usage	Number of shared buffer hits in a session
SSD performance (Only user root can view it.)	Write Performance	The dd command (flag=direct bs=8M count=2560) is used to write data into an SSD every 10s.
Read Performance	The dd command (flag=direct bs=8M count=2560) is used to read data from an SSD every 7s.

Examples

Example 1: Run the following command to display performance statistics in simple mode on the screen:

gs_checkperf -i pmk -U omm
Cluster statistics information:
    Host CPU busy time ratio                     :    1.43       %
    GaussDB 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

Example 2: Run the following command to display performance statistics in detailed mode on the screen:

gs_checkperf -i pmk -U omm --detail
Cluster statistics information:
Host CPU usage rate:
    Host total CPU time                          :    42386.667  Jiffies
    Host CPU busy time                           :    1873.333   Jiffies
    Host CPU iowait time                         :    20.000     Jiffies
    Host CPU busy time ratio                     :    4.42       %
    Host CPU iowait time ratio                   :    .05        %
GaussDB CPU usage rate:
    GaussDB CPU time % in busy time                :    19.04      %
    GaussDB CPU time % in total time               :    .84        %
Shared buffer hit rate:
    Shared Buffer Reads                          :    0
    Shared Buffer Hits                           :    24129
    Shared Buffer Hit ratio                      :    100.00     %    In-memory sort ratio                         :    100.00     %I/O usage:
    Number of files                              :    537
    Physical Reads                               :    0
    Physical Writes                              :    0
    Read Time                                    :    0          ms
    Write Time                                   :    0          ms
Disk usage:
    DB size                                      :    70         MB
    Total Physical writes                        :    0
    Average Physical write                       :    0
    Maximum Physical write                       :    0
Activity statistics:
    Active SQL count                             :    2
    Session count                                :    3
Node statistics information:
dn_6001_6002:
    GaussDB CPU Time                               :    200        Jiffies
    Host CPU Busy Time                           :    3490       Jiffies
    Host CPU Total Time                          :    42330      Jiffies
    GaussDB CPU Time % in Busy Time                :    5.73       %
    GaussDB CPU Time % in Total Time               :    .47        %
    Physical memory                              :    8231776256 Bytes
    DB Memory usage                              :    877236224  Bytes
    Shared buffer size                           :    33554432   Bytes
    Shared buffer hit ratio                      :    100.00     %
    Sorts in memory                              :    123
    Sorts in disk                                :    0
    In-memory sort ratio                         :    100.00     %
    Number of files                              :    149
    Physical Reads                               :    0
    Physical Writes                              :    0
    Read Time                                    :    0
    Write Time                                   :    0
dn_6003_6004:
    GaussDB CPU Time                               :    170        Jiffies    Host CPU Busy Time                           :    1030       Jiffies
    Host CPU Total Time                          :    42470      Jiffies    GaussDB CPU Time % in Busy Time                :    16.50      %
    GaussDB CPU Time % in Total Time               :    .40        %
    Physical memory                              :    8231776256 Bytes
    DB Memory usage                              :    881434624  Bytes    Shared buffer size                           :    33554432   Bytes
    Shared buffer hit ratio                      :    100.00     %
    Sorts in memory                              :    119
    Sorts in disk                                :    0
    In-memory sort ratio                         :    100.00     %
    Number of files                              :    165
    Physical Reads                               :    0
    Physical Writes                              :    0
    Read Time                                    :    0
    Write Time                                   :    0
dn_6005_6006:
    GaussDB CPU Time                               :    220        Jiffies
    Host CPU Busy Time                           :    1100       Jiffies
    Host CPU Total Time                          :    42360      Jiffies
    GaussDB CPU Time % in Busy Time                :    20.00      %
    GaussDB CPU Time % in Total Time               :    .52        %
    Physical memory                              :    8231776256 Bytes
    DB Memory usage                              :    881430528  Bytes
    Shared buffer size                           :    33554432   Bytes
    Shared buffer hit ratio                      :    100.00     %
    Sorts in memory                              :    122
    Sorts in disk                                :    0
    In-memory sort ratio                         :    100.00     %
    Number of files                              :    115
    Physical Reads                               :    0
    Physical Writes                              :    0
    Read Time                                    :    0
    Write Time                                   :    0
Session statistics information(Top 10):
Session CPU statistics:
1 dn_6003_6004-postgres-dbazt:
    Session CPU time                             :    1211
    Database CPU time                            :    37670
    Session CPU time %                           :    3.21       %
2 dn_6005_6006-postgres-dbazt:
    Session CPU time                             :    1204
    Database CPU time                            :    35730
    Session CPU time %                           :    3.37       %
3 dn_6001_6002-postgres-dbazt:
    Session CPU time                             :    1064
    Database CPU time                            :    34120
    Session CPU time %                           :    3.12       %

Session Memory statistics:
1 dn_6005_6006-postgres-dbazt:
    Buffer Reads                                 :    8989
    Shared Buffer Hit ratio                      :    96.97
    In Memory sorts                              :    136
    In Disk sorts                                :    0
    In Memory sorts ratio                        :    100.00
    Total Memory Size                            :    21593048
    Used Memory Size                             :    18496600
2 dn_6003_6004-postgres-dbazt:
    Buffer Reads                                 :    9030
    Shared Buffer Hit ratio                      :    96.94
    In Memory sorts                              :    133
    In Disk sorts                                :    0
    In Memory sorts ratio                        :    100.00
    Total Memory Size                            :    21576664
    Used Memory Size                             :    18495688
3 dn_6001_6002-postgres-dbazt:
    Buffer Reads                                 :    8668
    Shared Buffer Hit ratio                      :    96.97
    In Memory sorts                              :    138
    In Disk sorts                                :    0
    In Memory sorts ratio                        :    100.00
    Total Memory Size                            :    21258856
    Used Memory Size                             :    18159736

Session IO statistics:
1 dn_6003_6004-postgres-dbazt:
    Physical Reads                               :    285
    Read Time                                    :    5320
2 dn_6005_6006-postgres-dbazt:
    Physical Reads                               :    281
    Read Time                                    :    5811
3 dn_6001_6002-postgres-dbazt:
    Physical Reads                               :    271
    Read Time                                    :    4662

Helpful Links

gs_check, gs_checkos

Issue