記憶體耗盡導致系統緩慢的解決方法

一套測試庫系統響應緩慢，通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

再次登入資料庫，即可瞬間完成。