MySQL Bugs: #66234: Drop table innodb_monitor can cause lockups on very busy servers

Bug #66234	Drop table innodb_monitor can cause lockups on very busy servers
Submitted:	7 Aug 2012 0:07	Modified:	24 Jan 2013 18:07
Reporter:	Fernando Ipar (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S3 (Non-critical)
Version:	5.5.12	OS:	Linux
Assigned to:	Matthew Lord	CPU Architecture:	Any

Description:
The manual (http://dev.mysql.com/doc/refman/5.5/en/innodb-monitors.html) does not mention any risks associated with dropping the innodb_monitor table, but I have experienced global locking on very busy servers while this happens (noticed by peaks in the number of running transactions and in response time). 

I have experienced this on 5.1.60 and 5.5.12. 

How to repeat:
1- Create the innodb_monitor table
2- Generate highly concurrent load on a server with a large buffer pool (192G in the case where I hit this), and monitor response time (I used cacti, the locking was seen as peaks in the number of running mysql processes) 
3- Drop the innodb_monitor table

Suggested fix:
I believe the manual should inform of the risks of dropping this magic table on a busy server, or if possible, logging should be enabled/disabled by a global variable and not a magic table.

Do you have, by chance, the output of SHOW PROCESSLIST at the moment of lockup?

Hello Valeriy, 

No, but I will set up a test env to reproduce the problem and will send you this output within the next few hours.

processlist captures

Attachment: processlist-1344567384.txt (text/plain), 188.91 KiB.

I couldn't reproduce the same query pileup I saw in production probably due to workload issues. 

However, I've attached a file that contains the output from 'show processlist' run roughly every half a second, while sysbench is running queries on the server and the innodb_monitor table is dropped. 

I can see that all of the queries that run while that table is dropped are either Opening or Closing tables. I can't confirm this is what happened in production (I got late to the scene and could only see clear spikes in the number of Threads_running while the table was being dropped. it was being created and dropped every minute for debugging purposes).

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not clearly the result of a verifiable/repeatable bug.

The attached SHOW PROCESSLIST output doesn't show anything hung up.

If you are able to come up with a test case then please let me know.

Otherwise, support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.