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.
Note:
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:
SHOW [GLOBAL | SESSION] VARIABLES;
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.
SHOW STATUS;
SHOW SESSION STATUS;
SHOW GLOBAL STATUS;
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).
Note:
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:
SHOW INDEX FROM
< table>
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.
SHOW PLUGINS
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.
SHOW
[ FULL]
PROCESSLIST
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.
Note:
You must have the global SUPER
privilege to see all processes running on the system.
SHOW
[GLOBAL |
SESSION] STATUS
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.
SHOW TABLE [FROM
< db>] STATUS
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.
SHOW [GLOBAL |
SESSION] VARIABLES
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.
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”: mysql> SHOW SESSION STATUS LIKE '%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).
Note:
The older synonyms for the SHOW
ENGINE commands (SHOW
<engine> LOGS and SHOW
<engine> STATUS) have been
deprecated.
SHOW ENGINES
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.
Note:
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.
SHOW BINARY LOGS
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.
SHOW MASTER STATUS
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.
SHOW SLAVE HOSTS
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.
SHOW SLAVE STATUS
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.
Note:
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.
