OpenGauss 数据库性能调优概述及实例分析

调优思路概述

openGauss 的总体性能调优思路为性能瓶颈点分析、关键参数调整以及 SQL 调优。在调优过程中，通过系统资源、吞吐量、负载等因素来帮助定位和分析性能问题，使系统性能达到可接受的范围。

openGauss 性能调优过程需要综合考虑多方面因素，因此，调优人员应对系统软件架构、软硬件配置、数据库配置参数、并发控制、查询处理和数据库应用有广泛而深刻的理解。

须知：性能调优过程有时候需要重启 openGauss，可能会中断当前业务。因此，业务上线后，当性能调优操作需要重启 openGauss 时，操作窗口时间需向管理部门提出申请，经批准后方可执行。

调优流程

调优流程如图 1 所示。

图 1 openGauss 性能调优流程

调优各阶段说明，如表 1 所示。

表 1 openGauss 性能调优流程说明

确定性能调优范围

数据库性能调优通常发生在用户对业务的执行效率不满意，期望通过调优加快业务执行的情况下。正如性能因素小节所述，数据库性能受影响因素多，从而性能调优是一项复杂的工程，有些时候无法系统性地说明和解释，而是依赖于 DBA 的经验判断。尽管如此，此处还是期望能尽量系统性的对性能调优方法加以说明，方便应用开发人员和刚接触 openGauss 的 DBA 参考。

性能因素

多个性能因素会影响数据库性能，了解这些因素可以帮助定位和分析性能问题。

(1) 系统资源

数据库性能在很大程度上依赖于磁盘的 I/O 和内存使用情况。为了准确设置性能指标，用户需要了解 openGauss 部署硬件的基本性能。CPU、硬盘、磁盘控制器、内存和网络接口等这些硬件性能将显著影响数据库的运行速度。

(2) 负载

负载等于数据库系统的需求总量，它会随着时间变化。总体负载包含用户查询、应用程序、并行作业、事务以及数据库随时传递的系统命令。比如：多用户在执行多个查询时会提高负载。负载会显著地影响数据库的性能。了解工作负载高峰期可以帮助用户更合理地利用系统资源，更有效地完成系统任务。

(3) 吞吐量：

负载等于数据库系统的需求总量，它会随着时间变化。总体负载包含用户查询、应用程序、并行作业、事务以及数据库随时传递的系统命令。比如：多用户在执行多个查询时会提高负载。负载会显著地影响数据库的性能。了解工作负载高峰期可以帮助用户更合理地利用系统资源，更有效地完成系统任务。

(4) 竞争：

使用系统的吞吐量来定义处理数据的整体能力。数据库的吞吐量以每秒的查询次数、每秒的处理事务数量或平均响应时间来测量。数据库的处理能力与底层系统(磁盘 I/O、CPU 速度、存储器带宽等)有密切的关系，所以当设置数据库吞吐量目标时，需要提前了解硬件的性能。

(5) 优化：

竞争是指两组或多组负载组件尝试使用冲突的方式使用系统的情况。比如，多条查询视图同一时间更新相同的数据，或者多个大量的负载争夺系统资源。随着竞争的增加，吞吐量下降。

调优范围确定

性能调优主要通过查看 openGauss 节点的 CPU、内存、I/O 和网络这些硬件资源的使用情况，确认这些资源是否已被充分利用，是否存在瓶颈点，然后针对性调优。

• (1) 如果某个资源已达瓶颈，则：
• 检查关键的操作系统参数和数据库参数是否合理设置，进行系统调优指南。
• 通过查询最耗时的 SQL 语句、跑不出来的 SQL 语句，找出耗资源的 SQL，进行 SQL 调优指南。

(2) 如果所有资源均未达瓶颈，则表明性能仍有提升潜力。可以查询最耗时的 SQL 语句，或者跑不出来的 SQL 语句，进行针对性的 SQL 调优指南。

(3) 硬件瓶颈点分析：

获取 openGauss 节点的 CPU、内存、I/O 和网络资源使用情况，确认这些资源是否已被充分利用，是否存在瓶颈点。

(4) 查询最耗性能的 SQL

(5) 分析作业是否被阻塞

