How to resolve slow downs induced by HADR_FLAGS = STANDBY_RECV_BLOCKED?

Question

We had a severe slow down of our applications in our HADR environment. We are seeing the following when we run db2pd -hadr:

HADR_FLAGS = STANDBY_RECV_BLOCKED
STANDBY_RECV_BUF_PERCENT = 100
STANDBY_SPOOL_PERCENT = 100

These recovered later and seem better now with STANDBY_SPOOL_PERCENT coming down gradually. Can you please help understand the implications of the values of the above parameters and what needs to be done to ensure we don't get into such a situation?

Answer 1

This isssue is most likely triggered by a peak amount of transactions occuring on the primary. The standby receive buffer and spool got saturated. Unless you are running with configuration parameter HADR_SYNCMODE in SUPERASYNC mode, you could fall into this situation. The slow down on application was induced by the primary waiting for an acknowledgement from the standby that it had received the log file, but since its spool and receive buffers were full at the time, the standby was delaying this acknowledgement.

You could consider setting HADR_SYNCMODE to SUPERASYNC, but this would also mean that the system will be more vulnerable to data loss should there be a failure on the primary. To manage these temporary peaks, you can make either of the following configuration changes:

• Increase the size of the log receive buffer on the standby database by modifying the value of the DB2_HADR_BUF_SIZE registry variable.
• Enable log spooling on a standby database by setting the HADR_SPOOL_LIMIT

For further details, you can refer to HADR Performance Guide