When it comes to blocking, DPA can currently alert when the cumulative blocking wait time goes beyond a threshold. The alert can be found under the category of Wait Time and is named Total Blocking Wait Time. However, one drawback to alerting on cumulative blocking time, is that it can potentially be noisy in a busy server. For example, lets take 2 blocking scenarios:
- 10 root blocker sessions each blocking 20 other sessions for 15 seconds
- 1 root blocker session blocking 2 other sessions for 150 seconds
The base DPA alert would calculate both of these as 300 cumulative seconds of blocking wait time. Depending on your environment, both could potentially be bad, but I would probably want an alert for the second case and not the first. How can we tell DPA to alert differently.
Custom Alerts in DPA allow you to extend the capability of the alerting feature. If you can write a SQL statement to do what you want, that SQL can be plugged into DPA. In my case, rather than alert on cumulative blocking wait time, I want to alert if a session has been blocking for more than 3 minutes of clock time. For this, we can use a query similar to this:
SELECT CONCAT('blocking_trx_id: ', blocking_trx_id, ' || blocking_query: ', blocking_query, ' || waiting_trx_id: ', waiting_trx_id, ' || waiting_query: ',waiting_query, ' || locked_table: ', locked_table) AS BlockingDetails, wait_age_secsFROM sys.innodb_lock_waits;
We can configure a custom alert in DPA to execute this once a minute to perform the check. Below is a screenshot of the alert and when creating it, choose Custom SQL Alert - Multiple Numeric Return. In my example, I set thresholds for warning and critical at 1 minute (60 seconds) and 3 minutes (180 seconds), but adjust accordingly for your requirements. The thresholds will be compared to the value of wait_age_secs column that is the 2nd column of the query above.
