WL#3983: LOCK_open: no longer necessary for disk I/O (to read/write .frm files)

Affects: Server-6.x   —   Status: Assigned   —   Priority: Medium

Improve concurrency and throughput of the server by removing acquisitions of a
global mutex that has now become redundant (LOCK_open).

See also:
BUG#33948 performance issue when using sysbench benchmark on a multiple-core
system.
BUG#41158  DROP TABLE holds LOCK_open during unlink()
Close this bug when WL#3726/3983/4440/4441 are complete.
BUG#50589 "Server hang on a query evaluated using a temporary table"
With implementation of WL#3726, any DDL statement in the server
acquires exclusive metadata locks on all used tables.

This ensures that the concurrency on the tables that participate
in the DDL is zero.

The old mechanism employed to stop concurrent connections from
using a table was acquisition of LOCK_open mutex, and now it has
become redundant.

In other words, LOCK_open was used to protect:

 - operations with the table definition cache, such as
   get_table_share() and release_table_share()
 - operations with .FRM files, such as mysql_frm_type(),
   openfrm(), access(), and others. These are now always
   performed while keeping at least a shared metadata lock
   on the subject table.
 - access to thd->open_tables.
 - access to thd->version and the global variable refresh_version.

With exclusive metadata locks at least the second is pure 
redundancy. Moreover, only by implementing this task and removing
acquisition of LOCK_open from all the places that are no longer
expected to need it can we verify that the new locking is deadlock
and race free, as well as that all places in the server are using it.

This task needs stress testing, to ensure that the server performs
without internal errors under high load of concurrent DDL and DML.
Additionally, the test may inject random "filesystem failures" by
temporarily removing one or another .frm file, index or data file
(in case of a myisam table).  In no case the server should crash.
It also is expected to operate without deadlocks or failures as
long as there are no filesystem errors.


PLEASE inform Magnus BlÄudd in MySQL Cluster team when the changes to
ha_ndbcluster* takes place so that code can be added also to the ha_ndbcluster*
files in MySQL Cluster allowing that handler to work with 5.5+ MySQL server.
1) DROP TABLE

Remove the acquisition of LOCK_open when manipulating with
.frm files in mysql_rm_table_part2().

2) ndbcluster_setup_binlog_table_shares()

Change to acquire an exclusive metadata lock on the table
(NDB_REP_DB.NDB_APPLY_TABLE) instead of LOCK_open.

3) ndbcluster_drop_database_impl()

Do not acquire LOCK_open and do not assert that there are MDL
locks. There are no database-level MDL locks (see WL#4450) and
each database is dropped by dropping all its tables.
An assert should be added that there is an exclusive metadata
lock on the database when WL#4450 is implemented.

4) recover_from_failed_open_table_attempt()

Remove acquisition of LOCK_open, assert that there is an
exclusive metadata lock.

5) ndbcluster_log_schema_op()

Remove have_lock_open parameter and release of LOCK_open.
Assert that there is an exclusive metadata lock.

6) ndb_binlog_thread_handle_schema_event()

Change to acquire an exclusive metadata lock on the table
instead of LOCK_open.

7) ndbcluster_handle_drop_table()

Remove the temporary release of LOCK_open, it's not acquired
any more, assert that there is an exclusive metadata lock.

8) ndbcluster_find_all_files(), ndbcluster_find_files()

Remove the acquisition of LOCK_open, assert that there is an
exclusive metadata lock.

9) mysql_truncate()

Remove acquisition of LOCK_open around ha_create_table().

10) plugin_load()

Remove the call to check_if_table_exists() and acquisition of
LOCK_open that surrounds it. Install a silencer for the possible
error of open_and_lock_tables() instead.

11) mysql_create_view(), mysql_drop_view(),
    mysql_create_or_drop_trigger()

Remove acquisition of LOCK_open. Assert that there is an
exclusive metadata lock instead.

12) mysql_rename_tables()

Move rename_tables() outside the critical section protected by
LOCK_open. Assert that there is an exclusive metadata lock
instead. LOCK_open should only be taken to issue
tdc_remove_table().

13) create_table_from_items()

Remove acquisition of LOCK_open around quick_rm_table(). Assert
that there is an exclusive metadata lock on the table instead.

14) show_create_trigger()

In order to find out the table name for a trigger name, make a "dirty read" of
the trigger file. There are no metadata locks on triggers yet. If later on it
turns out that the table or trigger on the table doesn't exist, produce "no such
trigger" error.

15) fill_schema_table_from_frm()

Move open_new_frm() outside the critical section protected by
LOCK_open. Assert that there is a high priority shared metadata
lock on the table instead.

16) drop_open_table()

Move quick_rm_table() outside the critical section protected by
LOCK_open.

17) tdc_open_view()

Assert that there is a metadata lock on the view. Do not acquire
LOCK_open.

18) auto_repair_table()

Do not acquire LOCK_open.
Put a dummy share into the table definition cache for the duration
of disk look up if the share is not in the cache.

19) mysql_write_frm()

Do not lock LOCK_open when calling ha_create_handler_files() or
my_delete(). Assert that there is an exclusive metadata lock on
the table instead.

20) mysql_create_table_no_lock()

Verify if access() call is still necessary. In any case move it
outside the critical section protected by LOCK_open.
The critical section should only cover get_cached_table_share(),
everything else is protected by the exclusive metadata lock.

21) open_table_from_share()

Add safe_mutex_assert_not_owner(&LOCK_open) to the beginning.

22) Check that MyISAM storage engine does not rely on LOCK_open
being locked when manipulating with MYISAM_SHARE hash.

23) Ensure mysql_create_like_schema_frm() is called without
LOCK_open.

24) mysql_fast_or_online_alter_table()

Do not wrap the call to mysql_rename_table() with acquisition of
LOCK_open.

25) mysql_alter_table()

Do not wrap the filesystem operations with acquisition of
LOCK_open.

26) check_if_table_exists(), open_table()

Rewrite to optionally put a dummy share into the table definition
cache for the duration of disk look up if the share is not in the
cache. This is necessary to ensure that in case two threads attempt
to get a share and it doesn't exist, only one of them will be loading
the table from disk.

27) Rename LOCK_open to LOCK_tdc to highlight all places where it
is still used, and prevent erroneous usage to come from merges.
If any place is missing in the LLD, it should be checked:
LOCK_tdc should be protecting only operations with the table
definition cache, thd->open_tables, and the open tables cache.

See also: BUG#48832  	Disconnection is slow when using non-persistent mysql
connections