B.4 Common Tasks
With the basic operation of
mytop covered, let's look at
the steps you take to perform common tasks using
mytop. This it intended to give you a hands-on
feel for using mytop.
- Find, analyze, and kill long-running queries
-
When
mytop starts, it sorts threads by the Time
column—that is, how long the thread has been in that state.
Those that have most recently changed appear at the top of the
screen. To locate long-running queries, first remove all idle threads
from the display by pressing i, then reverse the sort order by
pressing o. The resulting display will show the longest running
queries at the top of the screen.
Once you've located a long-running query, you can
obtain the full query by pressing f and entering the thread ID when
prompted. When looking at the full query, you can ask MySQL to
explain the query by pressing e. Or you may kill the query by
pressing k and supplying the thread ID.
- Determine what type of queries MySQL has been running
-
It's often
useful to know whether MySQL is running more
SELECT or more INSERT queries.
Maybe you suspect an application is misbehaving, or perhaps
you'd simply like to compute your
server's typical read to write ratio. In
mytop's thread view (Figure B-1), it displays the percentage of
SELECT, INSERT,
UPDATE, and DELETE queries. On
the right side of the header, you'll see something
like this:
Se/In/Up/De(%): 61/30/02/05
... 63/07/12/10
The first line means that, overall, 61% of the
server's queries are SELECTs, 30%
are INSERTs, 2% are UPDATEs,
and 5% are DELETEs. The second line displays
values that apply to the last refresh interval (5 seconds by default)
only. The two together can give a quick feel for what your server has
been doing recently and how that compares to the longer term average.
If you want more detail, press c to switch mytop
into command view. There you'll find detailed counts
and percentages for each type of command or query executed. The first
column of numbers summarizes overall counts (since the server was
started or counters reset), while the second set of numbers reflects
the last refresh interval only.
- Kill a group of queries
-
Use mytop's
"super-kill" feature by pressing K.
You'll be prompted for a username, and
mytop will then kill all of that
user's threads. In the future this may be extended
to evaluate more complex expressions, such as killing all nonidle
threads from a given hostname or IP address.
- Limit the display to a particular user or host
-
You can ask mytop to filter out all threads
except those from a given host or those owned by a given user. If you
press u, mytop prompts for a username to filter
on. Similarly, pressing h allows you to provide a hostname or IP
address which is used to filter the display. If you supply both,
mytop restricts the display based on both
criteria.
To clear the filtering, you can press F to remove all filters at
once. Otherwise, you can use the u or h keys to remove either of the
filters manually.
|