一次因网络引起的诡异GC问题,DBA该怎么做?

版权声明:本文为Buddy Yuan原创文章,转载请注明出处。原文地址:一次因网络引起的诡异GC问题,DBA该怎么做?

我们在日常工作中,就像西天取经的僧人,总是会遇到各式各样的“妖怪”。这些“妖怪”总是一个又一个的阻挡在我们面前,你必须想办法击败它们。
听说小A同学最近遇到了一个很妖的问题,就这个问题我们来采访一下小A同学。
小B:你觉得RAC GC问题一般在什么情况下会产生?
小A:这个问题嘛其实很简单,我们要先从Oracle RAC的机制说起,RAC是一种共享磁盘的体系结构,多个服务器上的实例会同时打开数据库,并缓存磁盘中的数据。而当在一个节点上执行SQL,需要请求的buffer在remote实例上时,就会使用心跳进行传输。此时在本地节点上可能就会观察到GC类的等待事件。一般大量GC问题都是应用交叉访问引起的。
小B:那这一次的GC问题是应用交叉访问导致的吗?
小A:这次并不是,因为很多时候我们观察到系统权限类的SQL语句也在等待大规模的gc buffer busy acquire。
小B:系统语句?
小A:对,就是下面这个语句,他也产生了很多的GC。

