DB2 CPU高案例

环境: db2 10.5.0.5, suse linux 11
问题：%usr CPU高，大约95%，db2sysc进程使用的最多
数据收集：
在问题出现期间，收集了以下数据：https://www.cndba.cn/hbhe0316/article/4803https://www.cndba.cn/hbhe0316/article/4803https://www.cndba.cn/hbhe0316/article/4803

vmstat 1 10 > vmstat.1
ps -elf > pself.1
ps aux > psaux.1
iostat 1 10 > iostat.1
db2pd -eve > db2pd_eve.out
db2pd -edus interval=120 > db2pd_edu_120.out
db2pd -stack all dumpdir=/tmp
db2 GET SNAPSHOT FOR APPLICATIONS ON <db_name> GLOBAL > applications.log
db2 GET SNAPSHOT FOR DATABASE ON <db_name> GLOBAL > DB.log
db2 get snapshot for all on <db_name> > dbsnap

1.iostat 输出显示 user CPU 很高.https://www.cndba.cn/hbhe0316/article/4803

avg-cpu: %user %nice %system %iowait %steal %idle

89.42 0.01 4.55 1.89 0.00 4.12

avg-cpu: %user %nice %system %iowait %steal %idle

94.41 0.00 5.59 0.00 0.00 0.00

avg-cpu: %user %nice %system %iowait %steal %idle

95.42 0.00 4.58 0.00 0.00 0.00

avg-cpu: %user %nice %system %iowait %steal %idle

94.67 0.00 5.33 0.00 0.00 0.00

2.ps aux的输出显示db2sysc进程使用了最多的CPUhttps://www.cndba.cn/hbhe0316/article/4803

USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND

root 1 0.0 0.0 10540 884 ? Ss Oct15 0:01 init [5]

root 2 0.0 0.0 0 0 ? S Oct15 0:00 [kthreadd]

...

root 7065 0.0 0.2 1029412 75296 ? Sl Oct15 0:00 db2wdog 0 [db2inst1]

db2inst1 7067 1127 69.2 29760484 22761184 ? Sl Oct15 13895:25 db2sysc 0

root 7069 0.0 0.1 1035728 47704 ? S Oct15 0:02 db2ckpwd 0

root 7070 0.0 0.1 1035728 47632 ? S Oct15 0:02 db2ckpwd 0

root 7071 0.0 0.1 1035728 47632 ? S Oct15 0:02 db2ckpwd 0

db2inst1 7079 0.0 0.1 502756 41276 ? S Oct15 0:00 db2vend (PD Vendor Process - 1) 0

db2inst1 7089 0.0 0.1 592156 33260 ? Sl Oct15 0:22 db2acd 0 ,0,0,0,1,0,0,0,0000,1,0,995bc4,14,1e014,2,0,1,41fc0,0x210000000,0x210000000,1600000,c8010,2,128021

...

root 27022 50.0 0.0 4940 972 pts/1 R+ 13:59 0:00 ps aux

root 27499 0.0 0.0 0 0 ? S 12:06 0:00 [kworker/9:0]

patrol 27604 0.0 0.0 11300 1048 ? Ss Oct15 0:00 /bin/bash

patrol 27605 0.0 0.0 11300 1052 ? Ss Oct15 0:00 /bin/bash

root 28654 0.0 0.0 0 0 ? S Oct15 0:01 [kworker/8:1]

3.db2pd_edu_120.out 输出按照对EDU按照占用的CPU时间排序

Database Member 0 -- Active -- Up 0 days 21:21:14 -- Date 2017-10-16-14.48.37.897471



List of all EDUs for database member 0



db2sysc PID: 7067

db2wdog PID: 7065

db2acd PID: 7089



EDU ID TID Kernel TID EDU Name USR (s) SYS (s) USR DELTA SYS DELTA

