Table of Contents
You can customize your MySQL Enterprise Monitor rules, advisors, and graphs, based on your organization's business rules, best practices, and the types of issues you can anticipate.
For common scenarios, reuse or edit the advisors and rules
provided by MySQL Enterprise. To create new advisors and rules
for your own needs, go to the Advisors tab
and choose the Create/Edit Rule link.
Similar existing rules are grouped together in advisor groups.
The built-in advisor groups are:
Administration
Heat Chart
Performance
Replication
Schema
Security
You can also create your own advisor group to suit your
circumstances. Click the
button. Enter an appropriate name and click the
button. The newly created group
appears in the Advisor column.
The newly created advisor is added to the list box of advisors shown in Figure 2.5, “MySQL Enterprise Dashboard: Editing Rules”. You can now use this category of advisors when you create a new rule.
You create rules using the same screen seen in Figure 2.5, “MySQL Enterprise Dashboard: Editing Rules”. To create a new rule with all-new settings, click the button. To create a rule similar to an existing one, click the button. You can edit any rule element during the copying process, unlike editing an existing rule.
You can change the rule name, the advisor group that a rule belongs to and you can set your own version number. In Figure 2.5, “MySQL Enterprise Dashboard: Editing Rules”, you have already seen how the threshold and frequency of a rule may be altered.
If you do not specify a version number for the new rule, the
version 1.0 is automatically added.
Most importantly, you can alter a rule's expression. Expressions are the core of a MySQL Enterprise Advisor and define the scenario being monitored. An expression can be as simple as a single server parameter or can be quite complex, combining multiple parameters with mathematical operations.
An expression has two main characteristics:
An expression tests whether a best practice is being violated.
The result of an expression must always be 1 or 0 (corresponding to true or false).
If an expression evaluates to true for a specific server, an alarm is raised, indicating that a best practice is not being followed. If an expression evaluates to false, the best practice is being followed and no alarm is raised.
For example, if you decide that enabling binary logging is a
best practice for a production server (as Oracle recommends),
then this best practice is violated if
log_bin is OFF.
Consequently, the expression for the “Binary Logging Not
Enabled” rule is “%log_bin% == OFF”. If this
evaluates to 1, an alarm is raised because the best practice is
not being followed.
An expression is made up of one or more variables and zero or more mathematical operators. The MySQL Enterprise Monitor uses the MySQL database server's expression parser and evaluator. For a complete list of operators and functions, see http://dev.mysql.com/doc/refman/5.0/en/functions.html. For a complete list of the built-in variables used to create rules, see http://dev.mysql.com/doc/refman/5.0/en/mysqld-option-tables.html.
Creating an expression is dependent on variables defined in the Variable Assignment frame. This frame links variables used in the expression field with data gathered from the target MySQL server instance: server status variables, operating system status information, and table information. Variable names are associated with elements in the Data Item drop-down list. To define more than one variable, click the button. For a complete listing of the data collection items used in creating rules, see Appendix D, Data Collection Items.
The remaining fields determine the information that you receive in a notification email or the informational pop-up window associated with each advisor.
When saving a new rule, choose a unique name not used by any existing rule.
When MySQL Enterprise Monitor evaluates an expression, it replaces variables with values. For example, part of the expression for the “MyISAM Key Cache Has Sub-Optimal Hit Rate” rule calculates the hit rate as follows:
100-((%Key_reads% / %Key_read_requests%)*100)
If the current value of %Key_reads% is 4522
and the current value of %Key_read_requests%
is 125989, the hit ratio is 96.4%:
100 -((4522 / 125989) * 100)
By convention, the Advisors supplied by MySQL use
‘%’ as the delimiter, for
example, %Key_reads%. This makes variables
more readily identifiable.
Variables can be used in the Description,
Advice, Action, and
Links attributes of a rule, as well as in
expressions. This lets you report the current value of an
expression. For instance, you can add the message, “The
current value of Key_reads is %Key_reads%.” to the
Advice text box. When this is displayed on
the screen, the value of %Key_reads% is
substituted into the text. If %Key_reads% has
a value of 4522, the message becomes
“The current value of Key_reads is 4522.”
Each expression has a threshold value that triggers an alert.
The THRESHOLD keyword associates 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 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” rule is:
(%Uptime% > 10800) && (%Key_read_requests% > 10000) » && (100-((%Key_reads% / %Key_read_requests%) * 100) < THRESHOLD)
The first part of the expression, (%Uptime% >
10800), holds off evaluating this expression until the
system has been running for 10800 seconds (3 hours). 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 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). The rule is
not evaluated unless there is plenty of activity associated with
the key cache.
If there are periods of time during which you don't want a rule
to be evaluated, you can define a “blackout
period”. For example, the expression for the “Slave
Too Far Behind Master” rule is:
%Seconds_Behind_Master% > THRESHOLD. You
might run a backup process between 6 and 7 pm on a replication
slave, during which it is normal for that slave to get
substantially behind the master. To avoid an alert during this
blackout period, add the following to the expression:
&& CURTIME() NOT BETWEEN '18:00:00' AND
'19:00:00'.
Enclose string values within double quotation marks in the
Expression or the
Thresholds text boxes. For example, the
expression for the “Slave I/O Thread Not Running”
rule is:
(%Slave_running% == "ON") && (%Slave_IO_Running% != THRESHOLD)
Similarly, the Critical Alerts threshold text
box is set to a value of "Yes".
When the expression is evaluated, either
"OFF" or "ON" is
substituted for %Slave_running%, and
"Yes" or "No" for
%Slave_IO_Running%, depending on the state of
your system. If the slave is running but the I/O thread is not,
the expression becomes:
("ON" == "ON") && ("No" != "Yes")
Without quotation marks, this expression would not evaluate to
TRUE as it should.
So that it is interpreted properly, the ==
operator is converted to = before being
passed to the MySQL expression parser.
When editing or defining a rule, you can enter text in Wiki
format in the Problem Description,
Advice, Recommended
Action, and Links and Further
Reading text boxes. You can format and highlight text
and add hyperlinks, using the notation listed in the following
table.
Table 4.1. MySQL Enterprise Monitor: Wiki Formatting
| Example | Description |
|---|---|
__bold__ | boldface text |
~~italic~~ | italicize text |
| \\ | create a line break |
| \\ \\ | create a double line break |
\\\\G | create a backslash |
*item 1 | create a bulleted list item |
#item 1 | create a numbered list item |
\_ | use the ‘\’ to escape special characters |
| {moreInfo:name|url} | create a hyperlink |
So the following Wiki text:
Replication is a __very nice feature__ of MySQL. Replication can be very
useful for solving problems in the following areas:
* Data Distribution
* Load Balancing
* Backup and Recovery
You can check replication status and start a slave using the following
commands: SHOW SLAVE STATUS \\\\G\\START SLAVE;
{moreInfo:MySQL Manual: Replication
FAQ|http://dev.mysql.com/doc/refman/5.0/en/replication-faq.html}
Would be translated into the following HTML markup:
Replication is a <b>very nice feature</b> of MySQL. Replication can be very useful for solving problems in the following areas: <ul> <li>Data distribution</li> <li>Load Balancing</li> <li>Backup and recovery</li> </ul>You can check replication status and start a slave with the following commands: SHOW SLAVE STATUS \G;<br/>START SLAVE; <a href="http://dev.mysql.com/doc/refman/5.0/en/replication-faq.html" target="_blank" >MySQL Manual: Replication FAQ</a>
To find out more about this format go to the wikipedia.org web site.
This section documents the steps to create a rule. Before creating a rule, review the preceding sections of this chapter.
This example creates a rule that checks the number of rows in a table. We decide that having 50,000 rows in this table triggers a critical alert. Lesser numbers produce informational and warning level alerts.
Begin by navigating to the Advisors tab and
clicking the manage rules link. Then choose
the button.
Create your custom rule by following these steps:
Using the Rule Name text box, give the
rule an appropriate name, in this case something such as
"Excessive number of records in
table_name table".
From the Advisor drop down list box,
choose an advisor group for your rule. The
Administration group of rules might be
suitable but if you wish, create your own group of
advisors. For instructions on doing this, see
Section 4.1.1, “Creating Advisors”.
Enter the following expression in the
Expression text area:
'%table_name_num_rows% >
THRESHOLD'. Replace table_name
with the name of the table to monitor. Note that the
variable
% is not yet defined.
table_name_num_rows%
Set the Thresholds.
Set the Critical Alert level to
50000.
Set the Warning Alert level to
10000.
Set the Info Alert level to
5000.
Define your variable in the Variable
Assignment frame.
In the Variable text box, enter
'%,
the variable used in the table_name_num_rows%Expression
text box.
In the Data Item drop down list
box, find and select the
mysql:table:numrows entry. (For a
description of all the data items available, see
Appendix D, Data Collection Items.)
In the Instance text box, enter
database_name.table_name.
Add appropriate entries for the Problem
Description, the Advice, and
the Links text areas. Optionally, use
Wiki
markup for these text areas. You can also reference
the
'%
variable in these text areas. For example, you can display
the current number of rows with a message such as
'table_name_num_rows%table_name currently has
%table_name_num_rows% rows.'
Save the rule.
After you create the rule, schedule it against the server that contains the database table to monitor. For instructions on scheduling rules, see Section 2.3.2, “Scheduling Rules”.
Section 4.1.7, “Creating a New Rule: An Example” shows how to create a custom rule and Appendix D, Data Collection Items shows the data items you can use in rule creation. In some circumstances, you might want to create a rule that uses a custom data collection item.
To create additional custom data collection items, edit the
share/mysql-proxy/items/custom.xml custom
rule file added during installation. The
custom.xml is installed automatically, but
is not modified during an upgrade installation. The rules file
is listed last in the agent-item-files
configuration parameter, so any rules and data collection items
you add to this file supercede the standard data collection
definitions.
After you make changes to the custom.xml,
restart the agent to load the custom definitions.
As an example, this section shows how to create a data item
for monitoring the amount of free InnoDB tablespace. You can
have multiple items in your custom.xml
file, just specify additional class
entries. The custom.xml file in the
installation includes a sample entry. You can replace that
entry, or add the following definition to the file:
<?xml version="1.0" encoding="utf-8"?>
<classes>
<class>
<classname>innodb_min_free</classname>
<namespace>mysql</namespace>
<query><![CDATA[SELECT MIN(substring_index(substring_index(table_comment," ",3)," ",-1)/1024/1024)
as Free FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'InnoDB']]></query>
</class>
</classes>
After saving the file, ensure that the
agent-item-files parameter within the
configuration includes the custom.xml
file. For example:
[mysql-proxy]
...
agent-item-files = share/mysql-proxy/items/quan.lua,share/mysql-proxy/items/items-mysql-monitor.xml,»
share/mysql-proxy/items/agent-allocation-stats.lua,share/mysql-proxy/items/custom.xml
...
Restart the agent to put this change into effect. To do this, see:
Once the agent has restarted, the new data item is in the
Data Item drop down list box on the
Rule Definition page. Its fully qualified
name is mysql:table:innodb_min_free.
You can override the standard rules and the data collection items
that support them by adding entries to your
custom-items.xml with different queries to
collect information. To customize data collection for existing
advisors, you can use the information in
Section 4.1, “Creating Advisors and Rules”.
You can change a rule so that it no longer applies to a specific server or group of servers, or suspend a rule for a period of time.
To disable or unschedule an advisor, choose the Current
Schedule screen of the Advisors
tab.
You can disable or unschedule one or more rules using the buttons
on the upper or lower left of the screen. You can also change one
rule at a time by clicking the enabled or
unschedule hyperlink to the right of a rule.
To no longer run a rule against a specific server, expand the
advisor group and the specific rule by clicking the
button and then the
button. When the dialog window
opens, choose the button and
that rule will no longer be applied. If you wish to back out of
the operation, choose . To institute
this rule again, use the Add to Schedule page.
To suspend a rule temporarily, use the
button and follow the same process
as for unscheduling. Once a rule is disabled, the link under the
status column changes to red and reads
disabled. When a rule is disabled, data is no
longer collected for that rule. To re-enable a disabled rule,
click the disabled link or the
button.
You can alter multiple rules for one or more servers by selecting the appropriate checkbox and clicking the , , or button.
Rules associated with the heat chart cannot be disabled or unscheduled, as they are required by MySQL Enterprise Monitor.
During maintenance periods for database servers, you can stop Monitor Agents from reporting their findings. During such a blackout period, rules are not evaluated and notifications are put on hold, but Monitor Agents continue to collect data. In this respect, blacked-out rules differ from disabled rules; data continues to be collected and stored in the repository.
To enable a blackout period, enter the following URL into the address bar of your browser, substituting the appropriate host name, port and server name:
http://localhost:18080/rest?command=blackout » &server_name=SuSE:3306&blackout_state=true
Check the configuration_report.txt file for
the host name and port to use. Specify the correct port for the
Tomcat server. Specify the server to blackout using the name that
appears in the Server Tree, including the colon and port number as
shown in the preceding example.
When the HTTP authentication dialog box requesting your Dashboard
user name and password opens, specify the administrator's
credentials. The default user name is admin;
use the password you specified when you initially logged in to the
Dashboard.
You can also blackout a server group by entering the following URL into the address bar of your browser, substituting the appropriate host name, and server group name:
http://localhost:18080/rest?command=blackout » &group_name=Finance&blackout_state=true
When the HTTP authentication dialog box opens, enter the administrator's credentials.
To confirm that a server is blacked out, check that its name is greyed out in the Dashboard.
To reactivate the blacked-out server or server group, use the
appropriate URL and query string, changing the
blackout_state=true name/value pair to
blackout_state=false. Again, this must be done
by a user with administrative privileges.
Restarting MySQL Enterprise Monitor does not reactivate a blacked out server.
You can write a script to black out a server, rather than opening a web browser and typing entries into the address bar. This section documents a sample blackout script that can be run from the command line.
Create the following file and save it as
blackout.pl.
#!/usr/bin/perl
use LWP 5.64;
# USAGE: blackout.pl servicemanager:18080 admin password servername:3306 true
# $ARGV[0] = management server hostname:port
# $ARGV[1] = management server username
# $ARGV[2] = management server password
# $ARGV[3] = mysqld managed instance server name and port
# $ARGV[4] = blackout state (true/false)
my $browser = LWP::UserAgent->new;
$browser->credentials(
$ARGV[0],
'',
$ARGV[1],
$ARGV[2]
);
my $url = URI->new('http://'.$ARGV[0].'/rest');
$url->query_form( # And here the form data pairs:
'command' => 'blackout',
'server_name' => $ARGV[3],
'blackout_state' => $ARGV[4]
);
my $response = $browser->post( $url );
if (!$response->is_success) {
die $response->status_line . "\n";
}
Windows users can omit the initial #! line.
On Unix systems, use the chmod +x blackout.pl command to make the file executable.
At the command line, enter blackout.pl
.
servicemanager:18080 admin
password servername:3306
true
Check the configuration_report.txt file for
the host name and port to use. Specify the correct port for the
Tomcat server. Specify the server to black out using the name
that appears in the Server Tree, including the colon and port
number as shown in the preceding example. Specify the name of a
user who is a "manager". Specifying a user with "dba" rights
only will not black out a server and no error will be displayed.
To confirm that a server is blacked out, check that its name is
greyed out in the Dashboard. To end the blackout, run the same
script, changing the final argument to false.
Restarting MySQL Enterprise Monitor does not reactivate a blacked out server.