MySQL's InnoDB thread model

• IO Thread
  InnoDB uses a lot of AIO (Async IO Asynchronous IO) for read and write processing, which can greatly improve the capacity of the database.Before InnoDB 1.0, there were 4 IO threads, namely write, read, insert buffer, and log thread. Later versions increased the number of read threads and write threads to 4, for a total of 10 threads.

  • View command: show engine innodb status;
    
  • read thread: Responsible for reading operations, loading data from disk to page.4
  • write thread : Responsible for write operations, flushing cached dirty pages to disk.4
  • log thread : Responsible for flushing log buffer contents to disk.1
  • insert buffer thread: Responsible for flushing the write buffer content to disk.1

• Purge Thread
  After the transaction is committed, the undo log used by it is no longer needed, and the Purge Thread is responsible for recycling the allocated undo pages.

  • View command: show variables like '%innodb_purge_threads%';
    
  • Default Value = 4 means there are 4 threads responsible for recycling undo pages

• Page Claner Thread
  The function is to flush the dirty data to the disk. After the dirty data is flushed to the disk, the corresponding redo log can be overwritten, that is, the data can be synchronized, and theThe purpose of redo log recycling.Will call write thread thread processing.

  • View command: show variables like '%innodb_page_cleaners%';
    
  • Default Value = 1 means 1 thread

• Master Thread
  The master thread is the main thread of InnoDB and is responsible for scheduling other threads with the highest priority.The function is to asynchronously flush the data in the buffer pool to the disk to ensure data consistency.Including: flushing dirty pages (calling page chener thread), undo page recycling (scheduling purge thread), redo log log flushing (calling log thread), combined write buffering (that is, the data in the buffer and the data just queried from the disk)data are merged), etc.There are two main processing inside, one is processing every 1 second and every 10 seconds

  • Operations every 1 second:

    • flush log buffer to disk
    • Merge the write buffer data and decide whether to operate according to the IO read and write pressure
    • Refresh dirty page data to disk, and operate only when the proportion of dirty pages reaches 75% (the default value is 75%)
      • View command: show variables like '%innodb_max_dirty_pages_pct%';
        
      • innodb_max_dirty_pages_pct_lwm indicates that when the proportion of dirty pages in the Buffer Pool is lower than this value, the dirty page pre-flush function can be triggered to gradually control the proportion of dirty pages.The default is 0, which means off
      • The default refresh is 200 pages each time, and the maximum number cannot exceed 2000 pages
      • View command: show variables like '%innodb_io_capacity%'
        

  • Operations every 10 seconds:

    • Flush dirty page data to disk
    • Merge write buffer data
    • Flush log buffer data
    • Delete useless undo pages
      • Delete 300 each time by default
      • View command: show variables like '%innodb_purge_batch_size%'
        

Other chapters ->Jump

end...