内存耗尽导致系统缓慢的解决方法

一套测试库系统响应缓慢，通SQLPLUS登陆到数据库中大约5-6秒才能登陆进去，正常情况下也就1秒即可登陆，简单的一个show parameter 命令也得好几秒才返回。下面YJBYS小编为大家整理了关于内存耗尽导致系统缓慢的解决方法，希望对你有所帮助。

登陆到数据库中发现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用户执行如下命令：

SXTESTDB11@/# 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

SXTESTDB11@/# 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

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