MySQL Monitoring and Reporting Using the MySQL Shell

Monitoring Using the MySQL ShellMySQL 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.

  • Query
  • Thread
  • Threads

“\show” with “query”

It will just execute the query provided as an argument within the double quotes and print the result. 

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

“\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.

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 )

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 ):

From the general information, you can find some basic information about your id.

InnoDB information:

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: