ANBOB™

系统资源的最大利用是系统优化的目标，但是过度”优化”系统的容错性就相对较差，如果出现丝毫的卡顿就会“雪崩”，前段时间分析的一个案例，号称是亚洲最好的一套HP小机配置，有Oracle原厂操刀调试的一套Oracle 12c R1环境，不过确实效率很高，突然有一天因RAC 节点之间的private network 高latency导致频繁的 gc 相关等待，从而导致性能问题。

问题前半小时node2  AWR

Instance Efficiency Percentages (Target 100%)

Top 10 Foreground Events by Total Wait Time

15分钟后node2  AWR

Load Profile

Instance Efficiency Percentages (Target 100%)

Top 10 Foreground Events by Total Wait Time

Host CPU

Instance CPU

看问题时刻另一节点node1的AWR TOP EVENT

Top 10 Foreground Events by Total Wait Time

Host CPU

Instance CPU

当时互相ping私网已经显示出不正常的network latency，正常应该是小于1ms. 两个节点的CPU看使用率并不高，当然需要看osw更精确的时间点。

分析方法

1， 检查两节点系统资源利用率，如是否因为CPU使用率过导致lms进程慢

2，AWRDD对比

3，检查网络延迟和瓶颈

$ netstat -s
$ ss -s
$ sar -n DEV 1 5
$ sar -n TCP 1 5
# ip link show – check network interface status (up /down)
# ethtool eth0 – ethtool is used for querying settings of an ethernet device like network speed, packet drop info etc

other tools iftop/nload  speedometer  nethogs tcptrack -i eth0

4,  ASH和OSW分钟或秒级分析查找问题最初的表现

5,  应用跨实例部署

等等

看RAC 部分AWR

NODE1

RAC Statistics

Global Cache Load Profile

Global Cache and Enqueue Services – Workload Characteristics

Global Cache and Enqueue Services – Messaging Statistics

Interconnect Ping Latency Stats

Node2

RAC Statistics

Global Cache Load Profile

Global Cache and Enqueue Services – Workload Characteristics

Global Cache and Enqueue Services – Messaging Statistics

这个问题和几年前Troubleshooting high “gc current grant 2-way” and “gc cr grant 2-way” 案例(二) 有些相似之处,Estd Interconnect traffic (KB) 我们可以看到几乎达到了千兆的上限141,488KB/s。

ksxp 层决定 IPC 协议如何执行的可以查询x$ksxpclient，dba_hist_ic_client_stats 视图显示了相同的信息，并且该视图确实收集了所有实例的统计信息。

select
name,
trunc(bytes_sent/1024/1024) as mb_sent,
trunc(bytes_rcv/1024/1024) as mb_recv,
trunc((bytes_sent+bytes_rcv)/1024/1024) as mb_total,
to_char(round(100*(((bytes_sent+bytes_rcv)/1024/1024)/
sum((bytes_sent+bytes_rcv)/1024/1024) over ()),2),'990.00') as total_perc
from
sys.x$ksxpclient
order by
total_perc desc;

Poorly sized UDP receive (rx) buffer sizes / UDP buffer socket overflows

‘netstat -s’ or ‘netstat -su’ and look for either “udpInOverflowsudpInOverflows”, “packet receive errors”, “fragments dropped” or “outgoing packet drop” depending on the platform.
If there is a significant increases in “outgoing packets dropped” in the TCP section of netstat -s output on the nodes remote to where the workload is running, increase the wmem_default and wmem_max both to 4MB (Linux) could resolve the issue.

Poor interconnect performance and high cpu utilization. `netstat -s` reports packet reassembly failures

Large UDP datagrams may be fragmented and sent in multiple frames based on Maximum Transmission Unit (MTU) size.
`netstat -s` reports a large number of Internet Protocol (IP) “reassembles failed” and “fragments dropped after timeout” in the “IP Statistics” section of the output on the receiving node.

`netstat -s` IP stat counters:
3104582 fragments dropped after timeout
34550600 reassemblies required
8961342 packets reassembled ok
3104582 packet reassembles failed.

LINUX 6
netstat -s |grep reass
-sleep 5sec
netstat -s |grep reass

LINUX 7 
nstat -az|grep IpReasmFails
-sleep 5sec
nstat -az|grep IpReasmFails

Action: Increase fragment reassembly buffers, allocating more space for reassembly. Increase the time to reassemble packet fragment., increase udp receive buffers to accommodate network processing latencies that aggravate reassembly failures and identify CPU utilization that negatively impacts network stack processing. Note, increase the following settings will also increase the memory usage.

On LINUX:
To modify reassembly buffer space, change the following thresholds:

/proc/sys/net/ipv4/ipfrag_low_thresh (default = 196608)
/proc/sys/net/ipv4/ipfrag_high_thresh (default = 262144)

To modify packet fragment reassembly times, modify:

/proc/sys/net/ipv4/ipfrag_time (default = 30)

ADDM report

Finding 2: Interconnect Latency
Impact is 421.58 active sessions, 61.59% of total activity.
———————————————————–
Higher than expected latency of the cluster interconnect was responsible for significant database time on this instance.
The instance was consuming 414762 kilo bits per second of interconnect bandwidth.
65% of this interconnect bandwidth was used for global cache messaging, 0% for parallel query messaging and 33% for database lock management.
The average latency for 8K interconnect messages was 4708 microseconds. The instance is using the private interconnect device “lan901:801” with IP address 169.254.141.243 and source “33”.

Finding 5: Global Cache Congestion
Impact is 80.49 active sessions, 11.76% of total activity.
———————————————————-
Global Cache Service Processes (LMSn) in other instances were not processing requests fast enough.

Recommendation 1: Database Configuration Estimated benefit is 80.49 active sessions, 11.76% of total activity.
———————————————————————
Action
Increase throughput of the Global Cache Service (LMSn) processes.
Increase the number of Global Cache Service processes by increasing the value of the parameter “gcs_server_processes”. Alternatively, if the
host is CPU bound consider increasing the OS priority of the Global Cache Service processes.
Rationale
The value of parameter “gcs_server_processes” was “20” during theanalysis period.

简单记录一下问题现象， 这个case后来看是因为应用因为连接数满自动failover到了其它实例，跨实例的定时调度大量insert，加剧GC，当然期中还引起了latch  undo争用和看到index root split, 本篇仅记录一次interconnect network网络带宽耗尽后的gc现象。

打赏

微信扫一扫，打赏作者吧～