[MDEV-18698] Show InnoDB's internal background threads in SHOW ENGINE INNODB STATUS Created: 2019-02-22  Updated: 2020-03-31

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Fix Version/s: None

Type: Task Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Marko Mäkelä
Resolution: Unresolved Votes: 2
Labels: None

Issue Links:
PartOf
is part of MDEV-16264 Implement a common work queue for Inn... Closed
Relates
relates to MDEV-15528 Avoid writing freed InnoDB pages Closed
relates to MDEV-16264 Implement a common work queue for Inn... Closed
relates to MDEV-19514 Defer change buffer merge until pages... Closed
relates to MDEV-21566 Lock monitor doesn't print a name for... Closed
relates to MDEV-21870 Deprecate and ignore innodb_scrub_log... Closed
relates to MDEV-22087 Increase buffer size for query in SHO... Open
relates to MDEV-11703 InnoDB background threads show up in ... Stalled
relates to MDEV-17237 thread IDs are printed in different f... Open
relates to MDEV-17238 Document special thread IDs used in S... Open
relates to MDEV-18391 Print ENGINE INNODB STATUS in machine... Open
relates to MDEV-18429 Consistent non-locking reads do not a... Closed
relates to MDEV-18572 Thread executing DROP TABLE listed tw... Open
relates to MDEV-18582 Port status variables related to SHOW... Closed
relates to MDEV-21330 Lock monitor doesn't print a semaphor... Closed
relates to MDEV-21390 lock_print_info_summary() should work... Closed

 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.

 Comments   
Comment by Marko Mäkelä [ 2020-03-31 ]

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
Generated at Thu Feb 08 08:46:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.