IBM Support

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

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.


1. Preparation

[Common for Linux/UNIX/Windows environment]

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.
     
    db2 update db cfg using mon_act_metrics base
    db2 update db cfg using mon_req_metrics base


2. 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] > db2mon_`date +"%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] > db2mon_`date +"%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] > db2mon_`date +"%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)


[Windows environment]

In Windows environment, login as "Administrator" and open "Db2 command Window as Administrator" and issue following commands.

> 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)

3. contact IBM technical support and provide materials.

db2pd -stack all command generates files under DIAGPATH with the name <PID>.<TID>.stack.txt (Linux/UNIX) or <PID>.<TID>.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

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:

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 > db2pd_edus_`date +"%Y%m%d_%H%M%S"`.out
    $ db2pd -agents > db2pd_agents_`date +"%Y%m%d_%H%M%S"`.out
    $ db2pd -db [database name] -apinfo > db2pd_apinfo_`date +"%Y%m%d_%H%M%S"`.out

    [Windows environment]

    > db2pd -edus > db2pd_edus_"%DATE:/=_%_%TIME::=-%".out
    > db2pd -agents > db2pd_agents_"%DATE:/=_%_%TIME::=-%".out
    > db2pd -db [database name] -apinfo > db2pd_apinfo_"%DATE:/=_%_%TIME::=-%".out


Reference
db2pd - Monitor and troubleshoot Db2 database command
Collecting and reporting performance monitor data with db2mon
db2xprt - Format trap file command

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"v9.7;v10.1;v10.5;v11.1;v11.5","Edition":""}]

Document Information

Modified date:
31 January 2020

UID

ibm11284136