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:
Category:MySQL Server Severity:S5 (Performance)
Version:5.1.53 OS:Any
Assigned to: CPU Architecture:Any

[30 Apr 2012 17:39] Mark Callaghan
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.
[3 May 2012 8:13] Dmitry Lenev
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.