MySQL Background thread finishing summary

This article is compiled from "MySQL Technology insider InnoDB storage Engine"

MySQL through the background of various threads, memory pool, file interaction to achieve external services, different threads to achieve different resource operations, the various threads assist each other to complete the database services.
The following is a brief summary of some of MySQL's background threads and the main role, as well as the InnoDB engine change upgrade situation.

Overall, the background thread of MySQL is summarized below, divided into master Thread,io thread,purge Thread,page Cleaner Thread

In this, the Master thread is the most core thread of MySQL, which implements a variety of functions and is constantly being improved and optimized.
InnoDB 1.2.X Previous version of master thread main work content,
The following is pseudo-code to describe the Master Thread

masterthread{//the behavior involved in a single operation every 1 secondspersecondoperation () {1, refreshes the redo log buffer to disk, regardless of whether it is committed (always)
Checkpoint implementations that belong to the master thread
        2, merge Insert buffer (possible), based on the number of IO occurrences within one second, if less than 5, perform a merge insert buffer operation
        3, refresh up to 100 InnoDB buffer pools for dirty pages to disk (possibly),if(buf_get_modified_ratio_pct>buf_max_dirty_pages_pct>buf_get_modified_ratio_pct) Perform a flush of 100 dirty pages to disk
        4, if there is currently no user activity, switch to background mode}//the behavior involved in a single operation every 10 secondstensecondoperation () {1, refresh the dirty pages in the 100 InnoDB buffer pool to disk (possibly), determine if the IO operation is less than 200 times in the last 10 seconds, and if so, refresh 100 dirty pages to disk------>Hard-coded
        2, merge up to 5 insert buffers (always), unconditionally, always perform a merge up to 5 insert buffers
        3, the log cache is flushed to disk (always)
        4, delete useless undo pages (always), try to recycle up to 20 pages at a time---->Hard-coded
        5, refresh 100 or 10 dirty pages to disk (always):---->hard-coded dirty page scale> -%==>Refresh 100 dirty pages to disk, dirty page scale< -%==>Refresh 10 dirty pages to disk}//Backgroundoperation Threadsbackgroundoperation () {1, delete the useless undo page (always)
        2, merge 20 insert buffers (always)
        3, jump back to the main loop (always)
        4, continuously brush the line 100 until the page is satisfied (the dirty page is less than the parameter buf_max_dirty_pages_pct). }}

Not hard to find, InnoDB 1.2.X before the release of a lot of dead parameters are written,
InnoDB 1.2.X After the version of the master thread is based on the actual situation, the flexibility to add some configuration parameters, especially the IO-related innodb_io_capacity parameters.
Innodb_io_capacity represents the IO capability of physical storage, when the background thread interacts with the physical storage Io, because different storage devices have different IO processing capabilities,
Innodb_io_capacity is the parameter that represents the IO processing capability of the physical storage, the default value is 200
The processing performance of the MySQL server can be maximized by the configuration of the innodb_io_capacity according to the actual storage situation.

masterthread{1, add parameter innodb_io_capacity, merge insert buffer =innodb_io_capacity*5%; Refresh Dirty page Count =innodb_io_capacity2, the buf_max_dirty_pages_pct default value is modified to 75%3, Innodb_purge_batch_size, recycle undo page configuration, default value//the behavior involved in a single operation every 1 secondspersecondoperation () {1, refreshes the redo log buffer to disk, regardless of whether the commit (always) belongs to the master thread type checkpoint2, Merge Insert buffers (possibly), based on the number of IO occurrences within one second:if(last 1 seconds of ios<5%innodb_io_capacity) {perform merge 5%innodb_io_capacity A page insert buffer operation}3, flush the dirty pages in the buffer pool to disk (possibly):if(buf_get_modified_ratio_pct>buf_max_dirty_pages_pct) Refresh the number of dirty pages: -%*innodb_io_capacityif(buf_get_modified_ratio_pct<buf_max_dirty_pages_pct) Refresh the number of dirty pages:Ten%*innodb_io_capacity4, if there is currently no user activity, switch to background mode}//the behavior involved in a single operation every 10 secondstensecondoperation () {1, flush the dirty pages in the buffer pool to disk (possibly):if(buf_get_modified_ratio_pct>buf_max_dirty_pages_pct) Refresh the number of dirty pages: -%*innodb_io_capacityif(buf_get_modified_ratio_pct<buf_max_dirty_pages_pct) Refresh the number of dirty pages:Ten%*innodb_io_capacity2, Merge Insert buffers (possibly), based on the number of IO occurrences in the past second:if(ios<5%innodb_io_capacity) {perform merge 5%innodb_io_capacity A page insert buffer operation}3, the log cache is flushed to disk (always)4, delete the useless undo page (always) according to the parameters: Innodb_purge_batch_size} Backgroundoperation () {1, delete the useless undo page (always)

        2, merge innodb_io_capacity insert buffers (always)
        3, jump back to the main loop (always)
        4, constantly brush the line innodb_io_capacity a page to disk until satisfied (Dirty page is less than parameter buf_max_dirty_pages_pct). }}

Stand-alone page Cleaer thread

Pagecleanerthread () {        1, refreshes dirty pages in the buffer pool to disk (possible):        if(buf_get_modified_ratio _pct>buf_max_dirty_pages_pct)            number of dirty pages flushed:%*innodb_io_capacity        if (buf_get_modified_ratio_pct<buf_max_dirty_pages_pct)            Refresh dirty page number:%*  Innodb_io_capacity}

MySQL Background thread finishing summary