内存耗尽导致系统缓慢

客户的一套测试库系统响应缓慢，通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 mem0

goodsize 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            10G
SQL> 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=1
Setting 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

 

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