CPU使用率异常增长？别慌，试试这个办法......

     2024年4月2日上午10点00分到10点20分，某电力客户某系统数据库CPU使用出现异常增长，平均达到了90%以上，峰值达到了100%；会话数也从10点20分开始持续升高，持续到10点28分会话数超过数据库最大连接数8000，业务会话受影响；10点50分数据库部分空闲会话kill，数据库可以连接，此时数据库会话数降低到6000左右；最终13点09分左右对数据库进行了重启操作，数据库连接数稳定在2000左右，CPU使用率也维持到50%左右，业务彻底恢复正常。

环境描述

openGuass 3.0.3版本主备模式架构。

故障处理

故障现象

    从上图可以看到：

关于数据库主机CPU使用率100%的性能隐患：

1、性能下降：当CPU跑满时，服务器的处理能力已经达到极限，因此处理请求的速度变慢，响应时间延迟增加，这将直接影响应用程序不稳定。

2、其他资源受限：当CPU负载高时，服务器上的其他资源，如内存、磁盘和网络带宽，可能会受到限制，从而影响整体性能。

zCloud数据库云管平台不仅可以图形化展示CPU使用趋势、内存消耗数等变化趋势，还可以展示TopSQL 详情，各会话连接消耗资源占比等。

     通过zCloud数据库云管平台展示的Top SQL界面可以看到：

     数据库主机CPU使用率100%的时间段的等待事件：10点00分到10点20分时间段内数据库主要等待事件分类是on cpu、sort-fetch tuple和hashAgg-build hash，上述三个等待事件占据了数据库活动时间的63%以上，该等待事件主要由于出现大量top sql，大量的逻辑读 hash join 导致。

    期间的主要TOPSQL优化分析如下：（后续TOPSQL重点优化，降低主机的CPU负载）

截取排名前面的TOPSQL语句全表扫描执行计划：

1、全表扫描业务表：

问题：

该SQL语句需要全表扫描，该表达到3GB容量，SQL的语句where语句并无合适的条件，无法使用索引去过滤数据，高并发的SQL全表扫描会消耗的较多的系统资源（CPU、IO等资源）。

2、全表扫描dbe_perf.statement视图

系统内一直有对dbe_perf.statement视图去做周期性的查询，获取相关的SQL语句用于监控，该SQL执行时间较长，单次需要20秒以上：

10点开始CPU使用率持续较高，并且峰值达到了100%，会话数的进一步增加，进而超过会话数最大值8000，业务受到影响。

10点18分到10点28分的会话数变化趋势：

instr_unique_sql_count参数官方描述：

参数说明：控制系统中unique sql信息实时收集功能。配置为0表示不启用unique sql信息收集功能。

该值由大变小将会清空系统中原有的数据重新统计（备机不支持此能力）；从小变大不受影响。

  当系统中产生的unique sql条目数量（dbe_perf.statement/dbe_perf.summary_statement统计）大于instr_unique_sql_count时，若开启了unique sql自动淘汰（默认不开启，参数enable_auto_clean_unique_sql控制，默认该参数为off），则系统会按unique sql的更新时间由远到近自动淘汰一定比例的条目，使得新产生的unique sql信息可以继续被统计。若没有开启自动淘汰，则系统产生的新的unique sql信息将不再被统计。

默认值：100

取值范围：整型，0~2147483647

注意：

     在开启自动淘汰的情况下，如果该值设置的较小，可能会导致系统频繁的进行自动淘汰，有可能会影响数据库系统性能，所以实际场景中建议不要将该值设置的过小，建议值为200000。

    在开启自动淘汰的情况下，如果该值设置的较大（例如38347922），清理过程中可能会引发大内存问题而无法清理。

    关于unique SQL自动淘汰enable_auto_clean_unique_sql参数：

    由于开启unique SQL自动淘汰可能会引起wdr无法生成，因此该参数默认值也是off，默认不开启unique SQL自动淘汰任务。

enable_auto_clean_unique_sql参数官方描述：

参数说明：当系统中产生的unique sql条目数量大于等于instr_unique_sql_count时，是否启用unique sql自动淘汰功能。

