VastBase——全局性能调优

目录

一、系统资源调优

1.内存和CPU

2.网络

3.I/O

二、查询最耗性能的SQL

三、分析作业是否被阻塞

---

背景：影响性能的因素

• 系统资源

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

• 负载

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

• 吞吐量

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

• 竞争

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

• 优化

  数据库优化可以影响到整个系统的性能。在执行SQL制定、数据库配置参数、表设计、数据分布等操作时，启用数据库查询优化器打造最有效的执行计划。

一、系统资源调优

1.内存和CPU

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

前五行是系统整体的统计信息。

第一行是任务队列信息，同 uptime 命令的执行结果。

01:06:48    当前时间
up 1:22    系统运行时间，格式为时:分
1 user    当前登录用户数
load average: 0.06, 0.60, 0.48    系统负载，即任务队列的平均长度。三个数值分别为 1分钟、5分钟、15分钟前到现在的平均值。

如果“id”低于10%，即表明CPU负载较高，可尝试通过降低本节点任务量等手段降低CPU负载。

分析时，请主要关注Vastbase进程占用的内存百分比（%MEM）和操作系统的剩余内存。

以root用户执行free命令查看cache的占用情况。

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

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

其中，Vastbase-server为仓库代码，下载地址openGauss-server: openGauss kernel ~ openGauss is an open source relational database management system.

若用户内存占用过高，需查看执行计划，重点分析是否有不合理的join顺序。

例如，多表关联时，执行计划中优先关联的两表的中间结果集比较大，导致最终执行代价比较大。

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

• 如果是系统CPU占用过高，需要查找异常系统进程进行处理。

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

2.分析进程中的线程

top -H -p 3875

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

表明线程ID为3890的线程他的对应的线程号是10

2.网络

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

执行ifconfig命令查看服务器网络连接。

• errors表示收包错误的总数量。

• dropped表示数据包已经进入了Ring Buffer，但是由于内存不够等系统原因，导致在拷贝到内存的过程中被丢弃的总数量。

• overruns表示Ring Buffer队列中被丢弃的报文数目，由于Ring Buffer(aka Driver Queue)传输的IO大于kernel能够处理的IO导致。

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

排查网络状态使用netstat -anp | grep命令

标记所在情况；
一个叫“redis-server”的进程，正在监听127.0.1：6379/0.0.0.：2222端口等等，“LISTEN”表示监听 PID：代表该进程的id 38464 

检查特定程序的网络链接情况

netstat -anp | grep vastbase

查看端口 ： 例如 5432端口我们看下

netstat -anp | grep 5432

3.I/O

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

iostat -xm 1   #1为间隔时间

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

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

pidstat -d 1 10     #1表示查看时间间隔1秒，10表示查看次数10次

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

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

df -T

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

• 降低并发数。

• 对查询相关表做VACUUM FULL

​vacuum full tablename;

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

 

二、查询最耗性能的SQL

登录数据库后：查询最耗性能的SQL

SELECT current_timestamp - query_start AS runtime, datname, usename, query FROM pg_stat_activity where state != 'idle' ORDER BY 1 desc;

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

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

SELECT query FROM pg_stat_activity WHERE current_timestamp - query_start > interval '1 days';

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

 SET track_activities = on;

 查看正在运行的查询语句

SELECT datname, usename, state FROM pg_stat_activity;

查询结果为如下

datname  | usename | state  |
----------+---------+--------+
    postgres |   vastbase   | idle   |
    postgres |   vastbase   | active |
(2 rows)

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

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

SELECT datname, usename, state FROM pg_stat_activity WHERE state != 'idle';

分析长时间运行的查询语句状态

• 若查询语句处于正常状态，则等待其执行完毕。

• 若查询语句阻塞，则通过如下命令查看当前处于阻塞状态的查询语句：

SELECT datname, usename, state, query FROM pg_stat_activity WHERE waiting = true;

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

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

 

 三、分析作业是否被阻塞

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

看阻塞的查询语句及阻塞查询的表.模式信息。

SELECT w.query as waiting_query,
w.pid as w_pid,
w.usename as w_user,
l.query as locking_query,
l.pid as l_pid,
l.usename as l_user,
t.schemaname || '.' || t.relname as tablename
from pg_stat_activity w join pg_locks l1 on w.pid = l1.pid
and not l1.granted join pg_locks l2 on l1.relation = l2.relation
and l2.granted join pg_stat_activity l on l2.pid = l.pid join pg_stat_user_tables t on l1.relation = t.relid
where w.waiting;

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

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

SELECT PG_TERMINATE_BACKEND(139834762094352);

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

pg_terminate_backend
----------------------
    t
(1 row)

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

FATAL:  terminating connection due to administrator command
FATAL:  terminating connection due to administrator command
The connection to the server was lost. Attempting reset: Succeeded.

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