一文带你搞懂 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 相干的关键参数链接如下：                                 后端写进程：     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 下令，网络传输情况
      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、欧拉等各项根技术的开辟资源及工具，致力于为每位开辟者提供一台云主机、一套开辟工具及云上存储空间，让开辟者基于华为根生态创新。     点击链接     ，免费领取您的专属云主机                                       点击关注，第一时间相识华为云新鲜技术～                  

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