A brief introduction to MySQL InnoDB storage engine

InnoDB is a transaction-safe storage engine designed to draw on many Oracle architecture ideas, and in general, in OLTP applications, InnoDB should be the first storage engine for the core application table. InnoDB was developed by a third party Innobase Oy company and is now being acquired by Oracle, the founder of Heikki Tuuri, the Finnish Helsinki people, and the famous Linux founder Linus is an alumnus.

InnoDB System Architecture

Above is a sketch of InnoDB, in simple terms, InnoDB is composed of a series of background threads and a large chunk of memory.

Background threads

By default, there are 7 InnoDB background threads-4 io thread, 1 master thread, 1 lock monitor thread, one error monitor thread

Memory

The InnoDB memory consists of a buffer pool, a redo log buffer pool (redo log buffer), and an additional memory pool (additional memory pool), as shown in the following illustration:

The buffer pool occupies the largest chunk of memory, which is used to cache the respective data, and the data files are read to the buffer pool by page (16K per page), and the cached data is kept by the least recent algorithm (LRU).

Buffer pool buffer data types are: Data page, index page, insert buffer, Adaptive Hash Index, lock information, data dictionary information, such as data pages and index pages account for most of the memory.

Log buffering places the redo log information in this buffer first, and then flushes it to the Redo log file at a certain frequency (the default is 1s).