Shulou(Shulou.com)06/01 Report--

InnoDB is the first engine in the MySQL storage engine to fully support ACID transactions, which was previously developed by Innobase OY and later acquired by Oracle. InnoDB is the most widely used storage engine in MySQL database, which has been adopted by many large companies, such as Google, Facebook, YouTube and so on. If you use MySQL database service, without special requirements, InnoDB is the best choice.

1.InnoDB architecture

An in-depth understanding of InnoDB starts with understanding the architecture of InnoDB, as shown in the following figure:

The InnoDB storage engine has multiple memory blocks, which can be considered to form a large memory pool and are responsible for the following tasks:

Maintain all processes / threads, multiple internal data structures to be accessed

Cache the data on the disk, read it easily and quickly, and cache it here before the data of the disk file is modified

Redo log (redo log) buffering (to put it simply, redo log records the data update log corresponding to the transaction, which is used to reduce the disk IO generated by each data change. The log is regularly refreshed to the disk by the worker thread. When the database crashes, the data can be refreshed to the disk before the data is refreshed. If you restart the operation, you can extract and refresh the disk from redo log. The details of redo log will be described in more detail in the following relevant chapters).

The main function of the background thread is to refresh the data in the memory pool, to ensure that the memory in the buffer pool is buffered by the latest data, in addition to flushing the modified data files to disk, and to ensure that InnoDB can return to normal operation in case of database exception.

two。 Background thread

Master thread

Master thread is a very core background thread, which is mainly responsible for asynchronously refreshing the data in the buffer pool to disk to ensure data consistency, including dirty page refresh, merge insertion buffer, undo page recovery and so on.

Io thread

AIO (Async IO) is widely used in the InnoDB engine to handle write IO requests, which can greatly improve database performance. The main job of io thread is to handle the callbacks of these IO requests. You can use the command show engine innodb status\ G to observe io threa.

The picture is from Ruijiang Cloud, Guangdong.

As you can see from the figure above, there are 1 insert buffer thread, 1 log thread, 4 read threads, and 4 write threads.

Purge thread

After the transaction is committed, the undo log it uses (for the rollback operation after the transaction commit fails) may no longer be needed, so the purge thread is needed to recycle the undo page. Prior to InnoDB 1.1, purge thread was done in master thread; from InnoDB 1.1, purge operations were independent to dedicated threads to reduce master thread pressure, improve cpu utilization, and improve engine performance; and since InnoDB 1.2, multiple purge thread are supported to further speed up undo page recycling.

Page cleaner thread

Introduced in InnoDB 1.2.x version, its function is to refresh dirty pages in a separate thread, but also to reduce the pressure on master thread and improve performance.

3. Memory

Buffer pool

The data storage of InnoDB engine is based on disk, and records are recorded on disk according to a certain format, but because the speed of CPU is different from that of disk, memory buffering technology is introduced to improve the performance of database. To put it simply, load the disk contents of the data to be read into the memory buffer area first, determine whether the same data is cached by the buffer next time, and read the contents from the buffer next time; similarly, when you need to modify the data, you will not directly modify the corresponding data on the disk, but first modify the buffer area, and then flush the updated data to the disk through a mechanism called checkpoint (more on this later).

Therefore, the setting of the buffer pool becomes particularly important for the InnoDB engine, which can be set through the innodb_buffer_pool_size parameter.

Although the purpose of the buffer pool is to buffer data, the data types stored in the buffer pool are not only database records, but also the following types: index pages, data pages, undo pages, insert buffers (insert buffer), adaptive hash indexes, lock information stored by InnoDB (lock info), data dictionary information, and so on.

Starting with InnoDB version 1.0.x, multiple buffer pool instances are allowed, and each page is evenly allocated to different buffer pool instances according to the hash value, which increases the concurrent processing of the database. The specific multi-instance buffer pool is not described in detail in this article, and those who are interested can continue to learn more about it through other ways.

Memory management LRU List, Free List, Flush List

Generally speaking, memory buffers in a database are managed by the LRU (Latest Recent Used, least recently used) algorithm. That is, frequently used pages are at the front of the LRU list, while the least used pages are at the end of the LRU. When the buffer pool cannot hold the newly read page, the page at the end of the LRU list is released first.

