InnoDB Storage engine background thread and Memory Pool

InnoDB fully supports ACID transactions, row lock design, MVCC, consistent non-locked reads, and foreign key-> transaction security, and is suitable for OLTP applications (online transaction processing ).
Mytrix ., inc. store more than 1 TB of data on InnoDB, some other sites process an average of 800 insert/update operations on InnoDB each time-> it proves that InnoDB is a high-performance, high-availability, and high-scalability storage engine.
Transaction ACID:
Atomicity: transaction commit or rollback
Consistency: integrity constraints before and after transactions are not damaged
Isolation: Two transactions do not interfere with each other (that is, one transaction cannot see data in the middle of other transactions)
Durability Persistence: Change persistence (rollback does not occur after commit)

The InnoDB architecture is as follows:

Where:
Background thread, responsible:
Refresh the data in the memory pool;
Refresh the modified data file to the disk file;
Ensure that InnoDB can return to normal operation in case of database exceptions.
By default, there are 7 Background threads:
1 master thread;
Four IO threads (insert buffer, log, read, write)-> you can modify the innodb_file_io_threads parameter in the configuration file. in MySQL and innodb plugin versions, by default, I/O threads are increased to four. read/write threads are represented by innodb_read_io_thread and innodb_write_io_thread respectively;
1 lock monitoring thread;
One error monitoring thread.
Memory Pool, which consists of multiple memory blocks to form a large memory pool and is responsible:
Maintains the data structure of multiple memories to be accessed by all processes/threads;
Cache data on the disk to facilitate fast reading, and cache data before modifying disk file data;
Redo log buffering;
......

The memory pool structure is as follows:

Where:
Buffer pool: includes data page, index page, insert buffer, lock information, adaptive hash index, and data dictionary information;
Redo log buffer Pool: the transaction volume generated per second is within the buffer size;
Additional memory buffer extra memory pool: The Frame buffer in each buffer pool also has corresponding buffer control objects, such as LRU and lock wait.
InnoDB is used to read data files to the buffer pool by PAGE (16 K per page), and then retain the cached data in the buffer pool by least recently used algorithms by LRU. If the file needs to be modified, always first modify the pages in the buffer pool (after modification, the page is dirty), and then flush the dirty pages to the files according to a certain frequency.

Reference: <MySQL technology insider InnoDB Storage engine>

1 2 Next Page