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

This article introduces the relevant knowledge of "memory structure and characteristics of InnoDB in MySQL". In the operation process of actual cases, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

buffer pool

 The InnoDB storage engine is based on disk storage and manages records in pages. But because of the gap between CPU speed and disk speed, disk-based database systems often use buffer pool records to improve the overall performance of the database.

 Read operation in the database, first read from the disk to the page in the buffer pool, the next time read the same page, first determine whether the page in the buffer pool. If it is in the buffer pool, the page is said to be hit in the buffer pool and the page is read directly. Otherwise, read the page on disk.

 For page modification operations in the database, the pages in the buffer pool are first modified, and then flushed to disk at a certain frequency. The flushing of pages back to disk from the buffer pool is not triggered every time a page is updated, but rather through a mechanism called CheckPoint.

 Therefore, the size of the buffer pool directly affects the overall performance of the database and can be set by configuring the parameter innodb_buffer_pool_size.

 Specifically, the types of data pages cached in the buffer pool include index pages, data pages, undo pages, insert buffer, adaptive hash index, lock info stored in InnoDB, and data dictionary information.

 As you can see in the architecture diagram, the memory area of the InnoDB storage engine has redo log buffers and extra memory pools in addition to buffer pools. The InnoDB storage engine first places redo log information into this buffer, and then flushes it into redo log files at a certain frequency. The redo log buffer generally does not need to be set very large, and this value can be controlled by the configuration parameter innodb_log_buffer_size.

Data and index pages

 Page is the most basic structure of Innodb storage and the smallest unit of Innodb disk management. All contents related to database are stored in Page structure. Pages are divided into several types, data pages and index pages are the two most important types.

Insert Buffer

 As we all know, when performing insert operations on InnoDB engines, you generally need to insert in primary key order to achieve high insert performance. When there are non-clustered and non-unique indexes in a table, the data pages are stored in order according to the primary key during insertion, but the insertion of non-clustered index leaf nodes is no longer sequential, and discrete access to non-clustered index pages is required. The performance of the insertion operation is degraded due to the existence of random reads.

 InnoDB designed Insert Buffer for this purpose to optimize insertion. For the insert or update operation of non-clustered index, it is not directly inserted into the index page every time, but first determine whether the inserted non-clustered index is in the buffer pool. If it is, insert it directly; if it is not, put it into an Insert Buffer first. It seems that the database has already found leaf nodes in this non-clustered index, but in fact there is no leaf node, and it is stored in another location. Then insert Buffer and non-clustered index page child node merge operation with a certain frequency and situation. It is often possible to combine multiple inserts into a single operation, which greatly improves insert performance for non-clustered indexes.

Double Write

 If Insert Buffer gives InnoDB storage engines a performance boost, Double Write gives InnoDB storage engines reliability of data pages.

doublewrite diagram

 As shown in the figure above, Double Write consists of two parts, one is a double write buffer in memory with a size of 2MB, and the other is a contiguous 128 pages on the physical disk that share the table space, also with a size of 2MB. When flushing dirty pages in the buffer pool, instead of directly writing to the disk, the dirty pages are copied to this area of memory through the memcpy function, and then written to the physical disk sharing the table space twice through the doublewrite buffer, 1MB each time, and then the fsync function is immediately called to synchronize the disk, avoiding the problems caused by buffer writing of the operating system. After writing the doublewrite page, write the pages in the doublewirite buffer to each table space file.

 If the operating system crashes while writing a page to disk, during recovery, the InnoDB storage engine can find a copy of the page from doublewrite in the shared table space, copy it to the table space file, and apply the redo log.

Redo Log Buffer

 When the version of a page in the buffer pool is newer than disk, the database needs to flush the new version of the page from the buffer pool to disk. However, if a page is flushed every time a change is sent, then the performance development is very large, so InnoDB adopts the Write Ahead Log policy, that is, when the transaction is committed, the redo log is written first, and then the dirty page is written to disk at an appropriate time. If an outage occurs resulting in data loss, data recovery is performed through redo logs.

InnoDB Data Writing Diagram

 The InnoDB storage engine first places redo log information into the redo log buffer and then flushes it to the redo log file at a certain frequency. The redo log buffer generally does not need to be large because it is flushed to the log file every second. It can be controlled through the configuration parameter innodb_log_buffer_size, which defaults to 8MB.

 In addition to the per-second flush mechanism, the redo log buffer is flushed to the log every time a transaction commits. InnoDB is a storage engine for transactions. It implements transaction persistence through the Force Log at Commit mechanism. That is, when a transaction is committed, all logs of the transaction must be written to the redo log file for persistence, and then the commit operation of the transaction is completed. InnoDB's write mechanism is roughly as shown in the figure below.

 To ensure that each log is written to the redo log file, after each redo log buffer is written to the redo log, an fsync operation must be invoked to actually write the buffered file from the file system cache to disk.

 You can control the policy of redo log flushing to disk by innodb_flush_log_at_trx_commit. The default value of this parameter is 1, which means that the transaction commit must perform an fsync operation. It can also be set to 0 and 2. 0 means that the transaction commits without writing to the redo log, which is done only in the main thread, and 2 means that the transaction commits with writing to the redo log, but only to the file system cache, without fsync. As you can see, a setting of 0 provides the highest performance, but loses transaction consistency.

Adaptive Hash Index

 InnoDB builds hash indexes for hot pages based on frequency and pattern of visits to improve query efficiency. The InnoDB storage engine monitors queries against index pages on the table and builds hash indexes if it observes that building hash indexes can bring speed improvements, so it is called adaptive hash indexes.

 Adaptive hash indexes are built from B+ tree pages of the buffer pool, so they are built quickly and do not require a hash index for the entire data table. There is a requirement that the continuous access pattern to this page must be the same, that is, the query conditions (WHERE) must be exactly the same, and must be continuous.

Lock info

 We all know that the InnoDB storage engine locks table data at the row level. However, InnoDB also uses locks in many other places within the database, allowing concurrent access to a variety of different resources. Database systems use locks to support concurrent access to shared resources and to provide data integrity and consistency. We'll learn more about locks later.

Data Dictionary Information

 InnoDB has its own table cache, which can be called a table definition cache or data dictionary. When InnoDB opens a table, it adds a corresponding object to the data dictionary.

 A data dictionary is a collection of meta-information about data, library objects, table objects, etc. in a database. In MySQL, data dictionary information includes table structure, database name or table name, field data type, view, index, table field information, storage procedure, trigger and so on. The MySQL INFORMATION_SCHEMA library provides access to database metadata, statistics, and information about MySQL servers (e.g. database or table names, field data types, and access permissions). The information stored in this repository can also be referred to as MySQL's data dictionary.

"MySQL InnoDB memory structure and characteristics" content introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

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.