[MDEV-31220] MariaDB:performance seems to be limited when the value of parameter innodb_thread_concurrency is not zero Created: 2023-05-09  Updated: 2023-06-06  Resolved: 2023-06-06

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.4.25
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Wenwen Jing Assignee: Unassigned
Resolution: Won't Fix Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-23379 Deprecate and ignore options for Inno... Closed
relates to MDEV-23397 Remove deprecated InnoDB options in 10.6 Closed

 Description   

We operate performance testing of select statments on MariaDB Gelara cluster by jmeter whose concurrency is 30 . The results tell that QPS is about 20 when the value of parameter innodb_thread_concurrency is not zero (e.g. ),and QPS rises to 200 when the value of parameter innodb_thread_concurrency sets to zero.
It seems the various values of parameter innodb_thread_concurrency have no effects on QPS,that does not make senses.

CPUCores Concurrency innodb_thread_concurrency QPS
144 30 32 20
144 30 0 200
144 30 128 20
144 30 144 20
144 30 500 20


 Comments   
Comment by Marko Mäkelä [ 2023-05-09 ]

This is to be expected, by design. If the throttling parameter is nonzero, there will be some overhead involved with managing “concurrency tickets” that could become a bottleneck by itself.

Some parameters related to working around scalability bottlenecks were deprecated and ignored in MariaDB Server 10.5 by MDEV-23379, and removed from MariaDB Server 10.6 in MDEV-23397. I think that the original motivation around these was contention on buf_pool.mutex or the kernel_mutex that was later split into trx_sys.mutex, lock_sys.mutex and many others.

The contention on mutexes or rw-locks depends somewhat on the type of workload. For MDEV-29401 I recently collected statistics for a particular type of read-only workload. It is dominated by the following InnoDB latches:

For write-heavy workloads, log_sys.mutex or the 10.8+ log_sys.latch should dominate, and there could be some contention on buf_pool.flush_list_mutex as well.

I am reluctant to work on any performance improvements of older releases than 10.6.

Comment by Sergei Golubchik [ 2023-06-06 ]

We cannot implement performance enhancements in a stable 10.4 series, as it would risk its stability. You're encouraged to upgrade to a later release (the latest LTS is 10.11) and try there.

Generated at Thu Feb 08 10:22:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.