MySQL DBA system Learning (INNODB) The principle of the Redo log log of the engine

In InnoDB, the dirtypage on the Bufferpool can speed up data processing while also causing inconsistency (ramvsdisk). This article describes how dirtypage is produced and how InnoDB uses Redolog to eliminate inconsistencies in the data produced by Dirtypage.

When a transaction (Transaction) needs to modify a record (row), InnoDB needs to read the page from disk to the Bufferpool where the data resides, and InnoDB modify the record (row) in the page after the transaction is committed. At this time the page in Bufferpool and disk is not the same, we call Bufferpool in the page as Dirtypage. Dirtypage waiting for flush to disk.

Dirtypage since it is in Bufferpool, then if the system suddenly power off dirtypage data modification will be lost? This concern is necessary, for example if a user completes an operation (the database completes a transaction, the page has been modified in Bufferpool, but the dirtypage has not yet flush), when the system is powered down and the Bufferpool data disappears. So, does the user's completed operation (the resulting database modification) be lost? The answer is no (innodb_flush_log_at_trx_commit=1). This is what redolog to do, record the update on disk.

Redolog Records transaction change operations to Redolog every time a transaction commits. So even if the dirtypage in Bufferpool is lost during power outages, InnoDB will still be able to recover data from the records in Redolog when it is started.

Another effect of redolog is to minimize the randomwrites of the disk by delaying the flush of the dirtypage. (Redolog will be merged for a period of time to modify the page of Trx)

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/

Under normal circumstances, when does dirtypage flush to disk?

1. Redolog is a ring structure that, when redo space is full, will dirtypageflush the part to disk and then release some redolog. This situation can be observed by innodb_log_wait (Showglobalstatus), which occurs when the counter is incremented once.

2. When you need to assign a page to the Bufferpool, but it is full, and all the page is dirty (otherwise you can release the page that is not dirty), it usually does not happen. This time must be flushdirtypagestodisk. This situation will be recorded in the Innodb_buffer_pool_wait_free. Generally, you can control this situation by starting the parameter innodb_max_dirty_pages_pct, and when the dirtypage in the bufferpool reaches this ratio, it will force a checkpoint to be set, And put the Dirtypageflush into disk.

3. When the system is idle, it will be flush, 64pages 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.

Source: http://wolfword.blog.51cto.com/4892126/1288383