First, the architecture of the InnoDB storage engine is simply presented in a single diagram.
As can be seen, the InnoDB storage engine has multiple blocks of memory that make up a large pool of memory, primarily responsible for the following tasks:
Maintain multiple internal data structures that all processes/threads need access to
Cache data on disk for quick reading and caching before disk file modification
Redo log (redo log) buffering
The primary role of the background thread is to refresh the data in the memory pool, ensure that the memory in the buffer pool is up-to-date, refresh the modified data file to the disk file, and ensure that the InnoDB can return to normal operation when the database is abnormal.
Background thread
InnoDB uses a multithreaded model, with several different threads in the background responsible for handling different tasks
1. Master Thread
This is the core of a thread, mainly responsible for the buffer pool data asynchronously flushed to disk, to ensure consistency of data, including dirty page refresh, merge insert buffer, UNDO page recycling.
2. IO Thread
Asynchronous IO is used extensively in the InnoDB storage engine to handle write IO requests, and IO Thread is primarily responsible for callbacks for these IO requests.
The IO Thread in InnoDB can be observed by command:
Mysql>Show engine InnoDB status\g*************************** 1. Row***************************type:innodb name:status:===================================== .- -- A xx: -: -7f4a37451700 INNODB MONITOR OUTPUT=====================================--------FILEI/O--------I/O thread0State:waiting forCompleted AIO requests (Insertbuffer thread) I/O thread1State:waiting forCompleted AIO requests (Logthread) I/O thread2State:waiting forCompleted AIO requests (Readthread) I/O thread3State:waiting forCompleted AIO requests (Readthread) I/O thread4State:waiting forCompleted AIO requests (Readthread) I/O thread5State:waiting forCompleted AIO requests (Readthread) I/O thread6State:waiting forcompleted AIO requests (write thread) I/O thread7State:waiting forcompleted AIO requests (write thread) I/O thread8State:waiting forcompleted AIO requests (write thread) I/O thread9State:waiting forcompleted AIO requests (write thread) Pending normal AIO reads:0 [0, 0, 0, 0], Aio writes:0 [0, 0, 0, 0], Ibuf Aio reads:0,LogI/O's:0, sync I/O'S0Pending Flushes (fsync)Log:0; Buffer pool:0188OSfileReads27180OSfileWrites,26031OS Fsyncs0.00Reads/S0 avgbytes/Read,0.00Writes/S0.00Fsyncs/S
As you can see, InnoDB has 10 IO Thread, 4 write, 4 read, 1 insert buffer, and one log thread, respectively.
3. Perge Thread
After the transaction is committed, undo log may no longer be required, so the Purge Thread is required to reclaim the undo page that has been used than the allocation. InnoDB supports multiple Purge thread, which speeds up the recovery of the undo page InnoDB engine defaults to 1 Purge thread:
Mysql>SHOW VARIABLES like"Innodb_purge_threads";+----------------------+-------+|Variable_name|Value|+----------------------+-------+|Innodb_purge_threads| 1 |+----------------------+-------+1Rowinch Set(0.00Sec
4. Page Cleaner Thread
Page Cleaner thread is a new introduction, which is done by putting the refresh of dirty pages in a previous version into a separate thread, which relieves the work of the Master thread and the blocking of the user's query thread
Memory 1. Buffer pool
The InnoDB storage engine is disk-based, where records are managed by page, and because of the gap between CPU speed and disk speed, the InnoDB engine uses buffer pool technology to improve the overall performance of the database.
The buffer pool is simply a piece of memory area. In the database to read the operation of the page, first read from disk in the buffer pool, the next time the same page read, the first to determine whether the page is in the buffer pool, if the page is said to be hit in the buffer pool, read the page directly. Otherwise, read the page on the disk.
For modifications to the page in the database, the page is first modified in the buffer pool, then flushed to disk at a certain frequency , and not refreshed back to disk every time the page changes.
The size of the buffer pool directly affects the overall performance of the database, and for the InnoDB storage engine, the buffer pool configuration is set by parameters innodb_buffer_pool_size
. The following shows a MySQL database configuration on a native virtual machine:
Mysql>SHOW VARIABLES like 'innodb_buffer_pool_size';+-------------------------+------------+|Variable_name|Value|+-------------------------+------------+|Innodb_buffer_pool_size| 2147483648 |+-------------------------+------------+1Rowinch Set(0.00Sec
The data page types cached in the buffer pool are: Index page, data page, undo page, insert buffer, Adaptive Hash index, InnoDB lock information, data dictionary information, and so on. index pages and data pages represent a large portion of the buffer pool. Displays the structure of the total memory of the InnoDB storage engine.
2. Redo the log buffer
The InnoDB storage engine first places the redo log information in this buffer and then flushes it to the Redo log file at a certain frequency. Redo log files generally do not need to be set up very large because the contents of the redo log buffer are flushed to the disk's redo log file in the following three scenarios.
Master Thread Flushes the redo log buffer to the redo log file every second
Redo log buffers are flushed to redo log files when each thing commits
Redo log buffering flushes to redo log files when redo log buffer has less than 1/2 remaining space
3. Additional Memory pools
In the InnoDB storage engine, when allocating memory for some data structures themselves, applications need to be made from an additional pool of memory. For example, a buffer pool is allocated, but the framebuffer in each buffer pool has a corresponding buffer control object that records some information such as LRU, lock, wait, etc. The memory of this object needs to be requested from an additional pool of memory.
InnoDB Storage Engine Introduction-(1) InnoDB Storage Engine architecture