select event,p1,p2,p3 from v$session where sql_id='05uqdabhzncdc'
EVENT P1 P2 P3
---------------------------------------------------------------- ---------- ---------- ----------
gc buffer busy acquire 1 46842 1
gc buffer busy acquire 1 46842 1
gc buffer busy acquire 1 46842 1
gc buffer busy acquire 1 46842 1
gc buffer busy acquire 1 46842 1
gc buffer busy acquire 1 46842 1
gc cr request 1 46842 1
gc buffer busy acquire 1 46842 1

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 05uqdabhzncdc, child number 1
-------------------------------------
select role# from defrole$ d,user$ u where d.user#=:1 and
u.user#=d.user# and u.defrole=2 union select privilege# from sysauth$
s,user$ u where (grantee#=:1 or grantee#=1) and privilege#>0 and not
exists (select null from defrole$ where user#=:1 and
role#=s.privilege#) and u.user#=:1 and u.defrole=3
Plan hash value: 552533229
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
| 1 | SORT UNIQUE | | 2 | 30 | 7 (29)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | NESTED LOOPS | | 1 | 14 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS CLUSTER| USER$ | 1 | 7 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | I_DEFROLE1 | 1 | 7 | 1 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 16 | 2 (0)| 00:00:01 |
|* 8 | TABLE ACCESS CLUSTER| USER$ | 1 | 7 | 1 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | I_USER# | 1 | | 1 (0)| 00:00:01 |
| 10 | INLIST ITERATOR | | | | | |
|* 11 | INDEX RANGE SCAN | I_SYSAUTH1 | 1 | 9 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | I_DEFROLE1 | 1 | 7 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("U"."DEFROLE"=2)
5 - access("U"."USER#"=:1)
6 - access("D"."USER#"=:1)
8 - filter("U"."DEFROLE"=3)
9 - access("U"."USER#"=:1)
11 - access((("GRANTEE#"=:1 OR "GRANTEE#"=1)) AND "PRIVILEGE#">0)
filter( IS NULL)
12 - access("USER#"=:1 AND "ROLE#"=:B1)

小B:这个语句感觉没什么问题啊,这是查数据字典权限的,执行计划很好,关键表走的UNIQUE SCAN,应该很快就返回结果的。
小A:是的,正常情况都是秒出结果的,并不会产生GC等待。但是我们这个有点小异常,经常看到100-200个GC等待事件。
小B:那究竟是什么问题呢?
小A:我们先来看AWR报告吧,从11.2.0.4我们就可以通过来Interconnect Ping Latency Stats查看网络延迟类的问题了。

你注意看,这是2节点的AWR报告。报告里ping 1和ping 3节点的延迟非常的高。这里分别是做了500字节和8KB的ping,平均延迟都是30几ms和10几ms。我们在看oswatch,从节点1到节点2的traceroute可以看到正常时间点是0.1ms,慢的时候足足5ms。

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
zzz ***Fri Sep 6 10:00:10 CST 2019
traceroute to 192.168.187.129 (192.168.187.129), 30 hops max, 60 byte packets
1 sid1-priv (192.168.187.129) 0.126 ms 0.103 ms 0.100 ms
traceroute to 192.168.187.130 (192.168.187.130), 30 hops max, 60 byte packets
1 sid2-priv (192.168.187.130) 5.015 ms 5.096 ms 5.074 ms <<<<<<<<<<<<<<<<<<<<<<<<<<<
traceroute to 192.168.187.131 (192.168.187.131), 30 hops max, 60 byte packets
1 sid3-priv (192.168.187.131) 1.286 ms 1.176 ms 1.156 ms
zzz ***Fri Sep 6 10:00:41 CST 2019
traceroute to 192.168.187.129 (192.168.187.129), 30 hops max, 60 byte packets

小B:嗯,这么看来,是网络问题啊,网络延迟这么高,GC高那应该是理所当然的!
小A:错了,表面看是网络延迟问题,但是经过我们的验证发现,把数据库停下来之后,网络延迟就消失了。当没有业务运行的时候,延迟都在0.00几,这说明是业务的压力上来导致的延迟。

这里bond0是双网卡绑定的私网的地址。rxkb/s是每秒收包的数量,而txkb/s是每秒发包的数量。这里数据库运行的时候每秒收发都上100MB/S了。
小B:那这个问题,怎么处理的?
小A:这个问题说实话是比较妖的,按照道理说这个网卡是万兆网卡,不至于100MB/S就处理不过来了,我们开始怀疑肯定是硬件之类的问题,或者是网络配置的问题,于是我们首先和其他数据库主机做了对比,就发现这个网卡的绑定模式和其他数据库不太一样。这个网卡的绑定模式是4。
说到网卡绑定模式,主要有7种模式。分别是:
mode=0 round-robin  轮询策略(Round-robin policy)
mode=1 active-backup  主备策略(Active-backup policy)
mode=2 load balancing (xor)  异或策略(XOR policy)
mode=3 fault-tolerance (broadcast)  广播策略(Broadcast policy)
mode=4 lacp IEEE 802.3ad   动态链路聚合(IEEE 802.3ad Dynamic link aggregation)
mode=5 transmit load balancing  适配器传输负载均衡(Adaptive transmit load balancing)
mode=6 adaptive load balancing  适配器负载均衡(Adaptive load balancing)

当前我们采取的是动态链路聚合模式,这种模式必须是两块网卡具备相同速率和双工模式才行。而且还需要交换机的支持。这种模式本身也没太大的问题,不过不是很常用。

一般应用的是mode=0的轮询策略、mode=1的主备策略,还有mode=6的负载均衡方式。由于其他数据库使用的是mode=1,唯独这个使用的是mode 4,首先怀疑的就是这个点。
但是因为停机时间一个月只有一次,没有办法进行测试,这次大家集体商量之后,决定双网卡绑定工作模式改造的同时,把网卡、网卡插槽、网线都更换一下。彻底的排除硬件上网络可能出现的问题。

小B:嗯,那做完这些操作之后,变好了吗?
小A:没有,当我们把这些操作都做了,数据库启动之后,白天工作时间段延迟依旧很高。
小B:额,那不是没找到问题的根源?
小A:是的,我们又仔细的检查了一遍,这次发现主要来源于oswatch中的mpstat,可以看到在业务高峰期cpu 15的%soft,总是100%。这个发现是非常重要的。

在网上搜索一番,可以发现大量的网卡软中断导致的网络延迟。

小B:越来越精彩了,这块属于网络问题了,我们DBA遇到这种问题该怎么办呢?
小A:我们要研究啊。DBA什么事情都要干,必须追求卓越。
网卡与操作系统的交互其中一个就是方式就是中断,网卡在收到了网络信号之后,主动就发送中断到cpu,而cpu会立即停止其他事情对这个中断信号进行处理。由于数据包速率的增长,带来的中断渐渐超过了单个cpu核可处理的范围。从而导致了网络延迟和丢包。在这里我还要提高Linux上的一个服务,叫做irqbalance。该服务就是专门解决网卡性能问题的,用于优化中断分配,将中断尽可能的均匀的分发给各个cpu core,充分利用cpu多核,提升性能。虽然开启了这个服务,但是我们实际情况是网卡中断就绑定在特殊的cpu 15上面。我们必须把这个中断手动重新绑一下。这个就不得不提到中断亲缘性(smp_affinity)设置。只有 kernel 2.4 以后的版本才支持把不同的硬件中断请求(IRQs)分配到特定的 CPU 上,这个绑定技术被称为 SMP IRQ Affinity。当前操作系统版本是RedHat 6,内核是2.6的。我们可以查看操作系统自带的说明:Linux-2.6.31.8/Documentation/IRQ-affinity.txt
至于绑定方式,因为购买的是华为服务器,在华为服务器的性能优化最佳的附录里面,会有网卡中断绑定的方法介绍。

操作方法有点复杂:
① 首先我们需要停止irqbalance服务。
Service irqbalance off
② 确认哪块网卡是私有网卡,然后执行下列语句,查看分配给网卡的中断号。
cat /proc/interrupts | grep -i ethx
③ 查看中断号和cpu绑定的情况,根据上面的中断号查看和cpu的亲缘性。
cat /proc/irq/126/smp_affinity
④ 中断绑定,将ethx的N个中断绑定到不同的cpu。
echo 16 > /proc/irq/126/smp_affinity

如果觉得麻烦,可以直接使用华为驱动包中提供的脚本。
当然做了这个操作之后缓解了一下症状。之前都是压到一个cpu核上造成100% soft,现在感觉还是压在一个核上,那么这个又是什么问题呢?在网上搜到了一篇美团点评的帖子,是这么理解这个问题的。当给中断设置了多个cpu core后,它也仅能由设置的第一个cpu core来处理,其他的cpu core并不会参与中断处理,原因猜想是当cpu平行收包时,不同的核收取了同一个queue的数据包,但处理速度不一致,导致提交到IP层后的顺序也不一致,这就会产生乱序的问题,由同一个核来处理可以避免了乱序问题。
参考链接:https://tech.meituan.com/2018/03/16/redis-high-concurrency-optimization.html
小B:问题还是没解决啊,看来也是有限制的。
小A:是的,当然也可以优化,根据上面的文档,咱们也可以把Oracle数据库的LMS进程的亲缘性设置到指定的cpu上去,然后把中断设置到另外的cpu上去,互相不冲突就可以解决了。但是我们没这么做,因为LMS进程比较多,主机上出cpu也比较多,设置起来较为麻烦,我们最后是通过参数优化来解决的。
第一个优化方式,是IRQ coalescing,中断合并主要是为了做延迟跟cpu开销之间的权衡。当网卡适配器收到一个帧之后,不会立即的对系统产生中断,而是等一段时间,收集到更多的包之后再一次性的处理,这会降低cpu的负载,但是会产生等待时间。

自适应模式使网卡能够自动调节中断聚合,在我们的机器上可以看到是没开启的。在自适应模式下,驱动程序将检查流量模式和内核接收模式,并在运行中估算合并设置,以防止数据包丢失。这里我们可以建议启动自适应模式。

# ethtool -C ethx adaptive-rx on

第二个优化的手段是,UDP根据源IP和目的IP,按照哈希结果将数据流分发到网卡的不同接收队列中。

# ethtool --config-ntuple ethx rx-flow-hash udp4 sdfn

在做了上面两个操作后,软中断的cpu使用率下降到了30%-60%之间,起到了明显的改善,处理中断的cpu只要不是100%,网络延迟丢包也就不存在了。后续观察网络的延迟都是在0.01ms以下,数据库的GC等待事件也随之消失了
小B:嗯,这个问题真是麻烦啊,还好你们一直坚持排查。
小A:那当然,DBA得追求卓越,把问题都搞清楚然后再解决。虽然这个问题是一只很厉害的“妖怪”,一度让我们很困扰,但是打败了这只“妖怪”之后,我们像经历了一次脱胎换骨,对网络问题又加深了理解,还是收获很大的。
小B:嗯,感谢分享,确实收获颇多,以后我也要像你一样在技术上追求卓越!

ADG连环排坑实战,希望你别遇到

版权声明:本文为Buddy Yuan原创文章,转载请注明出处。原文地址:ADG连环排坑实战,希望你别遇到
老马也有失蹄时,万万没想到,这次当了一回“头痛医头,脚痛医脚”的庸医。先来介绍下这次当庸医的情况吧:客户的一套三节点的RAC,上线了三年时间,随着业务量越来越大,数据库运行越来越慢。客户为了缓解特殊时间段的业务促销压力,决定搭建一套单机的ADG来分摊原生产库上的一部分查询压力。

由于业务的实时性较高,ADG配置采取了LGWR和ASYNC的模式来进行Redo变化的传输。在搭建环境之后的第二天,一个只读查询业务便迁移了过来。该业务的特点是并发高,实时性要求高。由于上线比较仓促,业务刚迁上去2-3天之内相继出现了不少问题,并且数据库还连续宕机了几次。面对问题,我们是出现一个处理一个,但这种被动式的处理方式非常低效。
我们先来看看都出现了哪些问题。

问题一、监听连接问题
当把一个只读业务迁移之后,监听出现大量的短连接冲击,出现一些TNS错误。
1、操作系统参数设置过低,导致资源不足

这里的错误主要是Linux Error: 11: Resource temporarily unavailable,需要通过调整操作系统参数nproc来解决。在Redhat/CentOS 7的系统中调整位置发生了变化,修改指定用户的nproc在/etc/security/limits.d/20-nproc.conf文件中配置,可参考官方文档:https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7/html/migration_planning_guide/sect-red_hat_enterprise_linux-migration_planning_guide-system_management

sed -i 's/4096/131072/g' /etc/security/limits.d/20-nproc.conf

不过还要注意一点,当你调整了nproc,也不一定能达到它最大的值。这是由于内核参数pid_max设置过小导致的,还需要把kernel.pid_max参数设大一点。

vi /etc/sysctl.conf
kernel.pid_max = 131072

2、监听短链接并发过高,出现连接Refuse和Timeout
这里最常规的优化方法是改成长链接。但是由于历史原因没办法修改,所以还是从监听本身着手。

第一种是设置RATE_LIMIT,如图所示,当限制为每秒3个时,同时并发连接超过3个就会报错。这种方法起到高并发连接的限制作用,超出了限制就会报错。而业务程序基本上每秒就超过100个并发连接,同时这些连接需要取最新实时的数据。我们就是为了让并发连接不报错。因此设置RATE_LIMIT参数并不符合要求。

第二种是在监听中增加QUEUESIZE参数,QUEUESIZE参数是指监听器在建立连接时可以存储的并发连接请求数。如果传入请求数超过缓冲区大小,则请求连接的客户端将接收失败。在比较理想的情况下,此缓冲区大小应该等于或者大于预期并发请求的最大数量。Oracle官方文档也提到了这个参数适用于处理大量并发连接的请求。

在CentOS7平台我们可以通过strace命令查看默认的QUEUESIZE参数。

strace -fo /tmp/queue.log lsnrctl start listener
[root@oracle-002 ~]# cat /tmp/queue.log | grep -i "listen("
90066 listen(14, 128)=0

可以看到在cenot7和11g数据库版本中默认是128,我们把这个设置成512。

3.ADG数据库出现大量的library cache lock和ORA-00600错误
我们安装了和主库相同的PSU,但是在运行的过程中数据库仍然会出现大量的library cache lock和ORA-00600[kgllkde-bad-lock]及ORA-00600[kss_get_type: bad control]等错误。可以通过安装单独的patch 24385983 18515268、19180394、17608518等来增强数据库的健壮性。
针对在ADG数据库上出现的library cache lock,我们做了单独的补丁分析,参考了文档WAITEVENT: “library cache lock” Reference Note (Doc ID 34578.1),如下excel列表所示,相关和容易触发问题的补丁都做了安装。

这里有几个小技巧的分享:
首先这里的一些bug是特殊操作才会触发的,例如添加interval partition分区。而数据库中根本没使用到interval的分区,所以这类Bug就不会触发。
其次Prob列代表触发的概率,分为I,II,III,IIII等等,IIII是触发几率最高的。而I,II是触发几率最低的,这种触发较低的补丁可以考虑不安装。毕竟为了一个极低的概率去安装一些补丁获取的收益不大(除非这个极低的概率一直会触发)。
最后就是NB列。NB代表安装了补丁,还需要做一些设置才能enable它的功能,例如设置event事件(注意:如果不设置事件或者相关参数,安装了补丁也不起作用)。

4.Centos7操作系统cache回收问题
通过观察操作系统我们发现当free内存值下降之后,系统会触发文件系统cache回收动作,这些动作会让system cpu变高。往往会造成主机hang住没办法操作。系统当前的vm.dirty_background_ratio和vm.dirty_ratio参数设置的是平衡模式,一般推荐使用这个方案。但是在出现问题的情况下,我们还是需要考虑做一些调整的。

首先我们来看两个参数的含义:
vm.dirty_ratio:内存中脏数据的限制,内存中的脏数据不能超过这个百分比的值,如果脏数据超出了这个数量。则会直接进行同步刷到磁盘。此时如果有新的I/O请求,将会被阻塞,需要等待脏数据写进磁盘。
dirty_background_ratio:这个参数指定了当文件系统缓存脏页数量达到系统内存百分之多少时(如5%)就会触发pdflush/flush/kdmflush等后台回写进程运行,将一定缓存的脏页异步刷入磁盘中。
当前我们设置的参数:vm.dirty_background_ratio = 5 和vm.dirty_ratio = 80。也就是当达到5%的上限时,后台进程将立即开始异步I/O写入,但是不会强制同步I/O。直到达到80%的满载情况后,系统才会大量的同步写入。当大量同步写入的时候,此时观察到数据库就会出现大量的异常等待。

问题:这里我们考虑一下,如果我们把内存脏数据的限制从80%下调到10%,改成减少缓存的模式会怎么样呢?

修改成减少缓存的模式。将vm.dirty_ratio从80下调到10,这样当开始强制同步I/O的时候,刷脏页是比较少的。虽然会频繁的就触发刷脏页,但是数量下降了,不会一下子刷很多。这样造成的I/O同步的延迟的时间就会变短。关于平衡模式和减少缓存模式的,可以参考Bob Plankers写的文章《Better Linux Disk Caching & Performance with vm.dirty_ratio & vm.dirty_background_ratio》。

同时系统还需要设置min_free_kbytes,这个参数相当重要。当请求分配内存的时候,如果有足够的内存,则可以成功分配,当没有足够内存的时候,操作就会阻塞。他需要等待系统先去释放内存,再分配内存。而对于系统级别的一些原子性的请求,它是不能被阻塞的,如果分配不到内存的话,就会出现失败。内核为了避免原子请求失败,必须设置一块保留的内存。而这个就是通过这个参数来设置的。

vm.min_free_kbytes=10485760

上述参数设置完毕之后,我们还可以补充一个脚本手动来强制刷cache。让cache没达到vm.dirty_background_ratio参数设置的5%就强制刷脏页。
手动刷cache,15分钟1次。

crontab -l
*/15 * * * *  sync && echo 3 > /proc/sys/vm/drop_caches

5.Centos 7系统与Oracle软件不兼容问题
做完上述三个调整之后,业务切换到ADG上,白天不会出现问题,但是在半夜仍然会出现问题,同时系统Oracle后台报了大量的trace。如果不清理基本上在一天内能占满300GB。
正常运行的Oracle软件不会出现这么多trace,于是怀疑是Oracle 11gR2软件和Centos 7.5版本存在兼容性问题(主库是Centos 6和Oracle 11g),通过使用另外的一套机器RedHat6搭建了一套新的11g adg环境后,彻底解决了半夜宕机的问题。通过OSWatch可以发现内存的波动是相对稳定的。

结论
其实解决了这么多问题,都只是一些表象,而真正的原因是通过一步一步的踩坑及补锅才能最终发现的。
上述是一个综合性的问题,首先是操作系统和数据库的兼容性有问题,在安装时使用了一些特殊的设置(Centos 7安装Oracle 11g),导致安装后,在高并发业务运行时出现问题,系统产生大量的trace,短时间就能产生几百GB。这些trace文件本身并不会导致问题,只是巧就巧在和系统设置的内存回收机制又有冲突,虽然操作系统一直是达到5%就异步刷新脏页,但是处理能力远远不够。当达到设置的80%时,大量同步刷脏页的时候,系统就会因为I/O请求发生阻塞。因而造成了system cpu变高,然后导致数据库出现各类异常等待事件触发Bug,也会影响监听导致各种连接异常,最终造成数据库宕机影响了业务。
通过改成稳定的操作系统平台Redhat6和数据库11gR2,优化了操作系统内核相关的内存参数,安装了Oracle相关补丁之后,系统再也没出现过类似问题,系统资源使用也相对稳定。
针对这次问题,其实我缺乏的是一种将”线索”串联起来的能力。就单对单线索处理的能力,我们是很强的,但是对于多个“线索”,如何串联起来我们往往有所欠缺。
第一:这个和个人职业经历及遇到的问题、场景是有关系的。如果经历过几次这样的事情,自己的大局观更加开阔,会更早就把问题串联起来进行分析。
第二:这个问题是可以提前预防和发现的,这要求就是多做几轮压力测试,然后再迁移上来。也可以考虑做灰度测试,开启两套程序,一套在生产上运行,一套在ADG上运行。

“庸医”对应的是“神医”,扁鹊在第一次见齐桓公的时候,就说出了:“君有疾在腠理,不治将恐深”。可见扁鹊厉害之处就在于他总能提前发现病情,防患于未然。当“君有疾在骨髓”的时候,扁鹊已经删库跑路了,根本就救不了齐桓公。
我一直认为,会解决问题不牛逼,能把问题扼杀在萌芽才是真正的牛逼,这就要求我们必须锻炼自己的大局观,把系统的整个架构,操作系统、网络、存储、数据库中间层、代理层及程序框架全面打通,就像武侠高手打通任督二脉一样。但这个靠个人学习是很难实现,至少就有100种产品等着我们去研究。
如何做到牛逼不苦逼?其实可以通过一些监控软件来实现大局观,虽然自己有些技术不懂,但通过监控知道哪里出现了问题,就可以联系人来解决。目前我所在公司就有DPM这样的数据库监控软件,有IVORY大数据日志分析平台,这些软件集成在一起就能帮助我迅速找出问题所在,并判断何为对的方向。

一次MOSTLY LATCH-FREE SCN LATCH 问题分析

今天接到告警中间件侧反馈堵塞,通过查询相关日志,发现数据库上一条SQL出现了latch free的等待,该SQL执行时间大概持续了180秒。导致应用服务出现堵塞。

1) 通过收集SQLHC信息,发现该SQL在问题时间点存在很严重性能问题。问题时间点平均执行时长170秒,平均逻辑读1200万。

 
2)通过中间件日志,我们找到了当时调用的绑定变量。通过手动执行该SQL,发现在绑定变量和执行计划没发生改变的时候,SQL执行时快时慢。有时候能够迅速执行出来,有时候就会等待很久。通过观察,在异常执行时间段产生的等待事件是latch free。

