Indeed, there are a great many tools available to the Windows administrator. We won’t
try to cover them all here, but instead we’ll focus on tools that let you monitor a Win-
dows system in real time. Let’s examine some of the basic reporting tools first.
The following are the most popular tools you can use to diagnose and monitor per-
formance issues in Windows:
• Windows Experience Index
• System Health Report
• Event Viewer
• Task Manager
• Reliability Monitor
• Performance Monitor
An excellent source for information about Microsoft Windows performance, tools,
techniques, and documentation can be found at the Microsoft Technet website.
The Windows Experience
If you want a quick glance at how your system is performing compared to the expect-
ations of Microsoft’s hardware performance indexes, you can run the Windows Expe-
rience report.
To launch the report, click Start, then select Control Panel→System and Mainte-
nance→Performance Information and Tools. You will have to acknowledge the User
Account Control (UAC) to continue.
You can also access the System Health Report using the search feature on the Start
menu. Click Start and enter “performance” in the search box, then click Performance
Information and Tools. Click Advanced Tools and then click the link “Generate a sys-
tem health report” at the bottom of the dialog. You will have to acknowledge the UAC
to continue.
Microsoft has changed the Windows Experience in Windows 7. The
report is very similar to that of earlier Windows versions, but it supplies
more information that you can use to judge the performance of your
system.
The report is run once after installation, but you can regenerate the report by clicking
Update My Score.

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
click the link “Generate a system health report” at the bottom of the dialog. You will
have to acknowledge the UAC to continue.
You can also access the System Health Report using the search feature on the Start
menu. Click Start and enter “performance” in the search box, then click Performance
Information and Tools. Click Advanced Tools and select the link “Generate a system
health report” at the bottom of the dialog. Another way to access the System Health
Report is to use the search feature on the Start menu. Click Start and enter “system
health report” in the search box, then click the link in the Start menu. You will have to
acknowledge the UAC to continue. Figure 7-25 shows an example of the System Health
Report.
This report has everything—all of the hardware, software, and many other aspects of
your system are documented here. Notice the report is divided into sections that you
can expand or collapse for easier viewing. The following list briefly describes the in-
formation displayed by each section:
System Diagnostics Report
The system name and the date the report was generated.
Diagnostic Results
Warning messages generated while the report was being run, identifying potential
problem areas on your computer. Also included is a brief overview of the perform-
ance of your system at the time the report was run.
Software Configuration
A list of all of the software installed on your system, including system security
settings, system services, and startup programs.
Hardware Configuration
A list of the important metadata for disk, CPU performance counters, BIOS infor-
mation, and devices.
CPU
A list of the processes running at report time and metadata about system compo-
nents and services.

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The Event Viewer
The Windows Event Viewer shows all the messages logged for application, security,
and system events. It is a great source of information about events that have occurred
(or continue to occur) and should be one of the primary tools you use to diagnose and
monitor your system.
You can accomplish a great deal with the Event Viewer. For example, you can generate
custom views of any of the logs, save the logs for later diagnosis, and set up alerts for
specific events in the future. We will concentrate on viewing the logs. For more infor-
mation about the Event Viewer and how you can set up custom reports and subscribe
to events, consult your Windows help files.
To launch the Event Viewer, click the Start button, then right-click Computer and
choose Manage. You will have to acknowledge the UAC to continue. You can then
click the Event Viewer link in the left panel. You can also launch the Event Viewer by
clicking Start, typing “event viewer,” and pressing Enter.
The dialog has three panes by default. The left pane is a tree view of the custom views,
logfiles, and applications and services logs. The logs are displayed in the center pane,
and the right pane contains the Action menu items. The log entries are sorted, by de-
fault, in descending order by date and time. This allows you to see the most recent
messages first.
You can customize the Event Viewer views however you like. You can
even group and
sort events by clicking on the columns in the log header.
Open the tree for the Windows logs to see the base logfiles for the applications, security,
and system (among others). Figure 7-26 shows the Event Viewer open and the log tree
expanded.
The logs available to view and search include:
Application
All messages generated from user applications as well as operating system services.
This is a good place to look when diagnosing problems with applications.

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Warning
Indicates a less serious condition or event of note, such as low memory or low disk
space.
Information
Conveys data about an event. This is generally not a problem, but it could provide
additional information when diagnosing problems, such as when a USB drive was
removed.
To view a log, open the corresponding tree in the left pane. To view the details about
any message, click on the message. The message will be displayed below the log entries,
as shown in Figure 7-26. In the lower part of the center pane, you can click the General
tab to see general information about the message, such as the statement logged, when
it occurred, what log it is in, and the user who was running the process or application.
You can click the Details tab to see a report of the data logged. You can view the
information as text (Friendly View) or XML (XML View). You can also save the infor-
mation for later review; the XML View is useful to pass the report to tools that recognize
the format.
The Reliability Monitor
The most interesting monitoring tool in Windows is the Reliability Monitor. This is a
specialized tool that plots the significant performance and error events that have oc-
curred over time in a graph.
A vertical bar represents each day over a period of time. The horizontal bar is an ag-
gregate of the performance index for that day. If there are errors or other significant
events, you will see a red X on the graph. Below the bar is a set of drop-down lists that
contain the software installations and removals, any application failures, hardware
failures, Windows failures, and any additional failures.
This tool is great for checking the performance of the system over a period of time. It
can help diagnose situations when an application or system service has performed cor-
rectly in the past but has started performing poorly, or when a system starts generating
error messages. The tool can help locate the day the event first turned up, as well as