硬件瓶颈点分析

• CPU：通过 top 命令查看 openGauss 内节点 CPU 使用情况，分析是否存在由于 CPU 负载过高导致的性能瓶颈。
• 内存：通过 top 命令查看 openGauss 节点内存使用情况，分析是否存在由于内存占用率过高导致的性能瓶颈。
• I/O：通过 iostat、pidstat 命令或 openGauss 健康检查工具查看 openGauss 内节点 I/O 繁忙度和吞吐量，分析是否存在由于 I/O 导致的性能瓶颈。
• 网络：通过 sar 或 ifconfig 命令查看 openGauss 内节点网络使用情况，分析是否存在由于网络导致的性能瓶颈。

CPU

通过 top 命令查看 openGauss 内节点 CPU 使用情况，分析是否存在由于 CPU 负载过高导致的性能瓶颈。

1. 查看 CPU 状况

查询服务器 CPU 的使用情况主要通过以下方式：

在所有存储节点，逐一执行 top 命令，查看 CPU 占用情况。执行该命令后，按 “1” 键，可查看每个 CPU 核的使用率。

top-17:05:04up32days, 20:34,  5users,  loadaverage: 0.02, 0.02, 0.00
Tasks: 124total,   1running, 123sleeping,   0stopped,   0zombie
Cpu0  :  0.0%us,  0.3%sy,  0.0%ni, 69.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu1  :  0.3%us,  0.3%sy,  0.0%ni, 69.3%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu2  :  0.3%us,  0.3%sy,  0.0%ni, 69.3%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu3  :  0.3%us,  0.3%sy,  0.0%ni, 69.3%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   8038844ktotal,  7165272kused,   873572kfree,   530444kbuffers
Swap:  4192924ktotal,     4920kused,  4188004kfree,  4742904kcached

PIDUSERPRNIVIRTRESSHRS%CPU%MEMTIME+COMMAND

35184omm200822m421m128mS05.45:28.15gaussdb
1root20013592820784S00.01:16.62init

分析时，请主要关注进程占用的 CPU 利用率。

其中，统计信息中 “us” 表示用户空间占用 CPU 百分比，“sy” 表示内核空间占用 CPU 百分比，“id” 表示空闲 CPU 百分比。如果 “id” 低于 10%，即表明 CPU 负载较高，可尝试通过降低本节点任务量等手段降低 CPU 负载。

2. 性能参数分析

使用 “top -H” 命令查看 CPU，显示内容如下所示：

14root200000S00.00:16.41events/3
top-14:22:49up5days, 21:51,  2users,  loadaverage: 0.08, 0.08, 0.06
Tasks: 312total,   1running, 311sleeping,   0stopped,   0zombie
Cpu(s):  1.3%us,  0.7%sy,  0.0%ni, 95.0%id,  2.4%wa,  0.5%hi,  0.2%si,  0.0%st
Mem:   8038844ktotal,  5317668kused,  2721176kfree,   180268kbuffers
Swap:  4192924ktotal,        0kused,  4192924kfree,  2886860kcached

PIDUSERPRNIVIRTRESSHRS%CPU%MEMTIME+COMMAND

3105root2005049211m2708S30.122:22.56acc-snf

4015gdm200232m23m11mS00.311:34.70gdm-simple-gree
51001omm200121401484948R00.00:00.94top

54885omm200615m396m116mS05.10:09.44gaussdb


1root20013592944792S00.00:08.54init

根据查询结果中 “Cpu (s)” 分析是系统 CPU(sy)还是用户 CPU(us)占用过高。

• 如果是系统 CPU 占用过高，需要查找异常系统进程进行处理。
• 如果是 “USER” 为 omm 的 openGauss 进程 CPU 占用过高，请根据目前运行的业务查询内容，对业务 SQL 进行优化。请根据以下步骤，并结合当前正在运行的业务特征进行分析，是否该程序处于死循环逻辑。

使用 “top -H -p pid” 查找进程内占用的 CPU 百分比较高的线程，进行分析：

top-H-p54952

top -H -p 54952查询结果如下所示，top 中可以看到占用 CPU 很高的线程，下面以线程 54775 为主，分析其为何占用 CPU 过高。

