GaussDB性能调优

数据库性能调优是一项复杂且体系性的工作，必要综合思量多方面的因素。因此，调优职员应对体系软件架构、软硬件设置、数据库设置参数、并发控制、查询处理惩罚和数据库应用拥有广泛而深刻的明白。
本文旨在分析GaussDB性能调优的总体思绪，探究体系团体性能题目，以及对锁壅闭题目举行分析和优化。

一、性能调优思绪

GaussDB总体性能调优的思绪是：先举行性能瓶颈点分析，找到相应的瓶颈点之后，再针对性地举行优化，直到体系性能到达业务可继承的范围内。
调优思绪，如图1所示：

图1 GaussDB总体性能调优思绪

起首，应该确认应用压力是否转达到数据库，可以通太过析数据库节点的资源利用环境，如CPU、I/O、内存以及数据库线程池、活泼会话等信息来辅助判断。GaussDB数据库的管控平台提供了丰富的监控指标体系，便于性能分析职员检察数据库的及时大概汗青资源利用环境。

登录管控平台后，进入监控巡检菜单，选择监控大盘，即可检察对应实例的CPU/内存利用率，如图2所示。

图2 对应实例的CPU/内存利用率

点击磁盘/存储菜单，可以检察磁盘I/O利用率，重点关注磁盘读写速率以及时延是否符合预期，如图3所示。

图3 磁盘读写速率以及时延环境

点击网络菜单，可以检察网络传输速率及网卡是否有丢包、错包等环境，如图4所示。

图4 网络传输速率及网卡发送速率

选择毗连菜单，可以检察数据库的毗连及会话状态，如图5所示。

图5 毗连及会话状态

图5中，假如活泼会话的占比远低于应用的并发数，分析数据库中大量会话处于空闲状态。同时，假如CPU利用率也很低，那么，就可以判断压力没到达数据库，此时必要排查应用端是否存在瓶颈。
导致应用侧瓶颈的题目比力常见的缘故起因有：
1）应用服务器资源瓶颈。好比，应用服务器的CPU满载，应用步伐内存分配不敷等；
2）应用到数据库网络题目。好比，网络时延高，带宽满，存在丢包征象等；
3）应用自身逻辑处理惩罚速率慢；
4）应用设置不优，好比毗连池参数、内存相干设置等设置不妥。
比方，某个客户通过 jmeter 做大并发压测，性能不及业务预期。颠末分析，发现是 jmeter 工具分配的最大可用内存不敷，导致压力没有到达数据库。通过修改如下设置，题目得到了办理。
编辑jmeter.sh文件：

2. set HEAP=-Xms1g -Xmx4g

复制代码

确认压力到达数据库后，再针对相应的瓶颈点举行分析优化。紧张从以下两个方面举行：
1）排查数据库中是否存在性能不优的业务SQL语句，并对性能不优的SQL举行优化。通过如下语句，检察数据库中耗时高的TOP SQL语句，并对那些实行性能不符合预期的SQL语句逐一举行分析与调优。

2. select unique_sql_id,substr(query,1,50) as
3. query ,n_calls,round(total_elapse_time/n_calls/1000,2)
4. avg_time,round(total_elapse_time/1000,2) as total_time from
5. dbe_perf.summary_statement t where  n_calls>10 and avg_time>3  
6. and user_name='root' order by total_time desc;

复制代码

如图6所示，n_calls 表现该SQL语句在数据库中的实行次数，avg_time 为该SQL 语句的均匀实行时间，total_time 为该SQL语句的总耗时。对于均匀实行时间凌驾阈值的SQL语句，重点举行分析与优化。

图6 SQL语句指标及对应数据展示

针对实行性能不优的SQL语句，通过unique_sql_id可以检察该SQL语句的实行详情，资助分析SQL语句的性能瓶颈点。

2. select * from dbe_perf.statement where
3. unique_sql_id=3508314654;

复制代码

如图7所示，该视图记载了SQL语句在数据库的详细实行环境，好比，总实行次数（n_calls）和总耗时（total_elapse_time），便于获取该SQL的总耗时以及均匀耗时。

图7 SQL语句在数据库中的详细实行环境视图

行活动，包罗随机扫描、序次扫描行数、返回的行数、插入/更新/删除的行数以及buffer掷中的页面数等信息。别的，还记载了软剖析（n_soft_parse）、硬剖析（n_hard_parse）的次数，好比SQL大量硬剖析导致的数据库CPU飚高，可以通过该指标举行分析定位。

