4. The mysqladmin Utility
The mysqladmin command-line utility is the workhorse of the suite of
command-line tools. There are many options and tools (called commands)
this utility can perform.
Since the utility runs from the command line, it allows
administrators to script sets of operations much more easily than they
can process SQL commands. Indeed, some of the third-party monitoring
tools use a combination of the mysqladmin and SQL commands to gather
information for display in other forms.
You must specify connection information (user, password, host,
etc.) to connect to a running server. The following is a list of
commonly used commands. As you will see, most of these have equivalent
SQL commands that produce the same information.
status
Displays a concise description of the status of the server,
including uptime, number of threads (connections), number of
queries, and general statistical data. This command provides a
quick snapshot of the server’s health.
extended-status
Displays the entire list of system statistics and is similar to
the SQL SHOW STATUS
command.
processlist
Displays the list of current processes and works the same way as
the SQL SHOW PROCESSLIST
command.
kill
< thread id>
Allows you to kill a specified thread. Use this in conjunction
with processlist to help manage
runaway or hung processes.
variables
Displays the system server variables and values. This is
equivalent to the SQL SHOW
VARIABLES command.
There are many options and other commands not listed here,
including commands to start and stop a slave during replication and
manage the various system logs.
One of the best features of the mysqladmin utility is its comparison of
information over time. The --sleep
n option tells the utility to
execute the specified command once every n seconds.
For example, to see the process list refreshed every three seconds on
the local host, use the following command:
mysqladmin -uroot --password --socket=<sock> processlist --sleep 3
This command will execute until you cancel the utility using
Ctrl+C.
Perhaps the most powerful option is the comparative results for
the extended status command. Use the --relative option to
compare the previous execution values with the current values. For
example, to see the previous and current values for the system status
variables, use this command:
mysqladmin -uroot --password --socket=<sock> extended-status -relative --sleep 3
You can also combine commands to get several reports at the same
time. For example, to see the process list and status information
together, issue the following command:
mysqladmin --root ... processlist status
The mysqladmin tool has many
other uses. You can use it to shut down the server, flush the logs, ping
a server, start and stop slaves in replication, and refresh the
privilege tables. Figure 1 shows the sample
output of a system with no load.
5. MySQL GUI Tools
The MySQL GUI tools are currently bundled together in a single download and are
available on the MySQL website. You can download the tools packaged for
several popular operating systems:
We will discuss the MySQL Administrator and MySQL Query Browser in
more detail in the following sections. The MySQL Migration
Toolkit is designed to automate the migration of database schema
and data from other database systems. It can be a really handy tool to
make adoption of MySQL easier and faster.
Note:
The MySQL Migration Toolkit is not available for the Mac OS X
platform.
6. MySQL Administrator
The MySQL Administrator is a jack of all (or most) trades. It provides facilities for
viewing and changing system variables, managing configuration files,
examining the server logs, monitoring status variables, and even viewing
graphical representations of performance for some of the more important
features. It also has a full set of administration options that allow
you to manage users and view database configurations. While it was
originally intended to replace the mysqladmin tool, popular demand ensures we
will have both for the foreseeable future.
You can use MySQL Administrator on any platform and can access one
or more servers connected to the client. This makes the tool much more
convenient when monitoring several servers on the network.
Note:
A unique feature of the MySQL Administrator allows you to
analyze a downed server. While most of the features are disabled
(e.g., there are no real-time performance indicators), you can still
view the configuration and examine the logs. This can be a very handy
tool for diagnosing server crashes.
To connect to a downed server (called configure-service mode),
open the tool and press the Ctrl key (the Apple key on Mac OS X).
Notice the Connect button changes to Skip. Press Skip and the tool
will switch to the configure-service mode. From here, you can start
and stop the server, change startup variables, and view the server
logs.
MySQL Administrator features are divided into 10 groups or tools,
each represented as tabs in the application. Principally, we’re
interested in tools that show us the values of the status variables. The
MySQL Administrator has a dynamic feature similar to the mysqladmin tool, but it takes the feature a
bit further by showing a running graph of the values. These are grouped
under the health tools. If you click the Connection Health tab, you will see a graph like the one
shown in Figures Figure 2 through Figure 4.
This graph shows three sections. The first monitors the connection
usage (how many connections are on the server currently); this can be
very helpful in determining whether you have an overburdened server (one
with too many connections) or when troubleshooting sporadic
connections.
Note:
Figures Figure 2 through Figure 4 are a mixture of snapshots taken
from Mac OS X, Windows 7, and Linux systems. As you'll see, each
platform has a slightly different look and feel. The Mac OS X system
differs in menu placement, but otherwise has the same feature
set.
The Traffic graph in Figure 2 shows the network
traffic and can be helpful in determining potential problems with your
network.
The most interesting graph is the bottom graph, which shows the
SQL queries that have been run over time. This can be very helpful in
determining if your system is overburdened with queries.
The examples shown here depict a system running with very few
connections, a minimal amount of network traffic, and a moderate load of
queries that demonstrate a pattern of bursts.
The graphical representation of the status variables is one of the
best features of the MySQL Administrator. While the graphs hide the
underlying status variables and only present the maximum and minimum
values over time, they can be very helpful in spotting potential
problems pictorially.
Prolonged use of tools like this can add a small amount of
overhead and skew the results slightly. However, this may not be a
problem for systems with many operations.
Figure 3
depicts a system that is basically unused. We include it here to show
you an example of the MySQL Administrator on the Windows platform.
However, it is important to consider this example as something to watch
out for in your replication topology. Specifically, if one of your
slaves demonstrates this behavior, it is a sure sign something is
seriously wrong. Similarly, it could also be an indication that your
scale-out scheme is not working as you designed it to, or that your load
balancing strategy has failed to distribute queries to this
server.
Figure 4 shows
MySQL Administrator on a Linux machine and demonstrates another example
of a system under moderate load. Notice that the connection usage and
network traffic have spike indicators that show relative percentage of
usage. This gauge allows you to see spikes at a glance.
The MySQL Administrator Memory Health tab displays dynamic graphical charts of the health of the
query cache and the key cache. Figure 5 shows an example of the graphical
reports running on a system of moderate load. In this case, the system
is running advanced diagnostic tests for the MySQL Cluster
product.
Unlike the manual queries for the query cache status variables,
this display makes it much easier to see the general trends of
performance for the query cache. Like some of the other dynamic
displays, this graph also has a peak indicator that can warn you of
sudden peaks in the values.
You may be familiar with the Heisenberg uncertainty
principle, which states that the more precisely a particle’s
position is determined, the less precisely the momentum is known in
that instant, and vice versa. Monitoring MySQL involves running
queries to get the values of status variables, so each time you gather the data, you
increase the value of some of the variables you’re measuring. Thus,
there is some overhead in monitoring MySQL, and oversampling can make
the values of the metrics less meaningful. Of course, this principle
applies to data probing in general. |
You should consider that frequent sampling can cause the system to
spend more time sampling than processing when planning your sampling
frequency. This graph displays only the query cache hit rate. This is the same as the status
variable with the name Qcache_hits,
which is a counter of the hits or successful query result retrievals
from cache. Clearly, a high hit rate indicates better performance than a
low rate, but the opposite is not necessarily true. A low hit rate
simply means the server is not making use of the query cache, not that
it is performing poorly. However, if your goal is to increase
performance by using standardized queries and you see a low hit rate, it
may be that you need to tune your query cache or that your database
activity is causing one or more of the events that invalidate cache
results.
The Qcache_hits is a
monotonically increasing statistic that you must reset periodically to
measure periods of activity. In the Query Cache Hitrate graph in Figure 5, the MySQL
Administrator is actually issuing a FLUSH STATUS command
to reset the value to 0 between samplings for the current session. This
is a common practice for most monitoring that involves sampling.
The Key Efficiency graph shown in Figure 8-5 is a
dynamic display of the key buffer usage. Specifically, this is the
MyISAM key cache and it measures how much the key buffer
is being used or how many requests have been made to read the key cache.
The status variable used here is the key_read_requests variable.
Note:
The key cache is the primary mechanism the MyISAM storage engine
uses to increase its performance. It caches the most frequently
accessed index blocks (pointers to the data) so indexed searches (and
subsequently the data) can be retrieved more quickly.
You can use this graph to determine when you need to increase the
key_cache_size variable for better
performance. That is, if the values displayed are high (a large
percentage), you may want to increase the value. Basically, the higher
the value, the more effective the key cache becomes. Conversely, if you
see low values and you know you are executing many queries on MyISAM
tables, you may want to decrease the size of the key cache.
The next tabs we will discuss present more traditional numeric
data. Before we look at these, there is one feature that warrants
mentioning: you can create your own custom health graphs. To add your
own graph, right-click in the open space on the working area and select
“New page.” You will be asked to name the page. Once you enter a name,
you will see the new name on the tab bar for the health tools. Click the
new page, then right-click the blank form and select “New group” to name
this group. You can then right-click the new group and add a new graph.
Figure 8-6 shows the custom graph
dialog.
From here, specify the status variable you want to monitor along
with any calculations you want to perform and how to present the data.
We encourage you to try this out for yourself. Notice in Figure 6 we chose a line graph and supplied the
status variable with the notation ^
followed by the variable name inside square brackets. The dialog gives
you a hint as to how you can enter a formula if you want a more complex
report.
You can also specify units for the value, several caption options,
the minimum and maximum values, and a formula for calculating the
maximum if needed. If you specify a name for the value caption, you will
see the small peak indicator shown on other graphs. Figure 7 shows the example graph in
action.
Note:
You can right-click any of the standard graphs and edit them.
Use this feature to see how some of the other graphs are
calculated.
As mentioned earlier, you can also see the server variables among
the health tools. Click the Server Variables tab and the display will change to a
data chart similar to a spreadsheet. On the left is a tree control that
groups all of the system variables into categories and
subcategories.
The center pane displays the details for each of these categories.
You can expand the categories by double-clicking the category name. For
example, if you double-click the General category you will see several
subcategories. Click once more on the Performance subcategory to see a
listing of the system variables related to performance tuning.
While this is a partial list of performance tuning variables, it
is indeed the most general of all of the performance variables. To see
the others, click on any of the other features and those variables will
be displayed with that group. Figure 8 shows the general performance
variables for a default installation.
The best feature of this tool is that you can change the values
dynamically. For example, if you want to increase the time the system
counts slow threads, simply click the value for the slow_launch_time variable and enter a new
value. The values are changed automatically on the server. This is a
really nice feature that makes the MySQL Administrator much easier to
use than a client window.
Note:
You can tell which variables can be changed (are not read-only)
by the little pencil icon next to the name. If the pencil icon is
missing, the variable is read-only.
When you combine the ability to mine and edit the system variables
at a glance with the ability to watch how changes affect the system
under load via custom dynamic health graphs, you are using the MySQL
Administrator as a professional performance tuning tool. Just remember
the golden rule of tuning: change only one thing at a time and measure,
measure, measure!
The Status Variables tab shows the system status variables grouped into features.
For example, to see all of the status variables for network traffic,
click the Networking group and then click the Traffic subgroup. Figure 9 shows the Status Variables
window.
As in the Server Variables window, all of the system status
variables are available for you to examine. The values shown are the
values at the time the feature group was selected and they are not
updated automatically. Click Refresh to retrieve new values. Unlike the
dynamic health graphs, the values returned during a refresh are the
values as they are read from the server with a SHOW STATUS command. Keep this in mind when
using this tool—it shows only the static right-then values, not trends
or historical lists.
Also included is a short description of each of the status
variables. This can be really helpful when digging through the status
variables looking for something to help diagnose a performance issue. It
also saves a great deal of time over issuing SHOW STATUS commands and trying to guess keywords to use (see Figure 5).
The server logs record significant events that occur while the
system is running. This information can include errors incurred during
an operation or connection, all of the queries run on the server, and
even which queries are running the slowest.
MySQL Administrator allows you to mine these logs. While it only
allows access to the error, general, and slow logs, it is still very
useful.
To see the logs on the server to which you are connected, click
the Logs tab in the toolbar. This will open the Logs tool (shown in
Figure 10).
Warning:
On some platforms, the MySQL Administrator may require you to
enter your administrator credentials to see or interact with the
server logs.
Across the top of the window, under the toolbar, is a page tool that allows you to click and drag the bar to a
specific page or to browse through the pages of the error log.
Below that is a two-paned display that lists the significant
events in the log (on the left) and the details from the log for the
event (on the right).
As you can see, there are several errors listed. Click an error in
the list to view its details. This can be a real time saver when you are
digging through a large log to find some events that you know happened
at a certain time on a certain day.
Now that you’ve had a look at what the MySQL Administrator
provides for advanced diagnostics, performance monitoring, and tuning,
we hope that you will consider this tool when you set about to practice
on your own servers. The MySQL Administrator removes much of the tedium
incurred by issuing the SQL commands to retrieve the same data. The new
features, like the health and custom health graphs, make performance
tuning a much easier and more immediate process.