top-14:23:27up5days, 21:52,  2users,  loadaverage: 0.04, 0.07, 0.05
Tasks:  13total,   0running,  13sleeping,   0stopped,   0zombie
Cpu(s):  0.9%us,  0.4%sy,  0.0%ni, 97.3%id,  1.1%wa,  0.2%hi,  0.1%si,  0.0%st
Mem:   8038844ktotal,  5322180kused,  2716664kfree,   180316kbuffers
Swap:  4192924ktotal,        0kused,  4192924kfree,  2889860kcached

PIDUSERPRNIVIRTRESSHRS%CPU%MEMTIME+COMMAND
54775omm200684m424m131mS05.40:00.32gaussdb
54951omm200684m424m131mS05.40:00.84gaussdb
54732omm200684m424m131mS05.40:00.24gaussdb
54758omm200684m424m131mS05.40:00.00gaussdb
54759omm200684m424m131mS05.40:00.02gaussdb
54773omm200684m424m131mS05.40:02.79gaussdb
54780omm200684m424m131mS05.40:00.04gaussdb
54781omm200684m424m131mS05.40:00.21gaussdb
54782omm200684m424m131mS05.40:00.02gaussdb
54798omm200684m424m131mS05.40:16.70gaussdb
54952omm200684m424m131mS05.40:07.51gaussdb
54953omm200684m424m131mS05.40:00.81gaussdb
54954omm200684m424m131mS05.40:06.54gaussdb

使用 “gstack” 查看进程内各线程的函数调用栈。查找上一步骤中占用 CPU 较高的线程 ID 对应的线程号：

gstack54954

查询结果如下所示，其中线程 ID54775 对应线程号是 10。

192.168.0.11:~ # gstack54954
Thread10 (Thread0x7f95a5fff710 (LWP54775)):
#00x00007f95c41d63c6inpoll () from/lib64/libc.so.6
#10x0000000000d3d2d3inWaitLatchOrSocket(Latchvolatile*, int, int, long) ()
#20x000000000095ed25inXLogPageRead(XLogRecPtr*, int, bool, bool) ()
#30x000000000095f6ddinReadRecord(XLogRecPtr*, int, bool) ()
#40x000000000096aef0inStartupXLOG() ()
#50x0000000000d5607ainStartupProcessMain() ()
#60x00000000009e19f9inAuxiliaryProcessMain(int, char**) ()
#70x0000000000d50135inSubPostmasterMain(int, char**) ()
#80x0000000000d504ecinMainStarterThreadFunc(void*) ()
#90x00007f95c79b85f0instart_thread () from/lib64/libpthread.so.0
#100x00007f95c41df84dinclone () from/lib64/libc.so.6
#110x0000000000000000in?? ()

内存

通过 top 命令查看 openGauss 节点内存使用情况，分析是否存在由于内存占用率过高导致的性能瓶颈。

1. 查看内存状况

查询服务器内存的使用情况主要通过以下方式：

执行 top 命令，查看内存占用情况。执行该命令后，按 “Shift+M” 键，可按照内存大小排序。

top-11:38:26up2days, 17:59, 10users,  loadaverage: 0.01, 0.05, 0.15
Tasks: 685total,   1running, 684sleeping,   0stopped,   0zombie
%Cpu(s):  0.2us,  0.2sy,  0.0ni, 99.7id,  0.0wa,  0.0hi,  0.0si,  0.0st
KiBMem : 19740646+total, 23503420free, 15947100used, 15795595+buff/cache
KiBSwap:  8242172total,  8242172free,        0used. 13366219+availMem

PIDUSERPRNIVIRTRESSHRS%CPU%MEMTIME+COMMAND
29838omm2001373104456904175248S3.60.298:53.16gaussdb
27789omm20015073241363216S0.00.00:00.00gsql
45659omm20011716440521860S0.00.00:00.24bash
8087omm20011716440001848S0.00.00:00.05bash
27459omm20011716040001848S0.00.00:00.04bash
33619omm20011712038521740S0.00.00:00.04bash
27282omm20011712038401728S0.00.00:00.03bash
9923omm20015806429321612R0.30.00:00.04top