而通过历史视图查询在出问题期间的event name是空的。但是可以看到等待事件p1和p2值。这里主要是看p2,p2的值是166。

 
通过查询v$latchname,可以发现166是一种叫mostly latch-free scn的等待事件。

 
而正常的时候是秒出的,基本看不到任何等待事件。

3)从历史的情况来看。这个问题主要出现过2次,一次是在8月27日,而另外一次是今天9月27日。而其他时间该SQL一直运行良好没出现任何问题。

 

问题解决:
1).根据当前的情况,目前怀疑是特殊场景下:(因为该等待事件和scn有关,怀疑可能是分布式事务问题,正好出问题期间也产生了一些tx锁和无效的分布式事务)导致触发了Oracle Bug,下午和Oracle工程师一起复现了这个问题,目前Oracle方面倾向于命中了Bug 9951190导致的。临时的workground是将参数_ktb_debug_flags从8改成0,但是因为该参数设置为8是为了阻止另外一个ora-00600问题。修改参数为0则很容易触发这个ora-00600问题,所以不建议修改参数。

2).通过在bcv上执行同样的sql语句,发现一个更好的执行计划。建议考虑执行sql profile先绑定。目前汇在16点40左右得到授权执行了绑定,绑定后已经执行了13次。sql性能运行良好。

