aix oracle 内存耗尽,内存耗尽导致系统缓慢

客户的一套测试库系统响应缓慢，通SQLPLUS登陆到数据库中大约5-6秒才能登陆进去，正常情况下也就1秒即可登陆，简单的一个show parameter 命令也得好几秒才返回。

登陆到数据库中发现ALERT文件报了大量的ORA-3136错误信息。

WARNING: inbound connection timed out (ORA-3136)

Tue Jan 10 11:19:17 2012

WARNING: inbound connection timed out (ORA-3136)

Tue Jan 10 11:19:17 2012

WARNING: inbound connection timed out (ORA-3136)

Tue Jan 10 11:19:17 2012

WARNING: inbound connection timed out (ORA-3136)

Tue Jan 10 11:19:17 2012

WARNING: inbound connection timed out (ORA-3136)

Tue Jan 10 11:19:17 2012

WARNING: inbound connection timed out (ORA-3136)

Tue Jan 10 11:19:17 2012

WARNING: inbound connection timed out (ORA-3136)

Tue Jan 10 11:19:17 2012

WARNING: inbound connection timed out (ORA-3136)

Tue Jan 10 11:19:17 2012

WARNING: inbound connection timed out (ORA-3136)

Tue Jan 10 11:19:17 2012

WARNING: inbound connection timed out (ORA-3136)

Tue Jan 10 11:19:17 2012

WARNING: inbound connection timed out (ORA-3136)

Tue Jan 10 11:19:17 2012

WARNING: inbound connection timed out (ORA-3136)

Tue Jan 10 11:19:17 2012

WARNING: inbound connection timed out (ORA-3136)

Tue Jan 10 11:19:17 2012

WARNING: inbound connection timed out (ORA-3136)

Tue Jan 10 11:19:17 2012

WARNING: inbound connection timed out (ORA-3136)

Tue Jan 10 11:19:17 2012

WARNING: inbound connection timed out (ORA-3136)

Tue Jan 10 11:19:17 2012

WARNING: inbound connection timed out (ORA-3136)

Tue Jan 10 11:19:17 2012

WARNING: inbound connection timed out (ORA-3136)

Tue Jan 10 11:19:17 2012

WARNING: inbound connection timed out (ORA-3136)

ORA-3136错误一般在网络不稳定，数据库系统资源耗尽的时候，客户端进行连接的时候容易出现。

topas一下发现系统的内存资源耗光了，交换空间都用掉了30%多。

MEMORY

Real,MB   32768

% Comp     39.6

% Noncomp  61.2

% Client   61.2

PAGING SPACE

Size,MB   32768

% Used     31.1

% Free     68.8

大部分内存都被客户端分页占掉了。

数据库的物理内存为32G，交换空间为32G如下：

$ lsattr -El mem0goodsize 32768 Amount of usable physical memory in Mbytes False

size     32768 Total amount of physical memory in Mbytes  False

$ lsps -a

Page Space      Physical Volume   Volume Group    Size %Used Active  Auto  Type

paging00        hdisk1            rootvg       16384MB    31   yes   yes    lv

hd6             hdisk0            rootvg       16384MB    31  yes   yes    lv

$

检查了一下数据库配置ORACLE的SGA,PGA都设置的不大

SQL> show parameter sga

NAME                                 TYPE                   VALUE

------------------------------------ ---------------------- -----------

lock_sga                             boolean                FALSE

pre_page_sga                         boolean                FALSE

sga_max_size                         big integer            10G

sga_target                           big integer            10GSQL> show parameter pga

NAME                                 TYPE                   VALUE

------------------------------------ ---------------------- -----------

pga_aggregate_target                 big integer            4G

总共才14G，还有大约18G的空间可供操作系统利用，不应该出现内存紧张的问题。

$ vmstat -v

8388608 memory pages

7961825 lruable pages

10110 free pages

4 memory pools

994480 pinned pages

80.0 maxpin percentage

20.0 minperm percentage                 80.0 maxperm percentage                 63.9 numperm percentage              5093543 file pages

0.0 compressed percentage

0 compressed pages

63.9 numclient percentage                80.0 maxclient percentage              5093543 client pages

0 remote pageouts scheduled

32561 pending disk I/Os blocked with no pbuf

18706130 paging space I/Os blocked with no psbuf

2740 filesystem I/Os blocked with no fsbuf

200 client filesystem I/Os blocked with no fsbuf

1904898 external pager filesystem I/Os blocked with no fsbuf

0 Virtualized Partition Memory Page Faults

0.00 Time resolving virtualized partition memory page faults

通过vmstat -v 发现系统的内核参数 maxperm, maxclient 都设置为80%，对于数据库系统来说，这个设置的太高了。

numperm percentage 都达到了63.9 了，大部分内存都被文件系统缓存占掉了。

切换到root用户执行如下命令：

# vmo -p -o maxclient%=30 -o maxperm%=30 -o minperm%=10 -o strict_maxclient=1Setting minperm% to 10 in nextboot file

Setting maxperm% to 30 in nextboot file

Setting maxclient% to 30 in nextboot file

Setting strict_maxclient to 1 in nextboot file

Setting minperm% to 10

Setting maxperm% to 30

Setting maxclient% to 30

Setting strict_maxclient to 1

# vmo -L | grep strict

strict_maxclient          1      1      1      0      1      boolean           D

strict_maxperm

strict_maxperm            0      0      0      0      1      boolean           D

strict_maxclient

一段时间后系统内存恢复正常;

$ vmstat -v

8388608 memory pages

7961825 lruable pages

2700191 free pages

4 memory pools

994864 pinned pages

80.0 maxpin percentage

10.0 minperm percentage

30.0 maxperm percentage

29.9 numperm percentage              2384473 file pages

0.0 compressed percentage

0 compressed pages

29.9 numclient percentage

30.0 maxclient percentage              2384473 client pages

0 remote pageouts scheduled

32561 pending disk I/Os blocked with no pbuf

18706130 paging space I/Os blocked with no psbuf

2740 filesystem I/Os blocked with no fsbuf

200 client filesystem I/Os blocked with no fsbuf

1904898 external pager filesystem I/Os blocked with no fsbuf

0 Virtualized Partition Memory Page Faults

0.00 Time resolving virtualized partition memory page faults

numperm percentage 已经大大降低。

topas系统内存情况如下：

MEMORY

Real,MB   32768

% Comp     39.6

% Noncomp  28.9

% Client   28.9

再次登录数据库，即可瞬间完成。