InnoDB cache problems

The InnoDB cache-related InnoDB storage engine is based on disk storage and manages records on pages. In the database system, due to the gap between the CPU speed and the disk speed, the buffer pool technology is usually used to improve the overall performance of the database.

1. Innodb_buffer_pool

A buffer pool is simply a memory area. Data page types cached in the buffer pool include: index page, data page, undo page, insert buffer, adaptive hash index, InnoDB storage lock information, and data dictionary information. You cannot simply think that the buffer pool only caches index pages and data pages. they only occupy a large part of the buffer pool.

To read pages in a database, first store the pages read from the disk in the buffer pool. when you read the same page next time, first determine whether the page is in the buffer pool. If yes, the page is hit in the buffer pool, and the page is read directly. Otherwise, read the page in the disk.

root@rac3 mysql> 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 |+---------------------------------------+--------+13 rows in set (0.00 sec)

From the above values, we can see that there are a total of 131072 pages, and 129951 are Free. only 1118 pages have data and 202858 read requests, among them, 756 of the requested data are not in the buffer pool, that is, 756 are read by reading the data from the physical disk, therefore, we can easily conclude that the Read hit rate of the Innodb Buffer Pool is: (202858-756)/202858 * 100%.

When Innodb modifies data, it only modifies the data in the buffer pool. it does not synchronize the modified data in the buffer pool to the disk when a transaction is committed, instead, it records the modification information to the corresponding transaction logs through another common means of database systems that support transactions.

The data in the buffer pool modified by our application is random. each modification is one or a few data pages, and the data pages modified several times are rarely consecutive. If we synchronize the data in the buffer pool to the disk after each modification, the disk can only be busy with frequent instant read/write operations. At the beginning of the transaction log creation, it is a continuous physical space applied for, and each write is followed by the previous log data sequence, which is basically a sequential write process. Therefore, writing logs is much faster than synchronizing the modified data in the buffer pool.

2. redo log_buffer

The transaction log itself also has a buffer, that is, the redo log_buffer. each write of the transaction log is not directly written to the file, but also temporarily written to the redo log_buffer, the file will be synchronized to the file only when a certain event is triggered.

The size of the transaction log file is closely related to the overall IO performance of Innodb. Theoretically, the larger the log file, the less refresh actions required by the Buffer Pool, and the higher the performance. However, we cannot ignore another thing, that is, the recovery after the system Crash.

Innodb records every modification made to the data and indexes in the database and transaction information related to the modification. The checkpoint and log sequence number (log serial number) of the system are also recorded ). Assuming that MySQL Crash is completed at a certain time point, it is clear that data in all buffer pools will be lost, including the data that has been modified and cannot be refreshed to the data file. Can we just let this data be lost? Of course not. when MySQL is started again after the Crash, Innodb compares the checkpoint information recorded in the transaction log with the checkpoint information in each data file, find the log sequence number corresponding to the last checkpoint, and then reapply all the changes recorded in the transaction log from the last checkpoint before the Crash, synchronize all data files to the same state, so that all data loss caused by system Crash is recovered. Of course, the changed data in log buffer that has not been synchronized to the log file can no longer be retrieved. The longer the system Crash time is from the last checkpoint, the longer the recovery time is required. The larger the log file, the lower the checkpoint frequency of Innodb, and the higher the possibility of long-term recovery.

2.1 checkpoint

In the InnoDB storage engine, Fuzzy Checkpoint may occur in the following situations:

(1) Master Thread Checkpoint

For checkpoints in the Master Thread, a certain percentage of pages are refreshed from the dirty page list of the buffer pool every second or every 10 seconds. This process is asynchronous, that is, the InnoDB storage engine can perform other operations at this time, and the user query thread will not be blocked.

(2) FLUSH_LRU_LIST Checkpoint

The InnoDB storage engine must ensure that there are about 100 idle pages in the LRU list. If there are no 100 idle pages, the InnoDB storage engine will remove the pages at the end of the LRU list. If these pages contain dirty pages, you need to perform checkpoint. These pages are from the LRU list, so they are called FLUSH_LRU_LIST Checkpoint.

(3) Async/Sync Flush Checkpoint

Async/Sync Flush Checkpoint is used to ensure the cyclic availability of redo logs.

(4) Dirty Page too much Checkpoint

The number of dirty pages is too large, causing the InnoDB storage engine to force Checkpoint. It can be controlled by the innodb_max_dirty_pages_pct parameter.

root@rac3 mysql> show variables like 'innodb_max_dirty_pages_pct'/G*************************** 1. row ***************************Variable_name: innodb_max_dirty_pages_pctValue: 851 row in set (0.00 sec)

The innodb_max_dirty_pages_pct value is 85, which indicates that when the number of dirty pages in the buffer pool occupies 85%, the checkpoint is enforced to refresh some dirty pages to the disk.

2.2 innodb_flush_log_at_trx_commit

The innodb_flush_log_at_trx_commit parameter controls the policy for refreshing transaction logs to the disk.

By default, innodb_flush_log_at_trx_commit = 1 indicates that the log buffer is flushed to the file system every time a transaction is committed, and the file system's "flush" operation is called to refresh the cache to the disk. In this way, the database requires a very high IO. if the underlying hardware provides poor IOPS, the concurrency of the MySQL database will soon be unable to be improved due to hardware IO problems. To improve efficiency and ensure concurrency, data consistency is sacrificed. Innodb_flush_log_at_trx_commit can also be set to 0 and 2.

When innodb_flush_log_at_trx_commit = 0, the commit transaction does not write the log buffer to the disk, but waits for the master thread to refresh every second.

When innodb_flush_log_at_trx_commit = 2, the transaction logs are written to the redo log file when the transaction is committed, but only to the file system cache, without fsync. In this setting, when the MySQL database goes down and the operating system does not go down, transactions will not be lost.