分析时，请主要关注 gaussdb 进程占用的内存百分比(% MEM)、整系统的剩余内存。

显示信息中的主要属性解释如下：

• total：物理内存总量。
• used：已使用的物理内存总量。
• free：空闲内存总量。
• buffers：进程使用的虚拟内存总量。
• % MEM：进程占用的内存百分比。
• VIRT：进程使用的虚拟内存总量VIRT=SWAP+RES。
• SWAP：进程使用的虚拟内存中已被换出到交换分区的量。
• RES：进程使用的虚拟内存中未被换出的量。
• SHR：共享内存大小。

2. 性能参数分析

(1) 以 root 用户执行 “free” 命令查看 cache 的占用情况。

free

查询结果如下所示：

totalusedfreesharedbufferscached
Mem:       80388446336184170266003758962880912
-/+ buffers/cache:    30793764959468
Swap:      419292404192924

(2) 若 “cache” 占用过高，请执行如下命令开启自动清除缓存功能。

shopenGauss-server/src/bin/scripts/run_drop_cache.sh

其中，openGauss-server 为仓库代码，下载地址 https://gitee.com/opengauss/openGauss-server.git

(3) 若用户内存占用过高，需查看执行计划，重点分析以下内容。

是否有不合理的 join 顺序。例如，多表关联时，执行计划中优先关联的两表的中间结果集比较大，导致最终执行代价比较大。

I/O

通过 iostat、pidstat 命令或 openGauss 健康检查工具查看 openGauss 内节点 I/O 繁忙度和吞吐量，分析是否存在由于 I/O 导致的性能瓶颈。

1. 查看 I/O 状况

查询服务器 I/O 的方法主要有以下三种方式：

(1) 使用 iostat 命令查看 I/O 情况。此命令主要关注单个硬盘的 I/O 使用率和每秒读取、写入的数量。

iostat-xm1//1为间隔时间
Device:         rrqm/swrqm/sr/sw/srMB/swMB/savgrq-szavgqu-szawaitr_awaitw_awaitsvctm%util
sdc0.01519.622.3544.100.312.17109.660.6814.622.8015.250.311.42
sdb0.01515.955.8444.780.892.16123.510.7214.191.5515.840.311.55
sdd0.02519.932.3643.910.322.17110.160.6514.122.5814.740.301.38
sde0.02520.262.3445.170.312.18107.460.8016.862.9217.580.341.63
sda12.0715.723.975.010.070.0834.110.2830.6410.1146.920.980.88

“rMB/s” 为每秒读取的 MB 数，“wMB/s” 为每秒写入的 MB 数，“% util” 为硬盘使用率。

(2) 使用 pidstat 命令查看 I/O 情况。此命令主要关注单个进程每秒读取、写入的数量。

pidstat-d110//1为采样间隔时间，10为采样次数
03:17:12PMUIDPIDkB_rd/skB_wr/skB_ccwr/sCommand
03:17:13PM1006361340.0059436.000.00gaussdb

“kB_rd/s” 为每秒读取的 kB 数，“kB_wr/s” 为每秒写入的 kB 数。

(3) 使用 gs_checkperf 工具对 openGauss 进行性能检查，需要以 omm 用户登录。

gs_checkperf
Clusterstatisticsinformation:
HostCPUbusytimeratio                     :    .69%
MPPDBCPUtime%inbusytime                :    .35%
SharedBufferHitratio                      :    99.92%
In-memorysortratio                         :    100.00%
PhysicalReads                               :    8581
PhysicalWrites                              :    2603
DBsize                                      :    281MB
TotalPhysicalwrites                        :    1944
ActiveSQLcount                             :    3
Sessioncount                                :    11

显示结果包括每个节点的 I/O 使用情况，物理读写次数。

也可以使用 gs_checkperf –detail 命令查询每个节点的详细性能信息。

2.性能参数分析

(1) 检查磁盘空间使用率，建议不要超过 60%。

df-T

(2) 若 I/O 持续过高，建议尝试以下方式降低 I/O。

• 降低并发数。
• 对查询相关表做 VACUUM FULL。

vacuumfulltablename;

说明：

