Db2 HADR Performance Tuning using DB2 Log Scanner, HADR Simulator, and HADR Calculator

About this task

Use this procedure to tune your network and to select the optimal sync mode for HADR.

Procedure

  1. Use the Db2 Log Scanner to scan log files generated in your workload cycle.

    For example, if you workload cycles each day, then you need to scan your log files within that 24 hour period. If you have special processing window such as weekly reporting, or monthly table reorg, scan logs from these windows too.

    Ideally, you want to do this with HADR disabled, to establish a base line for performance study. If you already have HADR enabled and your business requires HADR to stay enabled, you can still scan the logs and explore various sync modes and parameters.

    Scanner command example: 
    db2logscan_linux S0003158.LOG S0003159.LOG ... S0003214.LOG > daily.scan

    You will need to provide the log files as arguments. A single scanner binary can scan files from any DB2 version and any endian. The scanner will auto detect endian and DB2 version where the files are generated. The scanner writes output to stdout. Therefore we redirect stdout to a file.

    To scan a large number of files, you can create a file with the file names in it, one per line, then feed the file to the scanner through the -f option. If some files need to be retrieved from the archive, you can use the -retrieve option.

    Note: You do not need to scan all files in one command. As long as you save the output files, you can scan the files using multiple invocations of the scanner. However, it is recommended that consecutive files be scanned using one command so that the scanner can process records spanning file boundary and treat files as one continuous stream), and generate distribution data for logging metrics as one set.

    Make sure that you save all output files from the scanner. We will need them in later steps.

  2. Use the Db2 HADR simulator to measure performance of your logging disks.

    Disk speed is modeled as: write_time = per_write_overhead + data_amount / transfer_rate

    On a traditional hard drive, the majority of per_write_overhead is spent on the disk head moving to a given sector on a given seek time. Once the disk head reaches the sector, data can be read or written at a constant transfer rate. On Solid State Drive (SSD) devices, the per_write_overhead is very small or negligible.

    Here, you will use a small log write and a big log write to get the per_write_overhead as well as the disk transfer rate. The in command is the actual log path of the database. The file testFile will be generated by simulator.

    The HADR simulator does not automatically remove the file, therefore you'll be able to examine sector layout on the disk after the test run if needed. Once you are done, make sure that you remove this file.

    Command for small write (single page write):

    simhadr_linux -write <logPath>/testFile -flushSize 1

    Sample output:

    Total 9409 writes in 4.000160 seconds, 0.000425 sec/write, 1 pages/writeTotal 38.539264 MBytes written in 4.000160 seconds. 9.634431 MBytes/sec

    In this example, the per_write_overhead is 0.000425 second.

    Command for big write (1000 pages per write):

    simhadr_linux -write /testFile -flushSize 1000

    Sample output:

    Total 174 writes in 4.014913 seconds, 0.023074 sec/write, 1000 pages/write
Total 712.704000 MBytes written in 4.014913 seconds. 177.514183 MBytes/sec

    This means transfer rate is 177.514183 MBytes/sec.

    Theoretically, you can feed the numbers from the two runs into a equation system (linear equation y = a*x + b) to get the two unknowns, but the result will be very close to the simplified method shown above. The error margin of the input (variation from run to run) will be greater than the additional accuracy gained from the formal method. Thus there is no need to do more complicated math.

    In step 4, you will use -disk 177.5 0.000425 as the disk speed option for the HADR calculator, with transfer rate from the 1000 page test and per_write_overhead from the single page test.

    Note: The disk read and write speeds are usually close. And we are mostly concerned about write speed. Thus only write test is needed.
    Important: Run the test only when database is offline or when logging load is zero or very low, as the test will stress the logging disk and slow down database logging.

    Perform the test on both the HADR primary and standby hosts. It is recommended that primary and standby have the same hardware.

    As the HADR calculator assumes same disk speed on primary and standby hosts, it only accepts one set of disk speeds as argument. The test on primary and standby disks is intended to verify that the disks indeed have same speed. If different disks are used, use the slower speed for HADR calculator.

  3. Next, measure the HADR primary/standby network speed.

    Network speed is specified by two numbers: send rate and round trip time. Round trip time can be easily measured by the "ping" command. Send rate may be sensitive to socket buffer size. A special procedure is used for the measurement.

    This step only provides the tentative socket buffer size based on TCP requirement. Final socket buffer size will also depend on the workload and flush size. It will be determined in step 4. Remember to configure DB2 with the chosen socket buffer size. Otherwise, Db2 will not receive the send rate from the simulator test.

    In step 4, use -network <send_rate> <round_trip_time> for network speed option for HADR calculator.

  4. Feed the scanner output, from step 1, into the HADR calculator.

    Example:

    hadrCalculator.pl -disk 200 0.001 -network 10 0.1 <scanOutFile1> ... <scanOutFileN> >
        daily.scan.calc

    The HADR Calculator writes the output to stdout. So we redirect its stdout to a file. In this example, the file is daily.scan.calc".

    Now look at the calculator output. Look for question mark ("?") in the output. The HADR Calculator uses question marks ?, ??, and ??? to indicate small, medium, and heavy impact to applications when HADR is enabled. If no question mark is found, then there should be no impact to applications.

    Once you have chosen a sync mode, find the max flush size section at end of calculator output:
    • SYNC Max flush size: predicted 360 pages, workload max 1126 pages
    • NEARSYNC Max flush size: predicted 360 pages, workload max 1126 pages
    • ASYNC Max flush size: predicted 17 pages, workload max 1126 pages

    The HADR socket buffer size should be at least the "predicted" size of your chosen sync mode. This is the expected flush size for the given sync mode.

    Due to variations in workload, a larger size (such as workload max) is preferred. Both predicted and workload max flush sizes are computed from an average transaction size of log rate sample intervals. Actual workload may have peak size above the average. Thus, a number higher than workload max is preferred, as long as the size is reasonable (no more than 32MB). In the above example, a 2000 page (8MB) socket buffer size is recommended.

    Some systems may not perform well with large (over 32MB) socket buffer. It is recommended to cap the size at 32 MB. Most databases wouldn't hit this cap. 32MB (8000 pages) is a very large flush size.

    For verification, run HADR simulator using the chosen sync mode, socket buffer size, disk speed, and predicted flush size to confirm that the system will perform as expected. Even though this is simulation, the network is stressed with real data. A simulator run with the final parameters is strongly recommended before applying the parameters to the database.