GaussDB系统调优与性能诊断（二）

原创已于 2024-12-12 19:22:33 修改 · 1.1k 阅读

24 ·

CC 4.0 BY-SA版权

文章标签：

#gaussdb #linux #服务器

于 2024-11-24 22:35:18 首次发布

GaussDB基础篇专栏收录该内容

18 篇文章

订阅专栏

GaussDB系统调优与性能诊断（二）

常见性能问题诊断

常见性能问题诊断

CPU占用高

通过top命令和sar命令，确认CPU占用率高的进程是gaussdb进程。

top
sar -u -f /var/log/sa/sa12

通过系统视图或WDR报告确认CPU占用高是否由业务SQL大量顺序扫描、或硬解析造成，并定位到具体SQL。

--（1）通过归一化的视图分析CPU占用高的SQL语句
select * from dbe_perf.statement where user_name='xxx' order by cpu_time desc limit 5;
select * from dbe_perf.summary_statement where user_name='xxx' order by cpu_time desc limit 5;
--重点关注plan_time/n_hard_parse（硬解析占比）和n_tuples_retunred（顺序扫描的行数）

--（2）查询对应时间段占用SQL高的SQL语句
select * from dbe_perf.get_global_full_sql_by_timestamp('2024-10-31 23:30:00','2024-11-01 00:30:00') 
order by cpu_time desc limit 20;

--（3）通过问题时间段的WDR报告中的SQL ordered by CPU Time来定位占用CPU的Top SQL。

分析定位到的SQL涉及的表结构信息，并按需创建索引。

\d+ bmsql_order_line   --查询表结构

--必要时创建索引
create index idx_wdo on bmsql_order_line(ol_w_id, ol_d_id, ol_o_id);

如果是非SQL语句导致的CPU占用高，可以使用火焰图分析。

#（1）使用top命令查看消耗CPU高的进程号
top

#（2）使用perf命令查看CPU正在执行的函数名以及调用栈
# 通常perf执行频率为每秒99次，如果99次都返回同一个函数名，就说明可能存在性能问题
./perf record -e cpu-lock -g -p 125396 -- sleep 60

#（3）使用perf script工具解析perf.data
./perf script -i perf.data &> perf.unfold

#（4）折叠perf.unfold中的符号
./stackcollapse-perf.pl perf.unfold &> perf.folded

#（5）生成svg图
./flamegraph.pl perf.folded > cn.svg

#（6）分析svg图
#- y轴表示调用栈，每一层都是一个函数。调用栈越深，火焰就越高，顶部就是正在执行的函数，下方都是它的父函数；
#- x轴表示抽样数，如果一个函数在x轴占据的宽度越宽，就表示它被抽到的次数多，即执行的时间长。

IO占用高

IO资源占用高表现为iostat命令输出中%util满、或者r_await较高（大于3ms）、或者w_await较高（大于3ms）。

通过iotop命令确认IO占用高的进程是否为gaussdb或者对应线程。

WALwriter和pagewriter为数据库内部写WAL日志和刷脏数据的线程，通常是数据库正常的IO消耗。

如果是TPLworker线程消耗的IO读写量异常，代表是用户SQL消耗IO多，其中TID表示内核线程ID。

yum install -y iotop
iotop

通过pg_thread_wait_status、dbe_perf.local_active_session、gs_asp等系统视图查询数据等待事件，会有大量的wait io、DataFileRead、DataFileWrite等待事件。

--查询实时的等待事件
select wait_status,wait_event,count(*) from pg_thread_wait_status 
group by wait_status,wait_event order by 3 desc;

--查询历史等待事件：dbe_perf.local_active_session查询两小时内等待事件，gs_asp查询两天内等待事件
select wait_status,event,count(*) from dbe_perf.local_active_session
where sample_time between '2024-11-07 13:00:00' and '2024-11-07 15:00:00'
group by 1,2 order by 3 desc;

通过性能视图获取IO占用高的业务SQL。

可查询dbe_perf.statement或dbe_perf.summary_statement内的n_blocks_fetched和n_blocks_hit字段。通常导致读IO高的情况，两个字段的差值会比较大，两者的差值表示物理读的次数。

select user_name,unique_sql_id,query,n_blocks_fetched,n_blocks_hit,data_io_time 
from dbe_perf.statement order by (n_blocks_fetched-n_blocks_hit) desc limit 10;

🐱 物理读高的常见原因：

