MySQL Server Monitoring (part 1) - SQL Commands

1/12/2012 4:24:20 PM
Managing the MySQL server falls in the category of application monitoring. This is because most of the performance parameters are generated by the MySQL software and are not part of the host operating system. As mentioned previously, you should always monitor your base operating system in tandem with monitoring MySQL because MySQL is very sensitive to performance issues of the host operating system.

1. How MySQL Communicates Performance

There are two mechanisms you can use to govern and monitor behavior in the MySQL server. You use server variables to control behavior and status variables to read behavior configuration and statistical information regarding features and performance.

There are many variables you can use to configure the server. Some can be set only at startup (called startup options, which can also be set in option files). Others can be set at the global level (across all connections), the session level (for a single connection), or both the global and session levels.


Session variable settings are not persistent beyond the current connection and are reset when the connection is closed.

You can read server variables using the following commands:


You can change those variables that are not static (read-only) using the following commands (you can include multiple settings on a single line using a comma separator):

SET [GLOBAL | SESSION] <variable_name> = <value>;
SET [@@global. | @@session. | @@]<variable_name> = <value>;

You can read status variables using the following commands. The first two commands display the value of all local or session scope variables (the default is session). The third command displays those variables that are global in scope.


2. Performance Monitoring

Performance monitoring in MySQL is the application of the previous commands—specifically, setting and reading system variables and reading status variables. The SHOW and SET commands are only two of the possible tools you can use to accomplish the task of monitoring the MySQL server.

Indeed, there are several tools you can use to monitor your MySQL server. The tools available in the standard distributions are somewhat limited in that they are console tools and include special commands you can execute from a MySQL client (e.g., SHOW STATUS) and utilities you can run from a command line (e.g., mysqladmin).


The MySQL client tool is sometimes called the MySQL monitor, but should not be confused with a monitoring tool.

There are also GUI tools available that make things a little easier if you prefer or require such options. You can also download the MySQL GUI tools, which include advanced tools that you can use to monitor your system, manage queries, and migrate your data from other database systems.

We begin by examining how to use the SQL commands and then discuss the MySQL Administrator GUI and Query Browser tools. We also take a look at one of the most overlooked tools available to the administrator—the server logs.

Some savvy administrators may consider the server logs the first and primary tool for administering the server. Although they are not nearly as vital for performance monitoring, they can be an important asset in diagnosing performance issues.

3. SQL Commands

All of the SQL monitoring commands are a variant of the SHOW command, which displays internal information about the system and its subsystems. While there are many forms of the SHOW command, the following lists the most common SQL commands you can use to monitor the MySQL server:


Displays the index cardinality statistics for the specified table, which are used by the optimizer to estimate join selectivity. This command can also be very helpful when diagnosing poorly performing queries, specifically whether the query is formulated in such a way as to make use of the indexes available.


Displays the list of all known plug-ins. It shows the name of the plug-in and its current status. The storage engines in newer releases of MySQL are implemented as plug-ins. Use this command to get a snapshot of the currently available plug-ins and their statuses.


Displays data for all threads (including connections) running on the system. This command resembles the process commands of the host operating system. The information displayed includes connection data along with the command executing, how long it has been executing, and its current state. Like the operating system command it resembles, it can diagnose poor response (too many threads), a zombie process (long running or nonresponding), or even connection issues. When dealing with poor performance or unresponsive threads, use the KILL command to terminate them. The default behavior is to show the processes for the current user. The FULL keyword displays all processes.


You must have the global SUPER privilege to see all processes running on the system.


Displays the values of all of the system variables. You will probably use this command more frequently than any other. Use this command to read all of the statistical information available on the server. Combined with the GLOBAL or SESSION keyword, you can limit the display to those statistics that are global- or session-only.


Displays detailed information about the tables in a given database. This includes the storage engine, collation, creation data, index data, and row statistics. You can use this command along with the SHOW INDEX command to examine tables when diagnosing poorly performing queries.