In the InnoDB engine, the default size of the page in the buffer pool is 16KB, which is also managed by the LRU algorithm. With a slight difference, the engine optimizes the traditional LRU algorithm. When a new page is read, it is not inserted directly into the head of the LRU list, but is inserted into the midpoint position of the LRU list, which can be controlled by the parameter innodb_old_blocks_pct. By default, the value is 37. Represents the 37% position inserted at the end of the LRU list. The algorithm of LRU under InnoDB is not explained in detail in this article, and interested readers can consult the relevant materials on their own.

After introducing LRU List, the following describes that the Free List,Free list saves free pages. When the engine needs to page from the buffer pool, it first looks up whether there are free pages from the Free list, and if so, retrieve the pages from Free List and delete them into LRU List. Similarly, when the pages of LRU List need to be eliminated, they will be rejoined to Free List.

After the page in the LRU list is modified, the page is a dirty page (dirty page), that is, the page in the buffer pool is inconsistent with the page data on disk. At this time, the database will refresh the dirty page back to disk through the checkpoint mechanism (described in the next section on checkpoint), and Flush List is a list of these dirty pages.

Redo log redo log

The memory area of the InnoDB storage engine has a redo log buffer (redo log buffer) in addition to the buffer pool. The InnoDB storage engine first puts the log information into this buffer, and then flushes it to the redo log file at a certain frequency. The later "documents" chapter will describe the respective files and their functions in detail, including redo log, which will not be explained too much here.

Additional memory pool

Additional memory pools record a variety of data structures that need to be used within the database, such as recording buffer pool information, recording LRU, locks, etc., which are often ignored.

4.checkpoint

As described in the previous summary, the buffer pool is designed to coordinate the gap between CPU speed and disk speed. All operations of the page are first done in the buffer pool, such as a update, delete, insert statement that changes the records in the page, then the page is dirty and the database needs to update the data of the page to disk.

If every change is updated to disk immediately, it will be very efficient and lose the meaning of the buffer pool. however, if it is not refreshed for a long time, it will lead to data inconsistency when the database crashes can not be updated to disk in time.

In order to avoid these problems, the current transactional databases generally adopt the strategy of wirte ahead log, that is, when a transaction commits, redo log is written first, and then the page is modified. If downtime occurs during this period, the data can be recovered through redo log.

Checkpoint technology is used to record redo log. Those log have been flushed to disk, and those log have not been refreshed yet. In InnoDB engine, the checkpoint tag is marked by a LSN (log sequence number). LSN is an 8-byte number. The log before LSN has been flushed to disk, and then it has not been refreshed, so when the database is restored from downtime, you only need to refresh the redo log after LSN.

Key features of 5.InnoDB

Key features of the InnoDB storage engine include:

Insert buffer (insert buffer)

Write twice (double write)

Adaptive hash indexing (adaptive hash index)

Asynchronous io (async io)

Refresh the adjacency page (flush neighbor page)

Below only select some of the more understandable features to briefly introduce, other more complex, such as insertion buffering, adaptive hash retention for readers to do detailed research.

Write twice

As the name implies, it means to write twice. In the InnoDB engine, there is a shared tablespace, which can store some shared data, such as indexes. When the database needs to refresh the pages of a buffer pool to disk, it first writes the page data to the shared tablespace, and then writes it to disk. When writing to disk, it finds that the exception leads to data loss. At this point, the data is restored from the shared tablespace and rewritten to disk.

Asynchronous io

To put it simply, asynchronous io makes io operations asynchronous. For example, if you need to read and write to the disk, initiate an io operation first and wait for the disk to read and write before notifying the upper layer for processing. Another advantage of asynchronous io is that some io operations can be merged and optimized, for example, requests for consecutive pages can be merged into a single request.

Refresh adjacency pages

This is also easy to understand, that is, when a dirty page is refreshed, the InnoDB storage engine will detect all pages in the area where the page is located. If all pages are dirty, refresh them together. You can merge multiple write operations with asynchronous io to improve efficiency.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.