Previous Section  < Day Day Up >  Next Section

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
figs/hpsql_AB01.gif

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
figs/hpsql_AB02.gif

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
figs/hpsql_AB03.gif

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.

    Previous Section  < Day Day Up >  Next Section