Displays the system variables. These are typically configuration options for the server and while they do not display statistical information, viewing the variables can be very important when determining whether the current configuration has changed or if certain options are set. Some variables are read-only and can only be changed via the configuration file or the command line on startup, while others can be changed globally or set locally. You can combine this command with the GLOBAL or SESSION keyword to limit the display to those variables that are global- or session-only.

Limit the Output of SHOW Commands

The SHOW commands in MySQL are very powerful. However, they often display too much information. This is especially true for the SHOW STATUS and SHOW VARIABLES commands.

To see less information, you can use the LIKE <pattern> clause, which permits you to view only those rows matching the pattern specified. The most common example is using the LIKE clause to see only variables for a certain subset, such as replication or logging. You can use the standard MySQL pattern symbols and controls in the LIKE clause in the same manner as a SELECT query.

For example, the following displays the status variables that include the name “log”:

| Variable_name | Value |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Com_binlog | 0 |
| Com_purge_bup_log | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_engine_logs | 0 |
| Com_show_relaylog_events | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
11 rows in set (0.11 sec)

The commands specifically related to storage engines include the following:

SHOW ENGINE < engine_name> LOGS

Displays the log information for the specified storage engine. The information displayed is dependent on the storage engine. This can be very helpful in tuning storage engines. Some storage engines do not provide this information.

SHOW ENGINE < engine_name> STATUS

Displays the status information for the specified storage engine. The information displayed depends on the storage engine. Some storage engines display more or less information than others. For example, the InnoDB storage engine displays dozens of status variables, while the NDB storage engine shows a few, and the MyISAM storage engine displays no information. This command is the primary mechanism for viewing statistical information about a given storage engine and can be vital for tuning certain storage engines (e.g., InnoDB).


The older synonyms for the SHOW ENGINE commands (SHOW <engine> LOGS and SHOW <engine> STATUS) have been deprecated.


Displays a list of all known storage engines for the MySQL release and their statuses (i.e., whether the storage engine is enabled). This can be helpful when deciding which storage engine to use for a given database or in replication to determine if the same storage engines exist on both the master and the slave.

The commands specifically related to MySQL replication include the following:

SHOW BINLOG EVENTS [IN '< log_file>'] [FROM < pos>] [LIMIT [< offset>,]
row count>]

Displays the events as they were recorded to the binary log. You can specify a logfile to examine (leaving off the file tells the system to use the current logfile), and limit output to the last events from a particular position or to the first number of rows after an offset into the file. This command is the primary command used in diagnosing replication problems. It comes in very handy when an event occurs that disrupts replication or causes an error during replication.


If you do not use a LIMIT clause and your server has been running and logging events for some time you could get a very lengthy output. If you need to examine a large number of events, you should consider using the mysqlbinlog utility instead.


Displays the list of the binary logs on the server. Use this command to get information about past and current binlog filenames. The size of each file is also displayed. This is another useful command for diagnosing replication problems because it will permit you to specify the binlog file for the SHOW BINLOG EVENTS command, thereby reducing the amount of data you must explore to determine the problem. The SHOW MASTER LOGS command is a synonym.

SHOW RELAYLOG EVENTS [IN '< log_file>'] [FROM < pos>] [LIMIT [< offset>,]
row count>]

Available in MySQL version 5.5.0, this command does the same thing as SHOW BINLOG EVENTS, only with relay logs on the slave. If you do not supply a filename for the log, events from the first relay log are shown. This command has no effect when run on the master.


Displays the current configuration of the master. It shows the current binlog file, the current position in the file, and all inclusive or exclusive replication settings. Use this command when connecting or reconnecting slaves.


Displays the list of slaves connected to the master that used the --report-host option. Use this information to determine which slaves are connected to your master.


Displays the status information for the system acting as a slave in replication. This is the primary command for tracking the performance and status of your slaves. A considerable amount of information is displayed that is vital to maintaining a healthy slave.