========================================================================================================================================
140354128963328 28988 db2agent (SAMPLE) 0 24280.590000 478.060000 42.250000 1.080000
140354313512704 16300 db2agent (SAMPLE) 0 23898.440000 454.140000 40.980000 0.900000
140354531616512 16316 db2agent (SAMPLE) 0 23530.870000 446.600000 40.830000 0.850000
140353910859520 348 db2agent (SAMPLE) 0 22660.420000 430.300000 40.600000 1.000000
140354514839296 16324 db2agent (SAMPLE) 0 23262.550000 443.450000 40.640000 0.900000
140354464507648 16327 db2agent (SAMPLE) 0 22989.210000 438.320000 40.660000 0.880000
140354061854464 13529 db2agent (SAMPLE) 0 22994.820000 436.550000 40.540000 0.900000
140354900715264 8439 db2agent (SAMPLE) 0 23656.830000 450.830000 40.540000 0.840000
140354162517760 21738 db2agent (SAMPLE) 0 23687.730000 450.920000 40.430000 0.920000
140354196072192 21722 db2agent (SAMPLE) 0 23498.360000 445.650000 40.440000 0.900000
140353961191168 21841 db2agent (SAMPLE) 0 22711.160000 427.530000 40.450000 0.850000
140354883938048 16295 db2agent (SAMPLE) 0 23338.770000 443.420000 40.270000 1.020000
140353776641792 15710 db2agent (SAMPLE) 0 22876.610000 435.050000 40.340000 0.920000
140354112186112 29588 db2agent (SAMPLE) 0 23423.810000 446.610000 40.340000 0.900000
140353994745600 16907 db2agent (SAMPLE) 0 22886.840000 433.260000 40.330000 0.910000
140354145740544 28987 db2agent (SAMPLE) 0 23631.250000 450.080000 40.220000 1.010000
140354816829184 16299 db2agent (SAMPLE) 0 23291.960000 443.260000 40.250000 0.980000
140354095408896 6654 db2agent (SAMPLE) 0 23276.780000 445.550000 40.270000 0.920000
140353977968384 18404 db2agent (SAMPLE) 0 22218.640000 419.450000 39.990000 0.960000
140354917492480 8433 db2agent (SAMPLE) 0 23387.590000 442.670000 39.970000 0.930000
140353927636736 23958 db2agent (SAMPLE) 0 22597.700000 427.340000 39.910000 0.970000
140354011522816 15711 db2agent (SAMPLE) 0 22962.390000 437.080000 39.990000 0.870000
140354867160832 16296 db2agent (SAMPLE) 0 23466.230000 448.500000 39.830000 1.020000
140354229626624 19731 db2agent (SAMPLE) 0 23581.980000 450.790000 39.910000 0.920000
140353894082304 1767 db2agent (SAMPLE) 0 22448.450000 423.550000 39.840000 0.930000
140354078631680 7525 db2agent (SAMPLE) 0 22930.950000 437.310000 39.740000 0.870000
140353877305088 2185 db2agent (SAMPLE) 0 22260.260000 423.640000 39.640000 0.910000

…

https://www.cndba.cn/hbhe0316/article/4803

4.这里以EDU ID 110为例子，可以通过db2pd_eve.out找到其执行的SQL语句(这里可能不准确，因为抓取的是当前正在执行的SQL, 推荐结合mon_get_pkg_cache_stmt的输出)https://www.cndba.cn/hbhe0316/article/4803

Dynamic SQL Statements:

Address AnchID StmtUID NumEnv NumVar NumRef

NumExe Text

...

0x00007FAC67B6C640 740 1 1 1 41 41

select * from TEST1 where KPICODE like '2222|!|!|%' order

by DCTIME desc fetch first 20 rows only with ur

...

5.Application SNAPSHOT显示sort、rows read都非常高https://www.cndba.cn/hbhe0316/article/4803

Application Snapshot



Application handle = 109

Application status = UOW Executing

..

Rows inserted = 12335

Rows updated = 0

Rows selected = 25680

Rows read = 175961810541

Rows written = 12335

..

Total sorts = 1284

..

Total User CPU Time used by agent (s) = 24801.492324

Total System CPU Time used by agent (s) = 0.000000



Agent process/thread ID = 110

6.剩下的工作是：可以按照相同的方法找到其他占用CPU的SQL，并进行调优。当然，实际上可能并没有这么简单，需要多分析应用的快照，可以看到SQL语句是什么时候开始执行的？执行了多长时间？执行SQL语句之前是什么状态？同一时刻有多少SQL语句在执行？平时有那么多吗？等等，这些都可以从应用的快照中获取。所以应用的快照也是必收数据，而且要多收几次。https://www.cndba.cn/hbhe0316/article/4803https://www.cndba.cn/hbhe0316/article/4803

版权声明：本文为博主原创文章，未经博主允许不得转载。

Linux,oracle