时间模子，包罗db_time、cpu_time、execution_time、plan_time、data_io_time、net_send_info、net_recv_info、sort_time以及hash_time等指标，有助于判断SQL在数据库中的时间斲丧在哪个阶段。比方，若某环境磁盘性能不佳，则data_io_time的耗时占比就会比力高。

假如必要进一步分析SQL本身的性能题目，好比实行操持是否最优、索引是否最优等性能题目，可以借助SQL的实行操持举行分析。

通过如下方式，可检察SQL的实行操持：

2. explain analyze SELECT c_id     
3. FROM bmsql_customer     
4. WHERE c_w_id = 1 AND c_d_id = 1 AND c_last = 'ABLEABLEABLE'     
5. ORDER BY c_first;

复制代码

联合SQL的实行操持，分析SQL性能的瓶颈点，再举行性能优化，如图8所示。

图8 SQL性能优化过程

2）从体系层面举行操纵体系级和数据库体系级的调优，充实利用呆板的CPU、内存、I/O和网络资源，克制资源辩说，从而提升整个体系查询的吞吐量。

二、体系级性能题目分析

2.1 CPU利用率高

数据库的CPU利用率高，通常是由业务SQL语句引起的。我们可以通过如下方式，获取数据库中斲丧CPU资源高的SQL语句，并对相应的业务SQL语句举行优化。

2. select unique_sql_id,substr(query,1,50) as
3. query ,n_calls,round(total_elapse_time/n_calls/1000,2)
4. avg_time,round(total_elapse_time/1000,2) as
5. total_time,round(cpu_time/1000,2) as cup_time from dbe_perf.statement 
6. t where  n_calls>10 and avg_time>3  and user_name='root'  
7. order by cpu_time desc limit 5;

复制代码

常见的导致CPU资源斲丧高的缘故起因有：
1）SQL语句大量利用了全表扫描，这大概是由索引缺失、索引失效、实行操持不优等因素所导致。
2）SQL语句大量举行硬剖析，通常是由于应用逻辑未利用PBE（Prepare Bind Execute）。
3）SQL语句扫描了大量的元组，好比，分区表分区剪枝失效，扫描了全分区，表中存在大量的死元组，导致扫描了大量无用页面等。

假如CPU利用率高是由非业务SQL语句引起的，可以借助火焰图来举行分析定位。通偏激焰图，可以直观地相识步伐中哪些函数占用了大量的 CPU 时间或资源，而且可以追踪函数调用路径。
GaussDB在内核505版本中内置了火焰图工具，默认每5分钟会主动收罗一次，生存在$GAUSSLOG/gs_flamegraph/{datanode}路径下，详细信息可参考GaussDB产物文档《内置perf工具》章节。
比方，某客户在压测过程中发现数据库服务器的CPU SYS占用率凌驾70%，通过抓取压测期间的火焰图举行分析，如图9所示，发现数据库加载时区文件的线程占比凌驾40%。

图9 某客户压测期间的火焰图

经分析，缘故起因是在高并发频仍创建毗连时，数据库每次建连都必要读取时区文件以获取时区信息，而应用未利用长毗连，导致CPU SYS利用率飙升。

2.2 内存不敷

内存资源，也是影响数据库性能的关键因素之一。在分析内存题目之前，我们先相识一下GaussDB的内存管理机制。
如图10所示，GaussDB的内存管理采取动态内存与静态内存相联合的方式，由参数 max_process_memory控制数据库可用的最大内存。

图10 GaussDB的内存管理机制

此中，静态内存地区紧张用作数据库的共享缓冲区，用于缓存数据页面，由shared_buffers参数控制。动态内存地区，则由数据库根据必要举举措态分配，紧张包罗元数据的缓存、实行操持的缓存、用户建连以及内部线程的内存斲丧等。
内存导致的性能题目，通常分为以下几个方面：
1）共享缓存区不敷，导致SQL的buffer掷中率低。为了检察相应的性能指标，可以借助GaussDB的管控平台大概WDR陈诉。通常环境下，TP数据库的buffer掷中率应该在99%以上。假如数据库的buffer掷中率较低，发起排查数据库的shared_buffers参数设置是否公道（如图11所示）。

图11 数据库的buffer掷中率

2）在GaussDB中，SQL的hash join大概sort算子存在数据落盘操纵，work_mem参数控制可下盘算子可用的物理内存空间。假如work_mem所限定的物理内存不敷，算子运算的数据将被写入暂时表空间，会带来5-10倍的性能降落。为了优化性能，可以检察SQL的实行操持，假如算子存在落盘的环境（如图12所示），可得当调解work_mem参数值。

图12 算子落盘环境

3）数据库动态内存不敷，导致业务实行报错（ERROR：memory is temporarily unavailable ）大概性能不敷。当动态内存不敷时，可以通过如下SQL语句找出内存斲丧高的SQL语句，以便排查是否存在不优的SQL 语句。借助SQL的实行筹分别析，可以查抄是否有不公道的join序次，大概是否存在非须要的排序操纵，从而克制斲丧大量内存。

2. select unique_sql_id,substr(query,1,50) as
3. query ,n_calls,round(total_elapse_time/n_calls/1000,2)
4. avg_time,round(total_elapse_time/1000,2) as
5. total_time,hash_mem_used,sort_mem_used
6. from dbe_perf.statement t
7. where  n_calls>10 and avg_time>3  
8. and user_name='root' order by (hash_mem_used+sort_mem_used) desc;

复制代码

假如必要排查由非业务SQL语句导致的非常的内存斲丧题目，好比内存堆积、内存走漏等，GaussDB提供了丰富的内存相干的监控视图，可以通过下面的视图（如图13所示），检察数据库节点的内存斲丧环境。

图13 GaussDB内存相干的监控视图

基于上面的查询结果，假如dynamic_used_shrctx的占用率高，分析是全局共享动态内存的占用高。可以通过如下SQL语句，检察全局共享动态内存上下文的斲丧环境。

2. select contextname, sum(totalsize)/1024/1024 totalsize,
3. sum(freesize)/1024/1024 freesize, count(*) count from
4. gs_shared_memory_detail group by contextname order by
5. totalsize desc limit 10;

复制代码

假如max_dynamic_memory的占用率高，但是dynamic_used_shrctx的占用率低，那么分析是线程大概会话占用的内存多。可以通过如下SQL语句，查询数据库线程的内存上下文斲丧环境。

2. select contextname, sum(totalsize)/1024/1024 totalsize,
3. sum(freesize)/1024/1024 freesize, count(*) sum from
4. gs_thread_memory_context group by contextname order by sum desc limit 10;

复制代码

查询结果如图14所示，可以看出，当前数据库中内存占用最高的为元数据的缓存(LocalSysCacheShareMemory)。联合图14中的查询结果，排查是否存在不公道的内存占用环境。

图14 数据库线程的内存上下文斲丧环境

2.3 IO瓶颈

通过 iostat 下令，可以检察数据库节点 I/O 的繁忙度和吞吐量，分析是否存在由于I/O 导致的性能瓶颈。如图15所示：

图15 数据库节点 I/O 的繁忙度和吞吐量

重点关注磁盘的读写吞吐量和读写时延。通常环境下，SSD盘的读写时延在2ms以下，单盘带宽在300MB以上。假如磁盘性能存在非常，优先排查硬件是否存在故障，如磁盘存在坏盘、慢盘、RAID卡故障或磁盘读写计谋不准确等；假如磁盘硬件性能正常，而I/O 压力大，可以得当调解数据库I/O 相干的参数，以低落数据的I/O 斲丧，从而优化数据库的团体性能。

I/O 相干的关键参数链接如下：

后端写历程：https://support.huaweicloud.com/distributed-devg-v2-gaussdb/gaussdb-12-1124.html

异步I/O：https://support.huaweicloud.com/distributed-devg-v2-gaussdb/gaussdb-12-1125.html

2.4 网络非常

在传统会集式数据库环境下，应用服务器与数据库服务器通常摆设在同一个机房内，从而确保应用与数据库间的网络开销较小。然而，在云+分布式数据库环境下，应用服务器到数据库服务器的网络链路较长，网络耗时对买卖业务性能至关紧张。在此情境下，我们不但必要关注应用与数据库之间的网络状态（通常应该小于0.2ms），还需思量数据库内部节点之间的网络环境，也会对性能产生较大的影响。

GaussDB要求AZ内网络时延小于0.2ms，AZ间的网络时延小于2ms，region间网络时延小于100ms。可以通过linux的ping下令，排查两个服务器之间的网络时延及丢包等环境，如图16所示。

图16 ping下令，排查两个服务器之间的网络时延及丢包等环境

通过 sar -n DEV 1 下令，检察网络的传输环境。
如图17所示，“rxkB/s”为每秒吸收的千字节数，“txkB/s”为每秒发送的千字节数，紧张关注每个网卡的传输量是否到达传输上限。

图17 sar -n DEV 1 下令，网络传输环境

三、锁壅闭题目分析

数据库锁机制是一种用于管理并发访问的技能。它通过对数据库中的数据举行锁定，来确保在多个用户并发访问数据库时数据的同等性和完备性。

