26.3. Server Monitoring Capabilities

Several sections of the MySQL Administrator main window are devoted exclusively or primarily to monitoring aspects of server operation:

• Server Information provides an overview of the characteristics of your connection to the server.

• Server Connections displays information about the clients connected to the server.

• Health displays performance, load, and memory use information in graphical form, and allows you to examine status variables and to examine and set system variables.

• Server Logs displays the contents of the error log, slow query log, and general query log.

• Replication Status helps you monitor a master server and the slaves that are connected to it.

• Catalogs displays information about databases, tables, columns, and indexes. It also provides access to the MySQL Table Editor and can perform table maintenance operations.

26.3.1. Server Information

The Server Information section provides an overview of server status. It displays information about your connection, the server host and the version of MySQL running on it, and the client host. Information shown by this section can be seen in Figure 26.1.

26.3.2. Server Connections

The Server Connections section displays information about the clients that currently are connected to the server. Connections also are known as "threads," which is the term used by MySQL Administrator. The Server Connections section displays thread information in two formats. One format lists threads by thread ID. The other format lists them grouped by user, which makes it easier to see what a given user is doing when that user has multiple connections open. In either display format, clicking a column heading re-sorts thread information rows by that column.

Clicking a thread line selects it and enables a Kill Thread button that you can use to terminate the connection. Terminating a connection can be useful if, for example, a runaway query has been issued. Clicking a user line selects all threads for that user and enables a Kill User button that you can use to terminate all connections for that user.

In the Server Connections section, the privileges that you have determine the scope of the information that you can see and the connections that you can terminate:

• If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads.

• If you have the SUPER privilege, you can terminate all threads. Otherwise, you can terminate only your own threads.

26.3.3. Health

The Health section displays server performance and memory use information in graphical form. It displays a predefined set of graphs by default, but is configurable and allows you to define your own graphs for monitoring the server.

The following status-monitoring graphical displays are predefined:

• Connection Usage displays the percentage of the maximum allowed number of connections that are in use. You can use this to see whether the client connection load is approaching the limit imposed by the max_connections system variable. If usage is consistently near the limit, consider increasing the value of this variable.

• traffic displays a graph showing the number of bytes sent to clients in each measurement interval.

• Number of SQL Queries displays a graph showing how many queries the server receives in each measurement interval.

• Query Cache Hitrate and Key Efficiency show the effectiveness of the query cache and the MyISAM key cache.

You can create your own graphs. Server monitoring graphs are based on formulas that can refer to status and system variables, so you can display whatever information you're interested in monitoring. Formulas can use the cumulative values of variables or the change in value relative to the previous measurement interval. For each graph, you can select characteristics such as the graph type (line or bar), captions, and the minimum and maximum of the graph range. Graphs can be organized into pages and groups.

The Health section also displays the server's status and system variables. These variables are displayed in hierarchical category/subcategory fashion to make it easy to examine related variables together. Categories can be expanded or collapsed to display more or less information.

System variables that are dynamic and can be set at runtime are so marked with a distinctive icon. Double-clicking a settable variable brings up a dialog for changing the value. This allows you to change server configuration easily, although changes made this way persist only until the server stops. Permanent changes can be made via the Server Variables section.

26.3.4. Server Logs

The Server Logs section displays the contents of the error log, slow query log, and general query log, if these logs are enabled. This capability is available for local servers only.

The section has a tab for each kind of log file that is monitored. Each tab contains controls that make it easy to move through the log. A page control selects "pages" of the log and two panels display summary and expanded views of the current page. To make it easier to see how the two views match up, clicking a summary line causes the corresponding expanded view lines to be highlighted.

There is a button to bring up a dialog for opening other log files. You can search for a given string within a log, which is useful when you're looking for a particular log entry. The current page of a log can be saved to a file (for example, to use it in other programs).

26.3.5. Replication Status

The Replication Status section provides an overview of your replication setup, if the server to which you're connected is acting as a master server. You can see the master's current replication status, and information about each of the master's slaves.

26.3.6. Catalogs

The Catalogs section provides the following capabilities:

• You can browse databases (schemas), tables and indexes within databases, and columns within tables.

• You can access the MySQL Table Editor. The Table Editor allows you to create tables and edit table definitions.

• If you right-click in the database browser, you can create and drop databases.

• If you right-click a table name and select Edit Table Data, MySQL Administrator launches MySQL Query Browser so that you can edit the table's contents.

• You can perform optimization, checking, and repair table-maintenance operations.