InnoDB Architecture (iv) how Master thread works

How Master Thread Works

In the previous article: InnoDB architecture-The background thread says: The main work of the InnoDB storage engine is done in a separate background thread, master thread. This article specifically describes the implementation of this thread and the possible problems with that thread.

First, the master Thread before the innodb1.0x version

The master thread has the highest thread priority and consists of multiple loops: main loop (loop), background loop (background loop), refresh loop (flush loop), pause Loop (suspend loop), The Master thread switches between loops based on the state in which the database is running.

Loop main Loop ( Most operations are in this loop ) This is done by two most operations, per second and every 10 seconds :

void Master_thread () {    loop:    for (int i=0; i<; i++) {          do thing once per second        1if  necessary    }      do things once per then seconds    goto  Loop;}

It can be found that loop loops are implemented by thread sleep, which means that the operation per second or every 10 per second is not very precise and may have varying degrees of latency (delay) in the case of a large load.

The operations once per second include:

1. log buffering flushes to disk (always): Even if the transaction is not committed, InnoDB flushes the redo log buffer to the redo log file every second, so it is understandable why the large transaction commits and the time is also very short.

2. Merge Insert bufferedInsert buffer (possible): not refreshed per second, if the number of IO in the previous second is less than 5, it is considered that the IO pressure is small, you can perform the merge insert buffer operation.

3. refresh up to 100 InnoDB buffer pool dirty pages to disk (possible): Determines whether the proportion of dirty pages in the current buffer pool (buf_get_modifyed_ratio_pct) exceeds the configuration file innodb_max_dirty_ pages_pct This parameter (default is 90) if this threshold is exceeded, the InnoDB storage engine thinks it needs to do a synchronous operation and writes 100 dirty pages to disk.

4. If there is currently no user activity, switch to background loop(possible)

Operation every 10 seconds:

1. Refresh 100 dirty pages to disk (possible)

2. merge up to 5 insert buffers (always)

3. Flush Log buffers to disk (always)

4. Delete the useless undo page (always): The InnoDB storage engine performs full purse operations, that is, delete useless undo pages, update the table, delete such operations, the original row was marked Delete, but because of the consistent read-read relationship, The version number of these lines needs to be preserved, and the collection is deleted.

5. Refresh 100 or 10 dirty pages to disk (always)

And then see background loop . If there is no user activity (database idle) or database shutdown (shutdown), switch to this loop to perform the following actions:

1. Delete the useless undo page (always)

2. Merge 20 insert buffers (always)

3. Jump back to the main loop (always)

4. Refresh the 100 pages continuously until they meet the criteria (possibly, jump to flush loop): If Fulsh loop does not have anything to do, the InnoDB storage engine switches to suspend loop, scraping the master thread.

Second, the innodb1.2.x version of the master Thread

In today's rapid development of disk technology, hard coding are made for buffer pools to disk refresh, which limits the performance of the InnoDB storage engine to disk IO, especially write performance.

As a result, the parameter innodb_io_capacity is used to represent the throughput of the IO, and by default 200, the number of pages flushed to disk is controlled as a percentage of innodb_io_capacity:

When merging the insert buffers, the number of merge insert buffers is innodb_io_capacity value 5%;

When a dirty page is refreshed from the buffer pool, the number of dirty pages in the brush line is innodb_io_capcity;

By following the code below, we can get the working method of innodb1.2x before master thread:

voidMaster_thread () {loop: for(intI=0; i<Ten; i++) {Thread_sleep (1)//Sleep 1 seconds         Dolog buffer flush to dishif(Last_one_second_ios <5%innodb_io_capacity) {             DoMerget5%innodb_io_capacity Insert Buffer}if(buf_get_modified_ratio_pct > innodb_max_dirty_pages_pct) {//if the dirty page ratio in the buffer pool is greater than innodb_max_dirty_pages_pct (default is 75 o'clock)             DoBuffer Pool Flush -% innodb_io_capacity Dirty page//Flush all dirty pages to disk}Else if(Enable adaptive flush) {//If you open an account, adaptive refresh             DoBuffer pool Flush Desired Amount Dirty page//determine the most appropriate number of dirty pages to refresh by judging the speed at which the redo log is generated        }        if(no user Activetuy) {Gotobackground Loop}} if(Last_ten_second_ios < innodb_io_capacity) {//if the number of disk IO times in the past 10 is less than the set innodb_io_capacity value (by default)         DoBuffer Pool Flush -%innodb_io_capacity Dirty Page} DoMerge5% innodb_io_capacity Insert Buffer//Merge Insert buffer is innodb_io_capacity 5% (10) (always)     Dolog buffer flush to dish DoFlush Purgeif(Buf_get_modified_ratio_pct > -%) {         DoBuffer Pool Flush -%innodb_io_capacity Dirty Page}Else {         DoBuffer Pool FlushTen%innodb_io_capacity Dirty Page}Gotoloop Backgroud Loop://Background Loop     DoFull purge//Delete useless undo page (always)     DoMerger5% innodb_io_capacity Insert Buffer//Merge Insert buffer is innodb_io_capacity 5% (10) (always)    ifNot idle://if it is not idle, jump back to the main loop, and if idle, jump into the flush loop    GotoLoop//Skip to main loop    Else:        GotoFlush Loop flush loop://Refresh Loop     Dobuf_get_modified_ratio_pct Pool Flush -% innodb_io_capacity Dirty page//Refresh 200 dirty pages to disk    if(buf_get_modified_ratio_pct > innodb_max_dirty_pages_pct)//if the dirty page ratio in the buffer pool is greater than the value of innodb_max_dirty_pages_pct (default 75%)        GotoFlush Loop//skip to refresh cycle, refresh dirty pages continuously until eligible        GotoSuspend loop//after completing the task of refreshing the dirty page, jump into the suspend loopsuspend Loop:suspend_thread ()//master thread hangs, waits for event to occurWaitingEvent    GotoLoop;}

Third, innodb1.2.x version of the master Thread

if  is idle) {    srv_master_do_idle_tasks ();     // operates every 10 seconds Else {    srv_master_do_active_tasks ();     // operations per second }

InnoDB Architecture (iv) how Master thread works