MySQL Architecture to Threads: Storage Engine and Thread Model Deep Dive

MySQL 5.7 Storage Engine Optimization

Undo Log Tablespace Improvement

  • Users can flexibly configure the number of Undo tablespaces (through innodb_undo_tablespaces parameter)
  • Supports independent management of Undo space

Temporary Tablespace

  • Stores temporary table data through independent temporary tablespace file (ibtmp1)

Dynamic Buffer Pool Adjustment

  • Supports online size adjustment through SET GLOBAL

MySQL 8.0 Storage Architecture Optimization

InnoDB Tablespace Separation

  • Completely separates data fields and Undo logs from shared tablespace ibdata

Temporary Tablespace Enhancement

  • Supports configuring multiple physical files as temporary tablespace

Doublewrite Buffer Separation

  • Separated from shared tablespace ibdata

InnoDB Thread Model

IO Thread Explained

Read Thread (4 by default)

  • Responsible for reading data pages from disk to buffer pool

Write Thread (4 by default)

  • Flushes dirty pages from buffer pool to disk

Log Thread (1)

  • Responsible for redo log buffer refresh

Insert Buffer Thread (1)

  • Handles Change Buffer merge operations

Configuration Parameters:

innodb_read_io_threads -- Controls read thread count
innodb_write_io_threads -- Controls write thread count

Purge Thread

  • Responsible for cleaning undo logs of committed transactions

Page Cleaner Thread

  • Flushes dirty pages from buffer pool to disk

Master Thread

  • Core scheduling thread, responsible for coordinating other background threads

Operations Executed Every Second:

  1. Log buffer refresh
  2. Write buffer merge
  3. Dirty page refresh

Operations Executed Every 10 Seconds:

  1. Force dirty page refresh
  2. Full write buffer merge
  3. Undo page cleanup