The two most important commands in this list are SHOW VARIABLES and SHOW STATUS. There are a great many variables (over 290 status variables alone), so once you learn to master the LIKE clause, you can target the results to specific aspects of the system you want to monitor.

The variable lists are generally in alphabetical order and are often grouped by feature. However, sometimes the variables are not neatly arranged; in this case you can find them using a keyword search. For example, SHOW STATUS LIKE '%thread%' shows all of the status variables related to thread execution. Example 1 shows this command on a recent beta release of MySQL.

Example 1. Showing thread status variables
mysql> SHOW VARIABLES LIKE '%thread%';
| Variable_name | Value |
| innodb_file_io_threads | 4 |
| innodb_read_io_threads | 4 |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_write_io_threads | 4 |
| max_delayed_threads | 20 |
| max_insert_delayed_threads | 20 |
| myisam_repair_threads | 1 |
| pseudo_thread_id | 1 |
| thread_cache_size | 0 |
| thread_handling | one-thread-per-connection |
| thread_stack | 262144 |
12 rows in set (0.00 sec)

This example shows not only those status variables for thread management, but also the thread control for the InnoDB storage engine. While you sometimes get more information than you expected, a keyword-based LIKE clause is sure to help you find the specific variable you need.

Knowing which variables to change and which variables to monitor can be the most challenging part of monitoring a MySQL server.

To illustrate the kinds of features you can monitor in a MySQL server, let us examine the variables that control the query cache. The query cache is one of the most important performance features in MySQL if you use the MyISAM storage engine for your application data. It allows the server to buffer frequently used queries and their results in memory. Thus, the more often a query is run, the more likely it is that the results can be read from the cache rather than reexamining the index structures and tables to retrieve the data. Clearly, reading the results from memory is much faster than reading them from disk every time. This can be a performance improvement if your data is read much more frequently than it is written (updated).

Each time you run a query, it is entered into the cache and has a lifetime governed by how recently it was used (old queries are dumped first) and how much memory there is available for the query cache. Additionally, there are a number of events that can invalidate (remove) queries from the cache. We include a partial list of these events here:

  • Frequent changes (to data or indexes).

  • Different forms of the query, which can cause missed cache hits. Thus, it is important to use standardized queries for commonly accessed data.

  • When a query derives data from temporary tables.

  • Transaction events that can invalidate queries in the cache (e.g., COMMIT).

You can determine if the query cache is configured and available in your MySQL installation by examining the have_query_cache variable. This is a system variable with global scope, but it is read-only. You control the query cache using one of several variables. Example 2 shows the server variables for the query cache.

Example 2. Query cache server variables
mysql> SHOW VARIABLES LIKE '%query_cache%';
| Variable_name | Value |
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 33554432 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
6 rows in set (0.00 sec)

As you can see, there are several things you can change to affect the query cache. Most notable is the ability to temporarily turn off the query cache by setting the query_cache_size variable—this sets the amount of memory available for the query cache. If you set this variable to 0, it effectively turns off the query cache. This is not related to the have_query_cache variable, which merely indicates that the feature is available.

You can observe the performance of the query cache by examining several status variables, as shown in Example 3.

Example 3. Query cache status variables
mysql> SHOW STATUS LIKE '%Qcache%';
| Variable_name | Value |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
8 rows in set (0.00 sec)

Here we see one of the more subtle inconsistencies in the MySQL server. You can control the query cache using variables that start with query_cache, but the status variables start with Qcache. While the inconsistency was intentional (to help distinguish a server variable from a status variable), oddities like this can make searching for the right items a challenge.


You can and should periodically defragment the query cache with the FLUSH QUERY CACHE command. This does not remove results from the cache, but instead allows for internal reorganization to better utilize memory.

There are many nuances to the query cache that allow you to manage and configure it and monitor its performance. This makes the query cache an excellent example to demonstrate the complexity of monitoring the MySQL server. No single volume can cover all such topics.

Another pair of commands that can be very useful in monitoring replication are the SHOW MASTER STATUS and SHOW SLAVE STATUS commands.

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