The Windows Task Manager displays a dynamic list of running processes. It has been
around for a long time and has been improved over various versions of Windows.
The Task Manager offers a tabbed dialog with displays for running applications, pro-
cesses (this is most similar to the Linux top command), services active on the system,
a CPU performance meter, a network performance meter, and a list of users. Unlike
some other reports, this tool generates its data dynamically, refreshing periodically.
This makes the tool a bit more useful in observing the system during periods of low
performance.
The reports display the same information as the System Health Report, but in a much
more compact form, and are updated continuously. You can find all of the critical
metrics needed to diagnose performance issues with CPU, resource-hogging processes,
memory, and the network. Conspicuously missing is a report on disk performance.
One of the interesting features of the Task Manager is that it shows a miniature per-
formance meter in the notification area on the Start bar that gives you a chance to watch
for peaks in usage.
Running a dynamic performance monitoring tool consumes resources
and can affect a system that already suffers poor performance.
You can launch
the Task Manager any time by pressing Ctrl+Alt+Del and choosing
Task Manager from the menu. Figure 7-28 shows an example of the Task Manager and
the process list.
The Performance Monitor
The Performance Monitor is the premier tool for tracking performance in a Windows
system. It allows you to select key metrics and plot their values over time. It can also
store the session so you can later review it and create a baseline for your system.
The Performance Monitor has metrics for just about everything in your system. There
are counters for many of the smaller details having to do with the basic areas of per-
formance: CPU, memory, disk, and network. There are a great many other categories
as well.
To launch the Performance Monitor, click Start, then select Control Panel→System and

lector Sets and changing the chart’s display characteristics. There are many excellent
texts that describe these features and more in great detail.
The versatility of the Performance Monitor makes it the best choice for forming base-
lines and recording the behavior of the system over time. You can use it as a real-time
diagnostic tool.
Figure 7-29. The Performance Monitor
Microsoft Windows Monitoring | 287
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
If you have used the Reliability or Performance Monitor, you may have
noticed a seldom-commented-on feature called the Resource Overview.
This is the default view of the Reliability and Performance Monitor. It
provides four dynamic performance graphs for CPU, disk, network, and
memory. Below the graphs are drop-down detail panes containing in-
formation about these areas. This report is an expanded form of the
Task Manager performance graphs and provides yet another point of
reference for performance monitoring and diagnosis on Microsoft
Windows.
This brief introduction to monitoring performance on Microsoft Windows should per-
suade you that the belief that Microsoft’s Windows platform is difficult to monitor and
lacks sophisticated tools is a myth. The tools are very extensive (some could argue too
much so) and provide a variety of views of your system’s data.
Monitoring as Preventive Maintenance
The techniques discussed so far give you a snapshot of the status of the system. How-
ever, most would agree that monitoring is normally an automated task that samples
the available statistics for anomalies. When an anomaly is found, an alert is sent to an
administrator (or group of administrators) to let someone know there may be a prob-
lem. This turns the reactive task of checking the system status into a proactive task.
A number of third-party utilities combine monitoring, reporting, and alerts into easy-
to-use interfaces. There are even monitoring and alert systems for an entire infrastruc-
ture. For example, Nagios can monitor an entire IT infrastructure and set up alerts for

Joel had a feeling today was going to be a better day. Everything was going well: the
performance measurements for the servers were looking good and the user complaints
were down. He had successfully reconfigured the server and improved performance
greatly. There was only one application still performing poorly, but he was sure the
problem wasn’t hardware- or operating-system-related; more likely it was an issue with
a poorly written query. Nevertheless, he had sent his boss an email message explaining
his findings and that he was working on the remaining problems.
Joel heard quickened footsteps approaching his office. He instinctively looked toward
his door, awaiting the now-routine appearance of his boss. He was shocked as Mr.
Summerson zipped by without so much as a nod in his direction.
He shrugged his shoulders and returned to reading his email messages. Just then a new
message appeared with “HIGH PRIORITY” in the subject line in capital letters. It was
from his boss. Chiding himself for holding his breath, Joel relaxed and opened the
message. He could hear his boss’s voice in his mind as he read through the message.
“Joel, good work on those reports. I especially like the details you included about
memory and disk performance. I’d like you to generate a similar report about the da-
tabase server. I’d also like you to look into a problem one of the developers is having
with a query. Susan will send you the details.”
With a deep sigh, Joel once again opened his favorite MySQL book to learn more about
monitoring the database system. “I hope it has something about drilling down into
individual components,” he mumbled, knowing he needed to get up to speed quickly
on an advanced feature of MySQL.
Now that you understand how monitoring works and how to keep your host’s oper-
ating systems at peak efficiency, how do you know if your MySQL servers are per-
forming at their peak efficiency? Better still, how do you know when they aren’t?
In this chapter, we begin with a look at monitoring MySQL, and then move on to
monitoring and improving performance in your databases. We conclude with a look
into best practices for improving database performance.
291
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

MySQL is very sensitive to performance issues of the host operating system.
There is an entire chapter in the online MySQL Reference Manual that covers all aspects
of monitoring and performance improvement, intriguingly titled “Optimization.” See
for more details. Rather than
repeat the facts and rhetoric of that excellent reference, we will discuss a general ap-
proach to monitoring the MySQL server and examine the various tools available.
This section is an introduction to the finer details of monitoring the MySQL server.
We'll start with a short discussion of how to change and monitor the behavior of the
292 | Chapter 8: Monitoring MySQL
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
system, then discuss monitoring primarily for the purposes of diagnosing performance
issues and forming a performance benchmark. We will also discuss best practices for
diagnosing performance issues and take a look at monitoring the storage engine sub-
layer in MySQL—an area not well understood or covered by other reference sources.
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 connec-
tion 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 com-
mands (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>;

SQL Commands
All of the SQL monitoring commands are a variant of the SHOW command, which dis-
plays 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 for-
mulated 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 in-
formation displayed includes connection data along with the command executing,
how long it has been executing, and its current state. Like the operating system
294 | Chapter 8: Monitoring MySQL
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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 termi-
nate 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 run-
ning on the system.
SHOW [GLOBAL | SESSION] STATUS

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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 dis-
played is dependent on the storage engine. This can be very helpful in tuning stor-
age 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