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

Show InnoDB's internal background threads in SHOW ENGINE INNODB STATUS

Details

    Description

      InnoDB uses internal background threads for a few different things. For example:

      • It uses a background thread to update persistent statistics.
      • It uses one or more background threads (determined by innodb_encryption_threads) to encrypt/decrypt tables where ENCRYPTED=DEFAULT.

      https://mariadb.com/kb/en/library/innodb-system-variables/#innodb_encryption_threads

      • It uses one or more background threads (determined by innodb_purge_threads) to purge unneeded undo logs.

      https://mariadb.com/kb/en/library/innodb-system-variables/#innodb_purge_threads

      • It uses one or more background threads (determined by either innodb_mtflush_threads or innodb_page_cleaners) to flush dirty pages from the buffer pool.

      https://mariadb.com/kb/en/library/innodb-system-variables/#innodb_mtflush_threads

      https://mariadb.com/kb/en/library/innodb-system-variables/#innodb_page_cleaners

      • It uses one or more background threads (determined by innodb_read_io_threads) to read pages from disk.

      https://mariadb.com/kb/en/library/innodb-system-variables/#innodb_read_io_threads

      • It uses one or more background threads (determined by innodb_write_io_threads) to write pages to disk.

      https://mariadb.com/kb/en/library/innodb-system-variables/#innodb_write_io_threads

      • Supposedly it uses some kind of background thread to implement some kind of "DROP TABLE" queue (based on the comments of MDEV-8069).

      It would probably make sense to show some or all of these threads in SHOW ENGINE INNODB STATUS output, since they can hold semaphores and cause or exacerbate concurrency issues.

      Attachments

        Issue Links

          is part of

          Task - A task that needs to be done. MDEV-16264 Implement a common work queue for InnoDB background tasks

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15528 Avoid writing freed InnoDB pages

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-16264 Implement a common work queue for InnoDB background tasks

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-19514 Defer change buffer merge until pages are requested

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21566 Lock monitor doesn't print a name for RW-latches

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-21870 Deprecate and ignore innodb_scrub_log and innodb_scrub_log_speed

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22087 Increase buffer size for query in SHOW ENGINE INNODB STATUS output

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11703 InnoDB background threads show up in the processlist

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Stalled

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17237 thread IDs are printed in different formats in different sections of SHOW ENGINE INNODB STATUS output

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17238 Document special thread IDs used in SHOW ENGINE INNODB STATUS output

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-18391 Print ENGINE INNODB STATUS in machine parsable format

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18429 Consistent non-locking reads do not appear in TRANSACTIONS section of SHOW ENGINE INNODB STATUS

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18572 Thread executing DROP TABLE listed twice in SHOW ENGINE INNODB STATUS output

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Task - A task that needs to be done. MDEV-18582 Port status variables related to SHOW ENGINE INNODB STATUS from XtraDB to InnoDB in 10.2+

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21330 Lock monitor doesn't print a semaphore's last reserved thread in non-debug builds and INFORMATION_SCHEMA.INNODB_SYS_SEMAPHORE_WAITS is totally broken

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21390 lock_print_info_summary() should work even when trx_sys.mutex is locked

          • Major - Major loss of function.
          • Closed

          Activity

            marko Marko Mäkelä added a comment -

            Starting with MDEV-16264 in MariaDB Server 10.5, InnoDB uses fewer dedicated threads, and instead the work is being performed in tasks that are invoked by a thread pool where threads can be created and destroyed based on demand.

            The innodb_mtflush was removed in MariaDB 10.2, and 10.1 will soon reach its end of life.

            The background DROP TABLE is executed by the InnoDB master thread. Starting with 10.5, we no longer have change buffer merge in the background (MDEV-19514) or any background activity related to scrubbing (MDEV-15528, MDEV-21870).

            10.5 still uses dedicated page I/O threads as well as dedicated threads for the following:

            • page cleaner (at least until MDEV-16526)
            • recv_writer_thread for recovery, likely to be removed in MDEV-16526 or MDEV-14481
            • fil_crypt_thread for encryption key rotation (should be rewritten as task, and maybe optimized to prevent unnecessary writes)
            • trx_rollback_all_recovered for rolling back recovered incomplete transactions, until MDEV-21169 is fixed
            marko Marko Mäkelä added a comment - Starting with MDEV-16264 in MariaDB Server 10.5, InnoDB uses fewer dedicated threads, and instead the work is being performed in tasks that are invoked by a thread pool where threads can be created and destroyed based on demand. The innodb_mtflush was removed in MariaDB 10.2, and 10.1 will soon reach its end of life. The background DROP TABLE is executed by the InnoDB master thread. Starting with 10.5, we no longer have change buffer merge in the background ( MDEV-19514 ) or any background activity related to scrubbing ( MDEV-15528 , MDEV-21870 ). 10.5 still uses dedicated page I/O threads as well as dedicated threads for the following: page cleaner (at least until MDEV-16526 ) recv_writer_thread for recovery, likely to be removed in MDEV-16526 or MDEV-14481 fil_crypt_thread for encryption key rotation (should be rewritten as task, and maybe optimized to prevent unnecessary writes) trx_rollback_all_recovered for rolling back recovered incomplete transactions, until MDEV-21169 is fixed

            People

              marko Marko Mäkelä
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              2 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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