[Db2] MustGather: When too high CPU usage in Db2 server

DB2数据库CPU高的收集方法
Question & Answer

Question
System performance degraded due to high CPU usage, where the Db2 server is running.
What kind of documents should be gathered for investigating the source of problem?

Answer
When the CPU usage become high, determine which process / EDU is consuming CPU, and whether there is any active statement running with high load, from the document gathered while the problem is happening.
If you want to pursue the root cause, contact IBM technical support with materials and information described in this document.https://www.cndba.cn/hbhe0316/article/4980

1. Preparation

[Common for Linux/UNIX/Windows environment]

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

Download the .tar file from the following link, extract db2mon script file and place into working directory used for gathering materials.

db2mon script for monitoring performance

The db2mon script file depends on the version of Db2 server, therefore check the version by db2level command output and download correspond file for the Db2 version.
db2mon script monitors database activity for 30 seconds (default) and generates report.
From Db2 v11.1, the db2mon script file become installed under sqllib/samples/perf directory.

[Additional preparation for Windows environment]

In Windows environment, db2mon.sql file included in .tar file downloaded as above.
Following conditions need to be satisfied to use db2mon.sql.

USER TEMPORARY table space is required. When there is no USER TEMPORARY table space, create it before db2mon.sql was run. Following statement is the sample for creating USER TEMPORARY table space.

db2 create user temporary tablespace [database name]
Database configuration parameter MON_ACT_METRICS need to be at least BASE (which is default), and MON_REQ_METRICS need to be at least BASE (default is EXTENDED). Check the current setting of these parameters and modify if any of them was below the requirement, by using UPDATE DB CFG command.

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

db2 update db cfg using mon_act_metrics base
db2 update db cfg using mon_req_metrics base

1. Gathering materials while the problem (High CPU usage) is happening.

[Linux/UNIX environment]

In Linux/UNIX environment, login as an instance owner and issue following commands.

$ top -b -n 5 > top`date +”%Y%m%d%H%M%S”.out $ db2pd -eve > db2pd-eve_date +”%Y%m%d%H%M%S”`.out
$ db2pd -stack all
$ ./db2mon.sh [database name] > db2mondate +"%Y%m%d_%H%M%S".out

— wait one minute —

$ top -b -n 5 > top`date +”%Y%m%d%H%M%S”.out $ db2pd -eve > db2pd-eve_date +”%Y%m%d%H%M%S”`.out
$ db2pd -stack all
$ ./db2mon.sh [database name] > db2mondate +"%Y%m%d_%H%M%S".out

— wait one minute —

$ top -b -n 5 > top`date +”%Y%m%d%H%M%S”.out $ db2pd -eve > db2pd-eve_date +”%Y%m%d%H%M%S”`.out
$ db2pd -stack all
$ ./db2mon.sh [database name] > db2mondate +"%Y%m%d_%H%M%S".out
Replace [database name] with appropriate database name.

After the commands were completed, gather db2support. Please refer following technote for detail procedure.
[Db2] How to collect db2support (for UNIX/Linux platforms)https://www.cndba.cn/hbhe0316/article/4980

[Windows environment]

In Windows environment, login as “Administrator” and open “Db2 command Window as Administrator” and issue following commands.https://www.cndba.cn/hbhe0316/article/4980

db2 connect to [database name]

typeperf -sc 15 -si 1 “/process()/% User Time” “/thread()/% User Time” -f BIN -o typeperf“%DATE:/=%%TIME::=-%”.blg
db2pd -eve > db2pd_eve“%DATE:/=%%TIME::=-%”.out
db2pd -stack all
db2 -tvf db2mon.sql > db2mon“%DATE:/=%_%TIME::=-%”.out

— wait one minute —

typeperf -sc 15 -si 1 “/process()/% User Time” “/thread()/% User Time” -f BIN -o typeperf“%DATE:/=%%TIME::=-%”.blg
db2pd -eve > db2pd_eve“%DATE:/=%%TIME::=-%”.out
db2pd -stack all
db2 -tvf db2mon.sql > db2mon“%DATE:/=%_%TIME::=-%”.out

— wait one minute —

typeperf -sc 15 -si 1 “/process()/% User Time” “/thread()/% User Time” -f BIN -o typeperf“%DATE:/=%%TIME::=-%”.blg
db2pd -eve > db2pd_eve“%DATE:/=%%TIME::=-%”.out
db2pd -stack all
db2 -tvf db2mon.sql > db2mon“%DATE:/=%_%TIME::=-%”.out
Replace [database name] with appropriate database name.

After the commands were completed, gather db2support. Please refer following technote for detail procedure.
[Db2] How to collect db2support (for Windows platforms)

1. contact IBM technical support and provide materials.

db2pd -stack all command generates files under DIAGPATH with the name . .stack.txt (Linux/UNIX) or . .stack.bin (Windows). These files are included in db2support.zip file.
The other output file is created in current directory.
Regarding to DIAGPATH, please refer the following technote.
[Db2] output path of db2diag.log and administration notification log file https://www.cndba.cn/hbhe0316/article/4980https://www.cndba.cn/hbhe0316/article/4980

In Windows environment, db2pd -stack all command generates trap file with binary format, so please format *.stack.bin file in DIAGPATH directory by using db2xprt command.

example:

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

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

db2xprt 4194320.000.stack.bin 4194320.000.stack.fmt
Other tips

top command / typeperf command gathers information for 15 seconds, db2mon.sh shell script gathers for 30 seconds, and generates report file after completion.
When these command / shell script failed into hang and did not complete, interrupt by pressing Ctrl+C.
When you terminated db2mon shell script in the middle, for example by pressing Ctrl+C, CURRENT SCHEMA special register value might not be reset and left as SESSION_USER.
When db2pd -eve command failed into hang and did not complete, use following set of db2pd command as an alternative.

[Linux/UNIX environment]

$ db2pd -edus > db2pdedusdate +"%Y%m%d_%H%M%S".out
$ db2pd -agents > db2pdagentsdate +"%Y%m%d_%H%M%S".out
$ db2pd -db [database name] -apinfo > db2pdapinfodate +"%Y%m%d_%H%M%S".out
[Windows environment]

db2pd -edus > db2pdedus“%DATE:/=%%TIME::=-%”.out
db2pd -agents > db2pdagents“%DATE:/=%%TIME::=-%”.out
db2pd -db [database name] -apinfo > db2pdapinfo“%DATE:/=%%TIME::=-%”.outhttps://www.cndba.cn/hbhe0316/article/4980

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

DB2