Mysql – What’s the exact meaning of `innodb_max_dirty_pages_pct`, and how does it relate to the redo log

buffer-pooldata-pagesinnodbMySQLtransaction-log

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:

copy dirty pages from the log buffer to the redo log, or
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?

Best Answer

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

Related Solutions

Mysql – High amount of Read Misses and Pages To Be Flushed

What really jumps at me is that 52% miss. It's possible your innodb_buffer_pool_size is just too small.

Keep in mind that the InnoDB Buffer Pool caches data pages and index pages.

How can you get a good size for it? Run this query:

SELECT
    CONCAT(CEILING(ibps/POWER(1024,1)),'K') IBPS_KB,
    CONCAT(CEILING(ibps/POWER(1024,2)),'M') IBPS_MB,
    CONCAT(CEILING(ibps/POWER(1024,3)),'G') IBPS_GB
FROM (SELECT SUM(data_length+index_length) ibps
FROM information_schema.tables WHERE engine='InnoDB') A;

This will give you something like this

mysql> SELECT
    ->     CONCAT(CEILING(ibps/POWER(1024,1)),'K') IBPS_KB,
    ->     CONCAT(CEILING(ibps/POWER(1024,2)),'M') IBPS_MB,
    ->     CONCAT(CEILING(ibps/POWER(1024,3)),'G') IBPS_GB
    -> FROM (SELECT SUM(data_length+index_length) ibps
    -> FROM information_schema.tables WHERE engine='InnoDB') A;
+-----------+---------+---------+
| IBPS_KB   | IBPS_MB | IBPS_GB |
+-----------+---------+---------+
| 30333520K | 29623M  | 29G     |
+-----------+---------+---------+
1 row in set (11.43 sec)

mysql>

If the output gives you settings beyond 80% of the DB Server's Installed RAM, then use 80% of whatever the installed RAM is as the innodb_buffer_pool_size.

Give it a Try !!!

UPDATE 2013-04-15 12:43EDT

Let's look at the definition of Innodb_buffer_pool_wait_free

Normally, writes to the InnoDB buffer pool happen in the background. However, if it is necessary to read or create a page and no clean pages are available, it is also necessary to wait for pages to be flushed first. This counter counts instances of these waits. If the buffer pool size has been set properly, this value should be small.

As stated, if the buffer pool size has been set properly, this value should be small. You may simply have lots of dirty pages in the Buffer Pool that need flushing to disk. You should be monitoring Innodb_buffer_pool_pages_dirty.

There are two things you could do to improve the situation:

IMPROVEMENT #1 : Upgrade to the latest MySQL

I trust MySQL 5.5. I have a client going to MySQL 5.6.10 soon. I trust it as well. These versions of MySQL have the InnoDB Plugin standard. They flush dirty pages much more efficiently.

You can also tune InnoDB. Under MySQL 5.1, there are 4 read IO threads and 4 write IO threads. MySQL 5.5+ allows you to increase these for better read and write InnoDB performance. InnoDB For MySQL 5.5.+ can access multiple CPUs/Core. MySQL 5.1 can do this if using MySQL 5.1.38+ and you install the InnoDB Plugin (IMHO too messy, go with MySQL 5.5/5.6). MySQL 5.1.27 cannot do this.

IMPROVEMENT #2 : Get Dirty Pages to Flush More Frequently

You can do this immediately with

SET GLOBAL innodb_max_dirty_pages_pct = 0;

The default value for innodb_max_dirty_pages_pct in MySQL 5.1 is 90. Drop this to zero(0). Then, start watching Innodb_buffer_pool_pages_dirty. On a busy write server, this should drop to 1% of Innodb_buffer_pool_pages_total.

Mysql – InnoDB internals – checkpoint, LSN, dirty pages

Your answers are on the source code: innodb_buffer_pool_pages_dirty. Most of these variables are maintained on memory directly or through very simple calculations. There is no danger of durability loss, as the ones that are "essential" for innodb to work can be recalculated from the transaction log or are 0 on start.
Not really, the lsn is just an offset in bytes, it is maintained on memory (log_sys->lsn) and in the case of a failure, it can be recovered from the header of the transaction log (plus pure file offsets). They are changed on commit/log flush/tablespace flush, both on memory and on disk (exact method depends on the durability configuration, buffering type, etc.).

For basic InnoDB stuff, you can check this blog post by the Oracle InnoDB team and/or check the source code for InnoDB.