The mysqladmin processlist command is used to view the queries that are currently active in the MySQL/MariaDB database. This command is ideal for understanding which queries are straining system resources, especially in situations such as high CPU or disk usage.

We start with the following command:

mysqladmin -u root -p processlist

After running the command, you will be prompted for the password and then it will list all running processes:

Output Example:

+----+------+-----------+----------+---------+------+-------+------------------+
| Id | User | Host      | db       | Command | Time | State | Info             |
+----+------+-----------+----------+---------+------+-------+------------------+
| 12 | user | localhost | mydb     | Query   | 50   | Sending data | SELECT * FROM large_table |
| 13 | user | localhost | mydb     | Sleep   | 20   |       | NULL             |

Columns to pay attention to here:

Time: Shows how long the query has been running. Those with high durations can create a load on the system.
Command: "Query" ones are active queries. "Sleep" ones are sessions with open connections but not performing any operations.
Info: The entire SQL query being executed appears here.

To Identify High Resource Consuming Queries:

grep and awk can be used to filter the processlist output:

mysqladmin -u root -p processlist | grep -i select

To see only those that have been running for a long time:

mysql -u root -p -e "SELECT * FROM information_schema.processlist WHERE TIME > 30 ORDER BY TIME DESC;"

With this command, you can see all queries that have been running for more than 30 seconds.

For Extra Detailed Monitoring:

To perform periodic tracking:

watch -n 2 'mysqladmin -u root -p processlist'

This command redisplays the running processes every 2 seconds.

To Stop High-Intensity Queries:

Some queries may take too long and lock the system. To terminate the query:

KILL 12;

Here, 12 is the value in the Id column.

With these methods, you can identify and intervene in queries that consume system resources on your MySQL/MariaDB server. Especially in large databases, the processlist command is very valuable in terms of live performance monitoring.