DB2分析CPU高方法_db2 压测cpu高-CSDN博客

DB2服务器CPU使用率高，可以通过以下方式去分析。DB2 V9.5版本以前是多进程，想看哪个进程占用CPU和内存高分别使用ps aux --sort=-%cpu和ps aux --sort=-rss，V9.5版本以后是单进程多线程。

查看db2sysc进程的PID

[db2inst1]# ps -ef | grep db2sysc

查看db2sysc多线程

AIX：ps -emo THREAD -p <PID>

Linux：ps -lLfp <PID>

看下DB2 V9.5版本之前与之后多进程与多线程的区别（以主进程db2sysc为例）

DB2 V9.1：

[inst91]# ps -ef | grep db2sysc
inst91   21741 21740  0 12:48 pts/1    00:00:00 db2sysc 0   

[inst91]# ps -lLfp 21741
F S UID        PID  PPID   LWP  C NLWP PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
4 S inst91   21741 21740 21741  0    1  78   0 - 85875 msgrcv 12:48 pts/1    00:00:00 db2sysc 0

DB2 V10.5：

[inst105]# ps -ef | grep db2sysc
inst105   5797  5795  0 Jan21 ?        00:01:02 db2sysc 0                                      
 
[inst105]# ps -lLfp 5797
F S UID        PID  PPID   LWP  C NLWP PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
4 S inst105   5797  5795  5797  0   11  80   0 - 276364 184466 Jan21 ?       00:00:00 db2sysc 0                                      
1 S inst105   5797  5795  5798  0   11  78   0 - 276364 msgrcv Jan21 ?       00:00:11 db2sysc 0                                      
1 S inst105   5797  5795  5799  0   11  75   0 - 276364 -     Jan21 ?        00:00:08 db2sysc 0                                      
1 S inst105   5797  5795  5800  0   11  75   0 - 276364 semtim Jan21 ?       00:00:00 db2sysc 0                                      
1 S inst105   5797  5795  5801  0   11  78   0 - 276364 -     Jan21 ?        00:00:00 db2sysc 0                                      
1 S inst105   5797  5795  5802  0   11  58   - - 276364 -     Jan21 ?        00:00:00 db2sysc 0                                      
1 S inst105   5797  5795  5806  0   11  58   - - 276364 -     Jan21 ?        00:00:00 db2sysc 0                                      
1 S inst105   5797  5795  5808  0   11  75   0 - 276364 msgrcv Jan21 ?       00:00:00 db2sysc 0                                      
1 S inst105   5797  5795  5809  0   11  78   0 - 276364 -     Jan21 ?        00:00:13 db2sysc 0                                      
1 S inst105   5797  5795 27824  0   11  75   0 - 276364 -     Jan21 ?        00:00:00 db2sysc 0                                      
1 S inst105   5797  5795 25718  0   11  75   0 - 276364 semtim Jan21 ?       00:00:01 db2sysc 0

以DB2 V10.5为例，V9.5之后db2pd才有edu参数，查看EDU（默认以CPU排序）

[inst105]# db2pd -edu

Database Member 0 -- Active -- Up 1 days 16:47:30 -- Date 2022-01-22-13.42.39.849120

List of all EDUs for database member 0

db2sysc PID: 5797
db2wdog PID: 5795
db2acd  PID: 5811

EDU ID    TID                  Kernel TID           EDU Name                               USR (s)         SYS (s) 
========================================================================================================================================
234       47175950133568       25718                db2agent (idle) 0                      0.470000     1.230000
170       47175883024704       27824                db2agent (idle) 0                      0.130000     0.300000
17        47175992076608       5809                 db2resync 0                            0.430000    13.140000
16        47175996270912       5808                 db2ipccm 0                             0.040000     0.440000
15        47176000465216       5806                 db2wlmtm 0                             0.340000     0.160000
14        47176004659520       5802                 db2wlmt 0                              0.170000     0.050000
13        47176008853824       5801                 db2licc 0                              0.000000     0.000000
12        47176013048128       5800                 db2thcln 0                             0.000000     0.020000
11        47176017242432       5799                 db2alarm 0                             2.310000     6.240000
1         47175757195584       5798                 db2sysc 0                              2.420000     8.950000

根据EDUID找出对应的ApplHandl ID

[inst105]# db2pd -d dbname -app

查看对应ApplHand ID的快照信息

db2 get snapshot for application agentid <applHandl ID>

根据实际情况是否force掉正在执行的事务

db2 "force application(agentid)"

查看消耗CPU高的SQL

ORDER BY USER CPU

select 
NUM_EXECUTIONS,
TOTAL_USR_CPU_TIME,
dec(double(TOTAL_USR_CPU_TIME)/double(case when NUM_EXECUTIONS=0 then 1 else NUM_EXECUTIONS end),20,2) as AVG_USR_CPU_TIME,
TOTAL_USR_CPU_TIME_MS,
dec(double(TOTAL_USR_CPU_TIME_MS)/double(case when NUM_EXECUTIONS=0 then 1 else NUM_EXECUTIONS end),20,2) as AVG_USR_CPU_TIME_MS,
TOTAL_SYS_CPU_TIME,
TOTAL_SYS_CPU_TIME_MS,
TOTAL_EXEC_TIME,
TOTAL_EXEC_TIME_MS,
ROWS_READ,
ROWS_WRITTEN,
STMT_SORTS,
SORT_OVERFLOWS,
TOTAL_SORT_TIME,
STMT_TEXT 
from SYSIBMADM.SNAPDYN_SQL order by TOTAL_USR_CPU_TIME desc,TOTAL_USR_CPU_TIME_MS desc fetch first 30 rows only with ur

ORDER BY SYS USER

select 
NUM_EXECUTIONS,
TOTAL_USR_CPU_TIME,
TOTAL_USR_CPU_TIME_MS,
TOTAL_SYS_CPU_TIME,
TOTAL_SYS_CPU_TIME_MS,
TOTAL_EXEC_TIME,
TOTAL_EXEC_TIME_MS,
ROWS_READ,
ROWS_WRITTEN,
STMT_SORTS,
SORT_OVERFLOWS,
TOTAL_SORT_TIME,
STMT_TEXT 
from SYSIBMADM.SNAPDYN_SQL order by TOTAL_SYS_CPU_TIME desc,TOTAL_SYS_CPU_TIME_MS desc fetch first 30 rows only with ur