SELECTsql_id,MODULE,cpu_time,elapsed_time,buffer_gets,sql_profile,last_load_time,executions FROM v$sql where sql_id='0jg8uq3w2d4v8';


 
目前暂时不清楚绑定该SQL之后, 是否仍然触发上述问题,需要再进行一段时间的观察。

3)最终解决问题的方法是安装补丁9951190

Linux下使用Vagrant快速安装Oracle Database 19c

自从使用了Linux环境,感觉环境配置这块越来越顺手了。今天就测了一下Linux环境下使用Vargant安装Oracle Database 19c。我们首先还是打开github ,这里有我们可以选择的各种镜像。

1.首要步骤是Linux上需要安装Vargrant和VirtualBox

Vagrant下载地址

VirtualBox下载地址

2.下载Vagrant的配置环境

git clone https://github.com/oracle/vagrant-boxes

3.下载Oracle安装介质

当我们下载完后,我们可以发现很多个安装模板,这里包括OracleDatabase、OracleDG、OracleRAC等等。我们选择OracleDatabase,发现下面从11g一直到19c模板环境都有。进入19.3文件夹。在这个路径下载Oracle 19c安装介质

4. 执行安装

等Oracle安装介质下载完毕,在19.3上执行vagrant up。

装完之后,会在virtualbox出现一个19c的虚拟机