在并发访问的场景下，经常会遇到由于锁辩说导致的性能题目。下面我们看一下在GaussDB中应该怎样定位和分析锁辩说的题目。

假如应用正在运行，可以通过下面的SQL语句，检察当前数据库中正在实行的会话是否存在锁壅闭。

会集式场景：

2. SELECT a.pid as w_pid,a.query as w_query,a.state,d.query as locking_query,d.state as l_state,d.pid as l_pid,d.sessionid as l_sessionid
3. FROM pg_stat_activity AS a
4. JOIN pg_thread_wait_status b ON b.query_id = a.query_id
5. JOIN pg_thread_wait_status c
6. ON c.sessionid = b.block_sessionid and c.node_name=b.node_name
7. JOIN pg_stat_activity d
8. on d.sessionid=c.sessionid
9. ;

复制代码

分布式场景：

2. SELECT a.pid as w_pid,a.query as w_query,a.state as w_state, a.datname, a.usename,d.query as lock_query,d.state as l_state,d.pid as l_pid,d.sessionid as l_sessionid
3. FROM pgxc_stat_activity AS a
4. JOIN pgxc_thread_wait_status b ON b.query_id = a.query_id
5. JOIN pgxc_thread_wait_status c ON c.sessionid = b.block_sessionid and c.node_name=b.node_name
6. JOIN pgxc_stat_activity d
7. on substring(d.global_sessionid,0,instr(d.global_sessionid,'#')) ilike substring(c.global_sessionid,0,instr(c.global_sessionid,'#'))
8. ;

复制代码

查询结果如图18所示，可以获取当前库中存在锁壅闭的SQL语句，同时获取到壅闭它的会话ID、线程ID以及对应的查询。

图18 锁壅闭查询结果展示

要找到并竣事壅闭当前查询的会话，可以利用以下语句。

2. SELECT PG_TERMINATE_BACKEND(pid);

复制代码

假如是汗青的锁壅闭导致的性能题目，可以通过下面语句查询指定时间段内的数据库等候事故。假如发现有大量的acquire lock（包罗transaction ID、relation、tuple）事故，表现该时间段内数据库存在锁壅闭题目。

2. select wait_status,event,count(*) from gs_asp
3. where sample_time>='20241016 18:45:00' and sample_time <='20241016 19:00:00'
4. group by 1,2 order by 3 desc;

复制代码

ASP（Active Session Profile，活泼会话概要信息），通过采样实例中活泼会话的状态信息，以低资源的方式复现已往一段时间内的体系活动，紧张包罗会话根本信息、会话事故、实行的语句、等候事故、会话状态（如active、idle等）、当前正壅闭在哪个事故上、正在等候哪个锁或被哪个会话壅闭。

如图19所示，该时间段数据库占比最高的两个等候事故，一个是等候dn_6004_6005_6006分片返回实行结果，这必要进一步排查该分片上性能瓶颈的缘故起因；别的一个等候事故是acquire lock（relation），表现存在大量的表级锁等候。

图19 特定事故内数据库占比最高的两个等候事故

联合数据库的归一化视图，可以获取数据库中存在锁等候的SQL语句，如图20所示。

图20 获取数据库中存在锁等候的SQL语句

通过该语句的Unique_query_id，得到查询壅闭该语句的query_id。

2. execute direct on datanodes $$select
3. t1.unique_query_id,t1.thread_id,t1.sessionid,t1.wait_status,t1.event,t1.state,
4. t2.query_id as lock_query_id
5. from gs_asp t1,gs_asp t2
6. where t1.block_sessionid=t2.sessionid and  
7. t1.unique_query_id=168353725$$;

复制代码

如图21所示，lock_query_id 为壅闭该SQL语句的query_id。

图21 获取壅闭锁等候SQL语句的query_id

利用上一步查询出来的query_id，并联合gs_asp视图，可以通过如下语句获取该SQL语句的详情。查询结果如图22所示，可以看到，壅闭该语句的也是同一张表的update语句，这表明是由于并发更新同一行数据所导致的锁辩说。

图22 锁等候的SQL语句查询结果

通常环境下，办理并发更新锁辩说题目标办理思绪必要从业务角度出发，审阅存在并发更新同一行的环境是否符合业务场景。假如业务中不存在如许的场景，那应该从业务逻辑大概业务数据上举行优化，以克制并发更新同一行的环境发生。

四、总结

数据库性能调优涉及硬件、操纵体系、数据库、应用等多个层面，因此，在性能调优过程中，必要综合思量各方面因素的影响。本文先容了在GaussDB中分析性能题目时常见的本事和思绪，资助各人认识GaussDB数据库性能诊断常用的工具及利用方法。