mytop is an open source tool that provides real-time performance statistics and query information for MySQL. It's named after and inspired by the Unix operating system tool, top.
If you're completing this procedure in our lab, we've pre-installed mytop for you. Otherwise, the install directions are in the README on Github.
The most comprehensive usage documentation is in the man page, which you can access with man mytop on a server that has mytop installed, or read on Github.
First, we're going to create some load on the server, since our lab server doesn't have any "real" traffic. If you're running mytop on an existing server, skip ahead to step 2.
SSH to mytop.example.com and set up a long-running mysqlslap background job.
mytop ~ $ mysqlslap --auto-generate-sql --number-of-queries=1000000 & [1] 2776 mytop ~ $
This starts mysqlslap running a million INSERTs and DELETEs against a database and table it generates automatically.
In the sample output above, mysqlslap is running in the background as job 1, process id 2776.
Now run mytop to view realtime MySQL performance.
mytop ~ $ mytop
The output will update every 5 seconds, the example below is after it has had a few iterations to collect data.
Here's a complete screenshot:
MySQL on localhost (5.5.31) up 0+00:56:34 [19:44:54]
Queries: 55.2k qps: 17 Slow: 0.0 Se/In/Up/De(%): 45/55/00/00
qps now: 18 Slow qps: 0.0 Threads: 3 ( 2/ 0) 44/53/00/00
Key Efficiency: 90.0% Bps in/out: 1.8k/ 4.8M Now in/out: 1.8k/ 1.5M
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
49 root localhost mysqlslap 0 Query INSERT INTO t1 VALUES (95275444,'bNIrBDBl8
50 root localhost test 0 Query show full processlist
48 root localhost mysqlslap 49 Sleep
Let's break that down line-by-line. In the first line:
MySQL on localhost (5.5.31) up 0+00:56:34 [19:44:54]
localhost is the host name we're connected to.
5.5.31 is the MySQL version on that server.
up 0+00:56:34 is the MySQL process's uptime, in day+hour:minute:second format.
19:44:54 is the current time (our lab servers use UTC).
In the second line, all statistics are cumulative since server startup.
Queries: 55.2k qps: 17 Slow: 0.0 Se/In/Up/De(%): 45/55/00/00
Queries: 55.2k the server has processed more than 55,200 queries.
qps: 17 the server averages 17 queries per second.
Slow: 0.0 the server averages 0 slow queries per second. (This mysqlslap configuration runs lots of short, simple queries.)
45/55/00/00 of all the traffic this server has handled, 45% of queries are SELECTs, 55% INSERTs, 0% UPDATEs, and 0% DELETEs.
In the third line, all statistics are only from the last sample (5 seconds, by default).
qps now: 18 Slow qps: 0.0 Threads: 3 ( 2/ 0) 44/53/00/00
qps now: 18 In the last 5 seconds, the server averaged 18 queries per second.
Slow qps: 0.0 the server averaged 0 slow queries per second.
Threads 3 ( 2/ 0) There are 3 connected threads, 2 are active (one is sleeping), and there are 0 threads in the thread cache.
44/53/00/00 in this 5-second sample, 44% of queries were SELECTs, 53% were INSERTs.
In the fourth line:
Key Efficiency: 90.0% Bps in/out: 1.8k/ 4.8M Now in/out: 1.8k/ 1.5M
Key Efficiency: 90.0% 90% of keys are read from the buffer, not from disk.
Bps in/out: 1.8k/ 4.8M Since startup, MySQL has averaged 1.8kbps inbound traffic and 4.8Mbps outbound traffic.
Now in/out: 1.8k/ 1.5M In the last 5 seconds, MySQL has averaged 1.8kbps inbound traffic and 1.5Mbps outbound traffic.
The remainder of the output shows three active threads.
Id User Host/IP DB Time Cmd Query or State -- ---- ------- -- ---- --- ---------- 49 root localhost mysqlslap 0 Query INSERT INTO t1 VALUES (95275444,'bNIrBDBl8 50 root localhost test 0 Query show full processlist 48 root localhost mysqlslap 49 Sleep
Thread 49 is mysqlslap inserting a value.
Thread 50 is mytop collecting this process table.
Thread 48 is sleeping (not processing a query, but still connected).
Press q to close mytop.
We used & to start mysqlslap as a background job. Look up the job number.
mytop ~ $ jobs [1]+ Running mysqlslap --auto-generate-sql --number-of-queries=1000000 &
It's listed as job 1, bring that job from the background to the foreground with fg
mytop ~ $ fg 1 mysqlslap --auto-generate-sql --number-of-queries=1000000
Stop mysqlslap by pressing control + c
^C mytop ~ $
This will start a new mysqlslap load, but instead of running the million statements sequentially, it will start 10 threads in parallel.
mytop ~ $ mysqlslap --auto-generate-sql --concurrency=10 --number-of-queries=1000000 & [1] 2997 mytop ~ $
Now start mytop again to watch the large workload. This time, we'll specify that mytop should connect to the mysqlslap database, so we can look in depth at queries in the next step.
mytop ~ $ mytop --database mysqlslap
Here's a screenshot after mytop has collected a few 5-second samples.
Notice that specifying the --database doesn't change this view.
MySQL on localhost (5.5.31) up 0+01:29:07 [21:42:06]
Queries: 21.5k qps: 4 Slow: 0.0 Se/In/Up/De(%): 45/55/00/00
qps now: 43 Slow qps: 0.0 Threads: 12 ( 11/ 0) 45/55/00/00
Key Efficiency: 90.0% Bps in/out: 447.6/659.4k Now in/out: 4.6k/14.8M
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
11 root localhost mysqlslap 0 Query SELECT intcol1,charcol1 FROM t1
12 root localhost mysqlslap 0 Query SELECT intcol1,charcol1 FROM t1
13 root localhost mysqlslap 0 Query SELECT intcol1,charcol1 FROM t1
14 root localhost mysqlslap 0 Query SELECT intcol1,charcol1 FROM t1
15 root localhost mysqlslap 0 Query SELECT intcol1,charcol1 FROM t1
16 root localhost mysqlslap 0 Query SELECT intcol1,charcol1 FROM t1
17 root localhost mysqlslap 0 Query INSERT INTO t1 VALUES (100669,'q
18 root localhost mysqlslap 0 Query SELECT intcol1,charcol1 FROM t1
19 root localhost mysqlslap 0 Query SELECT intcol1,charcol1 FROM t1
20 root localhost mysqlslap 0 Query INSERT INTO t1 VALUES (100669,'q
21 root localhost test 0 Query show full processlist
10 root localhost mysqlslap 86 Sleep
What's new in this run?
In the third line, 12 ( 11/ 0) summarizes the 12 connected threads, 11 active (one sleeping), and still 0 threads in the thread cache.
At the bottom, this screenshot happened to capture eight SELECT statements in progress, and just two INSERTs, although we can see in lines 2 and 3 that the blend since start and in this sample are both 45/55/00/00, meaning 45% SELECT and 55% INSERT.
Pick a query that looks interesting. From this screenshot, I'd like to see the complete INSERT query (not just what fits on screen).
MySQL on localhost (5.5.31) up 0+01:29:07 [21:42:06]
Queries: 21.5k qps: 4 Slow: 0.0 Se/In/Up/De(%): 45/55/00/00
qps now: 43 Slow qps: 0.0 Threads: 12 ( 11/ 0) 45/55/00/00
Key Efficiency: 90.0% Bps in/out: 447.6/659.4k Now in/out: 4.6k/14.8M
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
11 root localhost mysqlslap 0 Query SELECT intcol1,charcol1 FROM t1
12 root localhost mysqlslap 0 Query SELECT intcol1,charcol1 FROM t1
13 root localhost mysqlslap 0 Query SELECT intcol1,charcol1 FROM t1
14 root localhost mysqlslap 0 Query SELECT intcol1,charcol1 FROM t1
15 root localhost mysqlslap 0 Query SELECT intcol1,charcol1 FROM t1
16 root localhost mysqlslap 0 Query SELECT intcol1,charcol1 FROM t1
17 root localhost mysqlslap 0 Query INSERT INTO t1 VALUES (100669,'q
18 root localhost mysqlslap 0 Query SELECT intcol1,charcol1 FROM t1
19 root localhost mysqlslap 0 Query SELECT intcol1,charcol1 FROM t1
20 root localhost mysqlslap 0 Query INSERT INTO t1 VALUES (100669,'q
21 root localhost test 0 Query show full processlist
10 root localhost mysqlslap 86 Sleep
From the first column, I see that was running on thread id 20.
Press f then type the thread Id, for example 20 and press enter.
Thread 20 was executing following query: INSERT INTO t1 VALUES (100669,'qMa5SuKo4M5OM7ldvisSc6WK9rsG9E8sSixocHdgfa5uiiNTGFxkDJ4EAwWC2e4NL1BpAgWiFRcp1zIH6F1BayPdmwphatwnmzdwgzWnQ6SRxmcvtd6JRYwEKdvuWr') -- paused. press any key to resume or (e) to explain --
There's no point running explain on this query, explaining INSERTs is not supported until MySQL 5.6 and as we see above, this server is running MySQL 5.5.31.
So press any other key, and choose a SELECT query:
MySQL on localhost (5.5.31) up 0+02:03:25 [22:16:26]
Queries: 56.8k qps: 8 Slow: 0.0 Se/In/Up/De(%): 45/54/00/00
qps now: 35 Slow qps: 0.0 Threads: 12 ( 11/ 0) 45/54/00/00
Key Efficiency: 90.0% Bps in/out: 847.0/ 5.0M Now in/out: 3.6k/52.6M
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
25 root localhost mysqlslap 0 Query show full processlist
11 root localhost mysqlslap 1 Sleep
12 root localhost mysqlslap 1 Query SELECT intcol1,charcol1 FROM t1
13 root localhost mysqlslap 1 Query SELECT intcol1,charcol1 FROM t1
14 root localhost mysqlslap 1 Query SELECT intcol1,charcol1 FROM t1
15 root localhost mysqlslap 1 Query SELECT intcol1,charcol1 FROM t1
16 root localhost mysqlslap 1 Query SELECT intcol1,charcol1 FROM t1
17 root localhost mysqlslap 1 Sleep
18 root localhost mysqlslap 1 Query INSERT INTO t1 VALUES (73673339,
19 root localhost mysqlslap 1 Sleep
20 root localhost mysqlslap 1 Query SELECT intcol1,charcol1 FROM t1
10 root localhost mysqlslap 2146 Sleep
Note that in the previous step, we connected mytop to the mysqlslap database, the same database this row indicates in the fourth column.
Press f, type the session Id you've chosen, then press enter.
Thread 12 was executing following query: SELECT intcol1,charcol1 FROM t1 -- paused. press any key to resume or (e) to explain --
This time, press e and mytop will show you the EXPLAIN results for that query. Unfortunately the queries and the tables mysqlslap uses are both very boring, but using this with your production data will be much more interesting.
EXPLAIN SELECT intcol1,charcol1 FROM t1:
*** row 1 ***
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 25916
Extra: NULL
-- paused. press any key to resume --If you're running this in our lab, you can just skip to the bottom of the page and press Release the Lab Servers and leave the clean up to us.
Press q to close mytop.
Look up mysqlslap's job number.
mytop ~ $ jobs [1]+ Running mysqlslap --auto-generate-sql --concurrency=10 --number-of-queries=1000000 &
Bring job 1, to the foreground with fg
mytop ~ $ fg 1 mysqlslap --auto-generate-sql --concurrency=10 --number-of-queries=1000000
Stop mysqlslap by pressing control + c
^C mytop ~ $