Description:
Also see: http://www.mysqlperformanceblog.com/2010/03/19/is-your-mysql-server-loaded/
Many people use this status variable to see if a server is busy "doing things" and in theory that seems like a good thing to look at. The documentation at http://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Threads_runnin... states that this is: "The number of threads that are not sleeping."
One might think that this is the same. However, on servers with a large number of slaves all the slave threads are actually not sleeping, so the value provided by this variable becomes next to useless for measuring "server load" (even if this is a crude measurement mechanism). [1]
Even "idle" slave threads on the master server count in the Threads_running when in fact they may not be doing anything.
This is confusing and non-intuitive and it would be helpful to provide information on the number of "busy" threads and given that there may be a large number of replication threads and that their usage behaviour is quite different to normal threads (they probably all jump into action when a statement is executed and pushed out to the binlogs) providing extra information on their activity might be good.
[1] I graph this and other values over time but on a master server with 100+ slaves the change in "real usage" of "other threads" from say 5 to 20 being busy is almost invisible as it's a small percentage of the total number of threads that have this current status.
How to repeat:
Configure a server with a number of slaves, but doing nothing and Threads_running will be quite high.
So it'll be harder to spot when there's activity on the server that's unrelated to these replication threads.
Suggested fix:
I'd suggest:
1. Make Threads_running only include the master's slave threads if they are busy, and leave them in the equivalent of a sleep state which is what they're in anyway really. That would fix what I consider as a bug.
2. I'd expect that the slave threads will frequently alternate between being busy or idle so this will make the Threads_running value (if graphed) very unstable, so it might be useful to split out the usage of the SQL threads and add a couple of new variables:
Slave_threads the counter of the number of slaves connected to the server
Slave_threads_running when the slave thread is busy doing something, or waiting on something other than the next command to be written to the binlogs to be acted upon
and then defining
Threads_running as "user threads" (that excludes system threads whose state may be visible, or the event scheduler for example, and also excludes the master's slave threads) that are not in the Sleep state, and therefore by implication _are_ busy doing something.
Option 2 would make the value of Threads_running more intuitive and would also allow the number of slave threads to be monitored easily, something which currently requires processing the whole PROCESSLIST output to do.
