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

发布时间:2016-11-30 00:00:00 编辑:嘉辉 手机版

  一套测试库系统响应缓慢,通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

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

本文已影响861
+1
0