Developer Forums | About Us | Site Map
Search  
HOME > TUTORIALS > SERVER SIDE CODING > ADMINISTRATION TUTORIALS > USING MYTOP: A MYSQL MONITOR


Sponsors





Useful Lists

Web Host
site hosted by netplex

Online Manuals

Using Mytop: A MySQL Monitor
By Matthew Drouin - 2003-11-29 Page:  1 2 3 4 5

Mytop Thread View Header

The thread view is the first view we see when we start up mytop. You can get back to this view if you are in another view by pressing the "t" key. This view has a lot of information to take in so we will start by splitting the page up into 2 different sections. The first section of the view is called the header which can be toggled on or off by pressing "H". The first line in this first section is pretty straight forward; in the top left we are told what server we are monitoring and the version of mysql. On the right hand side of the line we see the uptime for the system along with the system time.

The second two lines are interesting in that for the most part the first line and the second line have the same information; the first line is since the server started or since the counter was reset and the second line is the average of what has happened since the last time mytop refreshed. The default refresh rate of mytop is 5 seconds but can be changed by pressing the "s" button which will allow you to change the number of seconds, or the delay, between the screen updates.

mytop thread view screen shot

Starting at the top left we will see Queries. The number to the right of that, which in the picture below is 369.1k, is the total number of queries the MySQL server has processed since it started. To the right of Queries we see qps which stands for "queries per second", but more accurately this is the average queries per second since the server started.

We are going to jump around a little now as we see qps now right below the qps. This qps now stands for the average number of queries per second the server has handled since the last time mytop refreshed. The normal update time for mytop is 5 seconds so if 100 queries were run in the last 5 seconds since the update then the qps now number would be 20. To the right of the qps and qps now we see Slow and Slow qps. The Slow number is pretty easy to figure out in that they are the number of slow queries run by the server since the serer was started. The Slow qps number is the average number of slow queries per second since the last refresh of mytop.

One important thing to know about this interface if you reset the counters is the fact that Queries does not reset. This means anything discussed that uses the number of queries compared to another number to do a calculation will be off if reset counters is run (reset of counters is done by pressing the "r" key).

The next part of the view, which is very important for understanding what is happening within the MySQL server itself, is the Se/In/Up/De(%), which stands for Select, Insert, Update, Delete. This tells us what percent of the sql statements are Selects, Inserts, Updates, or Deletes respectively. There are more query types than that but those are the 4 major ones and the command view, which we will discuss next, has the rest of them listed for us. Below this again we have the Se/In/Up/De % for the queries that have run since the last mytop refresh.

There is one column we did not discuss in the display and that is the Threads column. The numbers in the image below are 2 ( 1/ 11) and what that stands for is 2 threads are connected, 1 thread is running, and 11 threads are cashed. Having cashed threads is important because if you do not have a cached thread and a request comes in then MySQL has to create a thread and if this happens enough times it can cause MySQL to take up more CPU time of the server than one would like doing the useless task of creating threads.

Cache Hits: 0 Hits/s: 0.0 Hits now: 0.0 Ratio: 0.0% Ratio now: 0.0%
?>

If you are running MySQL 4.0 or greater with query cache turned on you will see a third line as well that would look something similar to the line below.

This is information regarding query cache which was implemented in MySQL 4.0. Cache Hits stands for the number of queries that were served from the query cache since the server started. Hits/s is the average number of queries the server is serving up from the cache per second. Hits now is the number of queries served from the cache since mytop last refreshed. Ratio is the percent of queries that are served from the query cache since the server started. Ratio now is the percent of queries served from the query cache since the last mytop refresh.

The key efficiency tells us the percent of queries that are using a key in the query. When doing any kind of SQL statement one should use a key unless it is a statement that is not often run. The reason for this is that queries without keys take longer to run but at the same time the more keys a database uses the larger a database will be because the key information has to be stored. So if a query is not run often and does not cause a load on the server due to the lack of a key in the query then you can probably get away without having a key in the query.

Bps in/out stands for Bytes per second with the first number being the average number of bytes per second into the server since it was started and the second is the average number of bytes per second out from the server. The Now in/out is the average number of bytes in and out of the server since mytop has refreshed.



View Using Mytop: A MySQL Monitor Discussion

Page:  1 2 3 4 5 Next Page: Mytop Thread View Body

Copyright 2003-2004 Matthew Drouin. All rights reserved.


Copyright 2004-2017 GrindingGears.com. All rights reserved.
Article copyright and all rights retained by the author.