mysql 内存latch_aix使用太多内存导致shared pool 相关latch异常

某客户有一服务器,shared pool 相关latch出现异常等待,影响系统性能.分析结果:因为系统空闲内存太少,使用太多Paging Space导致该异常;解决办法:1.增加内存，2.在业务接受范围内减小sga等其他和内存消耗相关参数

nmon查看剩余内存

x Physical PageSpace | pages/sec In Out | FileSystemCache

x% Used 99.8% 34.9% | to Paging Space 0.0 0.0 | (numperm) 14.8%

x% Free 0.2% 65.1% | to File System 0.0 33.0 | Process 63.9%

xMB Used 21452.8MB 11446.1MB | Page Scans 0.0 | System 21.1%

xMB Free (少)-->51.2MB 21321.9MB | Page Cycles 0.0 | Free 0.2%

xTotal(MB) 21504.0MB 32768.0MB | Page Steals 0.0 | ------

topas查看内存配置

Disk Busy% KBPS TPS KB-Read KB-Writ MEMORY

Topas Monitor for host: p570b03 EVENTS/QUEUES FILE/TTY

Wed Jan 9 13:30:30 2013 Interval: 2 Cswitch 785 Readch 173.1K

Syscall 54407 Writech 213.1K

CPU User% Kern% Wait% Idle% Reads 118 Rawin 0

ALL 43.6 1.7 0.0 54.8 Writes 110 Ttyout 352

Forks 0 Igets 0

Network KBPS I-Pack O-Pack KB-In KB-Out Execs 0 Namei 5

Total 84.5 146.0 200.6 26.4 58.1 Runqueue 0.5 Dirblk 0

Waitqueue 0.0

Disk Busy% KBPS TPS KB-Read KB-Writ MEMORY

Total 0.0 164.6 17.0 0.0 164.6 PAGING Real,MB 21504

Faults 12408 % Comp 86

FileSystem KBPS TPS KB-Read KB-Writ Steals 0 % Noncomp 13

Total 316.3 17.9 151.5 164.9 PgspIn 0 % Client 13

PgspOut 0

Name PID CPU% PgSp Owner PageIn 0 PAGING SPACE

oracle 6357252 16.7 8.4 oracle PageOut 42 Size,MB 32768

oracle 15401474 8.3 70.8 oracle Sios 42 % Used 35

oracle 12714542 8.3 8.3 oracle % Free 65

oracle 5767556 8.3 8.3 oracle NFS (calls/sec)

oracle 5898996 8.3 134.9 oracle SerV2 0 WPAR Activ 0

oracle 17629634 8.3 134.9 oracle CliV2 0 WPAR Total 0

oracle 13959694 0.0 8.4 oracle SerV3 0 Press: "h"-help

oracle 5439860 0.0 134.3 oracle CliV3 0 "q"-quit

内存参数配置

vmo -F -a

--数据库相关参数

minperm% = 3

v_pinshm = 0

lru_file_repage = 0

maxclient% = 90

maxperm% = 90

strict_maxclient = 1

strict_maxperm = 0

page_steal_method = 1

因为是AIX 6.1,这里的vmo配置基本上是oracle 推荐值(大页没有配置,非必须选项)

会话进程占用内存

procmap 15466998

15466998 : oraclewasudb (LOCAL=NO)

100000000 97466K read/exec oracle

11000088d 2430K read/write oracle

9fffffff0000000 51K read/exec /usr/ccs/bin/usla64

9fffffff000cfe2 0K read/write /usr/ccs/bin/usla64

900000000b14930 2K read/exec /usr/lib/libC.a[shr3_64.o]

9001000a0122930 0K read/write /usr/lib/libC.a[shr3_64.o]

900000000af5b00 118K read/exec /usr/lib/libC.a[shrcore_64.o]

9001000a0319100 12K read/write /usr/lib/libC.a[shrcore_64.o]

900000000ad7000 118K read/exec /usr/lib/libC.a[ansicore_64.o]

9001000a030fe00 36K read/write /usr/lib/libC.a[ansicore_64.o]

900000000411468 0K read/exec /usr/lib/libicudata.a[shr_64.o]

9001000a0121468 0K read/write /usr/lib/libicudata.a[shr_64.o]

90000000040f738 2K read/exec /usr/lib/libC.a[shr2_64.o]

9001000a0323738 0K read/write /usr/lib/libC.a[shr2_64.o]

