MySQL Shell is the advanced MySQL client, which has many excellent features. In this blog, I am going to explain the MySQL shell commands “\show” and “\watch”. Both commands are very useful to monitor the MySQL process. It provides more insights into the foreground and background threads as well.
Overview
“\show” and “\watch” are the MySQL shell commands, which can be executed using the Javascript (JS), Python (Py), and SQL interfaces. Both commands are providing the same information, but the difference is you can refresh the results when using the command “\watch”. The refresh interval is two seconds.
- \show: Run the specified report using the provided options and arguments.
- \watch: Run the specified report using the provided options and arguments, and refresh the results at regular intervals.
Below are the available options you can use with the “\show” or “\watch” command to retrieve the data.
1 2 3 4 5 | MySQL localhost:33060+ ssl percona JS > \show Available reports: query, thread, threads. MySQL localhost:33060+ ssl percona JS > \watch Available reports: query, thread, threads. |
- Query
- Thread
- Threads
“\show” with “query”
It will just execute the query provided as an argument within the double quotes and print the result.
1 2 3 4 5 6 7 8 9 | MySQL localhost:33060+ ssl percona JS > \show query "select database()" +------------+ | database() | +------------+ | percona | +------------+ MySQL localhost:33060+ ssl percona JS > \show query --vertical "select database()" *************************** 1. row *************************** database(): percona |
You can also use the same option with the “\watch” command. Let’s say, if you want to monitor the processlist for every two seconds, then you can use the command like
1 | \watch query “show processlist” |
“\show” with “thread”
This option is designed to provide various information about the specific thread. Below are some of the important details you can retrieve from the specific thread.
- InnoDB details ( –innodb )
- Locks Details ( –locks )
- Prepared statement details ( –prep-stmts )
- Client connection details ( –client )
- Session status ( –status ) and session variables details ( –vars )
Example:
I am going to show the example for the below scenario.
At session1:
My connection id is 121. I have started the transaction and updated the row where “id=3”. But, still not committed or rolled back the transaction.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | mysql> \r Connection id: 121 Current database: percona mysql> select * from herc; +------+--------+ | id | name | +------+--------+ | 1 | jc | | 2 | herc7 | | 3 | sakthi | +------+--------+ 3 rows in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update herc set name='xxx' where id=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
At session 2:
My connection id is 123. I have started the transaction and tried to update the same row where “id=3”. The query is still executing because the transaction from session 1 is blocking the row ( id = 3 )
1 2 3 4 5 6 7 8 | mysql> \r Connection id: 123 Current database: percona mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update herc set name='hercules' where id=3; |
Now let’s use the command “\show thread” for both connection IDs (121, 123) and see what information we can get.
General information ( conncetion id = 123 ):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | MySQL localhost:33060+ ssl JS > \show thread --cid=123 --general GENERAL Thread ID: 161 Connection ID: 123 Thread type: FOREGROUND Program name: mysql User: root Host: localhost Database: percona Command: Query Time: 00:08:49 State: updating Transaction state: LOCK WAIT Prepared statements: 0 Bytes received: 282 Bytes sent: 131 Info: update herc set name='hercules' where id=3 Previous statement: NULL |
From the general information, you can find some basic information about your id.
InnoDB information:
1 2 3 4 5 6 7 8 9 10 11 | MySQL localhost:33060+ ssl JS > \show thread --cid=123 --innodb INNODB STATUS State: LOCK WAIT ID: 28139179 Elapsed: 00:10:23 Started: 2021-02-23 17:40:06.000000 Isolation level: REPEATABLE READ Access: READ WRITE Locked tables: 1 Locked rows: 1 Modified rows: 0 |
Using the “–innodb” option, you can find out the information about the InnoDB like transaction state, thread start time, elapsed time, locked tables, rows, modified rows.
Locks information:
For connection id 123:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | MySQL localhost:33060+ ssl JS > \show thread --cid=123 --locks LOCKS Waiting for InnoDB locks +---------------------+----------+------------------+--------+-----+-------+----------------+---------------------+----------+ | Wait started | Elapsed | Locked table | Type | CID | Query | Account | Transaction started | Elapsed | +---------------------+----------+------------------+--------+-----+-------+----------------+---------------------+----------+ | 2021-02-23 17:40:06 | 00:12:27 | `percona`.`herc` | RECORD | 121 | NULL | root@localhost | 2021-02-23 17:39:32 | 00:13:01 | +---------------------+----------+------------------+--------+-----+-------+----------------+---------------------+----------+ Waiting for metadata locks N/A Blocking InnoDB locks N/A Blocking metadata locks N/A |
Connection id 123 is from session 2. Which is currently waiting to release the lock from connection id 121 (session 1). Let’s see the “–locks” status for connection id 121.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | MySQL localhost:33060+ ssl JS > \show thread --cid=121 --locks LOCKS Waiting for InnoDB locks N/A Waiting for metadata locks N/A Blocking InnoDB locks +---------------------+----------+------------------+--------+-----+--------------------------------------------+ | Wait started | Elapsed | Locked table | Type | CID | Query | +---------------------+----------+------------------+--------+-----+--------------------------------------------+ | 2021-02-23 17:40:06 | 00:14:23 | `percona`.`herc` | RECORD | 123 | update herc set name='hercules' where id=3 | +---------------------+----------+------------------+--------+-----+--------------------------------------------+ Blocking metadata locks N/A |
Here, you can find the details on “Blocking InnoDB Locks”. It blocks the connection id 123 (session 2).
Like the above example, you can explore the other options as well, which are helpful.
“\show” with “threads”
This is very helpful to know the details about your ongoing threads. It will provide the details about both “FOREGROUND” and “BACKGROUND” threads. There are many columns, which are very useful to know about thread status. You can filter the needed columns with the option “-o”. By executing the command “\show threads –help”, you can find all the available options and their purposes.
- It supports the WHERE clause for generating the report
- It supports ORDER BY for generating the report
- It supports LIMIT for generating the report.
Below, I am sharing some examples, which will help you to understand how we can use the “threads” command with the MySQL shell.
- How to find the running “FOREGROUND” threads details
- How to find the running “BACKGROUND” threads details
- How to find the top five threads, which are consuming more memory from a particular user
- How to find the Query digest details from ongoing threads
- How to find the top five threads which consumed huge IO operations
- How to find the top five blocked and blocking threads
I am running the sysbench against the server to get my database loaded.
1 | sysbench /usr/share/sysbench/oltp_read_write.lua --events=0 --time=30000 --mysql-host=localhost --mysql-user=root --mysql-password=Course@321 --mysql-port=3306 --delete_inserts=10 --index_updates=10 --non_index_updates=10 --report-interval=1 --threads=100 run |
How to Find the Running “FOREGROUND” Threads Details
You can use the option “–foreground” to see all the running foreground threads.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | MySQL localhost:33060+ ssl JS > \show threads --foreground +-----+-----+-----------------+-----------+---------+---------+----------+------------------------+-----------+-------------------------------------------------------------------+-----------+ | tid | cid | user | host | db | command | time | state | txstate | info | nblocking | +-----+-----+-----------------+-----------+---------+---------+----------+------------------------+-----------+-------------------------------------------------------------------+-----------+ | 27 | 114 | root | localhost | NULL | Query | 00:00:00 | executing | NULL | SELECT json_object('cid',t.PRO ... READ_ID = io.thread_id WHERE t | 0 | | 42 | 5 | event_scheduler | localhost | NULL | Daemon | 17:42:20 | Waiting on empty queue | NULL | NULL | 0 | | 46 | 7 | NULL | NULL | NULL | Daemon | 17:42:20 | Suspending | NULL | NULL | 0 | | 158 | 120 | root | localhost | NULL | Sleep | 00:32:24 | NULL | NULL | . . . . .. . ... . . . .. . .. . .. . . . . . .. . ... . . . .. . .. . .. . . . . . .. . ... . . . .. . .. . .. . | 0 | | 260 | 222 | root | localhost | sbtest | Execute | 00:00:00 | updating | LOCK WAIT | NULL | 1 | | 261 | 223 | root | localhost | sbtest | Execute | 00:00:00 | updating | LOCK WAIT | NULL | 0 | +-----+-----+-----------------+-----------+---------+---------+----------+------------------------+-----------+-------------------------------------------------------------------+-----------+ |
How to Find the Running “BACKGROUND” Threads Details
This will give detailed information about the background threads, mostly InnoDB. You can use the flag “–background” to get these details. These details will be really helpful for debugging the performance issues.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | MySQL localhost:33060+ ssl JS > \show threads --background +-----+--------------------------------------+---------+-----------+------------+------------+------------+ | tid | name | nio | ioltncy | iominltncy | ioavgltncy | iomaxltncy | +-----+--------------------------------------+---------+-----------+------------+------------+------------+ | 1 | sql/main | 92333 | 192.51 ms | 229.63 ns | 96.68 us | 1.42 ms | | 3 | innodb/io_ibuf_thread | NULL | NULL | NULL | NULL | NULL | | 4 | innodb/io_log_thread | NULL | NULL | NULL | NULL | NULL | | 5 | innodb/io_read_thread | NULL | NULL | NULL | NULL | NULL | | 6 | innodb/io_read_thread | NULL | NULL | NULL | NULL | NULL | | 7 | innodb/io_read_thread | NULL | NULL | NULL | NULL | NULL | | 8 | innodb/io_read_thread | NULL | NULL | NULL | NULL | NULL | | 9 | innodb/io_write_thread | 37767 | 45.83 s | 1.26 us | 1.21 ms | 17.81 ms | | 10 | innodb/io_write_thread | 36763 | 44.57 s | 1.23 us | 1.21 ms | 30.11 ms | | 11 | innodb/io_write_thread | 37989 | 45.87 s | 1.26 us | 1.21 ms | 24.03 ms | | 12 | innodb/io_write_thread | 37745 | 45.78 s | 1.23 us | 1.21 ms | 28.93 ms | | 13 | innodb/page_flush_coordinator_thread | 456128 | 2.19 min | 5.27 us | 419.75 us | 29.98 ms | | 14 | innodb/log_checkpointer_thread | 818 | 479.84 ms | 2.62 us | 710.63 us | 9.26 ms | | 15 | innodb/log_flush_notifier_thread | NULL | NULL | NULL | NULL | NULL | | 16 | innodb/log_flusher_thread | 1739344 | 41.71 min | 1.46 us | 1.44 ms | 30.22 ms | | 17 | innodb/log_write_notifier_thread | NULL | NULL | NULL | NULL | NULL | | 18 | innodb/log_writer_thread | 5239157 | 10.23 min | 1.14 us | 117.16 us | 29.02 ms | | 19 | innodb/srv_lock_timeout_thread | NULL | NULL | NULL | NULL | NULL | | 20 | innodb/srv_error_monitor_thread | NULL | NULL | NULL | NULL | NULL | | 21 | innodb/srv_monitor_thread | NULL | NULL | NULL | NULL | NULL | | 22 | innodb/buf_resize_thread | NULL | NULL | NULL | NULL | NULL | | 23 | innodb/srv_master_thread | 270 | 4.02 ms | 6.75 us | 14.90 us | 41.74 us | | 24 | innodb/dict_stats_thread | 3088 | 429.12 ms | 3.22 us | 138.96 us | 5.93 ms | | 25 | innodb/fts_optimize_thread | NULL | NULL | NULL | NULL | NULL | | 26 | mysqlx/worker | NULL | NULL | NULL | NULL | NULL | | 28 | mysqlx/acceptor_network | NULL | NULL | NULL | NULL | NULL | | 32 | innodb/buf_dump_thread | 1060 | 7.61 ms | 2.74 us | 7.18 us | 647.18 us | | 33 | innodb/clone_gtid_thread | 4 | 689.86 us | 4.46 us | 172.46 us | 667.95 us | | 34 | innodb/srv_purge_thread | 7668 | 58.21 ms | 3.34 us | 336.20 us | 1.64 ms | | 35 | innodb/srv_worker_thread | 30 | 278.22 us | 5.57 us | 9.27 us | 29.69 us | | 36 | innodb/srv_purge_thread | NULL | NULL | NULL | NULL | NULL | | 37 | innodb/srv_worker_thread | NULL | NULL | NULL | NULL | NULL | | 38 | innodb/srv_worker_thread | 24 | 886.23 us | 5.24 us | 36.93 us | 644.75 us | | 39 | innodb/srv_worker_thread | NULL | NULL | NULL | NULL | NULL | | 40 | innodb/srv_worker_thread | 22 | 223.92 us | 5.84 us | 10.18 us | 18.34 us | | 41 | innodb/srv_worker_thread | NULL | NULL | NULL | NULL | NULL | | 43 | sql/signal_handler | NULL | NULL | NULL | NULL | NULL | | 44 | mysqlx/acceptor_network | NULL | NULL | NULL | NULL | NULL | +-----+--------------------------------------+---------+-----------+------------+------------+------------+ |
How to Find the Top Five Threads, Which are Consuming More Memory From a Particular User
From the below example, I am finding the top five threads, which are consuming more memory from user “root”.
1 2 3 4 5 6 7 8 9 10 | MySQL localhost:33060+ ssl JS > \show threads --foreground -o tid,user,memory,started --order-by=memory --desc --where "user = 'root'" --limit=5 +-----+------+----------+---------------------+ | tid | user | memory | started | +-----+------+----------+---------------------+ | 247 | root | 9.47 MiB | 2021-02-23 18:30:29 | | 166 | root | 9.42 MiB | 2021-02-23 18:30:29 | | 248 | root | 9.41 MiB | 2021-02-23 18:30:29 | | 186 | root | 9.39 MiB | 2021-02-23 18:30:29 | | 171 | root | 9.38 MiB | 2021-02-23 18:30:29 | +-----+------+----------+---------------------+ |
How to Find the Query Digest Details From Ongoing Threads
You can use the options “digest” and “digesttxt” to find the digest output of the running threads.
1 2 3 4 5 6 7 | MySQL localhost:33060+ ssl JS > \show threads -o tid,cid,info,digest,digesttxt --where "digesttxt like 'UPDATE%'" --vertical *************************** 1. row *************************** tid: 161 cid: 123 info: update herc set name='hercules' where id=3 digest: 7832494e46eee2b28a46dc1fdae2e1b18d1e5c00d42f56b5424e5716d069fd39 digesttxt: UPDATE `herc` SET NAME = ? WHERE `id` = ? |
How to Find the Top Five Threads Which Consumed Huge IO Operations
1 2 3 4 5 6 7 8 9 10 | MySQL localhost:33060+ ssl JS > \show threads -o tid,cid,nio --order-by=nio --desc --limit=5 +-----+-----+-------+ | tid | cid | nio | +-----+-----+-------+ | 27 | 114 | 36982 | | 238 | 200 | 2857 | | 215 | 177 | 2733 | | 207 | 169 | 2729 | | 232 | 194 | 2724 | +-----+-----+-------+ |
Nio → Total number of IO events for the thread.
How to Find the Top Five Blocked and Blocking Threads
- nblocked – The number of other threads blocked by the thread
- nblocking – The number of other threads blocking the thread
- Ntxrlckd – The approximate number of rows locked by the current InnoDB transaction
Blocking threads:
1 2 3 4 5 6 7 8 9 10 | MySQL localhost:33060+ ssl JS > \show threads -o tid,cid,nblocked,nblocking,ntxrlckd,txstate --order-by=nblocking --desc --limit 5 +-----+-----+----------+-----------+----------+-----------+ | tid | cid | nblocked | nblocking | ntxrlckd | txstate | +-----+-----+----------+-----------+----------+-----------+ | 230 | 192 | 0 | 7 | 5 | LOCK WAIT | | 165 | 127 | 0 | 6 | 2 | LOCK WAIT | | 215 | 177 | 0 | 5 | 9 | LOCK WAIT | | 221 | 183 | 0 | 4 | NULL | NULL | | 233 | 195 | 1 | 4 | NULL | NULL | +-----+-----+----------+-----------+----------+-----------+ |
Blocked threads:
1 2 3 4 5 6 7 8 9 10 | MySQL localhost:33060+ ssl JS > \show threads -o tid,cid,nblocked,nblocking,ntxrlckd,txstate --order-by=nblocked --desc --limit 5 +-----+-----+----------+-----------+----------+-----------+ | tid | cid | nblocked | nblocking | ntxrlckd | txstate | +-----+-----+----------+-----------+----------+-----------+ | 203 | 165 | 15 | 0 | 8 | LOCK WAIT | | 181 | 143 | 10 | 1 | 5 | LOCK WAIT | | 223 | 185 | 9 | 0 | 8 | LOCK WAIT | | 209 | 171 | 9 | 1 | 5 | LOCK WAIT | | 178 | 140 | 6 | 0 | 7 | LOCK WAIT | +-----+-----+----------+-----------+----------+-----------+ |
Like this, you have many options to explore and you can generate the report based on your requirements. I hope this blog post is helpful to understand the “\show” and “\watch” commands from the MySQL shell!
Thanks for the nice article. Is it also possible to list the top 5 CPU consuming threads?