[MySQL] about the InnoDB Storage Engine

[MySQL] the InnoDB Storage engine InnoDB is a transaction-safe storage engine, which draws 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. The above figure shows the InnoDB architecture. In simple terms, InnoDB is composed of a series of background threads and a large block of memory. By default, there are 7 Background threads in InnoDB: 4 IO threads, 1 master thread, and 1 lock monitor thread, an error monitor thread memory InnoDB memory consists of the following parts: 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 the latest least-used algorithm (LRU) is used) cache data is retained. 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. The main work of the Master background thread InnoDB is completed 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: [plain] 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 seconds goto loop;} operations per second include refreshing the log buffer (always) merge insert buffer (possibly) to refresh up to 100 dirty data pages (possibly) if there is no current user activity, switch to background loop (possibly) similar to Oracle, even if the transaction is not committed, refresh the redo log buffer every second. Operations every 10 seconds include merging up to five insert buffers (always) refreshing log buffers (always) refreshing 100 or 10 dirty pages to the disk (always) generate a checkpoint (always) to delete useless Undo pages (always) and then look at the background loop. If there is no user activity or the database is closed, switch to this loop to perform the following operations: delete useless undo pages (always) Merge 20 insert buffers (always) jump back to main loop (always) constantly refresh 100 pages until the conditions are met and jump to flush loop (possibly) if there is nothing to do in the flush loop, switch to the suspend loop and suspend the master thread.