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)”.