What's the exact meaning of `innodb_max_dirty_pages_pct`, and how does it relate to the redo log?

Question

I'm puzzled by the innodb_max_dirty_pages_pct parameter, and its relation to the redo log.

The definition itself is simple:

InnoDB tries to flush data from the buffer pool so that the percentage of dirty pages does not exceed this value.

However, what confuses me is the relationship with the redo log.

Does "flush data" mean:

1. copy dirty pages from the log buffer to the redo log, or
2. from the redo log to the buffer pool file?

I can't imagine how case 1 can happen. Even in systems with innodb_flush_log_at_trx_commit = 0, logs are flushed one per second, so how can 90% of the buffer pool be changed in one second?

If the case is 2 instead (and this interpretation would be, I guess, (checkpoint age > total log size * pct), such high number of dirty pages don't fit in the redo log.

What am I misunderstanding?

Answer 1

You say:

logs are flushed one per second, so how can 90% of the buffer pool be changed in one second? [...] such high number of dirty pages don't fit in the redo log

You seem to think that the log buffer and the buffer pool mean the same thing; that is not true.

There is no relation between flushing dirty pages from the buffer pool and the redo log. Log buffer and the buffer pool serve different purposes and are managed separately.

As the manual points out:

The buffer pool is an area in main memory where InnoDB caches table and index data

and

The log buffer is the memory area that holds data to be written to the log files on disk

(Emphasis mine.)

By the time a dirty data or index page is flushed, the corresponding log records will have been written out already -- this is the nature of write-ahead logging. Flushing dirty pages from the buffer pool means these pages are written to the appropriate ibdata* and/or .ibd files.

EDIT By RolandoMySQLDBA

Adding Visual Representation of InnoDB from Percona CTO Vadim Tkachenko

Answer 2

First thing to understand is that redo log buffer and log files don't contain the same type of items as buffer pool and data files (see here). No items are ever copied from redo log to buffer pool or from buffer pool to redo log. Therefore, both your views of the relation between them are incorrect.

"Flush data" means writing pages from buffer pool to data file (see here).

I think innodb_max_dirty_pages_pct still has something to do with redo log because dirty page can't be flushed before correspondent log records are already written on disk (I can't find confirmation in docs but it seems likely because otherwise database restore procedure would have incoherent set of log data records).

So it's a possibility that when innodb_max_dirty_pages_pct is reached and background process starts trying to find pages that could be written on disk but can't because correspondent redo log records are not written from buffer on disk, it may then trigger redo log flush, maybe causing a temporary slowdown of foreground operations. (But hardly it could happen with buffer pool large enough.)