建议用户在系统空闲时进行 VACUUM FULL 操作，VACUUM FULL 操作会造成短时间内 I/O 负载重，反而不利于降低 I/O。

网络

通过 sar 或 ifconfig 命令查看 openGauss 内节点网络使用情况，分析是否存在由于网络导致的性能瓶颈。

1. 查看网络状况

查询服务器网络状况的方法主要有以下两种方式：

(1) 使用 root 用户身份登录服务器，执行如下命令查看服务器网络连接。

SIA1000056771:~ # ifconfig
eth0Linkencap:EthernetHWaddr28:6E:D4:86:7D:D5
inetaddr:10.180.123.163Bcast:10.180.123.255Mask:255.255.254.0
inet6addr: fe80::2a6e:d4ff:fe86:7dd5/64Scope:Link
UPBROADCASTRUNNINGMULTICASTMTU:1500Metric:1
RXpackets:5669314errors:0dropped:0overruns:0frame:0
TXpackets:4955927errors:0dropped:0overruns:0carrier:0
collisions:0txqueuelen:1000
RXbytes:508077795 (484.5Mb)  TXbytes:818004366 (780.1Mb)

loLinkencap:LocalLoopback
inetaddr:127.0.0.1Mask:255.0.0.0
inet6addr: ::1/128Scope:Host
UPLOOPBACKRUNNINGMTU:16436Metric:1
RXpackets:711938errors:0dropped:0overruns:0frame:0
TXpackets:711938errors:0dropped:0overruns:0carrier:0
collisions:0txqueuelen:0
RXbytes:164158862 (156.5Mb)  TXbytes:164158862 (156.5Mb)

• “errors” 表示收包错误的总数量。
• “dropped” 表示数据包已经进入了 Ring Buffer，但是由于内存不够等系统原因，导致在拷贝到内存的过程中被丢弃的总数量。
• “overruns” 表示 Ring Buffer 队列中被丢弃的报文数目，由于 Ring Buffer (aka Driver Queue) 传输的 IO 大于 kernel 能够处理的 IO 导致。

分析时，如果发现上述三个值持续增长，则表示网络负载过大或者存在网卡、内存等硬件故障。

(2) 使用 sar 命令查看服务器网络连接。

sar-nDEV1//1为间隔时间
Average: IFACErxpck/stxpck/srxkB/stxkB/srxcmp/stxcmp/srxmcst/s%ifutil
Average:    lo1926.941926.9425573.9225573.920.000.000.000.00
Average:  A1-00.000.000.000.000.000.000.000.00
Average:  A1-10.000.000.000.000.000.000.000.00
Average:  NIC05.171.480.440.920.000.000.000.00
Average:  NIC10.000.000.000.000.000.000.000.00
Average:  A0-08173.0692420.6697102.22133305.090.000.000.000.00
Average:  A0-111431.379373.06156950.45494.400.000.000.000.00
Average:  B3-00.000.000.000.000.000.000.000.00
Average:  B3-10.000.000.000.000.000.000.000.00

“rxkB/s” 为每秒接收的 kB 数，“txkB/s” 为每秒发送的 kB 数。

分析时，请主要关注每个网卡的传输量和是否达到传输上限。

检查完后，按 “Ctrl+Z” 键退出查看。

查询最耗性能的 SQL

系统中有些 SQL 语句运行了很长时间还没有结束，这些语句会消耗很多的系统性能，请根据本章内容查询长时间运行的 SQL 语句。

操作步骤：

(1) 以操作系统用户 omm 登录数据库节点。

(2) 使用如下命令连接数据库：

gsql-dpostgres-p8000

postgres 为需要连接的数据库名称，8000 为数据库节点的端口号。

连接成功后，系统显示类似如下信息：

