Previous Section  < Day Day Up >  Next Section

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.[1]

[1] In MySQL 3.23 and 4.0, killing a query also terminates the client's connection to the server. In other words, it kills the connection, and the query dies as a byproduct of that.

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.

    Previous Section  < Day Day Up >  Next Section