MySQL processlist phrase book

MySQL processlist phrase book: "

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

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

I probably didn’t write quite a few important ones, but some of them are self-evident (such as “init” and “end”), and others probably will never show up :)

PlanetMySQL Voting:
Vote UP /
Vote DOWN"