PHP Developer News

MySQL Monitoring and Reporting Using the MySQL Shell

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.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. 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(): perconaYou 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\watch query “show processlist”
Have open source expertise you want to share? Submit your talk for Percona Live ONLINE 2021!

“\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.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: 0At 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 )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 ):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:       NULLFrom the general information, you can find some basic information about your id.
InnoDB information: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:            0Using 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: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/AConnection 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.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/AHere, 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. 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.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.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”. 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.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
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: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: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!