MySQL Server Monitoring (part 2) - MySQL Administrator

1/12/2012 4:28:14 PM

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.


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.


Displays the entire list of system statistics and is similar to the SQL SHOW STATUS command.


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.


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.

Figure 1. Sample mysqladmin process and status report

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:

  • MySQL Administrator 1.2

  • MySQL Query Browser 1.2

  • MySQL Migration Toolkit 1.1

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.


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.


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.

Figure 2. The Connection Health tab (Mac OS X)

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.


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.

Figure 3. The Connection Health tab (Windows 7)

Figure 4. The Connection Health tab (Linux)

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.

Figure 5. The Memory Health tab

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.

Uncertainty Principle in MySQL

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.


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.

Figure 6. Custom graph dialog

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.

Figure 7. Example custom graph


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.

Figure 8. The Server Variables window

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.


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.

Figure 9. 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).

Figure 10. The Logs window


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.

Video tutorials
- How To Install Windows 8

- How To Install Windows Server 2012

- How To Install Windows Server 2012 On VirtualBox

- How To Disable Windows 8 Metro UI

- How To Install Windows Store Apps From Windows 8 Classic Desktop

- How To Disable Windows Update in Windows 8

- How To Disable Windows 8 Metro UI

- How To Add Widgets To Windows 8 Lock Screen

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010
programming4us programming4us