一文带你搞懂GaussDB数据库性能调优

择要：数据库性能调优涉及硬件、操作体系、数据库、应用等多个层面，因此，在性能调优过程中，需要综合考虑各方面因素的影响。
  本文分享自华为云社区《【GaussTech技能专栏】GaussDB性能调优》，作者：GaussDB 数据库。

数据库性能调优是一项复杂且体系性的工作，需要综合考虑多方面的因素。因此，调优人员应对体系软件架构、软硬件设置、数据库设置参数、并发控制、查询处理和数据库应用拥有广泛而深刻的明确。
本文旨在剖析GaussDB性能调优的总体思路，探讨体系整体性能问题，以及对锁阻塞问题进行分析和优化。


1. 性能调优思路

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文件：

1. set HEAP=-Xms1g -Xmx4g

复制代码

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

1. select unique_sql_id,substr(query,1,50) as
2. query ,n_calls,round(total_elapse_time/n_calls/1000,2)
3. avg_time,round(total_elapse_time/1000,2) as total_time from
4. dbe_perf.summary_statement t where  n_calls>10 and avg_time>3  
5. 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语句的性能瓶颈点。

1. select * from dbe_perf.statement where 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的实行筹划：

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

复制代码

联合SQL的实行筹划，分析SQL性能的瓶颈点，再进行性能优化，如图8所示。

   

  

图8 SQL性能优化过程

2）从体系层面进行操作体系级和数据库体系级的调优，充分利用机器的CPU、内存、I/O和网络资源，避免资源冲突，从而提升整个体系查询的吞吐量。


2. 体系级性能问题分析

2.1 CPU使用率高

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

1. select unique_sql_id,substr(query,1,50) as
2. query ,n_calls,round(total_elapse_time/n_calls/1000,2)
3. avg_time,round(total_elapse_time/1000,2) as total_time,round(cpu_time/1000,2) as cup_time from
4. dbe_perf.statement t where n_calls>10 and avg_time>3
5. and user_name='root' 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 控制数据库可用的最大内存。此中，静态内存区域紧张用作数据库的共享缓冲区，用于缓存数据页面，由shared_buffers参数控制。动态内存区域，则由数据库根据需要进行动态分配，紧张包罗元数据的缓存、实行筹划的缓存、用户建连以及内部线程的内存斲丧等。

图10 GaussDB的内存管理机制

内存导致的性能问题，通常分为以下几个方面：
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顺序，大概是否存在非须要的排序操作，从而避免斲丧大量内存。

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

复制代码

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

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

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

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

复制代码

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

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

复制代码

查询结果如下图所示，可以看出，当前数据库中内存占用最高的为元数据的缓存(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 相干的关键参数链接如下：
后端写进程：后端写进程_云数据库 GaussDB_华为云
异步I/O：异步I/O_云数据库 GaussDB_华为云


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 下令，网络传输情况

3. 锁阻塞问题分析

数据库锁机制是一种用于管理并发访问的技能。它通过对数据库中的数据进行锁定，来确保在多个用户并发访问数据库时，数据的一致性和完备性。
在并发访问的场景下，常常会遇到因为锁冲突导致的性能问题。下面我们看一下在GaussDB中应该怎样定位和分析锁冲突的问题。
如果应用正在运行，可以通过下面的SQL语句，检察当前数据库中正在实行的会话是否存在锁阻塞。
集中式场景：

1. 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
2. FROM pg_stat_activity AS a
3. JOIN pg_thread_wait_status b ON b.query_id = a.query_id
4. JOIN pg_thread_wait_status c
5. ON c.sessionid = b.block_sessionid and c.node_name=b.node_name
6. JOIN pg_stat_activity d
7. on d.sessionid=c.sessionid
8. ;

复制代码

分布式场景：

1. 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
2. FROM pgxc_stat_activity AS a
3. JOIN pgxc_thread_wait_status b ON b.query_id = a.query_id
4. JOIN pgxc_thread_wait_status c ON c.sessionid = b.block_sessionid and c.node_name=b.node_name
5. JOIN pgxc_stat_activity d
6. on substring(d.global_sessionid,0,instr(d.global_sessionid,'#')) ilike substring(c.global_sessionid,0,instr(c.global_sessionid,'#'))
7. ;

复制代码

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

图18 锁阻塞查询结果展示

要找到并结束阻塞当前查询的会话，可以使用以下语句。

1. SELECT PG_TERMINATE_BACKEND(pid);

复制代码

如果是历史的锁阻塞导致的性能问题，可以通过下面语句查询指定时间段内的数据库等待变乱。如果发现有大量的acquire lock（包罗transaction ID、relation、tuple）变乱，表示该时间段内数据库存在锁阻塞问题。

1. select wait_status,event,count(*) from gs_asp
2. where sample_time>='20241016 18:45:00' and sample_time <='20241016 19:00:00'
3. 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。

1. execute direct on datanodes $$select
2. t1.unique_query_id,t1.thread_id,t1.sessionid,t1.wait_status,t1.event,t1.state,
3. t2.query_id as lock_query_id
4. from gs_asp t1,gs_asp t2
5. where t1.block_sessionid=t2.sessionid and  
6. 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语句查询结果

通常情况下，解决并发更新锁冲突问题的解决思路需要从业务角度出发，审阅存在并发更新同一行的情况是否符合业务场景。如果业务中不存在如许的场景，那应该从业务逻辑大概业务数据上进行优化，以避免并发更新同一行的情况发生。


4. 总结

数据库性能调优涉及硬件、操作体系、数据库、应用等多个层面，因此，在性能调优过程中，需要综合考虑各方面因素的影响。本文介绍了在GaussDB中分析性能问题时常见的本事和思路，资助大家认识GaussDB数据库性能诊断常用的工具及使用方法。
华为开发者空间，汇聚鸿蒙、昇腾、鲲鹏、GaussDB、欧拉等各项根技能的开发资源及工具，致力于为每位开发者提供一台云主机、一套开发工具及云上存储空间，让开发者基于华为根生态创新。点击链接，免费领取您的专属云主机

点击关注，第一时间了解华为云新鲜技能~

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。