MySQL Bugs: #55180: make the Threads_running server status variable more meaningful

Bug #55180	make the Threads_running server status variable more meaningful
Submitted:	12 Jul 2010 16:27
Reporter:	Axel Schwenke	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S4 (Feature request)
Version:	any	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The Threads_running global server status variable is defined as "the number of threads that are not sleeping" (in http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html). This statement is not very clear. What threads? What exactly means sleeping?

It seems this counts (number of server threads owning a client connection) - (number of those threads is state "sleep"). Probably there are more (delayed insert, event scheduler, ...)?

This is far from perfect because it misses some threads:

- each connected replication slave increased Threads_running by 1. The replication master thread has an idle state "Has sent all binlog to slave; waiting for binlog to be updated" but it is always counted as not sleeping.

- if the instance is a replication slave, then there can be 0, 1 or 2 slave threads. Those are currently not counted in Threads_running. Again those threads can be idle or active.

How to repeat:
This was discovered by an unexpected drop of the "running" line in the "connections" graph in the Enterprise Monitor (this line shows the Threads_running status variable). The drop was caused by moving some slaves off this instance.

Suggested fix:
This depends on the amount of backwards compatibility to keep.

One way would be to detect an "idle" status for more server threads than now and calculate Threads_running accordingly. I.e. if a replication master thread is waiting for the binlog to be updated, it should not count towards Threads_running. Same goes for replication slave threads. The goal would be to count *all* non-idle server threads.

If the meaning of Threads_running is to be kept for backwards compatibility then one could add new server status variables. I.e.

Threads_replication_master
Threads_replication_slave
...

My preferred solution would be #1 - make Threads_running what the average DBA expects it to be - the number of active threads in the server.

As stated in EM-49094 It seems clearer to me have extra counter for:

- master server's 0, 1 or 2 replication threads (I/O and SQL)
- replication slave threads
- any others like the event scheduler

and then indicate correctly if these threads are busy with the existing variable Threads_running.

All this information gives better monitoring possibilities of the db server, in tools such as MySQL Enterprise Manager.

See also: http://bugs.mysql.com/bug.php?id=68963