Each expression has a threshold value that triggers an alert. The THRESHOLD keyword is used to associate that value with an alert level—either an Info, 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 quite simple. 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. In this situation you might think we could just use the expression %log_bin% == "OFF". However, doing this would not test binary logging against a threshold so would not result in an alert.

When you create an expression, think carefully about the conditions under which it should be evaluated and the conditions under which it should not. For example, the expression for the “MyISAM Key Cache Has Sub-Optimal Hit Rate” rule is:

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

The essence of the rule is really: (100-((%Key_reads% / %Key_read_requests% ) * 100) < THRESHOLD). However, when a server is first starting up, it may take a while to reach a state that is representative of normal operations. For example, the key cache and the query cache may need some period of time before they have cached typical application data as opposed to start-up and initialization data. In this case, the first part of the expression, (%Uptime% > 10800), holds off evaluating this expression until the system has been running for 10800 seconds (3 hours).

In addition, if some part of the system is not heavily used an alert may 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” rule may 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)–meaning the rule won't be evaluated unless there is plenty of activity associated with the key cache.

In other circumstances, there may be periods of time during which you don't want a rule to be evaluated—a blackout period. For example, the expression for the “Slave Too Far Behind Master” rule is: %Seconds_Behind_Master% > THRESHOLD. However, suppose you run a backup process between 6 and 7 pm on a replication slave, and it's normal for that slave to get behind the master by an amount more than the THRESHOLD during that time. In that case you don't want to receive an alert because the rule violation is expected. You can achieve this by adding the following to the expression: && CURTIME() NOT BETWEEN '18:00:00' AND '19:00:00' In essence, this means “don't trigger an alert between 18:00:00 and 19:00:00 (6 pm and 7 pm)”.