InnoDB creates a large number of threads that are specializing on a single task. This makes debugging hard, because core dumps contain stack traces for a large number of threads. It also causes unnecessary thread stack allocation and increases the complexity of scheduling threads. Many of the threads are waking up periodically, polling for work(for those, we can introduce a timer task , for example OS timers would submit work to common pool). A lot of CPU and context switching is nowadays spent on "coordinator" threads(purge, page-cleaner).
We should make InnoDB use a pool of threads, and scale the size of this pool based on the workload. There should be a common work queue for all the threads.
All of the following background threads would be replaced by the common thread pool, listed in roughly descending order of impact/difficulty ratio:
- buf_flush_page_cleaner_worker,buf_flush_page_cleaner_coordinator (only one after MDEV-15058)
- recv_writer_thread (a special "page cleaner" during redo log apply; triggered by buffer pool LRU)
- fil_crypt_thread (needs to be rewritten to use a queue of tablespaces that need key rotation)
- buf_dump_thread (triggered by SET GLOBAL innodb_buffer_pool_(dump|load)_(abort|now))
- srv_purge_coordinator_thread, srv_worker_thread (see also MDEV-16260; work added by transaction commit)
- trx_rollback_all_recovered (any work is submitted at InnoDB startup)
- log_scrub_thread (can probably be removed in MDEV-14425)
- dict_stats_thread (work submitted by dict_stats_update_if_needed() and for defragmentation, btr_page_split_and_insert())
- btr_defragment_thread (work submitted by btr_defragment_add_index() in OPTIMIZE TABLE)
- buf_resize_thread (work initiated by SET GLOBAL innodb_buffer_pool_size)
- fts_optimize_thread (work initiated by fts_optimize_add_table() on DDL or when loading table definition)
- fts_parallel_tokenization, fts_parallel_merge (should be generalized to allow parallel execution of multiple ADD INDEX for any ALTER TABLE; work added by ALTER TABLE)
Some of the following might still need dedicated threads:
We should implement native asynchronous I/O on BSD systems using kevent(), and remove the support for simulated asynchronous I/O threads.
Pending read requests can be directly waited for by buf_page_get_gen(). If read-ahead is desired, that can be implemented by adding a read completion request when handling the I/O completion.