取值范围：布尔型

默认值：off

注意：

由于快照有部分信息是来源于unique sql，所以开启自动淘汰的情况下，在生成wdr报告时，如果选择的起始快照和终止快照跨过了淘汰发生的时间，会导致无法生成wdr报告。

track_activity_query_size参数官方描述：

参数说明：设置用于跟踪每一个活动会话当前正在执行命令的字节数。

参数类型：POSTMASTER

取值范围：整型，100～102400

该参数控制记录单个uniqueSQL的最大行长，默认值是1024，该设置会直接对每个SQL分配这么大的内存区域，即使SQL文本远远小于该参数，也会分配这么大的内存区域。该参数一般不建议调整的特别大，避免较多的内存消耗，对于大部分系统1024已经足够，如果需要记录更长的SQL语句到相关视图，可以做一些变更调整。

1、观察dbe_perf.statement查询耗时随着内存中unique sql条数增加变化曲线。测试过程中外部压力不变，通过构造空表的不同列的查询组合来模拟unique sql。CPU使用率为60%左右。

      2. dbe_perf.statement 查询时间随着内存中unique sql数量越来越大，查询时间出现明显上涨。

3. 参数track_activity_query_size 由51200 调整到1024 后， 相同测试条件下，dbe_perf.statement查询耗时如下。可以看到参数track_activity_query_size 调小后，查询耗时比较稳定，且数据库中未出现 【UniqueSQL】相关报错。

原理解释：

    调大instr_unique_sql_count 后，内存的hash 表又可以写入，业务SQL 执行过程中需要向hashtabl写入数据，需要以EXCLUSIVE 模式申请UniqueSQLMappingLock ，会与查询dbe_perf.statement语句产生锁竞争。

   下图是调整后业务时延随时间的变化图，可以看到业务有周期性的长时延，这与dbe_perf.statement的周期性查询有关。当查询dbe_perf.statement时，会导致业务SQL执行时间显著上升。这与之前故障环境修改参数后产生大量超时报错现象上是一致的。

一旦内存中的unique sql 重新达到设置的最大值，业务SQL 就不会再去更新内存中hash 表，也就与查询dbe_perf.statement 不会有锁冲突，业务查询时延恢复到正常值。从下图可以看出unique sql达到最大值后，不再受dbe_perf.statement查询的影响。

      此次问题的根源为大量TOPSQL导致主机CPU使用率偏高，并且最终达到100%（特别大量全表扫描SQL），在CPU不足情况下数据库中SQL在执行过程中持有获取的锁时间将会加剧，业务SQL越跑越慢，连接数也会进一步的增加，系统出现大量的UniqueSQLMappingLock等待，并且最终超过数据库最大连接数8000，从而影响业务正常运行。

由于数据库目前的unique SQL数量已经达到了instr_unique_sql_count参数，不会记录新的SQL语句到数据库中。数据库管理员考虑到CPU使用率100%的风险，在线修改instr_unique_sql_count参数从250000为300000，以便可以收集更多的SQL语句来优化TOPSQL和降低CPU使用率，该参数之前也在线调整过并无异常，故障后也多次模拟在线修改该参数也并未触发异常，并且官方给出的文档中也并没有提及该参数修改可能会遇到相关风险和案例。

     后续关于该参数instr_unique_sql_count修改和等待事件UniqueSQLMappingLock的关系模拟之后发现：

1、如果CPU系统本身负载较高，业务程序并发较大；

instr_unique_sql_count设置的较大，系统捕捉的uniqueSQL也就更多，并且track_activity_query_size设置的也比较大（本系统设置的102400），则instr_unique_sql_count*track_activity_query_size就是uniqueSQL部分的内存消耗为23.8GB（102400*250000/(1024*1024*1024)）。

2、如果数据库在线调整instr_unique_sql_count参数期间，还有相关的UniqueSQLMappingLock资源请求发生（例如周期性的查询dbe_perf.statement视图获取相关SQL进行监控，该视图查询会请求UniqueSQLMappingLock资源），则可能触发和加剧更严重的等待竞争，例如出现严重的UniqueSQLMappingLock等待、on CPU等待。

总体分析： 基于目前的信息判断本次会话数超过最大连接数的主要原因还是主机CPU使用率达到100%诱因，此时导致数据库出现了较多的ON CPU、sort-fetch tuple和hashAgg-build hash等待；系统内的instr_unique_sql_count参数是250000，track_activity_query_size之前被调整为102400，这个设置导致uniqueSQL区域内存非常大，达到了23GB，系统内还有周期性的查询dbe_perf.statement视图（该视图就是uniqueSQL区域对应的视图，由于uniqueSQL达到了23GB，此时查询该视图就导致特别消耗资源，需要时间接近20秒以上还查询不出来）数据获取SQL语句来监控，如果此时调整instr_unique_sql_count参数，则可能进一步加剧系统负载，出现更为严重的UniqueSQLMappingLock和on cpu等待，进而达到数据库最大连接数8000，严重影响业务运行。

     降低数据库的CPU负载优化业务SQL，确保CPU不会长时间处于高压力状态。当CPU承受过大压力时，数据库的整体性能可能会受到影响，包括但不限于数据块的访问、SQL语句的处理、WAL缓冲区的写入等关键操作的延迟。

     instr_unique_sql_count变更规范：高CPU使用率场景下也不建议在线修改instr_unique_sql_count参数，避免在线修改参数触发openGauss数据库相关版本的未知缺陷或者不足（当前openGauss数据库目前版本是3.0.3版本，目前社区最新LTS版本是5.0.0版本）。

停机修改track_activity_query_size参数从102400降低到4096，为原来的4%，此时uniqueSQL区域将大幅度降低，也避免dbe_perf.statement视图查询时候出现的性能瓶颈。

附：数据库相关名词解释

1、uniqueSQLMappingLock等待：uniqueSQLMapping等待是用户保护数据库的uniquesql hash table，openGauss数据库中每个SQL语句会对应一个uniquesql，关于uniquesql解释如下：

用户执行SQL语句时，每一个SQL语句文本都会进入解析器（Parser），生成“解析树”（parse tree）。遍历解析树中各个结点，忽略其中的常数值，以一定的算法结合树中的各结点，计算出来一个整数值，用来唯一标识这一类SQL，这个整数值被称为Unique SQL ID，Unique SQL ID相同的SQL语句属于同一个“Unique SQL”。

例如，用户先后输入如下两条SQL语句：

这两条SQL语句除了过滤条件的常数值不同，其他地方都相同，由此生成的解析树的拓扑结构完全相同，故Unique SQL ID也相同。因此两条语句属于如下同一个Unique SQL：

GaussDB、Opengauss内核会对所有上面形式的SQL语句汇总统计信息，通过视图呈现给用户。通过这种方式，可以排除一些无关的常量值的干扰，获得某一类SQL语句的统计数据，为性能分析和问题定位提供数值依据。

2、instr_unique_sql_count参数：该参数控制unique SQL数量最大值，如果超过该数值（不开启SQL自动淘汰），则数据库不再记录新的SQL语句。

3、enable_auto_clean_unique_sql参数：该参数控制unique SQL自动淘汰特性是否开启，由于开启unique SQL自动淘汰可能会引起wdr无法生成，因此该参数默认值也是off，默认不开启unique SQL自动淘汰任务。

4、track_activity_query_size参数：该参数控制记录单个uniqueSQL的最大行长，默认值是1024，该设置会直接对每个SQL分配这么大的内存区域，即使SQL文本远远小于该参数，也会分配这么大的内存区域，该参数一般不建议调整的特别大，避免较多的内存消耗，对于大部分系统1024已经足够，如果需要记录更长的SQL语句到相关视图，可以做一些变更调整。

如果uniqueSQL超过track_activity_query_size该值，会导致数据库很多视图例如pg_stat_activity、db_perf.statement（uniqueSQL对应的视图）记录SQL出现截断，例如如下SQL测试用例

修改track_activity_query_size为100（该参数最小只能设置为100）

查看uniqueSQL对应的视图：create table对应的语句出现截断

点个「在看」 你最好看