MySQL Bugs: #65157: set read_only=1|0 kills performance

Bug #65157	set read_only=1\|0 kills performance
Submitted:	30 Apr 2012 17:39	Modified:	3 May 2012 8:13
Reporter:	Mark Callaghan	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S5 (Performance)
Version:	5.1.53	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
See https://mariadb.atlassian.net/browse/MDEV-136. The problem is the work done by close_cached_tables. It deletes all table handler instances and table shares. So there is a huge convoy on LOCK_open afterwards. With MDL in modern MySQL releases there might be less of a convoy on locks -- I don't know for sure, but there is still a lot of wasted work (CPU and disk IO) done to reopen many table instances. This makes failover much more painful.

How to repeat:
read the source in close_cached_tables

Suggested fix:
Don't delete handler instances and table shares for InnoDB in this case.

Hello Mark!

This issue has the same cause as bug #57612 "SET GLOBAL READ_ONLY=1 cannot progress when a table is locked with LOCK TABLES" and has been fixed in 5.6 tree by the same patch. Therefore I am closing this bug as duplicate.