5.通过SSH进行连接


使用Vagrant真是太方便了。

ORA-7445 [opiaba] and ORA-600 [17147] 问题分析处理

今天早上发现数据库一个实例在08点38分29秒宕机了一次,迅速又在08点39分01秒自动拉起。重启后产生了很多分布式事务的报错。接着在09点03分39秒又再一次宕机,并在09点04分48秒再一次自动拉起。

通过对数据库alert日志进行分析。发现重启前后报了ORA-07445和ORA-00600错误。首先是应用进程报了ORA-07445错误。接下来是PMON进程产生了ORA-00600[17147错误]。最终PMON进程终止了实例。

 
进一步分析应用进程的trace,发现下列问题:

 
可以看到是在执行SQL语句9q342zu0xtkvx触发了Oracle内部opiaba函数的异常。而进一步观察发现这个是一个语句块,这个语句块中执行了很多条相同的insert语句,而这些insert语句使用了绑定变量达到86904个。

 
通过在mos上搜索,我们找到一篇文章Instance crashed after ORA-7445 [opiaba] and ORA-600 [17147] (Doc ID 1466343.1),其中的错误及堆栈信息和我们遇到的报错完全一致。

 
根据文档的描述,ora-7445 [opiaba]错误是由于在SQL或者PL/SQL的语句中使用了超过65535个绑定变量。从而最终导致实例的crash。解决的办法是修改应用程序以使用少于65535个绑定变量。或者是安装patch 12578873,但是安装完补丁之后,只会阻止实例不会宕机,并不能从根本上解决问题,目前为止还是不允许在SQL或者PL/SQL语句中使用超过65535个绑定变量。正常情况程序是不会使用到这么多绑定变量的。
参考文档:Instance crashed after ORA-7445 [opiaba] and ORA-600 [17147] (Doc ID 1466343.1)

