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.
“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.
Below are the available options you can use with the “show” or “watch” command to retrieve the data.
|
1 |
MySQL localhost:33060+ ssl percona JS > show<br>Available reports: query, thread, threads.<br><br>MySQL localhost:33060+ ssl percona JS > watch<br>Available reports: query, thread, threads. |
It will just execute the query provided as an argument within the double quotes and print the result.
|
1 |
MySQL localhost:33060+ ssl percona JS > show query "select database()"<br>+------------+<br>| database() |<br>+------------+<br>| percona |<br>+------------+<br>MySQL localhost:33060+ ssl percona JS > show query --vertical "select database()"<br>*************************** 1. row ***************************<br>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" |
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.
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 |
mysql> r<br>Connection id: 121<br>Current database: percona<br><br>mysql> select * from herc;<br>+------+--------+<br>| id | name |<br>+------+--------+<br>| 1 | jc |<br>| 2 | herc7 |<br>| 3 | sakthi |<br>+------+--------+<br>3 rows in set (0.00 sec)<br><br>mysql> begin;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql> update herc set name='xxx' where id=3;<br>Query OK, 1 row affected (0.00 sec)<br>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 |
mysql> r<br>Connection id: 123<br>Current database: percona<br><br>mysql> begin;<br>Query OK, 0 rows affected (0.00 sec)<br><br>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 |
MySQL localhost:33060+ ssl JS > show thread --cid=123 --general<br>GENERAL<br>Thread ID: 161<br>Connection ID: 123<br>Thread type: FOREGROUND<br>Program name: mysql<br>User: root<br>Host: localhost<br>Database: percona<br>Command: Query<br>Time: 00:08:49<br>State: updating<br>Transaction state: LOCK WAIT<br>Prepared statements: 0<br>Bytes received: 282<br>Bytes sent: 131<br>Info: update herc set name='hercules' where id=3<br>Previous statement: NULL |
From the general information, you can find some basic information about your id.
InnoDB information:
|
1 |
MySQL localhost:33060+ ssl JS > show thread --cid=123 --innodb<br>INNODB STATUS<br>State: LOCK WAIT<br>ID: 28139179<br>Elapsed: 00:10:23<br>Started: 2021-02-23 17:40:06.000000<br>Isolation level: REPEATABLE READ<br>Access: READ WRITE<br>Locked tables: 1<br>Locked rows: 1<br>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 |
MySQL localhost:33060+ ssl JS > show thread --cid=123 --locks<br>LOCKS<br>Waiting for InnoDB locks<br>+---------------------+----------+------------------+--------+-----+-------+----------------+---------------------+----------+<br>| Wait started | Elapsed | Locked table | Type | CID | Query | Account | Transaction started | Elapsed |<br>+---------------------+----------+------------------+--------+-----+-------+----------------+---------------------+----------+<br>| 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 |<br>+---------------------+----------+------------------+--------+-----+-------+----------------+---------------------+----------+<br><br>Waiting for metadata locks<br>N/A<br><br>Blocking InnoDB locks<br>N/A<br><br>Blocking metadata locks<br>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 |
MySQL localhost:33060+ ssl JS > show thread --cid=121 --locks<br>LOCKS<br><br>Waiting for InnoDB locks<br>N/A<br><br>Waiting for metadata locks<br>N/A<br><br>Blocking InnoDB locks<br>+---------------------+----------+------------------+--------+-----+--------------------------------------------+<br>| Wait started | Elapsed | Locked table | Type | CID | Query |<br>+---------------------+----------+------------------+--------+-----+--------------------------------------------+<br>| 2021-02-23 17:40:06 | 00:14:23 | `percona`.`herc` | RECORD | 123 | update herc set name='hercules' where id=3 |<br>+---------------------+----------+------------------+--------+-----+--------------------------------------------+<br><br>Blocking metadata locks<br>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.
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.
Below, I am sharing some examples, which will help you to understand how we can use the “threads” command with the MySQL shell.
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 |
You can use the option “–foreground” to see all the running foreground threads.
|
1 |
MySQL localhost:33060+ ssl JS > show threads --foreground<br>+-----+-----+-----------------+-----------+---------+---------+----------+------------------------+-----------+-------------------------------------------------------------------+-----------+<br>| tid | cid | user | host | db | command | time | state | txstate | info | nblocking |<br>+-----+-----+-----------------+-----------+---------+---------+----------+------------------------+-----------+-------------------------------------------------------------------+-----------+<br>| 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 |<br>| 42 | 5 | event_scheduler | localhost | NULL | Daemon | 17:42:20 | Waiting on empty queue | NULL | NULL | 0 |<br>| 46 | 7 | NULL | NULL | NULL | Daemon | 17:42:20 | Suspending | NULL | NULL | 0 |<br>| 158 | 120 | root | localhost | NULL | Sleep | 00:32:24 | NULL | NULL | <br><br>. . . . .. . ... . . . .. . .. . .. . <br>. . . . .. . ... . . . .. . .. . .. . <br>. . . . .. . ... . . . .. . .. . .. . <br> | 0 |<br>| 260 | 222 | root | localhost | sbtest | Execute | 00:00:00 | updating | LOCK WAIT | NULL | 1 |<br>| 261 | 223 | root | localhost | sbtest | Execute | 00:00:00 | updating | LOCK WAIT | NULL | 0 |<br>+-----+-----+-----------------+-----------+---------+---------+----------+------------------------+-----------+-------------------------------------------------------------------+-----------+ |
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 |
MySQL localhost:33060+ ssl JS > show threads --background<br>+-----+--------------------------------------+---------+-----------+------------+------------+------------+<br>| tid | name | nio | ioltncy | iominltncy | ioavgltncy | iomaxltncy |<br>+-----+--------------------------------------+---------+-----------+------------+------------+------------+<br>| 1 | sql/main | 92333 | 192.51 ms | 229.63 ns | 96.68 us | 1.42 ms |<br>| 3 | innodb/io_ibuf_thread | NULL | NULL | NULL | NULL | NULL |<br>| 4 | innodb/io_log_thread | NULL | NULL | NULL | NULL | NULL |<br>| 5 | innodb/io_read_thread | NULL | NULL | NULL | NULL | NULL |<br>| 6 | innodb/io_read_thread | NULL | NULL | NULL | NULL | NULL |<br>| 7 | innodb/io_read_thread | NULL | NULL | NULL | NULL | NULL |<br>| 8 | innodb/io_read_thread | NULL | NULL | NULL | NULL | NULL |<br>| 9 | innodb/io_write_thread | 37767 | 45.83 s | 1.26 us | 1.21 ms | 17.81 ms |<br>| 10 | innodb/io_write_thread | 36763 | 44.57 s | 1.23 us | 1.21 ms | 30.11 ms |<br>| 11 | innodb/io_write_thread | 37989 | 45.87 s | 1.26 us | 1.21 ms | 24.03 ms |<br>| 12 | innodb/io_write_thread | 37745 | 45.78 s | 1.23 us | 1.21 ms | 28.93 ms |<br>| 13 | innodb/page_flush_coordinator_thread | 456128 | 2.19 min | 5.27 us | 419.75 us | 29.98 ms |<br>| 14 | innodb/log_checkpointer_thread | 818 | 479.84 ms | 2.62 us | 710.63 us | 9.26 ms |<br>| 15 | innodb/log_flush_notifier_thread | NULL | NULL | NULL | NULL | NULL |<br>| 16 | innodb/log_flusher_thread | 1739344 | 41.71 min | 1.46 us | 1.44 ms | 30.22 ms |<br>| 17 | innodb/log_write_notifier_thread | NULL | NULL | NULL | NULL | NULL |<br>| 18 | innodb/log_writer_thread | 5239157 | 10.23 min | 1.14 us | 117.16 us | 29.02 ms |<br>| 19 | innodb/srv_lock_timeout_thread | NULL | NULL | NULL | NULL | NULL |<br>| 20 | innodb/srv_error_monitor_thread | NULL | NULL | NULL | NULL | NULL |<br>| 21 | innodb/srv_monitor_thread | NULL | NULL | NULL | NULL | NULL |<br>| 22 | innodb/buf_resize_thread | NULL | NULL | NULL | NULL | NULL |<br>| 23 | innodb/srv_master_thread | 270 | 4.02 ms | 6.75 us | 14.90 us | 41.74 us |<br>| 24 | innodb/dict_stats_thread | 3088 | 429.12 ms | 3.22 us | 138.96 us | 5.93 ms |<br>| 25 | innodb/fts_optimize_thread | NULL | NULL | NULL | NULL | NULL |<br>| 26 | mysqlx/worker | NULL | NULL | NULL | NULL | NULL |<br>| 28 | mysqlx/acceptor_network | NULL | NULL | NULL | NULL | NULL |<br>| 32 | innodb/buf_dump_thread | 1060 | 7.61 ms | 2.74 us | 7.18 us | 647.18 us |<br>| 33 | innodb/clone_gtid_thread | 4 | 689.86 us | 4.46 us | 172.46 us | 667.95 us |<br>| 34 | innodb/srv_purge_thread | 7668 | 58.21 ms | 3.34 us | 336.20 us | 1.64 ms |<br>| 35 | innodb/srv_worker_thread | 30 | 278.22 us | 5.57 us | 9.27 us | 29.69 us |<br>| 36 | innodb/srv_purge_thread | NULL | NULL | NULL | NULL | NULL |<br>| 37 | innodb/srv_worker_thread | NULL | NULL | NULL | NULL | NULL |<br>| 38 | innodb/srv_worker_thread | 24 | 886.23 us | 5.24 us | 36.93 us | 644.75 us |<br>| 39 | innodb/srv_worker_thread | NULL | NULL | NULL | NULL | NULL |<br>| 40 | innodb/srv_worker_thread | 22 | 223.92 us | 5.84 us | 10.18 us | 18.34 us |<br>| 41 | innodb/srv_worker_thread | NULL | NULL | NULL | NULL | NULL |<br>| 43 | sql/signal_handler | NULL | NULL | NULL | NULL | NULL |<br>| 44 | mysqlx/acceptor_network | NULL | NULL | NULL | NULL | NULL |<br>+-----+--------------------------------------+---------+-----------+------------+------------+------------+ |
From the below example, I am finding the top five threads, which are consuming more memory from user “root”.
|
1 |
MySQL localhost:33060+ ssl JS > show threads --foreground -o tid,user,memory,started --order-by=memory --desc --where "user = 'root'" --limit=5<br>+-----+------+----------+---------------------+<br>| tid | user | memory | started |<br>+-----+------+----------+---------------------+<br>| 247 | root | 9.47 MiB | 2021-02-23 18:30:29 |<br>| 166 | root | 9.42 MiB | 2021-02-23 18:30:29 |<br>| 248 | root | 9.41 MiB | 2021-02-23 18:30:29 |<br>| 186 | root | 9.39 MiB | 2021-02-23 18:30:29 |<br>| 171 | root | 9.38 MiB | 2021-02-23 18:30:29 |<br>+-----+------+----------+---------------------+ |
You can use the options “digest” and “digesttxt” to find the digest output of the running threads.
|
1 |
MySQL localhost:33060+ ssl JS > show threads -o tid,cid,info,digest,digesttxt --where "digesttxt like 'UPDATE%'" --vertical<br>*************************** 1. row ***************************<br> tid: 161<br> cid: 123<br> info: update herc set name='hercules' where id=3<br> digest: 7832494e46eee2b28a46dc1fdae2e1b18d1e5c00d42f56b5424e5716d069fd39<br>digesttxt: UPDATE `herc` SET NAME = ? WHERE `id` = ? |
|
1 |
MySQL localhost:33060+ ssl JS > show threads -o tid,cid,nio --order-by=nio --desc --limit=5<br>+-----+-----+-------+<br>| tid | cid | nio |<br>+-----+-----+-------+<br>| 27 | 114 | 36982 |<br>| 238 | 200 | 2857 |<br>| 215 | 177 | 2733 |<br>| 207 | 169 | 2729 |<br>| 232 | 194 | 2724 |<br>+-----+-----+-------+ |
Nio → Total number of IO events for the thread.
Blocking threads:
|
1 |
MySQL localhost:33060+ ssl JS > show threads -o tid,cid,nblocked,nblocking,ntxrlckd,txstate --order-by=nblocking --desc --limit 5<br>+-----+-----+----------+-----------+----------+-----------+<br>| tid | cid | nblocked | nblocking | ntxrlckd | txstate |<br>+-----+-----+----------+-----------+----------+-----------+<br>| 230 | 192 | 0 | 7 | 5 | LOCK WAIT |<br>| 165 | 127 | 0 | 6 | 2 | LOCK WAIT |<br>| 215 | 177 | 0 | 5 | 9 | LOCK WAIT |<br>| 221 | 183 | 0 | 4 | NULL | NULL |<br>| 233 | 195 | 1 | 4 | NULL | NULL |<br>+-----+-----+----------+-----------+----------+-----------+ |
Blocked threads:
|
1 |
MySQL localhost:33060+ ssl JS > show threads -o tid,cid,nblocked,nblocking,ntxrlckd,txstate --order-by=nblocked --desc --limit 5<br>+-----+-----+----------+-----------+----------+-----------+<br>| tid | cid | nblocked | nblocking | ntxrlckd | txstate |<br>+-----+-----+----------+-----------+----------+-----------+<br>| 203 | 165 | 15 | 0 | 8 | LOCK WAIT |<br>| 181 | 143 | 10 | 1 | 5 | LOCK WAIT |<br>| 223 | 185 | 9 | 0 | 8 | LOCK WAIT |<br>| 209 | 171 | 9 | 1 | 5 | LOCK WAIT |<br>| 178 | 140 | 6 | 0 | 7 | LOCK WAIT |<br>+-----+-----+----------+-----------+----------+-----------+ |
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!
Resources
RELATED POSTS
