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

Deprecate and ignore options for InnoDB concurrency throttling

Details

    Description

      The variable innodb_thread_concurrency was useful years ago when computing resources were limited, but nowadays setting this value to anything than unlimited (0) makes little sense and actually makes things worse ,because queries have to wait outside InnoDB.

      We have seen many customers mistakenly setting this to a small value like 16 or 64 and then complaining the server was slow.

      In the latest instantation of the above problem I've seen (just today), the available slots were all taken by long running transactions (left open) and the server was stalled as a result.

      So, a dangerous config option and IMHO a good candidate for deprecation/removal.

      Thanks
      Rick

      Attachments

        Issue Links

          Activity

            Funny, I was thinking exactly the same yesterday, after fixing MDEV-23369.

            With the MDEV-23369 fix in 10.5, we seem to scale rather well to an insane number of concurrent threads. I tested 1000 connections on a server where nproc reports 56. That would be at least 1033 threads on the server, and at least 1000 more threads for the client workload that was running on the same server.

            Yes, there is an inherent limit caused by there being only 128 rollback segments (the component in DB_ROLL_PTR is only 7 bits). The maximum write scalability ought to be reached with innodb_undo_tablespaces=128. But, we do not see a dramatic drop of the total throughput when we add more threads.

            Hence, the ‘thread throttling’ at the high level by innodb_thread_concurrency or innodb_commit_concurrency does not seem to make much sense, and those parameters should probably be deprecated and ignored starting with the 10.6 release.

            marko Marko Mäkelä added a comment - Funny, I was thinking exactly the same yesterday, after fixing MDEV-23369 . With the MDEV-23369 fix in 10.5, we seem to scale rather well to an insane number of concurrent threads. I tested 1000 connections on a server where nproc reports 56. That would be at least 1033 threads on the server, and at least 1000 more threads for the client workload that was running on the same server. Yes, there is an inherent limit caused by there being only 128 rollback segments (the component in DB_ROLL_PTR is only 7 bits). The maximum write scalability ought to be reached with innodb_undo_tablespaces=128 . But, we do not see a dramatic drop of the total throughput when we add more threads. Hence, the ‘thread throttling’ at the high level by innodb_thread_concurrency or innodb_commit_concurrency does not seem to make much sense, and those parameters should probably be deprecated and ignored starting with the 10.6 release.

            I would deprecate and ignore and hard-wire to 0 the following parameters:

            • innodb_thread_concurrency
            • innodb_commit_concurrency
            • innodb_replication_delay
            • innodb_concurrency_tickets
            • innodb_thread_sleep_delay
            • innodb_adaptive_max_sleep_delay

            Also, the column INFORMATION_SCHEMA.INNODB_TRX.trx_concurrency_tickets would become the constant 0.

            marko Marko Mäkelä added a comment - I would deprecate and ignore and hard-wire to 0 the following parameters: innodb_thread_concurrency innodb_commit_concurrency innodb_replication_delay innodb_concurrency_tickets innodb_thread_sleep_delay innodb_adaptive_max_sleep_delay Also, the column INFORMATION_SCHEMA.INNODB_TRX.trx_concurrency_tickets would become the constant 0.

            We will deprecate and ignore these parameters in MariaDB 10.5.5 Server and remove in 10.6, declaring them as MARIADB_REMOVED_OPTION for compatibility with old configuration files.

            marko Marko Mäkelä added a comment - We will deprecate and ignore these parameters in MariaDB 10.5.5 Server and remove in 10.6, declaring them as MARIADB_REMOVED_OPTION for compatibility with old configuration files.

            People

              marko Marko Mäkelä
              rpizzi Rick Pizzi (Inactive)
              Votes:
              4 Vote for this issue
              Watchers:
              6 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.