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
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 :)
domas mituzas 
“…
“Opening table” – the idiot thread (or not that smart DBA who thinks that table_cache should be set to current value)
…”
Just don’t forget your OS is, by default, unlikely to accept higher values. Windows will limit open files to 2048, Linux by default to 1024.
Shlomi, Linux will accept much more than 1024, if you specify –open-files-limit …
Hi Domaz,
Not unless you edit /etc/security/limits.conf to read
“mysql – nofile 8192” (or what number have you)
Otherwise 1024 is the default max-open-files for all users.
Please correct me if I’m wrong.
Shlomi,
no need to edit limits.conf, just put open_files_limit=XXXX into [mysqld] section of your my.cnf.
mysqld is started as root, will increase the resources before calling setuid().
Domaz,
Cool! Thanks
>> “Locked” – Even bigger chance that it is MyISAM table waiting to be converted to InnoDB
Definitely true.
Pingback http://twitter.com/MySQLExpert/status/4620744676
Very intersting, thanks for “translating”
Cheers
Hah!
Tell me, is there *ever* a reason to run MyISAM over InnoDB?
@David Gerard: Yes. Full text search is currently supported only in MyISAM. In addition, MyISAM has better read performance.
The key to success is in mixing and matching the storage engines leveraging the strengths of each of the them.
Yes, there is. Data that is not changing because it is not written to or is batch-loaded can easily be MyISAM. A regular MyISAM tables stores data in about half the space that InnoDB requires to store the same data, the actual size can vary a lot depending on a lot of factors all of which are InnoDB related.
If you care to run myisampack on your unchanging data, which you can, because it never changes, then you end up with a storage that is about 5 times smaller than the same amount of data in InnoDB.
If you need to run a full table scan on your data (which happens a lot in a DWHy environment) it may matter a lot of your query that took 20 minutes before now finishes in 4 minutes.
David, to run your blog, mostly. :)
FT is bad argument, anyone sane may use Lucene or Sphinx for that.
Better read performance is a myth – MyISAM key cache mutex will have quite sad effects, whereas InnoDB will continue to scale with more cores, as well as InnoDB does data buffering, whereas MyISAM goes to FS all the time.
So yes, Isotopp said it – full table scans may be faster with MyISAM, but various index range reads and such will be faster with InnoDB, at high performance environments.
And if it isn’t a high performance environment, one can use whatever they want, it doesn’t matter then – and MyISAM will have smallest footprint on environment.