Show running processes in MySQL

MySQL SHOW PROCESSLIST

To kill a query, we first need to track down the query that is slowing the performance - it's usually the one that takes the most time to run. For that measure, we need to look at the table that will show running MySQL queries which is done by the processlist command:

show full processlist;

The FULL modifier allows us to see the query text in its entirety instead of the first 100 symbols we would get without this modifier. In the id column, you will see the connection thread id of any currently running query - you can then use this id in the KILL command.

You can also retrieve detailed information about connections using the following queries:

SELECT * FROM information_schema.PROCESSLIST p;

SELECT * FROM performance_schema.threads t;

Session Manager in dbForge Studio

We saw how to identify and kill queries using a regular MySQL console. Let's now do it in dbForge Studio for MySQL - custom programs were build to make database administration plain, visual, and simple. Let's say, we are using Amazon RDS SQL instance and run it as a Master user. So we run the same script in the admin query window (New SQL in the upper left corner) to get the MySQL process list:

show full processlist;

or simply open Database > Session Manager in the upper menu.

Also, the following commands work here:

• Kill Query is an analogue to KILL QUERY
• Kill Session is an analogue to KILL CONNECTION
• Kill User Sessions kills all sessions of a user selected in the grid

Privileges

Only admin is the true superuser, meanwhile master doesn't have the same root privilege. So unless you want to run the whole process in a non-managed MySQL instance, you should run the following command:

CALL mysql.rds_kill(28);

In the Output tab, we see that the previous attempt to kill the query using KILL command was hampered by the lack of the root privilege. But once we entered the mysql.rds_kill command, the action was successful.

The CONNECTION_ADMIN or SUPER privileges will allow you to kill queries no only for your connections, but also for foreign ones. The SYSTEM_USER privilege may also be needed in some cases. Similarly, the PROCESS privilege allows you to see foreign connections in addition to yours when using SHOW PROCESSLIST.