Bug #68963 SHOW GLOBAL STATUS Threads_running may be misleading
Submitted: 15 Apr 2013 17:23 Modified: 16 Apr 2013 9:40
Reporter: Simon Mudd (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Options Severity:S3 (Non-critical)
Version:5.6.10 and earlier OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D5 (Feature request)

[15 Apr 2013 17:23] Simon Mudd
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.
[15 Apr 2013 17:49] Sveta Smirnova
Thank you for the report.

Slave thread is actually not in "Sleep" state, but in "Binlog Dump", but I agree having variables, reflecting slave threads is useful addition. Verifying as feature request.
[16 Apr 2013 9:40] Simon Mudd
See also: http://bugs.mysql.com/bug.php?id=55180 which I brought up 3 years ago.
[10 Dec 2018 9:50] feng guo
When will this bug be fixed? It has been 5 years since this bug been figured out!