mysql_processlist []

Investigating mysql

Query List

To view mysql query list run the following command from the command line:

SELECT * FROM information_schema.processlist WHERE command != 'Sleep' ORDER BY time;

mysqladmin pr | grep Query | sort -nk12

Example output:

| 5950 | user1      | localhost | example_com | Query   | 0    | checking query cache for query | SELECT m.filename, m.datedir, m.description, m.media_type, m.preview
| 5977 | root          | localhost |               | Query   | 0    |                                | show processlist                                                                                     |
| 5032 | user2 | localhost | new_shop | Query   | 49   | Sending data                   | SELECT m.*, mm.is_winner, (SELECT COUNT(*) FROM votes v WHERE month = '8' AND v.season = '0' AND v.m |

All Processes

If you need to investigate mysql you can troubleshoot by investigating the processlist

show processlist;

You should see an output similar to the following:

+--------+---------+-----------+------+---------+-------+-------+------------------+ 
| Id     | User    | Host      | db   | Command | Time  | State | Info             | 
+--------+---------+-----------+------+---------+-------+-------+------------------+ 
| 109530 | root   | localhost | NULL | Query   |     0 | NULL  | show processlist | 
| 106092 | wp_usr | localhost | wp   | Sleep   | 25140 |       | NULL             |
| 106090 | wp_usr | localhost | wp   | Sleep   | 25140 |       | NULL             |
| 106091 | wp_usr | localhost | wp   | Sleep   | 25139 |       | NULL             |
| 109751 | root   | localhost | NULL | Sleep   |   244 |       | NULL             |

Process list WITHOUT sleeping processes

SELECT * FROM information_schema.processlist WHERE command != 'Sleep' ORDER BY id;

Header	Description
ID	Connection identifier
User	MySQL user who issued the statement
Host	Host name of the client issuing the statement
db	Default database, if one is selected, otherwise NULL
Command	The type of command the thread is executing
Time	Time in seconds that the thread has been in its current state
State	An action, event, or state that indicates what the thread is doing
Info	The statement the thread is executing

mysql Common Commands

Command	Description
Connect	Replication slave is connected to its master
Connect Out	Replication slave is in the process of connecting to its master
Drop DB	Executing an operation to drop a database
Error	Error
Execute	Executing a prepared statement
Fetch	Fetching the results of an executed prepared statement
Kill	Killing another thread
Query	Executing a statement
Quit	In the process of terminating the thread
Sleep	Waiting for the client to send a new statement
Table Dump	Sending the contents of a table to a slave

source: https://mariadb.com/kb/en/mariadb/thread-command-values/