SQL语句不优，需扫扫描大量数据；
VACUUM不及时，表中有大量死元组，需要扫描大量无效页面；
Buffer Pool不足，频发触发buffer淘汰。

🐶 IO异常的常见排查思路：

自上而下：异常的SQL物理读（n_blocks_fetched-n_blocks_hit）、异常的SQL IO耗时（data_io_time）、异常的等待事件（DataFileRead/DataFileWrite/wait wal sync）等。
自下而上：IO读写量、IO读写延时、dmesg硬件故障（RAID卡异常/磁盘损坏等）、RAID卡读写策略、IO调度算法等。

动态内存不足

动态内存不足通常是SQL语句不优、内存参数配置不合理、应用并发连接数太高等原因导致。

排查数据库内存参数设置是否合理。

show max_process_memory;
show shared_buffers;

通过性能视图查看占用内存高的Top SQL语句。

select substr(query,1,50) as query, 
sum(sort_mem_used) as sort_mem, 
sum(hash_mem_used) as hash_mem 
from dbe_perf.statement group by 1 order by (sort_mem+hash_mem) desc;

查询当前数据库中应用并发连接数是否合理。

select count(*) from pg_stat_activity where username='xxx';

排查数据库异常的内存消耗，例如内存泄漏、异常的内存占用未正常释放。

select contextname,sum(totalsize)/1024/1024 as sum,
sum(usedsize)/1024/1024 as used, count(*) count
from dbe_perf.shared_memory_detail group by contextname order by used desc limit 10;

临时文件落盘

业务SQL存在临时文件落盘，语句执行性能差。

分析SQL执行计划，排序的算子耗时比较高，排序操作存在落盘的情况（关键字Disk）。

tpcc=> explain analyze select * from bmsql_stock order by s_ytd;
 id |           operation            |  A-time   |  A-rows  |  E-rows  | Peak Memory | A-width | E-width |         E-costs          
----+--------------------------------+-----------+----------+----------+-------------+---------+---------+--------------------------
  1 | ->  Sort                       | 49057.134 | 15000000 | 15015997 | 66567KB     |         |     313 | 6432614.082..6470154.074
  2 |    ->  Seq Scan on bmsql_stock | 20456.434 | 15000000 | 15015997 | 34KB        |         |     313 | 0.000..998685.970
(2 rows)

      Memory Information (identified by plan id)      
------------------------------------------------------
   1 --Sort
         Sort Method: external merge  Disk: 4741224kB
(2 rows)

        ====== Query Summary =====        
------------------------------------------
 Datanode executor start time: 0.382 ms
 Datanode executor run time: 50371.668 ms
 Datanode executor end time: 324.117 ms
 Planner runtime: 0.255 ms
 Query Id: 1688849871003724
 Total runtime: 50696.214 ms

适当调整work_mem参数，重新查看SQL执行计划，排序操作全部在内存中进行，SQL执行效率有明显提升（关键字Memory）。

tpcc=> explain analyze select * from bmsql_stock where s_w_id=1 order by s_ytd;
 id |                        operation                        |  A-time  | A-rows | E-rows | Peak Memory | A-width | E-width |        E-costs         
----+---------------------------------------------------------+----------+--------+--------+-------------+---------+---------+------------------------
  1 | ->  Sort                                                | 2629.191 | 100000 | 102276 | 55906KB     |         |     313 | 185264.792..185520.482
  2 |    ->  Index Scan using bmsql_stock_pkey on bmsql_stock | 2430.263 | 100000 | 102276 | 86KB        |         |     313 | 0.000..176754.351
(2 rows)

     Predicate Information (identified by plan id)      
--------------------------------------------------------
   2 --Index Scan using bmsql_stock_pkey on bmsql_stock
         Index Cond: (s_w_id = 1)
(2 rows)

   Memory Information (identified by plan id)    
-------------------------------------------------
   1 --Sort
         Sort Method: quicksort  Memory: 55895kB
(2 rows)

       ====== Query Summary =====        
-----------------------------------------
 Datanode executor start time: 0.080 ms
 Datanode executor run time: 2637.843 ms
 Datanode executor end time: 4.517 ms
 Planner runtime: 9.371 ms
 Query Id: 1688849871003304
 Total runtime: 2643.306 ms

网络异常

网络异常通常有几种场景：

