Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2812511
  • 博文数量: 599
  • 博客积分: 16398
  • 博客等级: 上将
  • 技术积分: 6875
  • 用 户 组: 普通用户
  • 注册时间: 2009-11-30 12:04
个人简介

WINDOWS下的程序员出身,偶尔也写一些linux平台下小程序, 后转行数据库行业,专注于ORACLE和DB2的运维和优化。 同时也是ios移动开发者。欢迎志同道合的朋友一起研究技术。 数据库技术交流群:58308065,23618606

文章分类

全部博文(599)

文章存档

2014年(12)

2013年(56)

2012年(199)

2011年(105)

2010年(128)

2009年(99)

分类: Oracle

2012-01-10 14:40:55

客户的一套测试库系统响应缓慢,通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
 
再次登录数据库,即可瞬间完成。
 

 
阅读(3230) | 评论(1) | 转发(5) |
给主人留下些什么吧!~~

☆彼岸★花开2012-01-12 20:49:23

感觉内存整理的那些个软件都不太管用啊!~