InnoDB Storage Engine Introduction-(1) InnoDB Storage Engine architecture

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.

1. Master Thread Flushes the redo log buffer to the redo log file every second

2. Redo log buffers are flushed to redo log files when each thing commits

3. 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