svoj, can you please test

cmake -DMUTEXTYPE:STRING=event …

cmake -DMUTEXTYPE:STRING=sys …

cmake -DMUTEXTYPE:STRING=futex …

on different instruction set architectures and possibly several recent microarchitectures of AMD64 implementations (such as Intel Skylake and its ‘temporal neighbours’)?

If the system mutex is not significantly slower than the specialized ones on any platform (GNU/Linux on AMD64, Aarch64; Windows on AMD64), I think that we should remove the specialized code. danblack conducted his tests on POWER 8.

On my 16-core, 32-thread Intel machines I see no significant difference between the different mutex implementations. See attached spread sheet MDEV-21452.ods

You won't see any difference for sure until backup locks is the largest bottleneck. With backup locks disabled I can immediately see 20% throughput decline in oltp_update_index benchmark.

Backup locks disabled with this patch:

diff --git a/sql/handler.cc b/sql/handler.cc

index eb7b5b8..012ef20 100644

--- a/sql/handler.cc

+++ b/sql/handler.cc

@@ -1567,7 +1567,7 @@ int ha_commit_trans(THD *thd, bool all)

   DBUG_PRINT("info", ("is_real_trans: %d  rw_trans:  %d  rw_ha_count: %d",

                       is_real_trans, rw_trans, rw_ha_count));

-  if (rw_trans)