gsql ((openGauss1.0build290d125f) compiledat2020-05-0802:59:43commit2143lastmr131
Non-SSLconnection (SSLconnectionisrecommendedwhenrequiringhigh-security)
Type"help"forhelp.

openGauss=#

(3) 查询系统中长时间运行的查询语句。

SELECTcurrent_timestamp-query_startASruntime, datname, usename, queryFROMpg_stat_activitywherestate!='idle'ORDERBY1desc;

查询后会按执行时间从长到短顺序返回查询语句列表，第一条结果就是当前系统中执行时间最长的查询语句。返回结果中包含了系统调用的 SQL 语句和用户执行 SQL 语句，请根据实际找到用户执行时间长的语句。

若当前系统较为繁忙，可以通过限制 current_timestamp – query_start 大于某一阈值来查看执行时间超过此阈值的查询语句。

SELECTqueryFROMpg_stat_activityWHEREcurrent_timestamp-query_start>interval'1 days';

(4) 设置参数 track_activities 为 on。

SETtrack_activities=on;

当此参数为 on 时，数据库系统才会收集当前活动查询的运行信息。

(5) 查看正在运行的查询语句。

以查看视图 pg_stat_activity 为例：

SELECTdatname, usename, stateFROMpg_stat_activity;
datname|usename|state|
----------+---------+--------+
postgres|omm|idle|
postgres|omm|active|
(2rows)

如果 state 字段显示为 idle，则表明此连接处于空闲，等待用户输入命令。

如果仅需要查看非空闲的查询语句，则使用如下命令查看：

SELECTdatname, usename, stateFROMpg_stat_activityWHEREstate!='idle';

(6) 分析长时间运行的查询语句状态。

• 若查询语句处于正常状态，则等待其执行完毕。
• 若查询语句阻塞，则通过如下命令查看当前处于阻塞状态的查询语句：

SELECTdatname, usename, state, queryFROMpg_stat_activityWHEREwaiting=true;

查询结果中包含了当前被阻塞的查询语句，该查询语句所请求的锁资源可能被其他会话持有，正在等待持有会话释放锁资源。

说明：

只有当查询阻塞在系统内部锁资源时，waiting 字段才显示为 true。尽管等待锁资源是数据库系统最常见的阻塞行为，但是在某些场景下查询也会阻塞在等待其他系统资源上，例如写文件、定时器等。但是这种情况的查询阻塞，不会在视图 pg_stat_activity 中体现。

分析作业是否被阻塞

数据库系统运行时，在某些业务场景下查询语句会被阻塞，导致语句运行时间过长，可以强制结束有问题的会话。

操作步骤

(1) 以操作系统用户 omm 登录数据库节点。

(2) 使用如下命令连接数据库：

gsql-dpostgres-p8000

postgres 为需要连接的数据库名称，8000 为数据库节点的端口号。

连接成功后，系统显示类似如下信息：

gsql ((openGauss1.0build290d125f) compiledat2020-05-0802:59:43commit2143lastmr131
Non-SSLconnection (SSLconnectionisrecommendedwhenrequiringhigh-security)
Type"help"forhelp.

openGauss=#

(3) 查看阻塞的查询语句及阻塞查询的表、模式信息。

SELECTw.queryaswaiting_query,
w.pidasw_pid,
w.usenameasw_user,
l.queryaslocking_query,
l.pidasl_pid,
l.usenameasl_user,
t.schemaname||'.'||t.relnameastablename
frompg_stat_activitywjoinpg_locksl1onw.pid=l1.pid
andnotl1.grantedjoinpg_locksl2onl1.relation=l2.relation
andl2.grantedjoinpg_stat_activitylonl2.pid=l.pidjoinpg_stat_user_tablestonl1.relation=t.relid
wherew.waiting;

该查询返回线程 ID、用户信息、查询状态，以及导致阻塞的表、模式信息。

(4) 使用如下命令结束相应的会话。其中，139834762094352 为线程 ID。

SELECTPG_TERMINATE_BACKEND(139834762094352);

显示类似如下信息，表示结束会话成功。

PG_TERMINATE_BACKEND
----------------------
t
(1row)

显示类似如下信息，表示用户正在尝试结束当前会话，此时仅会重连会话，而不是结束会话。

FATAL:  terminatingconnectionduetoadministratorcommand
FATAL:  terminatingconnectionduetoadministratorcommand
Theconnectiontotheserverwaslost. Attemptingreset: Succeeded.

说明：

gsql 客户端使用 PG_TERMINATE_BACKEND 函数终止本会话后台线程时，客户端不会退出而是自动重连。

本篇内容就到这里了，感谢小伙伴们的学习。