Influence of DB2 HADR on performanc

The db2trc command is a tracking tool provided by DB2. The tracking tool records information about the operation and formats the information into a readable format.

It is important to note that running a trace adds overhead, so enabling the trace tool may affect system performance.

Db2trace can track each function and the execution time of that function.

Using this property, we can calculate the time it takes to write each log and the overhead incurred by HADR.

First, we need to find the EDU (Engine Dispatchable Unit) that writes the log on the host, that is, the thread number of the db2loggw

# get the process number of db2sys

$ps-ef | grep-I db2sys | grep-I instref

Instref 15597584 14483602 3 10:24:08-6:33 db2sysc 0

# get the EDU number of db2loggw

$db2pd-edus | grep-I db2loggw

4372 4372 91947227 db2loggw (CTMPREF) 0 8.587111 3.557958

# Open db2trc monitoring and write it to the file

$db2trc on-t-p 15597584.4372-f trace.dump

Trace is turned on

# perform some db2 transactions operations

# disable monitoring

$db2trc off

Trace is turned off

# format file output

$db2trc flow-t trace.dump trace.flow

Total number of trace records: 861

Trace truncated: NO

Trace wrapped: NO

Number of trace records formatted: 861 (pid: 15597584 tid 4372 node: 0)

Interpretation of documents

263 18.318085568 sqlpgwlp entry [eduid 4372 eduname db2loggw]

..

359 18.319165218 | sqloWaitEDUWaitPost entry [eduid 4372 eduname db2loggw]

18.438925718 | sqloWaitEDUWaitPost exit

..

369 18.438940500 sqlpgwlp exit

Find the sqlpgwlp function

Find the sqloWaitEDUWaitPost in this function

Calculate the execution time of the sqlpgwlp. T1 = 18.438940500-18.318085568 = 0.120854932

Calculate the execution time of the sqloWaitEDUWaitPost. T2 = 18.438925718-18.319165218 = 0.119760500

T1 is the total time to complete this log, and T2 is the cost of HADR when writing the log.

If we track the log writing multiple times, we will get more than one of the above snippets. The average can be calculated based on all the fragments to get a more accurate percentage of the cost.