Table of Contents
The Health
section of MySQL Administrator shows you
how to graphically monitor the real-time status of your MySQL
server. A selection of pre-made status graphs are available, and
you can easily create your own status graphs and organize them
into custom pages and groups.
The default graphs provide graphical information about the MySQL server's current performance, load, and memory usage. Additional pages list all server variables, with tabs for memory health, status variables and system variables. Note that clicking on the status pages will open an additional connection to the MySQL server, which you can see when viewing threads and users in the Server Connections section.
We'll begin by covering the different pre-made status graphs, and
then describe how to create your own status graphs. Finally we'll
describe the pages in the Health
section
dedicated to the various status and system variables.
This tab provides information about connections to the MySQL server. The following three pre-made graphs are displayed:
Connection Usage: Displays a percentage
of the maximum number of simultaneous clients allowed
(threads connected
/
max_connections
). If this value gets close
to 100%, it could indicate that you should increase the value
of max_connections, which you can do in the
Startup
Variables section. The maximum number of connections
since MySQL Administrator was loaded is also shown. For more
information on server connections, see
SHOW VARIABLES
Syntax, and
Too many connections
.
Traffic: This is a representation of the
bytes_sent
status variable. This does not
represent total bytes sent since server startup, but instead
represents the current number of bytes sent (the
bytes_sent
variable is a cumulative total).
The initial maximum value of the graph is 102400, this maximum
value will be automatically increased as needed.
Number of SQL Queries: This is a
representation os the com_select
status
variable. This does not represent total queries since server
startup, but instead represents the current number of queries
(the com_select
variable is a cumulative
total). The initial maximum value of the graph is 10, this
maximum value will be automatically increased as needed.
This tab provides information about memory caches that affect the performance of the MySQL server. For each cache, there is a display that shows the current hit rate or usage of the cache, and a display that shows the hit rate or usage for the last few minutes.
Query Cache Hitrate: When in use, the
query cache of the MySQL server stores the text of a
SELECT
query together with the
corresponding result that was sent to the client. If an
identical query is later received, the server will retrieve
the results from the query cache rather than parsing and
executing the same query again. The hit rate indicates the
ratio of queries that were cached and queries that were not
cached. The higher the hit rate, the better the performance of
the server regarding SELECT
queries. See
The MySQL Query Cache.
Key Efficiency: The horizontal bar graph
indicates how much of the allocated key buffer is in use. If a
fairly large percentage of the key buffer is in use you may
wish to increase the key_cache_size
setting
in the
Startup
Variables section. The line graph is an indication of
the number of key_read_requests
that
resulted in actual key_reads
. The higher
the hit rate the more effective your key buffer is. A low hit
rate can indicate that you need to increase the size of your
keycache.
While the pre-made status graphs can provide you with information
of the current state of your MySQL server, there may be
additionally information that you wish to visually track. The
MySQL Administrator Health
section allows you to
add additional tabs and graphs to track any information that is
contained within the
Status
Variables.
The first step in creating custom health graphs is to create a new
page in the Health
section to store your
graphs. To create a new page right click anywhere in the working
area and choose the option. You will
be prompted for a page name and description and a new page will be
created.
Once you have created a new page, you will need to create a group.
All graphs are organized into groups. For example, the
Key buffer usage
and Key buffer hit
rate
graphs are both members of the group titled
Key Efficiency
. To create a group, right click
within your newly created page and choose the option.
To create a custom graph, right click within a group and choose the
option. The following dialog will appear:The Line-Graph is appropriate for showing trends over time, while the Bar-Graph option will be more appropriate for showing percentage information. If you choose to create a bar-graph, you will need to choose a caption for your graph. Captions are not allowed for line-graphs.
The data on your graph is set using the Value
Formula
. You can create a formula using any of the
variables available in the
Status
Variables and
System
Variables tabs. To use the value of a variable, wrap the
variable name in square brackets (for example,
[com_select]
), if you want the relative value
of the variable prepend the square brackets with a
^
character.
For example, if we wanted to graph the percentage of temporary tables that were created on disk we could use the following formula:
[created_tmp_disk_tables] / [created_tmp_tables]
If we wanted to track the number of temporary tables created on a continuous basis, we could use the following as our formula:
^[created_tmp_tables]
After creating your formula, select the Value
Unit that best represents your data. You can choose
from Percentage
, Count
,
Byte
, and Seconds
. In our
examples we would use Percentage
for the first
example, and Count
for the second. You can also
assign a caption to the value.
Once your formula is assigned you should configure the
Max. Value
and Min. Value
for the graph, so that your data is spread evenly across your
graph. You can set arbitrary values based on your estimates of how
large the values will grow, and check the option to allow MySQL Administrator to automatically
increase the Max Value
setting automatically to
prevent your data from extending off of your graph.
You can also set the Max. Value
option by way
of a formula assigned in the Max Formula
field. The same syntax applies in this field as applies in the
Value Formula field. For example, if you were
creating a bar graph tracking the number of temporary disk tables
created, you could use
[created_tmp_disk_tables]
as the
Value Formula and
[created_tmp_tables]
as the Max.
Formula.
This tab provides information about the status variables of the MySQL server. In the left box of the working area, you find categories and subcategories. Double click on a category name to expand or collapse its subcategories.
For each category, and each subcategory, the status variables are
listed in the right box of the working area, together with their
values, and with a short explanation. See
SHOW STATUS
Syntax, for more detail on server status
variables.
Use the
button to make MySQL Administrator retrieve the variables and their values once again.Right clicking in the right box allows you to copy variables to the clipboard.
This tab provides information about the system variables of the MySQL server. In the left box of the working area, you can find categories and subcategories. Double click on a category name to expand or collapse its subcategories.
For each category, and each subcategory, the system variables are
listed in the right box of the working area, together with their
values and a short explanation. See
SHOW VARIABLES
Syntax, for more information on system
variables.
System variables that can be set at runtime are marked as
editable, that is they are prefixed with an icon different from
the variables that cannot be set at runtime. Double clicking on
editable variables will open a dialog box allowing you to specify
another value for the variable. The changes you make will take
effect as soon as you confirm the changes by clicking the
SET
Syntax.
Use the
button to make MySQL Administrator retrieve the variables and their values once again.Right clicking in the right box allows you to copy variables to the clipboard.