Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-31220

MariaDB:performance seems to be limited when the value of parameter innodb_thread_concurrency is not zero

Details

    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

      Attachments

        Issue Links

          Activity

            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.

            marko Marko Mäkelä added a comment - 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: lock_sys.latch (despite improvements in MDEV-20612 ) fil_space_t::latch lock_sys.wait_mutex trx_rseg_t::latch (improved in MDEV-25062 ) dict_sys.latch (improved in MDEV-24258 ) buf_pool.mutex (most recently improved in MDEV-26055 , MDEV-26827 ) log_sys.mutex (replaced with log_sys.latch in MDEV-27774 ) 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.

            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.

            serg Sergei Golubchik added a comment - 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.

            People

              Unassigned Unassigned
              jjs Wenwen Jing
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.