For your convenience, a short phrase book, starting with explanation of process states where MySQL is mostly working to look up data from tables:
- “Sending data” – reading data from tables (or looking it up)
- “Copying to tmp table” – reading data from tables (or looking it up)
- “Copying to tmp table on disk” – query needs a rewrite
- “statistics” – looking up data from tables
- “Sorting result” – reading data from tables (or looking it up)
Locking is fun:
- “update” – There is big chance that it is waiting for a lock, can be updating as well, on busy systems
- “Locked” – Even bigger chance that it is MyISAM table waiting to be converted to InnoDB
Table opening is even funnier:
- “Opening tables” – some other idiot thread is holding the LOCK_open or similar mutex
- “Closing tables” – some other idiot thread is holding the LOCK_open or similar mutex
- “Opening table” – the idiot thread (or not that smart DBA who thinks that table_cache should be set to current value)
- “Flushing tables” – someone decided that flushing tables is good idea, but forgot to kill long running queries
- “Waiting for tables” – same as “Flushing tables”
If you have replication:
- “Connecting to server” – could not connect to server, waiting to retry
- “Reconnecting after a failed master event read” – master and slave have same @server_id
- “Registering slave on master” – master and slave have same @server_id
- “Waiting to reconnect after a failed master event read” – master and slave have same @server_id
- “Sending binlog event to slave” – actually, not sending binlog event – it is waiting for binlog mutex.
Few more tricky ones:
- “Sleep” – last millisecond it was probably not sleeping, due to how process scheduling and kernel level buffering works probably it has finished right before kernel switched to the ‘SHOW PROCESSLIST’ thread.
- “Writing to net” – usually, not writing to net (client has blocked) – actual writing to net is usually nearly non-blocking operation, as long as client doesn’t sleep.
- “Killed” – actually, someone tried to kill it, but it doesn’t care, as it doesn’t check for thd->killed flag
And in the user column:
- “unauthenticated user” – are you sure your DNS is working right?
- “system user” – it isn’t user, these are (hopefully plural) replication threads, that don’t have any “user” credentials
- “root” – either that is you running the PROCESSLIST, another session you forgot you are running or you forgot to create separate user for your application