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
Triage: Triaged: D3 (Medium) / R3 (Medium) / E4 (High)

[17 Feb 2009 11:44] Shane Bester
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
[17 Feb 2009 15:50] Davi Arnaut
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.
[3 Aug 2009 13:25] Shane Bester
still a performance bug.
[18 Jan 2011 7:00] Dmitry Lenev
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..
[30 Mar 2011 10:20] Marc Alff
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.
[29 Jun 2011 3:37] Shane Bester
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.......
[5 Oct 2012 16:42] Paul Dubois
Noted in 5.7.0 changelog. 

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