1

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?

2

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

InnoDB Architecture

  • Thanks! So, is it correct that, as a consequence, on a system with a redo log smaller than the dirty pages ratio (for example, respectively, 50% of the buffer pool and 90%), flushing will always happen before the innodb_max_dirty_pages_pct is reached, because the redo log can't contain enough pages to reach that threshold? – Marcus Nov 22 '19 at 16:58
  • I think you need to re-read my answer. There is no correlation between the log size and flushing dirty pages from the buffer pool. A small log file will limit the volume of your transactions, but will not trigger writing of dirty pages to disk. – mustaccio Nov 22 '19 at 17:29
  • @rolandomysqldba -- Your picture is getting out of date: XtraDB is ancient history; the "change buffer" is not labeled as such; old syntax for a SHOW; etc. – Rick James Nov 22 '19 at 22:25
  • @mustaccio from the manual: "A sharp checkpoint occurs when InnoDB wants to reuse a portion of a log file. Before doing so, all dirty pages with redo entries in that portion of the log file must be flushed. If log files become full, a sharp checkpoint occurs, causing a temporary reduction in throughput. This scenario can occur even if innodb_max_dirty_pages_pct threshold is not reached.". Based on this, a small log files can correlate to flushing activity, due to sharp checkpoints under heavy write conditions. – Marcus Nov 22 '19 at 23:23
1

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.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.