[MySQL] about the InnoDB Storage Engine

InnoDB is a transaction-safe storage engine. It is designed based on a lot of Oracle architecture ideas. Generally, in OLTP applications, InnoDB should be the first storage engine for core application tables. InnoDB is developed by a third-party InnobaseOy company and has been acquired by Oracle. Its founder is HeikkiTuuri, a Finnish Helsinki, and the founding of the famous Linux

InnoDB is a transaction-safe storage engine. It is designed based on a lot of Oracle architecture ideas. Generally, in OLTP applications, InnoDB should be the first storage engine for core application tables. InnoDB is developed by a third-party Innobase Oy Company and has been acquired by Oracle. Its founder is Heikki Tuuri, Helsinki, Finland, and the founding of the famous Linux

InnoDB is a transaction-safe storage engine. It is designed based on a lot of Oracle architecture ideas. Generally, in OLTP applications, InnoDB should be the first storage engine for core application tables. InnoDB is developed by a third-party Innobase Oy Company and has been acquired by Oracle. The founder is Heikki Tuuri, a Finnish Helsinki, and the famous Linux founder Linus is an alumnus.

InnoDB Architecture

The above is a simple figure of InnoDB. In short, InnoDB is composed of a series of background threads and a large block of memory.

Background thread

By default, InnoDB has 7 Background threads: 4 IO threads, 1 master thread, 1 lock monitor thread, and 1 error monitor thread.

Memory

The memory of InnoDB consists of the buffer pool, redo log buffer, and additional memory pool, as shown in:

The buffer pool occupies the largest block of memory, which is used to cache data. Data Files are read to the buffer pool by PAGE (16 K per page) and cached by the least recently used algorithm (LRU.

Buffer Pool buffer data types include: data page, index page, insert buffer, adaptive hash index, lock information, data dictionary information, and so on. The data page and index page occupy most of the memory.

The log buffer puts the redo log information into this buffer first, and then refreshes it to the redo log file at a certain frequency (1 s by default.

Master background thread

The main work of InnoDB is done in a separate Master thread. Master threads have the highest priority. They are mainly divided into the following cycles: Main loop, background loop, flush loop, and suspend loop ).

Let's take a look at the main loop. below is its pseudocode:

void master_thread() (loop:for (int i =0; i <10; i++){    do thing once per second    sleep 1 second if necessary}do things once per ten secondsgoto loop;}

Operations per second include:

• Refresh log buffer (always)
• Merge insert buffer (possible)
• Refresh up to 100 dirty data pages (possible)
• If no user activity exists, switch to background loop (possible)

Similar to Oracle, the refresh log buffer is Refresh Every second even if the transaction is not committed.

Operations every 10 seconds include:

• Merge up to 5 insert buffers (always)
• Refresh log buffer (always)
• Refresh 100 or 10 dirty pages to the disk (always)
• Generate a checkpoint (always)
• Delete useless Undo pages (always)

Next, let's look at the background loop. If there is no user activity or the database is closed, the system switches to this loop to perform the following operations:

• Delete useless undo pages (always)
• Merge 20 insert buffers (always)
• Jump back to the main loop (always)
• Refresh 100 pages until the page jumps to flush loop (possible)

If there is nothing to do in the flush loop, switch to the suspend loop and suspend the master thread.

Author: u010316792 posted on 12:17:57 Original article link

Read: 107 comments: 0 view comments

Original article address: [MySQL] talking about the InnoDB Storage engine, thanks to the original author for sharing.