31.1.5 Thresholds

Each expression has a threshold value that triggers an alert. The THRESHOLD keyword associates that value with an alert level: either an Notice, Warning, or Critical alert.

For example, the expression for the performance advisor, “Thread Cache Size May Not Be Optimal”, is:

100-((%Threads_created% / %Connections%) * 100) < THRESHOLD

The THRESHOLD is set at 95% for an Info level alert, 85% for a Warning alert, and 75% for a Critical alert, producing alerts of three different levels.

Expressions can be straightforward. The expression for “Binary Logging Not Enabled” (one of the Administration alerts) is:

%log_bin% == THRESHOLD

When the result is OFF, only one alert is triggered: a Warning level alert. You cannot just use the expression %log_bin% == "OFF", because this would not test binary logging against a threshold and so would not result in an alert.

Specify precise conditions when each expression should evaluated, to avoid false alarms. For example, the expression for the “MyISAM Key Cache Has Sub-Optimal Hit Rate” advisor is:

(%Uptime% > 10800) && (%Key_read_requests% > 10000) 
&& (100-((%Key_reads% / %Key_read_requests%) * 100) < THRESHOLD)

The first part of the expression, (%Uptime% > 10800), delays evaluating this expression until the system has been running for 10800 seconds (3 hours). When a server starts up, it might take a while to reach a state that is representative of normal operations. For example, the InnoDB buffer pool, MyISAM key cache, and the SQL query cache might require some time to fill up with application data, after which the cached data boosts performance.

In addition, if some part of the system is not heavily used, an alert might be triggered based on limited data. For example, if your application does not use the MyISAM storage engine, the “MyISAM Key Cache Has Sub-Optimal Hit Rate” advisor could be triggered based on very limited use of other MyISAM tables such as the mysql.user table. For this reason, this advisor has a second part: (%Key_read_requests% > 10000). The advisor is not evaluated unless there is plenty of activity associated with the key cache.