ProxySQL 2.0安装和Percona Cluster5.7集成

版权声明:本文为Buddy Yuan原创文章,转载请注明出处。原文地址:ProxySQL 2.0安装和Percona Cluster5.7集成
ProxySQL是MySQL的一款中间件产品,是灵活和强大的MySQL代理层,它可以实现像查询缓存,多路路由(multiplexing),镜像,读/写分离,路由等。在ProxySQL的早期版本中并不能原生支持Galera支持,需要通过schedule调度脚本实现。但是在2.0版本后,就增加了原生支持,只需要配置好参数表即可。

首先我们来安装它来控制我们安装好的PXC集群。

IP地址 主机名
192.168.56.161 pxc1
192.168.56.162 pxc2
192.168.56.163 pxc3
192.168.56.160 proxysql

1.添加yum源

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key
EOF

2.使用yum进行安装

yum install proxysql

3.启动proxysql服务

[root@localhost ~]# service proxysql start
Starting ProxySQL: 2019-07-02 14:03:13 [INFO] Using config file /etc/proxysql.cnf
2019-07-02 14:03:13 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generating new keys/certificates.
DONE!

4.在PXC集群中安装视图
参考上一篇文章:传送门
 
5.连接管理端

[root@localhost /]# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
bash: mysql: 未找到命令...

这里报错是因为没有安装mysql客户端。随便安装一个mysql客户端就行了。这里直接安装pxc,也可以安装其他MySQL产品。

yum install Percona-XtraDB-Cluster-57

安装好之后再次运行就可以登录了

[root@localhost /]# mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt 'admin> '
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

admin> show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)

这里自带了5个数据库.
main库:内存配置数据库,即memory,表里面存放后端db实例、用户验证、路由规则等信息。
disk库:持久化磁盘的配置。
stats库:统计信息的汇总。
monitor库:一些监控的收集信息,包括数据库的健康状态等。
stats_history 统计信息历史库

5.添加pxc信息到ProxySQL的mysql_servers 表