网络不通：通常发生在业务刚上线时，表现为数据库无压力、业务服务器无法连接到数据库，通过ping目标服务器无响应。
网络延时高：需要关注压测前和压测过程中的网络延时情况，同时关注应用到数据库服务器之间、以及数据库节点之间的网络时延。通过ping目标服务器来观察网络延时。
网络带宽满：通常存在大并发数据传输场景，可以通过sar命令或者运维管理平台监控数据库节点网络传输速率，排查数据库节点的网络带宽是否达到瓶颈。

[omm@gaussdb001 ~]$ sar -n DEV 1 10
Linux 4.19.90-25.16.v2101.ky10.x86_64 (gaussdb001) 	11/20/2024 	_x86_64_	(8 CPU)

02:00:21 PM     IFACE   rxpck/s   txpck/s    rxkB/s    txkB/s   rxcmp/s   txcmp/s  rxmcst/s   %ifutil
02:00:22 PM        lo     35.00     35.00     10.21     10.21      0.00      0.00      0.00      0.00
02:00:22 PM      ens4     11.00     11.00      0.97      0.89      0.00      0.00      0.00      0.00

网络丢包：通过 tcpdump 和 Wireshark 等工具分析是否存在网络丢包现象。

注：GaussDB网络延时要求数据库AZ内网络延时小于0.2ms，AZ间小于2ms，Region间小于100ms。

并发问题

并发问题通常是并发更新产生的锁等待，导致业务延时变大、TPS下降、或者线程池满等问题。

查看数据库各节点实时的等待事件：

select wait_status,wait_event,count(*) from pg_thread_wait_status 
group by wait_status,wait_event order by 3 desc;

查看数据库各节点2小时内的等待事件：

select wait_status,event,count(*) from dbe_perf.local_active_session 
where sample_time between '2024-11-07 13:00:00' and '2024-11-07 15:00:00'
group by wait_status,event order by 3 desc;

查询数据库各节点2天内的等待事件：

select wait_status,event,count(*) from gs_asp 
where sample_time between '2024-11-07 13:00:00' and '2024-11-07 15:00:00'
group by wait_status,event order by 3 desc;

锁阻塞

查询实时的锁等待：

select a.query_id, a.query, b.wait_status, b.wait_event, 
b.block_sessionid, c.pid block_pid, c.query as block_query
from pg_stat_activity a, pg_thread_wait_status b, pg_stat_activity c
where a.query_id=b.query_id 
and b.block_sessionid=c.sessionid 
and a.state != 'idle' and a.datname='tpcc';

Kill阻塞当前语句的会话：

select pg_terminate_backend(block_pid);

查询日志报错

# 应用端锁超时报错
Error: Lock wait timeout

# DN节点日志信息
grep 'deadlock detected' postgresql-yyyy-mm-dd-******.log  #死锁
grep 'Lock wait timeout' postgresql-yyyy-mm-dd-******.log  #锁超时

查询历史锁等待信息

--查询两小时以内的语句
select * from dbe_perf.local_active_session where thread_id=123456789 
and 
sample_time between '2024-11-07 13:00:00' and '2024-11-07 15:00:00';

--查询超过两小时的语句
select * from gs_asp where thread_id=123456789 
and sample_time between '2024-11-07 13:00:00' and '2024-11-07 15:00:00';

长事务

长事务是指执行时间长的事务。长事务会导致主备复制延迟大、表空间膨胀、阻塞xlog回收等问题。

长事务的可能原因包括大量的锁竞争、SQL执行耗时长、执行结束的事务未提交也没有回滚等。

查询执行时间超过1分钟的长事务：

select current_timestamp - query_start as runtime,pid,sessionid,substr(query,0,100) as query
from pg_stat_activity where state != 'idle' and 
datname in ('tpcc') and usename in ('benchmarksql')
and extract(epoch from current_timestamp-xact_start)/60 > 1 order by 1 desc;

如果是锁阻塞导致的长事务，获取阻塞当前长事务的SQL：

select pid,sessionid,substr(query,0,100) from pg_stat_activity
where sessionid in (
  select sessionid from pg_thread_wait_status 
  where sessionid in (
    select block_sessionid from pg_thread_wait_status 
    where sessionid in (
      select sessionid from pg_stat_activity where state != 'idle' and datname in ('tpcc') and usename in ('benchmarksql')
      and extract(epoch from current_timestamp-xact_start)/60 > 1 
    )
  )
);

结束造成长事务阻塞的会话：

select pg_terminate_session(pid,sessionid);

以上。