InnoDB Cache Correlation

The InnoDB storage engine is disk-based and manages the records in it as a page. In a database system, buffer pool technology is usually used to improve the overall performance of the database due to the gap between CPU speed and disk speed.

1. Innodb_buffer_pool

The buffer pool is simply a piece of memory area. The types of data pages cached in the buffer pool are: index pages, data pages, undo pages, insert buffers, adaptive hash indexes, lock information for INNODB storage, data dictionary information, and so on. It is not easy to think that the buffer pool is just caching index pages and data pages, which are only a large part of the buffer pool.

To read a page in a database, first the page read from disk in the buffer pool, the next time the same page is read again, first determine whether the page is in the buffer pool. If it is called, the page is hit in the buffer pool and read directly to the page. Otherwise, read the pages on the disk.

Root@rac3Mysql>Show Global Status like 'innodb_buffer_pool_%';+---------------------------------------+--------+|Variable_name|Value|+---------------------------------------+--------+|Innodb_buffer_pool_pages_data| 1118 ||Innodb_buffer_pool_pages_dirty| 0 ||Innodb_buffer_pool_pages_flushed| 1950 ||Innodb_buffer_pool_pages_free| 129951 ||Innodb_buffer_pool_pages_misc| 3 ||Innodb_buffer_pool_pages_total| 131072 ||Innodb_buffer_pool_read_ahead_rnd| 0 ||Innodb_buffer_pool_read_ahead| 311 ||innodb_buffer_pool_read_ahead_evicted| 0 ||Innodb_buffer_pool_read_requests| 202858 ||Innodb_buffer_pool_reads| 756 ||Innodb_buffer_pool_wait_free| 0 ||Innodb_buffer_pool_write_requests| 43825 |+---------------------------------------+--------+ -Rowsinch Set(0.00Sec

From the above values we can see a total of 131072 pages, and 129951 is the Free State, only 1118 page has data, read request 202,858 times, 756 times the requested data is not in the buffer pool, That is to say that there are 756 reads of the data by reading the physical disk, so it is easy to conclude that the read hit rate for the Innodb Buffer Pool is about: (202858-756)/202858 * 100%.

Innodb modifies data in the same way that it modifies the data in the buffer pool, rather than synchronizing the modified data in the buffer pool to disk at the time a transaction is committed, but through another common means of supporting a transactional database system, Logs the modification information to the appropriate transaction log.

The data in the buffer pool modified by our application is random, with each modification being one or a few data pages, and the data pages that are modified several times are rarely contiguous. If we synchronize the buffer pool data to disk after each modification, the disk will only be busy with frequent random read and write operations. The transaction log at the beginning of the creation is the continuous physical space requested, and each write is immediately followed by the previous log data in the order of the subsequent write, basically is a sequential write process. Therefore, the write operation of the log is much faster than synchronizing the modified data in the buffer pool.

2. Redo Log_buffer

The transaction log itself also has buffer, that is, redo Log_buffer, each write to the transaction log is not written directly to the file, it is also temporarily written to Redo Log_buffer, and then a certain event triggered before synchronization to the file.

The size of the transaction log file is very much related to the overall IO performance of the Innodb. Theoretically, the larger the log file, the less refreshing the buffer pool needs to do, and the higher the performance. However, we can not overlook another thing, that is, when the system crash after the recovery.

every modification to the data and indexes in the database and the transaction information related to the modification are recorded in InnoDB. It also records the system each time checkpoint and log sequence number (log sequence numbers). Assuming that MySQL is crash at some point, it is clear that all data in the buffer pool will be lost, including data that has been modified and has not been refreshed to the data file. Do we let this data be lost? Of course not, when MySQL starts again from crash, InnoDB will find the last checkpoint log by comparing the checkpoint information recorded in the transaction log with the checkpoint information in each data file. Sequence number, and then through the change record recorded in the transaction log, reapply all changes from the last checkpoint before crash, synchronizing all data files to a consistent state, so that all data lost due to the system crash is retrieved. Of course, the change data that was not synchronized to the log file in log buffer can no longer be retrieved. The longer the system crash time from the last checkpoint, the longer it will take to recover. The larger the log file, the lower the checkpoint frequency of the InnoDB, and the more likely it is that it will naturally experience long-time recovery.

2.1 Checkpoint

In the InnoDB storage engine, there are several scenarios where the fuzzy Checkpoint can occur:

(1) Master Thread Checkpoint

For checkpoint that occurs in master thread, a percentage of the page-back disk is flushed from the dirty pages list of the buffer pool at almost every second or every 10 seconds. This process is asynchronous, where the InnoDB storage engine can perform other operations, and the user query thread does not block.

(2) Flush_lru_list Checkpoint

The InnoDB storage engine needs to ensure that almost 100 free pages are available for use in the LRU list. Without 100 free pages, the InnoDB storage engine removes the page at the end of the LRU list. If there are dirty pages in these pages, then you need to checkpoint. These pages are from the LRU list and are therefore called Flush_lru_list Checkpoint.

(3) Async/sync Flush Checkpoint

The Async/sync Flush checkpoint is designed to ensure that the redo log uses the availability of the loop.

(4) Dirty Page too much Checkpoint

the number of dirty pages is too large, causing the InnoDB storage engine to force checkpoint. Can be controlled by the parameter innodb_max_dirty_pages_pct.

Root@rac3Mysql>Show variables like 'innodb_max_dirty_pages_pct'\g*************************** 1. Row***************************Variable_name:innodb_max_dirty_pages_pctvalue: -1Rowinch Set(0.00Sec

The value of innodb_max_dirty_pages_pct is 85, which indicates that when the number of dirty pages in the buffer pool occupies 85%, checkpoint is forced to flush part of the dirty page to disk.

2.2 Innodb_flush_log_at_trx_commit

The Innodb_flush_log_at_trx_commit parameter is used to control the transaction log flush to disk policy.

The default innodb_flush_log_at_trx_commit=1, which means that each time a transaction commits, the log buffer is flushed to the file system, and the "flush" operation of the file system is called to flush the cache to disk. In this case, the database requirements for IO is very high, if the underlying hardware provides poor IOPS, then the MySQL database concurrency will soon be due to hardware IO problems and can not be improved. in order to improve efficiency, ensure concurrency, sacrificing a certain degree of data consistency. The innodb_flush_log_at_trx_commit can also be set to 0 and 2.

Innodb_flush_log_at_trx_commit=0, when committing a transaction does not write log buffer to disk, but waits for the main thread to refresh every second.

innodb_flush_log_at_trx_commit=2, the transaction log is written to the redo log file when the transaction commits, but only to the file system's cache, not fsync operations. In this setting, when the MySQL database goes down and the operating system does not go down, it does not cause the loss of the transaction.