MySQL Bugs: #42930: 'show global status' causes lock contention on LOCK_thread

Bug #42930	'show global status' causes lock contention on LOCK_thread_count
Submitted:	17 Feb 2009 11:44	Modified:	5 Oct 2012 16:42
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S5 (Performance)
Version:	5.0, 5.1, 6.0	OS:	Any
Assigned to:	Geir Høydalsvik	CPU Architecture:	Any

Description:
Running 'show global status' often on a busy server causes significant performance problems.

The cause is that mutex LOCK_thread_count is locked entire time in the function void calc_sum_of_all_status(STATUS_VAR *to), which can take a while.

The more connected threads or rapid new connections, the worse the problem gets.  People using pool-of-threads with large number of connections will be affected even more.

How to repeat:
read the code, or study a server with plockstat

FWIW, this is not the only case when this can cause trouble, everywhere we take LOCK_thread_count to iterate over the thread (connection) list is a potential spot for contention. One simple solution that is employed by servers with a similar architecture (ie httpd) is to allocate a global score board at start up -- the size of the score board is set to the maximum number of concurrent connections/threads.

still a performance bug.

Please note that Mikael's WL#5138 "Move thread_running and global_query_id to my_atomic from LOCK_threadcount" which has made it into 5.5 should have alleviated this problem. After this patch LOCK_thread_count should not be acquired for each statement but mostly when a new connection is created or existing connection is closed. It is also acquired by various statements which perform iteration over connection list like SHOW GLOBAL STATUS, KILL, etc..

There are two issues here:

- contention on LOCK_thread_count kills the server, which is bad.
Reducing contention and dependencies on this mutex is needed for performance reasons.

- show global status causes locks on LOCK_thread_count
That's per show global status design.

Fixing *that* means re factoring a lot of code, and is high risk, high effort, for low reward (show commands still broken by design).

It would be much better to expose global status (and per user. per thread, per host) status in the performance schema, without the locks.

Now we have a commercial oracle 5.5 build with thread pooling enabled, I hope this bug will not resurface in a bad way with thousands of threads.......

Noted in 5.7.0 changelog. 

SHOW GLOBAL STATUS caused performance problems on busy servers due to 
lock contention.