9000000008ec800 1699K read/exec /usr/lib/libC.a[ansi_64.o]

9001000a0324a00 277K read/write /usr/lib/libC.a[ansi_64.o]

9000000008c9b00 135K read/exec /usr/lib/libC.a[shr_64.o]

9001000a031db00 19K read/write /usr/lib/libC.a[shr_64.o]

900000000708180 1732K read/exec /usr/lib/libicuuc.a[shr_64.o]

9001000a036bdac 180K read/write /usr/lib/libicuuc.a[shr_64.o]

900000000493d80 2510K read/exec /usr/lib/libicui18n.a[shr_64.o]

9001000a0399148 270K read/write /usr/lib/libicui18n.a[shr_64.o]

900000000473200 91K read/exec /usr/lib/libsrc.a[shr_64.o]

9001000a01127a8 55K read/write /usr/lib/libsrc.a[shr_64.o]

90000000045a300 98K read/exec /usr/lib/libcorcfg.a[shr_64.o]

9001000a04147c8 18K read/write /usr/lib/libcorcfg.a[shr_64.o]

900000000b16200 750K read/exec /usr/lib/liblvm.a[shr_64.o]

9001000a03dd028 219K read/write /usr/lib/liblvm.a[shr_64.o]

900000000444f00 82K read/exec /usr/lib/libcfg.a[shr_64.o]

9001000a027b8f0 26K read/write /usr/lib/libcfg.a[shr_64.o]

90000000040e3a0 2K read/exec /usr/lib/libcrypt.a[shr_64.o]

9001000a0106948 0K read/write /usr/lib/libcrypt.a[shr_64.o]

90000000233c860 5K read/exec /usr/lib/libc.a[aio_64.o]

9001000a0437568 0K read/write /usr/lib/libc.a[aio_64.o]

9000000003efc00 120K read/exec /usr/lib/libodm.a[shr_64.o]

9001000a0107cc8 40K read/write /usr/lib/libodm.a[shr_64.o]

900000000bd2c80 147K read/exec /usr/lib/libperfstat.a[shr_64.o]

9001000a041a960 14K read/write /usr/lib/libperfstat.a[shr_64.o]

900000000bf8000 0K read/exec /usr/lib/libdl.a[shr_64.o]

9001000a041f000 0K read/write /usr/lib/libdl.a[shr_64.o]

9000000024ac100 8680K read/exec /oracle/product/10g/lib/libjox10.a[shr.o]

8001000a0000ca0 588K read/write /oracle/product/10g/lib/libjox10.a[shr.o]

900000000a96000 257K read/exec /usr/lib/libpthreads.a[shr_xpg5_64.o]

9001000a0283000 559K read/write /usr/lib/libpthreads.a[shr_xpg5_64.o]

900000000000800 4025K read/exec /usr/lib/libc.a[shr_64.o]

9001000a0000020 1047K read/write /usr/lib/libc.a[shr_64.o]

Total 123902K

在上表中，标记为read/write的内存即是进程的私有内存，每个会话大概占用内存近6M,数据库大概有80多个会话,占用内存大概,占用内存大概500M左右.

数据库参数配置

SQL> select sum(PGA_ALLOC_MEM)/1024/1024/1024,count(*) from v$process;

SUM(PGA_ALLOC_MEM)/1024/1024/1024 COUNT(*)

--------------------------------- ----------

2.46758329 84

SQL> show parameter pga;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

_pga_max_size big integer 500M

pga_aggregate_target big integer 2000M

SQL> show parameter sga

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

lock_sga boolean FALSE

pre_page_sga boolean FALSE

sga_max_size big integer 12000M

sga_target big integer 10000M

SQL> show sga;

Total System Global Area 1.2583E+10 bytes

Fixed Size 2117744 bytes

Variable Size 7600082832 bytes

Database Buffers 4966055936 bytes

Redo Buffers 14655488 bytes

当前系统整体ORACLE使用内存汇总：sga 12G+pga 2.5G+process 0.5G,大概占用内存15G,留给系统内存6G左右,系统使用大量交换分区,导致系统性能下降,最明显的为:shared pool相关latch等待异常,具体awr为:

因为系统因为个别session需要大量内存设置_pga_max_size参数,导致部分会话系统占用2.5g内存,建议设置该参数为默认值,并对个别会话独立设置,设置pga_aggregate_target=1.5G,sga_target=sga_max_size=8.5G,awr结果为: