High Performance MySQL [Electronic resources] نسخه متنی

B.1 Overview

mytop does much of the hard work involved in
summarizing MySQL performance data. There are three primary display
modes in mytop. The default, thread
view (or top view), closely
resembles the Unix top command, as seen in Figure B-1. It produces a multiline summary at the top of
the screen followed by a listing of threads in MySQL. The
command view aggregates the data from
MySQL's Com_* command counters
(see Appendix A), as seen in Figure B-2. Finally, Figure B-3
illustrates status view, which tracks all the
other values in the output of SHOW
STATUS. Like top,
mytop refreshes the display periodically. The
default refresh interval is five seconds, but
that's easily adjusted.

Let's take a closer look at
mytop's display modes.

B.1.1 Thread View

In
mytop's default display (Figure B-1), the first several lines of the screen are
consumed by the mytop header. The first line
identifies the hostname of the MySQL server as well as its version
number. On the right side it displays the server's
uptime in Days+HH:MM:SS form followed by the current time.

Figure B-1. mytop thread view

The next two lines display statistics about queries and threads. The
first provides a cumulative count of the number of queries executed,
followed by the average number of queries executed per second, then
the total number of slow queries. Finally, the
Se/In/Up/De(%): section displays the relative
percentage of SELECT, INSERT,
UPDATE, and DELETE queries that
the server has executed.

The third line also displays queries per second (qps) and slow
queries, but they reflect only queries executed during the last
refresh interval. This line provides a count of the connected,
running, and cached threads followed by
Se/In/Up/De(%):. Again, those numbers reflect only
the most recent queries.

The rest of the screen is used to display connected threads. Each
thread listing shows the thread ID, username, database, hostname or
IP address, time, and information about what state the thread is in.
Generally, threads are either idle (Sleep) or executing a query or
command (Query). When a thread is executing a query, you see the
beginning of the query in the rightmost column of the display. The
time represents how long a thread has been in the same state. So if
you see a thread with a time of 10 running a
SELECT query, that means the query has been
running for 10 seconds. The display is also color-coded by default.
Idle threads are the default color, while yellow indicates a thread
running a query, and green indicates a thread that is in the process
of handing a new connection.

From here you can use mytop's
runtime keystrokes (see Table B-1 later in this
chapter) to control its behavior and appearance. For example, by
pressing f and entering the ID number of a thread, you can make
mytop display the full query.

B.1.2 Command View

This
view provides some insight into the relative number of times the
server is asked to execute various queries or commands:
SELECT, INSERT,
UPDATE, and so on. Figure B-2
shows an example of mytop's
command view.

Figure B-2. mytop command view

The first column lists the command being counted. Sometimes the
commands map directly to queries (SELECT,
INSERT, etc.), while others represent a calls of
commands. For example, set
option covers all SET commands,
such as SET AUTOCOMMIT=1,
SET GLOBAL.wait_timeout, etc. Still others
represent components of the MySQL C API. The best examples of this
are admin commands, which
represents the ping command (and a few others),
and change db, which represents
mysql_select_db( ) calls, including those
generated by the USE command.

The remaining columns measure the number of times each command has
been executed, both in absolute and relative terms. The
Total and Pct columns represent
the total number of command executions and the relative percentages
of each. The second set of numbers, Last and
Pct, do the same thing but consider only commands
executed in the last refresh cycle

B.1.3 Status View

The
newest view in mytop complements command view.
Status view summarizes the noncommand-related counters in
SHOW STATUS output. Even
without the command counters listed, there are quite a number of
values (over 60 as of MySQL 4.0). To see them all,
you'll need a tall window. Figure B-3 shows an example on a moderately busy server.

Figure B-3. mytop status view

The Total column displays the current value of
each counter, while the Change column contains the
delta from the last refresh interval. On a color display, positive
changes are reported in yellow and negative in red. Unchanging values
are shown in the default color. The i keystroke can be used in this
view to filter out unchanging values from the display. This can be
quite useful on small displays because many of the values
aren't changing frequently.