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

Author: Su Pu | [be sure to indicate the original origin and author information of the article and this statement in the form of hyperlink when reprinting]

Address: http://rdc.taobao.com/blog/dba/html/317_innodb-dirty-page-redo-log.html

In InnoDB, dirty page in buffer pool can not only speed up data processing, but also cause data inconsistency (RAM vs DISK). This article describes how dirty page is generated and how InnoDB uses redo log to eliminate data inconsistencies generated by dirty page.

[@ more@] when a transaction (Transaction) needs to modify a record (row), InnoDB needs to read the page of the data from disk to buffer pool. After the transaction is committed, InnoDB modifies the record (row) in page. At this point, the page in buffer pool is different from that in disk. We call the page in buffer pool dirty page. Dirty page waits for flush to disk.

Since dirty page is in Buffer pool, will the data modification in Dirty page be lost if the system is suddenly powered off? This concern is necessary, for example, if a user completes an operation (the database has completed a transaction, the page has been modified in buffer pool, but the dirty page has not yet been flush), the system is powered off and the buffer pool data disappears. So, will the actions completed by this user (resulting in database modifications) be lost? The answer is innodb_flush_log_at_trx_commit=1. This is what redo log does, recording updates on disk. Redo log records the transaction change operation to redo log immediately every time a transaction commit. So even if the dirty page in the buffer pool is lost during a power outage, the InnoDB will still complete the data recovery according to the records in the redo log when it starts. Another function of redo log is to minimize the random writes of the disk by delaying the flush of dirty page. (redo log will merge TRX modifications to a page over a period of time.)

Under normal circumstances, when does dirty page flush to disk?

1). Redo log is a ring structure, when the redo space is full, part of the dirty page flush will be on the disk, and then part of the redo log will be released. This can be observed through Innodb_log_wait (SHOW GLOBAL STATUS), and the counter increments itself when it happens.

2)。 When you need to allocate a page in Buffer pool, but it is full, and all page is dirty (otherwise you can release non-dirty page), it usually does not happen. It is necessary to flush dirty pages to disk at this time. This situation will be recorded in Innodb_buffer_pool_wait_free. In general, you can control this through the startup parameter innodb_max_dirty_pages_pct. When the dirty page in the buffer pool reaches this ratio, a checkpoint will be forcibly set and dirty page flush will be added to the disk.

3)。 When the system is detected to be idle, it will flush for 64 pages each time. InnoDB configuration parameters involved: innodb_flush_log_at_trx_commit, innodb_max_dirty_pages_pct; status parameters: Innodb_log_wait, Innodb_buffer_pool_wait_free.

reference

Http://mysqldump.azundris.com/archives/78-Configuring-InnoDB-An-InnoDB-tutorial.htmlhttp://dev.mysql.com/doc/refman/5.0/en/innodb.html

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.