+  if (0 && rw_trans)

   {

     /*

       Acquire a metadata lock which will ensure that COMMIT is blocked

diff --git a/sql/sql_base.cc b/sql/sql_base.cc

index c41e08e..f9e3f34 100644

--- a/sql/sql_base.cc

+++ b/sql/sql_base.cc

@@ -2100,7 +2100,7 @@ bool open_table(THD *thd, TABLE_LIST *table_list, Open_table_context *ot_ctx)

   }

   if (!(flags & MYSQL_OPEN_HAS_MDL_LOCK) &&

-      table->s->table_category < TABLE_CATEGORY_INFORMATION)

+      table->s->table_category < TABLE_CATEGORY_INFORMATION && 0)

   {

     /*

       We are not under LOCK TABLES and going to acquire write-lock/

Results for event vs sys mutex:

[ 10s ] thds: 40 tps: 235212.45 qps: 235212.45 (r/w/o: 0.00/235212.45/0.00) lat (ms,95%): 0.28 err/s: 0.00 reconn/s: 0.00

[ 20s ] thds: 40 tps: 239224.64 qps: 239224.64 (r/w/o: 0.00/239224.64/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00

[ 30s ] thds: 40 tps: 235586.62 qps: 235586.62 (r/w/o: 0.00/235586.62/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00

[ 40s ] thds: 40 tps: 237855.90 qps: 237855.90 (r/w/o: 0.00/237855.90/0.00) lat (ms,95%): 0.26 err/s: 0.00 reconn/s: 0.00

[ 50s ] thds: 40 tps: 242333.85 qps: 242333.95 (r/w/o: 0.00/242333.95/0.00) lat (ms,95%): 0.25 err/s: 0.00 reconn/s: 0.00

[ 60s ] thds: 40 tps: 230731.39 qps: 230731.29 (r/w/o: 0.00/230731.29/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00

[ 70s ] thds: 40 tps: 227764.88 qps: 227764.88 (r/w/o: 0.00/227764.88/0.00) lat (ms,95%): 0.26 err/s: 0.00 reconn/s: 0.00

[ 80s ] thds: 40 tps: 233360.57 qps: 233360.57 (r/w/o: 0.00/233360.57/0.00) lat (ms,95%): 0.26 err/s: 0.00 reconn/s: 0.00

[ 90s ] thds: 40 tps: 234724.00 qps: 234724.00 (r/w/o: 0.00/234724.00/0.00) lat (ms,95%): 0.26 err/s: 0.00 reconn/s: 0.00

[ 100s ] thds: 40 tps: 233649.87 qps: 233649.87 (r/w/o: 0.00/233649.87/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00

[ 110s ] thds: 40 tps: 228001.25 qps: 228001.45 (r/w/o: 0.00/228001.45/0.00) lat (ms,95%): 0.26 err/s: 0.00 reconn/s: 0.00

[ 120s ] thds: 40 tps: 229956.01 qps: 229955.81 (r/w/o: 0.00/229955.81/0.00) lat (ms,95%): 0.27 err/s: 0.00 reconn/s: 0.00

SQL statistics:

    queries performed:

        read:                            0

        write:                           28084784

        other:                           0

        total:                           28084784

    transactions:                        28084784 (234027.56 per sec.)

    queries:                             28084784 (234027.56 per sec.)

[ 10s ] thds: 40 tps: 187679.62 qps: 187679.62 (r/w/o: 0.00/187679.62/0.00) lat (ms,95%): 0.33 err/s: 0.00 reconn/s: 0.00

[ 20s ] thds: 40 tps: 192377.30 qps: 192377.30 (r/w/o: 0.00/192377.30/0.00) lat (ms,95%): 0.31 err/s: 0.00 reconn/s: 0.00

[ 30s ] thds: 40 tps: 194527.13 qps: 194527.13 (r/w/o: 0.00/194527.13/0.00) lat (ms,95%): 0.30 err/s: 0.00 reconn/s: 0.00

[ 40s ] thds: 40 tps: 187974.25 qps: 187974.25 (r/w/o: 0.00/187974.25/0.00) lat (ms,95%): 0.33 err/s: 0.00 reconn/s: 0.00

[ 50s ] thds: 40 tps: 189598.45 qps: 189598.45 (r/w/o: 0.00/189598.45/0.00) lat (ms,95%): 0.30 err/s: 0.00 reconn/s: 0.00

[ 60s ] thds: 40 tps: 192302.85 qps: 192302.85 (r/w/o: 0.00/192302.85/0.00) lat (ms,95%): 0.30 err/s: 0.00 reconn/s: 0.00

[ 70s ] thds: 40 tps: 190873.45 qps: 190873.45 (r/w/o: 0.00/190873.45/0.00) lat (ms,95%): 0.30 err/s: 0.00 reconn/s: 0.00

[ 80s ] thds: 40 tps: 182795.87 qps: 182795.97 (r/w/o: 0.00/182795.97/0.00) lat (ms,95%): 0.34 err/s: 0.00 reconn/s: 0.00

[ 90s ] thds: 40 tps: 188988.71 qps: 188988.61 (r/w/o: 0.00/188988.61/0.00) lat (ms,95%): 0.31 err/s: 0.00 reconn/s: 0.00

[ 100s ] thds: 40 tps: 190645.87 qps: 190645.97 (r/w/o: 0.00/190645.97/0.00) lat (ms,95%): 0.30 err/s: 0.00 reconn/s: 0.00

[ 110s ] thds: 40 tps: 191076.30 qps: 191076.20 (r/w/o: 0.00/191076.20/0.00) lat (ms,95%): 0.30 err/s: 0.00 reconn/s: 0.00

[ 120s ] thds: 40 tps: 190803.80 qps: 190803.90 (r/w/o: 0.00/190803.90/0.00) lat (ms,95%): 0.30 err/s: 0.00 reconn/s: 0.00

SQL statistics:

    queries performed:

        read:                            0

        write:                           22797410

        other:                           0

        total:                           22797410

    transactions:                        22797410 (189967.22 per sec.)

    queries:                             22797410 (189967.22 per sec.)

I tend to remember I saw larger difference in 10.2. In 10.3 we have trx_sys.mutex eliminated from hot path, so it matters less. FWIU in 10.5 we have log_sys mutexes eliminated from hot path, so it matters even less. But still 20% on not that recent hardware is a lot.

All in all event mutex seem to give better throughput compared to pthread mutex under certain loads. Implementing own locking primitives is a dead end for sure. But until we fix contention it'd probably be wise to keep them.

The tests innodb.innodb_sys_semaphore_waits and sys_vars.innodb_fatal_semaphore_wait_threshold depend on MUTEXTYPE=event.
If the server is built with MUTEXTYPE=sys or MUTEXTYPE=futex, then the InnoDB internal watchdog for hangs will not kill the server. If we were to change the option on any platform (presumably, other than IA-32 and AMD64) , we would have to decide what to do with this watchdog killer. I am rather sure that it cannot be made to work with native mutex, and maybe not with Linux fast userspace mutex either.

New results in MDEV-21452-nbl.ods

The server was now patched to disable backup locks. I also added the update_index test from sysbench OLTP (updating an indexed column with autocommit). Now I see a clear performance impact for 'sys' mutexes.

We decided to evaluate the performance of the said mutex change on all aarch. I evaluated it for ARM. Here are the results.

We didn't observe any major regression or performance gain arising from switching to sys mutex or futex. So, for now, the recommendation would be to continue with the existing default (event).

To find out more about how we executed the test you can checkout
https://github.com/mysqlonarm/benchmark-suites
https://github.com/mysqlonarm/benchmark-suites/blob/master/sysbench/conf/mdb_mutex_study.cnf

I think that before we can proceed on this, we must remove some already known InnoDB mutex contention points, mainly in lock_sys and log_sys.

Both futex and sys provide perf improvements over event with Power only, even with the lock_sys/log_sys currently. Maybe I can try to pick one of them trying to make an assumption on the potential gains of lock_sys/log_sys, however it doesn't obviously have to be permanent.

I assume you have significant confidence in the futex and sys implementations despite their non-default status?

One more case of underlying contention was filed in MDEV-22871.

danblack, the current default seems to work fine on AMD64 and ARM. I am OK to change the mutex type on POWER for some or all InnoDB mutexes on 10.5 or 10.6, if it can be demonstrated to improve the performance.

I believe that we must also deal with MDEV-22871 and MDEV-21212 fairly soon, targeting the 10.5 release.

While working on MDEV-23399, it occurred to me that the InnoDB os_event_t (which emulates the old synchronization primitive of Microsoft Windows NT) is rather inefficient, because it consists of a mutex and a condition variable. Usually the events are used in combination with another mutex. If we used conventional mutexes and condition variables (via the mysql_mutex_t and mysq_cond_t wrappers), we could simplify some inter-thread communication and reduce overhead.

Removing the InnoDB mutex implementation would also remove some instrumentation that is currently available either via SHOW ENGINE INNODB MUTEX or INFORMATION_SCHEMA.innodb_metrics (MDEV-15706). MDEV-23472 shows an example stack traces of that instrumentation. Because also mutexes in unused transaction objects are instrumented, we had to complicate the AddressSanitizer instrumentation (MDEV-15030, MDEV-22782) to allow the mutexes of freed transaction objects to be accessed.

I do not think that we can replace all InnoDB rw_lock_t with mysql_rwlock_t. Emulating the custom SX mode with two rw-locks would most likely incur some performance overhead.

I started work to replace os_event_t and ib_mutex_t with mysql_cond_t and mysql_mutex_t. So far, this includes simplifying the InnoDB rw_lock_t to use a mutex and two condition variables, instead of using two os_event_t.

We still have 10 os_event_t and 45 ib_mutex_t left.

We have 8 os_event_create() and 37 mutex_create() calls left. In fts_cache_t, two rw_lock_t can be replaced with ordinary mutex. Some mutexes were orphan.

In lock_wait_suspend_thread() we seem to have some low-hanging fruit:

	mysql_mutex_lock(&lock_sys.mutex);

	while (trx->lock.wait_lock) {

		mysql_cond_wait(&slot->cond, &lock_sys.mutex);

	}

	mysql_mutex_unlock(&lock_sys.mutex);

	thd_wait_end(trx->mysql_thd);

We should actually protect trx->lock.wait_lock with trx_t::mutex to reduce contention on lock_sys.mutex.

SHOW ENGINE INNODB MUTEX as well as INFORMATION_SCHEMA.INNODB_MUTEXES would no longer display any information about InnoDB mutexes. They may output information about InnoDB homebrew rw_lock_t.

The corresponding source tree behaved well during RQG testing.

INFORMATION_SCHEMA.INNODB_MUTEXES (MDEV-7399) did not report any mutex information since MariaDB 10.2.2. It was removed in MDEV-24142 along with replacing the InnoDB buf_block_t::lock and dict_index_t::lock with something simpler. Other rw-locks had already been replaced with simpler ones in MDEV-24167.

The command SHOW ENGINE INNODB MUTEX will return no output, because no InnoDB-specific instrumentation of rw-locks or mutexes will exist.

Also removed will be the InnoDB latching order checks and the debug parameter innodb_sync_debug as well as the view INFORMATION_SCHEMA.INNODB_SYS_SEMAPHORE_WAITS.

In the current state of development, the parameter innodb_fatal_semaphore_wait_threshold will not be enforced at all, and there will be no "Long semaphore wait" messages. I will have to implement something special (probably timeout-based acquisition) for dict_sys.mutex and lock_sys.mutex. Those should cover most hangs.

commit 9159383f32d8350dfa91bb62c825c64b1dc091d1 (HEAD, origin/bb-10.6-MDEV-21452)
behaved well during RQG testing.
Bad effects observed are in the in MariaDB versions without MDEV-21452 too.

I implemented special enforcement of innodb_fatal_semaphore_wait_threshold for dict_sys.mutex and lock_sys.mutex. Due to an observed performance regression at high concurrency, I removed the lock_sys.mutex instrumentation and retained only the one on dict_sys.mutex. If pthread_mutex_trylock() fails, then the current thread would compare-and-swap 0 with its current time before waiting in pthread_mutex_lock(). Either the srv_monitor_task() or a subsequent thread that attempts to acquire dict_sys.mutex would then enforce the innodb_fatal_semaphore_wait_threshold and kill the process if necessary.

While rewriting the test sys_vars.innodb_fatal_semaphore_wait_threshold accordingly, I noticed that not all hangs would be caught even in the data dictionary cache. For example, if a DDL operation hung while holding both dict_sys.latch and dict_sys.mutex, a subsequent DDL operation would hang while waiting for dict_sys.latch, before even starting the wait for dict_sys.mutex. But, DML threads that are trying to open a table would acquire dict_sys.mutex and be subject to the watchdog. Hopefully this type of watchdog testing will be adequate. We could of course add more instrumentation to debug builds.

The main reason for having the homebrew mutexes was that their built-in spin loops could lead to better performance than the native implementation on contended mutexes.

Some performance regression was observed for larger thread counts (exceeding the CPU core count) when updating non-indexed columns. I suspect that the culprit is contention on lock_sys.mutex, and I believe that implementing MDEV-20612 will address that.

Also log_sys.mutex is known to be a source of contention, but it was changed to a native mutex already in MDEV-23855. MDEV-23855 also removed some contention on fil_system.mutex, but kept it as a homebrew mutex. Contention on these mutexes should be reduced further in MDEV-14425.

We observed frequent timeouts and extremely slow execution time of the test mariabackup.xb_compressed_encrypted especially on Microsoft Windows builders. Those machines have 4 processor cores, and they run 4 client/server process pairs in parallel. (Our Linux builders have a lot more processor cores.) The used to specify innodb_encryption_threads=4. That is, there was one page cleaner thread doing the actual work of writing data pages, and 4 ‘manager’ threads that fight each other to see who gets to wield the shovel and add more dirt to the pile that the page cleaner is trying to transport away. Changing the test to use innodb_encryption_threads=1 seems to have fixed the problem.

With the previous setting, the test timed out on win32-debug on two successive runs; with the lower setting innodb_encryption_threads=1 it passed (at least once), consuming 13, 14, and 41 seconds on win32-debug and 14, 22, 27 seconds on win64-debug. On a previous run with innodb_encryption_threads=4 , the execution time was more than 500 seconds on win64-debug, and for 2 of the 3 innodb_page_size values, the execution time exceeded 900 seconds on win32-debug.

Thanks to wlad for making the observation that the encryption threads were conflicting with each other. In MDEV-22258 we did experiment with different settings, and back then (still with the homebrew mutexes) there seemed to be some benefit of having multiple encryption (page-dirtying) threads.

This highlights a benefit of the homebrew mutexes that we removed: Spinning may yield a little better throughput when there is a lot of contention. I agree with the opnion that svoj has stated earlier: it is better to fix the underlying contention than to implement workarounds. I am confident that with MDEV-14425 and MDEV-20612 we will regain some scalability when the number of concurrent connections exceeds the number of processor cores. We already reduced buf_pool.mutex contention in MDEV-15053 and MDEV-23399 et al, and fil_system.mutex contention in MDEV-23855.

The problem with the constantly sleeping and waking encryption threads was partially addressed in MDEV-24426. On GNU/Linux, with the native mutexes and condition variables, the CPU usage was low, but with the homebrew mutexes and events all threads seemed to spinning constantly. Maybe on Microsoft Windows the flood of sleeps and wakeups performs worse?