MDEV-19749 - MDL scalability regression after backup locks

[svoj]

trafficstars

Statements that intend to modify data have to acquire protection against ongoing backup. Prior to backup locks, protection against FTWRL was acquired in form of 2 shared metadata locks of GLOBAL (global read lock) and COMMIT namespaces. These two namespaces were separate entities, they didn't share data structures and locking primitives. And thus they were separate contention points.

With backup locks, introduced by 7a9dfdd, these namespaces were combined into a single BACKUP namespace. It became a single contention point, which doubled load on BACKUP namespace data structures and locking primitives compared to GLOBAL and COMMIT namespaces. In other words system throughput has halved.

MDL fast lanes solve this problem by allowing multiple contention points for single MDL_lock. Fast lane is scalable multi-instance registry for leightweight locks. Internally it is just a list of granted tickets, disable counter and a mutex.

Number of fast lanes (or contention points) is defined by the metadata_locks_instances system variable. Value of 0 disables fast lanes and lock requests are served by conventional MDL_lock data structures.

Since fast lanes allow arbitrary number of contention points, they should outperform pre-backup locks GLOBAL and COMMIT.

Fast lanes are enabled only for BACKUP namespace. Support for other namespaces is to be implemented separately.

Lock types are divided in 3 categories: lightweight, lightweight exception and heavyweight.

Lightweight lock types represent DML: MDL_BACKUP_DML, MDL_BACKUP_TRANS_DML, MDL_BACKUP_SYS_DML, MDL_BACKUP_COMMIT. They are fully compatible with each other. Normally served by corresponding fast lane, which is determined by thread_id % metadata_locks_instances.

Lightweight exception lock types represent DDL: MDL_BACKUP_DDL and MDL_BACKUP_ALTER_COPY. Technically they are also fully compatible with each other as well as with lightweight locks, so nothing prevents them from being served by fast lanes. However they use lock upgrade and downgrade routines, which have to be modified to support fast lanes. Which was considered unworthy. These lock types are always served by conventional MDL_lock data structures.

Heavyweight lock types represent ongoing backup: MDL_BACKUP_START, MDL_BACKUP_FLUSH, MDL_BACKUP_WAIT_FLUSH, MDL_BACKUP_WAIT_DDL, MDL_BACKUP_WAIT_COMMIT, MDL_BACKUP_FTWRL1, MDL_BACKUP_FTWRL2, MDL_BACKUP_BLOCK_DDL. These locks are always served by conventional MDL_lock data structures. Whenever such lock is requested, fast lanes are disabled and all tickets registered in fast lanes are moved to conventional MDL_lock data structures. Until such locks are released or aborted, lightweight lock requests are served by conventional MDL_lock data structures.

Strictly speaking moving tickets from fast lanes to conventional MDL_lock data structures is not required. But it allows to reduce complexity and keep intact methods like: MDL_lock::visit_subgraph(), MDL_lock::notify_conflicting_locks(), MDL_lock::reschedule_waiters(), MDL_lock::can_grant_lock().

It is not even required to register tickets in fast lanes. They can be implemented basing on an atomic variable that holds two counters: granted lightweight locks and granted/waiting heavyweight locks. Similarly to MySQL solution, which roughly speaking has "single atomic fast lane". However it appears to be it won't bring any better performance, while code complexity is going to be much higher.

Microbenchmark results

	Fast lanes	Pre fast lanes (068fc787ee6)	Pre backup (7fb9d64989a)	MySQL 8.0.42
1	4,088,307	3,637,686	4,205,214	3,231,018
2	8,028,904	519,211	1,629,328	3,849,115
4	14,593,214	948,317	1,523,693	4,995,629
8	26,272,578	506,201	786,960	4,314,995
10	30,257,186	446,249	756,945	3,506,311
20	46,253,469	394,680	697,301	3,150,102
40	43,845,226	348,183	648,204	2,761,859
80	26,774,658	348,235	647,564	2,743,767
100	33,760,972	348,165	649,773	2,750,426
200	43,833,695	457,080	861,067	3,678,905

Benchmarks were conducted on a 2 socket / 20 core / 40 threads Intel Broadwell system using mtr, mysqlslap and modified is_free_lock() function:

diff --git a/sql/item_func.cc b/sql/item_func.cc
index f9c0e3e8fbc..40f43883ae7 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -4461,6 +4461,21 @@ longlong Item_func_is_free_lock::val_int()
   THD *thd= current_thd;
   null_value= 1;

+  for (int i= 0; i < 1000000; i++)
+  {
+    MDL_request mdl_trans_dml, mdl_commit;
+    MDL_REQUEST_INIT(&mdl_trans_dml, MDL_key::BACKUP, "", "", MDL_BACKUP_TRANS_DML,
+                     MDL_EXPLICIT);
+    MDL_REQUEST_INIT(&mdl_commit, MDL_key::BACKUP, "", "", MDL_BACKUP_COMMIT,
+                     MDL_EXPLICIT);
+    if (thd->mdl_context.acquire_lock(&mdl_trans_dml, thd->variables.lock_wait_timeout))
+      abort();
+    if (thd->mdl_context.acquire_lock(&mdl_commit, thd->variables.lock_wait_timeout))
+      abort();
+    thd->mdl_context.release_lock(mdl_commit.ticket);
+    thd->mdl_context.release_lock(mdl_trans_dml.ticket);
+  }
+
   if (!ull_name_ok(res))
     return 0;

It simulates protection against backup locks that DML threads have to acquire/release. The patch was updated to match MySQL and pre-backup implementations.

Test:

create database mysqlslap;

--echo 1 connection, 10 queries
--exec $MYSQL_SLAP --concurrency=1 --number-of-queries=10 --iterations=3  --query="select is_free_lock('')"

--echo 2 connection, 20 queries
--exec $MYSQL_SLAP --concurrency=2 --number-of-queries=20 --iterations=3  --query="select is_free_lock('')"

--echo 4 connection, 40 queries
--exec $MYSQL_SLAP --concurrency=4 --number-of-queries=40 --iterations=3  --query="select is_free_lock('')"

--echo 8 connection, 80 queries
--exec $MYSQL_SLAP --concurrency=8 --number-of-queries=80 --iterations=3  --query="select is_free_lock('')"

--echo 10 connection, 100 queries
--exec $MYSQL_SLAP --concurrency=10 --number-of-queries=100 --iterations=3  --query="select is_free_lock('')"

--echo 20 connection, 200 queries
--exec $MYSQL_SLAP --concurrency=20 --number-of-queries=200 --iterations=3  --query="select is_free_lock('')"

--echo 40 connection, 400 queries
--exec $MYSQL_SLAP --concurrency=40 --number-of-queries=400 --iterations=3  --query="select is_free_lock('')"

--echo 80 connection, 800 queries
--exec $MYSQL_SLAP --concurrency=80 --number-of-queries=800 --iterations=3  --query="select is_free_lock('')"

--echo 100 connection, 1000 queries
--exec $MYSQL_SLAP --concurrency=100 --number-of-queries=1000 --iterations=3  --query="select is_free_lock('')"

--echo 200 connection, 2000 queries
--exec $MYSQL_SLAP --concurrency=200 --number-of-queries=2000 --iterations=3  --query="select is_free_lock('')"

drop database mysqlslap;

Options file:

--loose-performance-schema=off --metadata-locks-instances=64

Note that performance schema has to be disabled, it just doesn't scale.