Fix for bug#12695572 - "IMPROVE MDL PERFORMANCE IN PRE-VISTA

BY CACHING OR REDUCING CREATEEVENT CALLS".
 
5.5 versions of MySQL server performed worse than 5.1 versions 
under single-connection workload in autocommit mode on Windows XP.
 
Part of this slowdown can be attributed to overhead associated
with constant creation/destruction of MDL_lock objects in the MDL
subsystem. The problem is that creation/destruction of these
objects causes creation and destruction of associated
synchronization primitives, which are expensive on Windows XP.
 
This patch tries to alleviate this problem by introducing a cache
of unused MDL_object_lock objects. Instead of destroying such
objects we put them into the cache and then reuse with a new
key when creation of a new object is requested.

To limit the size of this cache, a new --metadata-locks-cache-size
start-up parameter was introduced.

mysql-test/r/mysqld--help-notwin.result:
  Updated test after adding --metadata-locks-cache-size
  parameter.
mysql-test/r/mysqld--help-win.result:
  Updated test after adding --metadata-locks-cache-size
  parameter.
mysql-test/suite/sys_vars/r/metadata_locks_cache_size_basic.result:
  Added test coverage for newly introduced --metadata_locks_cache_size
  start-up parameter and corresponding global read-only variable.
mysql-test/suite/sys_vars/t/metadata_locks_cache_size_basic-master.opt:
  Added test coverage for newly introduced --metadata_locks_cache_size
  start-up parameter and corresponding global read-only variable.
mysql-test/suite/sys_vars/t/metadata_locks_cache_size_basic.test:
  Added test coverage for newly introduced --metadata_locks_cache_size
  start-up parameter and corresponding global read-only variable.
sql/mdl.cc:
  Introduced caching of unused MDL_object_lock objects, in order to
  avoid costs associated with constant creation and destruction of
  such objects in single-connection workloads run in autocommit mode.
  Such costs can be pretty high on systems where creation and 
  destruction of synchronization primitives require a system call 
  (e.g. Windows XP).
   
  To implement this cache,a list of unused MDL_object_lock instances 
  was added to MDL_map object. Instead of being destroyed 
  MDL_object_lock instances are put into this list and re-used later 
  when creation of a new instance is required. Also added 
  MDL_lock::m_version counter to allow threads having outstanding 
  references to an MDL_object_lock instance to notice that it has 
  been moved to the unused objects list.
   
  Added a global variable for a start-up parameter that limits
  the size of the unused objects list.
   
  Note that we don't cache MDL_scoped_lock objects since they
  are supposed to be created only during execution of DDL 
  statements and therefore should not affect performance much.
sql/mdl.h:
  Added a global variable for start-up parameter that limits the
  size of the unused MDL_object_lock objects list and constant
  for its default value.
sql/sql_plist.h:
  Added I_P_List<>::pop_front() function.
sql/sys_vars.cc:
  Introduced --metadata-locks-cache-size start-up parameter
  for specifying size of the cache of unused MDL_object_lock
  objects.

1 files changed, 8 insertions, 0 deletions

diff --git a/sql/mdl.h b/sql/mdl.h
index fb2de45b831..5c1af23f5e2 100644
--- a/sql/mdl.h
+++ b/sql/mdl.h
@@ -851,4 +851,12 @@ extern "C" void thd_exit_cond(MYSQL_THD thd, const char *old_msg);
 extern mysql_mutex_t LOCK_open;
 #endif
 
+
+/*
+  Start-up parameter for the maximum size of the unused MDL_lock objects cache
+  and a constant for its default value.
+*/
+extern ulong mdl_locks_cache_size;
+static const ulong MDL_LOCKS_CACHE_SIZE_DEFAULT = 1024;
+
 #endif