How to Check Queries Running on MySQL/MariaDB Server

Many websites are dynamic websites that use CMS as a front and MySQL database as a back-end.

There are many chances for MySQL to consume more CPUs when running long queries.

If so, what would be the command to verify this information.

MySQL has a “Show Processing List” statement that displays queries running on your MySQL server.

It shows you what’s happening, how many users are currently using the mysql database, and a list of processes currently handled in it.

There are four ways we can check this information. We will go into detail about it.

How to Verify Queries Running on MySQL/MariaDB Server Using MySQLAdmin Command

MySQLAdmin is a client for performing administrative operations on a MySQL server.

# mysqladmin proc stat

+--------+---------------+-----------+---------------+---------+------+----------+------------------+
| Id     | User          | Host      | db            | Command | Time | State    | Info             |
+--------+---------------+-----------+---------------+---------+------+----------+------------------+
| 174601 | daygeekc_magi | localhost | daygeekc_magi | Sleep   | 0    |          |                  |
| 174602 | root          | localhost |               | Query   | 0    | starting | show processlist |
+--------+---------------+-----------+---------------+---------+------+----------+------------------+
Uptime: 181578  Threads: 2  Questions: 18561863  Slow queries: 0  Opens: 155362  Flush tables: 1  Open tables: 2000  Queries per second avg: 102.225

The mysqladmin status command result displays the following values:

  • Uptime: The number of seconds the MySQL server has been running.
  • Threads: The number of active threads (clients).
  • Questions: The number of questions (queries) from clients since the server was started.
  • Slow queries: The number of queries that have taken more than long_query_time seconds.
  • Opens: The number of tables the server has opened.
  • Flush tables: The number of flush-*, refresh, and reload commands the server has executed.
  • Open tables: The number of tables that currently are open.
  • Memory in use: The amount of memory allocated directly by mysqld. This value is displayed only when MySQL has been compiled with –with-debug=full
  • Maximum memory used: The maximum amount of memory allocated directly by mysqld. This value is displayed only when MySQL has been compiled with –with-debug=full.

How to Verify Queries Running on MySQL/MariaDB Server Using MySQLAdmin Processlist Command

MySQL processlist statement show a list of active server threads.

# mysqladmin processlist

+--------+---------------+-----------+---------------+---------+------+----------+------------------+
| Id     | User          | Host      | db            | Command | Time | State    | Info             |
+--------+---------------+-----------+---------------+---------+------+----------+------------------+
| 174688 | daygeekc_magi | localhost | daygeekc_magi | Sleep   | 0    |          |                  |
| 174689 | daygeekc_magi | localhost | daygeekc_magi | Sleep   | 0    |          |                  |
| 174690 | root          | localhost |               | Query   | 0    | starting | show processlist |
+--------+---------------+-----------+---------------+---------+------+----------+------------------+

The above output details are described below in detail.

  • Id: The client’s process ID.
  • User: The MySQL user who issued the statement.
  • Host: The host the client is connected to.
  • db: The default database, if one is selected; otherwise NULL.
  • Command: The type of command the thread is executing.
  • Time: The time in seconds that the thread has been in its current state.
  • State: An action, event, or state that indicates what the thread is currently doing.
  • Info: The statement the thread is executing, or NULL if it is not executing any statement.

How to Verify Queries Running on MySQL/MariaDB Server Using MySQL Show Processlist Command

MySQL SHOW PROCESSLIST statement shows which threads are running. If you have the PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads.
If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info field.

# mysql --uXXXX -pXXXX -e "show processlist"

+--------+---------------+-----------+-------------------+---------+------+----------+------------------+
| Id     | User          | Host      | db                | Command | Time | State    | Info             |
+--------+---------------+-----------+-------------------+---------+------+----------+------------------+
| 174515 | daygeekc_magi | localhost | daygeekc_magi     | Sleep   |    0 |          | NULL             |
| 174516 | arpothaa_Arp  | localhost | arpothaa_arpootha | Sleep   |    0 |          | NULL             |
| 174517 | root          | localhost | NULL              | Query   |    0 | starting | show processlist |
+--------+---------------+-----------+-------------------+---------+------+----------+------------------+

How to Verify Queries Running on MySQL/MariaDB Server Using MySQL Show Full Processlist Command

MySQL “SHOW FULL PROCESSLIST” statement shows detailed information about the running queries.

# mysql -uXXXX -pXXXX -e "show full processlist"

+--------+---------------+-----------+---------------+---------+------+----------+-----------------------+
| Id     | User          | Host      | db            | Command | Time | State    | Info                  |
+--------+---------------+-----------+---------------+---------+------+----------+-----------------------+
| 174473 | daygeekc_magi | localhost | daygeekc_magi | Sleep   |    0 |          | NULL                  |
| 174474 | root          | localhost | NULL          | Query   |    0 | starting | show full processlist |
+--------+---------------+-----------+---------------+---------+------+----------+-----------------------+

Use the “\ G” delimiter to print the result into a more readable format. Login to the MySQL/MariaDB database to execute the below three commands.

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 193184
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: starting
   Info: show processlist
*************************** 2. row ***************************
     Id: 193214
   User: daygeekc_magi
   Host: localhost
     db: daygeekc_magi
Command: Sleep
   Time: 0
  State: 
   Info: NULL
2 rows in set (0.01 sec)

Alternative, we can use the following command to get the same information.

mysql> SHOW FULL PROCESSLIST;

+--------+-------------+-----------+---------------+---------+------+----------+-----------------------+
| Id     | User        | Host      | db            | Command | Time | State    | Info                  |
+--------+-------------+-----------+---------------+---------+------+----------+-----------------------+
| 174272 | root        | localhost | NULL          | Query   |    0 | starting | SHOW FULL PROCESSLIST |
| 174285 | improtec_wp | localhost | improtec_main | Sleep   |    0 |          | NULL                  |
| 174286 | improtec_wp | localhost | improtec_main | Sleep   |    0 |          | NULL                  |
+--------+-------------+-----------+---------------+---------+------+----------+-----------------------+
3 rows in set (0.00 sec)

Alternative, we can use the following command to get the same information.

mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;

+--------+---------------+-----------+---------------+---------+------+-----------+----------------------------------------------+
| ID     | USER          | HOST      | DB            | COMMAND | TIME | STATE     | INFO                                         |
+--------+---------------+-----------+---------------+---------+------+-----------+----------------------------------------------+
| 174272 | root          | localhost | NULL          | Query   |    0 | executing | SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST |
| 174345 | daygeekc_magi | localhost | daygeekc_magi | Sleep   |    0 |           | NULL                                         |
+--------+---------------+-----------+---------------+---------+------+-----------+----------------------------------------------+
2 rows in set (0.00 sec)

How to Monitor a Command Activity in Linux

If you want to monitor MySQL activity over a period of time, use the watch command.

Watch is a very powerful command-line tool which execute a program at a regular intervals and displays the results on standard output in full screen.

By default, the program is run every 2 seconds. Add -n or –interval with watch command to specify a different interval.

# watch -n1 mysqladmin proc stat

or

# watch -n1 mysqladmin processlist

About Magesh Maruthamuthu

Love to play with all Linux distribution

View all posts by Magesh Maruthamuthu

Leave a Reply

Your email address will not be published. Required fields are marked *