MTS环境下的诊断
公司的数据库服务器由于并发量高,所以基本上都是配置为MTS模式,最近在业务应用中经常出现应用连不上数据库的现象:
25库的诊断:
SQL> show parameter mts
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
mts_circuits integer 1655
mts_dispatchers string (PROTOCOL=TCP)(DISP=8)(MUL=ON)
mts_listener_address string
mts_max_dispatchers integer 8
mts_max_servers integer 100
mts_multiple_listeners boolean FALSE
mts_servers integer 50
mts_service string MEDIA
mts_sessions integer 1650
SQL> select name "NAME", substr(network,1,15) "PROTOCOL",
2 status "STATUS", (busy/(busy + idle)) * 100 "%TIME BUSY",owned
3 from v$dispatcher;
NAME PROTOCOL STATUS %TIME BUSY OWNED
---- ------------------------------ ---------------- ---------- ----------
D000 (ADDRESS=(PROTO WAIT 11.8028789 5
D001 (ADDRESS=(PROTO WAIT 12.0602262 5
D002 (ADDRESS=(PROTO WAIT 11.9456599 7
D003 (ADDRESS=(PROTO WAIT 12.0674088 5
D004 (ADDRESS=(PROTO WAIT 12.1125251 7
D005 (ADDRESS=(PROTO WAIT 12.011585 6
D006 (ADDRESS=(PROTO WAIT 12.1310639 3
D007 (ADDRESS=(PROTO WAIT 12.0204903 6
8 rows selected.
目前的dispatcher配置为8,可以看到%TIME BUSY基本都在11-12%,说明dispatcher配置太少,需要增加.
SQL> select paddr,type,queued,wait,totalq,
2 decode(totalq,0,0,(wait/totalq)) "AVG WAIT" from v$queue;
PADDR TYPE QUEUED WAIT TOTALQ AVG WAIT
---------------- ---------- ---------- ---------- ---------- ----------
00 COMMON 0 0 425562139 0
00000004405BA120 DISPATCHER 0 0 56056358 0
00000004405BA550 DISPATCHER 0 0 57230661 0
00000004405BA980 DISPATCHER 1 0 56897821 0
00000004405BADB0 DISPATCHER 0 0 57154149 0
00000004405BB1E0 DISPATCHER 0 0 57497959 0
00000004405BB610 DISPATCHER 0 0 57225043 0
00000004405BBA40 DISPATCHER 0 0 57827243 0
00000004405BBE70 DISPATCHER 0 0 57021189 0
基本没有等待,等待的时间都为0,说明SHARE_SERVER数量比较足够.
SQL> select name "NAME", paddr,requests,
2 (busy/(busy + idle)) * 100 "%TIME BUSY",status from v$shared_server;
NAME PADDR REQUESTS %TIME BUSY STATUS
---- ---------------- ---------- ---------- ----------------
S000 00000004405ACFC0 225836532 3.06002999 WAIT(COMMON)
S001 00000004405AD3F0 113034297 3.28636972 WAIT(COMMON)
S002 00000004405AD820 51400128 3.29608179 WAIT(COMMON)
S003 00000004405ADC50 20309539 2.70136096 WAIT(COMMON)
S004 00000004405AE080 7571731 1.79895591 WAIT(COMMON)
S005 00000004405AE4B0 3175516 1.12329658 WAIT(COMMON)
S006 00000004405AE8E0 1549021 .726787415 WAIT(COMMON)
S007 00000004405AED10 867089 .523360051 WAIT(COMMON)
S008 00000004405AF140 536906 .386752886 WAIT(COMMON)
S009 00000004405AF570 334536 .317013223 WAIT(COMMON)
S010 00000004405AF9A0 229161 .255669898 WAIT(COMMON)
NAME PADDR REQUESTS %TIME BUSY STATUS
---- ---------------- ---------- ---------- ----------------
S011 00000004405AFDD0 177560 .208211866 WAIT(COMMON)
S012 00000004405B0200 125832 .177957762 WAIT(COMMON)
S013 00000004405B0630 95591 .156566387 WAIT(COMMON)
S014 00000004405B0A60 72660 .131092193 WAIT(COMMON)
S015 00000004405B0E90 57029 .116133285 WAIT(COMMON)
S016 00000004405B12C0 43610 .103641594 WAIT(COMMON)
S017 00000004405B16F0 36493 .09111092 WAIT(COMMON)
S018 00000004405B1B20 28000 .079879957 WAIT(COMMON)
S019 00000004405B1F50 20440 .073962302 WAIT(COMMON)
S020 00000004405B2380 17878 .066547118 WAIT(COMMON)
S021 00000004405B27B0 17546 .053228084 WAIT(COMMON)
NAME PADDR REQUESTS %TIME BUSY STATUS
---- ---------------- ---------- ---------- ----------------
S022 00000004405B2BE0 13478 .046659531 WAIT(COMMON)
S023 00000004405B3010 11612 .041468349 WAIT(COMMON)
S024 00000004405B3440 10772 .035766971 WAIT(COMMON)
S025 00000004405B3870 9432 .032666139 WAIT(COMMON)
S026 00000004405B3CA0 7731 .02839625 WAIT(COMMON)
S027 00000004405B40D0 7304 .025667882 WAIT(COMMON)
S028 00000004405B4500 6021 .020601241 WAIT(COMMON)
S029 00000004405B4930 5631 .021971391 WAIT(COMMON)
S030 00000004405B4D60 5062 .016303288 WAIT(COMMON)
S031 00000004405B5190 4090 .013538823 WAIT(COMMON)
S032 00000004405B55C0 3681 .011702391 WAIT(COMMON)
NAME PADDR REQUESTS %TIME BUSY STATUS
---- ---------------- ---------- ---------- ----------------
S033 00000004405B59F0 3377 .010594344 WAIT(COMMON)
S034 00000004405B5E20 3282 .008085622 WAIT(COMMON)
S035 00000004405B6250 2583 .00568475 WAIT(COMMON)
S036 00000004405B6680 2305 .004684337 WAIT(COMMON)
S037 00000004405B6AB0 2436 .007306083 WAIT(COMMON)
S038 00000004405B6EE0 2168 .004402171 WAIT(COMMON)
S039 00000004405B7310 2060 .004361161 WAIT(COMMON)
S040 00000004405B7740 2172 .002052297 WAIT(COMMON)
S041 00000004405B7B70 1810 .002832208 WAIT(COMMON)
S042 00000004405B7FA0 1905 .001652125 WAIT(COMMON)
S043 00000004405B83D0 1678 .001118526 WAIT(COMMON)
NAME PADDR REQUESTS %TIME BUSY STATUS
---- ---------------- ---------- ---------- ----------------
S044 00000004405B8800 1382 .000815816 WAIT(COMMON)
S045 00000004405B8C30 1578 .001636751 WAIT(COMMON)
S046 00000004405B9060 1471 .001026185 WAIT(COMMON)
S047 00000004405B9490 1375 .000738855 WAIT(COMMON)
S048 00000004405B98C0 1438 .000482308 WAIT(COMMON)
S049 00000004405B9CF0 1589 .000697804 WAIT(COMMON)
50 rows selected.
SQL> SELECT decode(totalq,0,'No Requests') "Wait Time",Wait,totalq,
2 Wait/totalq ||'hundredths of seconds' "Average Wait time per request"
3 FROM V$QUEUE
4 WHERE type = 'COMMON';
Wait Time WAIT TOTALQ
----------- ---------- ----------
Average Wait time per request
-------------------------------------------------------------
0 425770822
0hundredths of seconds
SQL> select maximum_connections "MAX CONN",
2 servers_started "STARTED",
3 servers_terminated "TERMINATED",
4 servers_highwater "HIGHWATER" from v$mts;
MAX CONN STARTED TERMINATED HIGHWATER
---------- ---------- ---------- ----------
410 0 0 50
目前50个SHARE SERVER进程都没有什么压力.
但是每次大概在1小时内会有10秒钟应用连接不上的情况.但是也没有任何的错误信息出现,通过LSNRCTL SERVICES也看不到任何的REJECT的情况.
解决:现在的压力都在dispatcher环节,增大dispatcher的数量成为关键.
以前为8,打算增加mts_dispatchers到12,最大mts_max_dispatchers到15.
需要修改的参数:mts_dispatchers跟mts_max_dispatchers
36库:
通过lsnrctl services发现有reject的现象发生:
SQL> show parameter mts
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
mts_circuits integer 885
mts_dispatchers string (PROTOCOL=TCP)(disp=3)(mul=on)
mts_listener_address string
mts_max_dispatchers integer 5
mts_max_servers integer 30
mts_multiple_listeners boolean FALSE
mts_servers integer 15
mts_service string WapMed
mts_sessions integer 880
mts_dispatchers=3
mts_max_dispatchers=5
SQL> select name "NAME", substr(network,1,15) "PROTOCOL",
2 status "STATUS", (busy/(busy + idle)) * 100 "%TIME BUSY",owned
3 from v$dispatcher;
NAME PROTOCOL STATUS %TIME BUSY OWNED
---- ------------------------------ ---------------- ---------- ----------
D000 (ADDRESS=(PROTO WAIT 1.66110661 2
D001 (ADDRESS=(PROTO WAIT 1.69761039 4
D002 (ADDRESS=(PROTO WAIT 1.6862683 2
%TIME BUSY很小,说明dispatcher足够,不是太忙.
SQL> select paddr,type,queued,wait,totalq,
2 decode(totalq,0,0,(wait/totalq)) "AVG WAIT" from v$queue;
PADDR TYPE QUEUED WAIT TOTALQ AVG WAIT
-------- ---------- ---------- ---------- ---------- ----------
00 COMMON 0 -1.452E+09 1284068818 -1.1305514
80A3C038 DISPATCHER 0 -61197047 1754990112 -.03487031
80A3C3E8 DISPATCHER 0 387019223 1822122947 .21240017
80A3C798 DISPATCHER 0 -988291430 1813798585 -.54487386
SQL> select name "NAME", paddr,requests,
2 (busy/(busy + idle)) * 100 "%TIME BUSY",status from v$shared_server;
NAME PADDR REQUESTS %TIME BUSY STATUS
---- -------- ---------- ---------- ----------------
S000 80A38C98 79912335 19.7542792 WAIT(COMMON)
S001 80A388E8 202651198 1.60985155 WAIT(COMMON)
S002 80A39048 43796144 16.2156604 WAIT(COMMON)
S003 80A3BC88 108447916 6.76056985 WAIT(COMMON)
S004 80A397A8 52865390 9.79095707 WAIT(COMMON)
S005 80A3ADC8 37436093 3.8625168 WAIT(COMMON)
S006 80A393F8 7863414 6.34321002 WAIT(COMMON)
S007 80A3DDB8 11046687 4.25535939 WAIT(COMMON)
S008 80A3A2B8 7496565 3.22494009 WAIT(COMMON)
S009 80A3A668 1280234 2.55571773 WAIT(COMMON)
S010 80A3AA18 239202 1.19580518 WAIT(COMMON)
NAME PADDR REQUESTS %TIME BUSY STATUS
---- -------- ---------- ---------- ----------------
S011 80A39F08 495816 1.40916284 WAIT(COMMON)
S012 80A3CEF8 217261 .819671784 WAIT(COMMON)
S013 80A3B528 34015 .365254185 WAIT(COMMON)
S014 80A3D2A8 7288 .131093163 WAIT(COMMON)
15 rows selected.
%TIME BUSY的值有点高,说明share_server值低
SQL> SELECT decode(totalq,0,'No Requests') "Wait Time",Wait,totalq,
2 Wait/totalq ||'hundredths of seconds' "Average Wait time per request"
3 FROM V$QUEUE
4 WHERE type = 'COMMON';
Wait Time WAIT TOTALQ
----------- ---------- ----------
Average Wait time per request
-------------------------------------------------------------
1566771426 1284089731
1.22014169896044437723176527762451205211hundredths of seconds
进一步说明了share_server的值低.
目前mts_servers为15
mts_max_servers为30
打算修改
mts_servers为30
mts_max_servers为50
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28419/viewspace-604018/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/28419/viewspace-604018/