admin> show tables from main;
+--------------------------------------------+
| tables |
+--------------------------------------------+
| global_variables |
| mysql_aws_aurora_hostgroups |
| mysql_collations |
| mysql_galera_hostgroups |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| proxysql_servers |
| runtime_checksums_values |
| runtime_global_variables |
| runtime_mysql_aws_aurora_hostgroups |
| runtime_mysql_galera_hostgroups |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules |
| runtime_mysql_query_rules_fast_routing |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers |
| runtime_mysql_users |
| runtime_proxysql_servers |
| runtime_scheduler |
| scheduler |
+--------------------------------------------+
24 rows in set (0.00 sec)

admin> insert into mysql_servers(hostgroup_id,hostname,port) values
-> (10,'192.168.56.161',3306),
-> (10,'192.168.56.162',3306),
-> (10,'192.168.56.163',3306);
Query OK, 3 rows affected (0.00 sec)

INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight) VALUES (2,'192.168.56.161',3306,100);
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight) VALUES (2,'192.168.56.162',3306,10);
INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight) VALUES (3,'192.168.56.163',3306,100);

增加galera的配置

INSERT INTO mysql_galera_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind)
VALUES (2,4,3,1,1,1,0,100);

load mysql servers to runtime;
save mysql servers to disk;

之后可以查看三台机器的状态。

Admin> select * from mysql_servers;
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 2 | 192.168.56.161 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 192.168.56.162 | 3306 | 0 | ONLINE | 10 | 0 | 1000 | 0 | 0 | 0 | |
| 3 | 192.168.56.163 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.01 sec)

Admin> select * from mysql_galera_hostgroups;
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| 2 | 4 | 3 | 1 | 1 | 1 | 0 | 100 | NULL |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.00 sec)

可以看到,我们通过插入数据,配置了Galera集群的一些设置。192.168.56.161和192.168.56.162属于组2,而192.168.56.163属于组3。而组2是writer_hostgroup,组3是reader_hostgroup。而在我们的组2中,192.168.56.161的权重是大于192.168.56.162的,因为161的weight是100,而162的weight是10。这里我们还设置了max_writers为1,也就是最多只有一个提供写入。而一旦该写入组出现故障,就会切换到备用的写入组backup_writer_hostgroup,也就是组4。

我们现在来看一下整个集群的连接情况。因为暂时没有连接。所以现在把192.168.56.163设置是写入组,而192.168.56.161和192.168.56.162设置的是备份写入组。

Admin> select hostgroup,srv_host,status,ConnUsed,MaxConnUsed,Queries,Latency_us from stats.stats_mysql_connection_pool order by srv_host;
+-----------+----------------+--------+----------+-------------+---------+------------+
| hostgroup | srv_host | status | ConnUsed | MaxConnUsed | Queries | Latency_us |
+-----------+----------------+--------+----------+-------------+---------+------------+
| 4 | 192.168.56.161 | ONLINE | 0 | 0 | 0 | 1226 |
| 4 | 192.168.56.162 | ONLINE | 0 | 0 | 0 | 1117 |
| 2 | 192.168.56.163 | ONLINE | 0 | 0 | 0 | 1160 |
+-----------+----------------+--------+----------+-------------+---------+------------+
3 rows in set (0.01 sec

当前的状态和我们插入到mysql_server表的设置不符。但是也符合当前的配置,为什么这么说呢?因为PXC默认三个节点是可写的,而163的延迟最低,所以他默认就把3当做了主要写入的节点。

6.创建监控用户
在pxc集群中的任意一个主机上创建监控用户,并赋予权限;

create user monitor@'192.168.56.%' identified by 'monitor';
grant all privileges on *.* to monitor@'192.168.56.%';

在pxc上创建完账号之后就回到ProxySQL节点上配置

UPDATE global_variables SET variable_value='monitor' where variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='proxysql' where variable_name='mysql-monitor_password';

load mysql variables to runtime;
save mysql variables to disk;

之后就可以验证监控信息:

Admin> select * from monitor.mysql_server_connect_log order by time_start_us desc limit 6;
+----------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+----------------+------+------------------+-------------------------+---------------+
| 192.168.56.162 | 3306 | 1562313405719594 | 4853 | NULL |
| 192.168.56.161 | 3306 | 1562313405093564 | 2650 | NULL |
| 192.168.56.163 | 3306 | 1562313404467721 | 5336 | NULL |
| 192.168.56.161 | 3306 | 1562313345470323 | 1261 | NULL |
| 192.168.56.163 | 3306 | 1562313344968944 | 3461 | NULL |
| 192.168.56.162 | 3306 | 1562313344467233 | 2646 | NULL |
+----------------+------+------------------+-------------------------+---------------+
6 rows in set (0.00 sec)

Admin> select * from monitor.mysql_server_ping_log order by time_start_us desc limit 6;
+----------------+------+------------------+----------------------+------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+----------------+------+------------------+----------------------+------------+
| 192.168.56.163 | 3306 | 1562313454473362 | 847 | NULL |
| 192.168.56.161 | 3306 | 1562313454363177 | 900 | NULL |
| 192.168.56.162 | 3306 | 1562313454253065 | 1398 | NULL |
| 192.168.56.161 | 3306 | 1562313444460262 | 1114 | NULL |
| 192.168.56.163 | 3306 | 1562313444356808 | 2072 | NULL |
| 192.168.56.162 | 3306 | 1562313444253269 | 1643 | NULL |
+----------------+------+------------------+----------------------+------------+
6 rows in set (0.00 sec)

7.设置ProxySQL,连接后端使用的PXC的用户,配置mysql_uses表。
在pxc集群的任意节点上创建一个连接用户。这里使用root用户来测试。

mysql> create user 'buddy'@'%' identified by 'buddy';
Query OK, 0 rows affected (0.03 sec)
mysql> grant all on *.* to 'buddy'@'%';
Query OK, 0 rows affected (0.03 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

然后回到ProxySQL,配置mysql_users表,将刚才的用户添加到表中。

admin> insert into mysql_users(username,password,default_hostgroup,transaction_persistent) values('buddy','buddy',2,1);
Query OK, 1 row affected (0.00 sec)

Admin> select * from mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| buddy | buddy | 1 | 0 | 2 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
1 row in set (0.00 sec)

admin> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
admin> save mysql users to disk;
Query OK, 0 rows affected (0.02 sec)

9.接下来就可以连接pxc集群了
通过6033端口进行连接

[root@localhost ~]# mysql -ubuddy -pbuddy -h 127.0.0.1 -P 6033
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.26 (ProxySQL)

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| pxc3 |
+------------+
1 row in set (0.01 sec)

配置ProxySQL出现“OFFLINE_HARD”的解决办法

版权声明:本文为Buddy Yuan原创文章,转载请注明出处。原文地址:配置ProxySQL出现“OFFLINE_HARD”的解决办法
这两天在做ProxySQL的一系列实验,不过在实验中遇到一个小问题。当我查询runtime_mysql_servers或者是stats.stats_mysql_connection_pool视图的时候,经常发现配置PXC主机status处于”OFFLINE_HARD”状态。这个状态的含义如下,硬离线”状态,不再接受新的连接,已建立的连接或被强制中断。当后端实例宕机或网络不可达会出现这种状态。但是我通过PXC集群连接进去发现集群的状态都是正常的。

[root@localhost proxysql]# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.26 (ProxySQL Admin Module)

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Admin> select hostgroup,srv_host,status,ConnUsed,MaxConnUsed,Queries,Latency_us from stats.stats_mysql_connection_pool order by srv_host;
+-----------+----------------+--------------+----------+-------------+---------+------------+
| hostgroup | srv_host       | status       | ConnUsed | MaxConnUsed | Queries | Latency_us |
+-----------+----------------+--------------+----------+-------------+---------+------------+
| 4         | 192.168.56.161 | ONLINE       | 0        | 0           | 0       | 1362       |
| 4         | 192.168.56.162 | ONLINE       | 0        | 0           | 0       | 1608       |
| 2         | 192.168.56.163 | ONLINE       | 0        | 0           | 0       | 969        |
| 3         | 192.168.56.163 | OFFLINE_HARD | 0        | 0           | 0       | 969        |
+-----------+----------------+--------------+----------+-------------+---------+------------+

针对这个问题,我们要做的首先是检查proxysql.log日志,该日志位于/var/lib/proxysql下面。

[root@localhost proxysql]# grep -i OFFLINE proxysql.log | grep because | cut -b 124- | sort | uniq -c
      1 setting host 192.168.56.161:3306 offline because: Table 'sys.gr_member_routing_candidate_status' doesn't exist
      1 setting host 192.168.56.162:3306 offline because: Table 'sys.gr_member_routing_candidate_status' doesn't exist
      1 setting host 192.168.56.163:3306 offline because: Table 'sys.gr_member_routing_candidate_status' doesn't exist

这里说明了offline问题的原因,是因为有个表不存在。叫sys.gr_member_routing_candidate_status,这个视图额外提供了一些复制组成员的信息。这些信息需要被ProxySQL使用。这个脚本需要下载。下载地址如下:传送门

[root@pxc1 mysql]# mysql -p < addition_to_sys.sql 
Enter password:

安装完成之后再次执行相关查询,OFFLINE_HARD的状态已经消失了。不过我发现需要先查询。runtime_mysql_servers状态再次查询stats.stats_mysql_connection_pool才会消失。

Admin> select hostgroup,srv_host,status,ConnUsed,MaxConnUsed,Queries,Latency_us from stats.stats_mysql_connection_pool order by srv_host;
+-----------+----------------+--------+----------+-------------+---------+------------+
| hostgroup | srv_host       | status | ConnUsed | MaxConnUsed | Queries | Latency_us |
+-----------+----------------+--------+----------+-------------+---------+------------+
| 4         | 192.168.56.161 | ONLINE | 0        | 0           | 0       | 1016       |
| 4         | 192.168.56.162 | ONLINE | 0        | 0           | 0       | 1003       |
| 2         | 192.168.56.163 | ONLINE | 0        | 0           | 0       | 1041       |
+-----------+----------------+--------+----------+-------------+---------+------------+
3 rows in set (0.00 sec)