How to fix InnoDB dirty pages?

Question

When I issue the SHOW GLOBAL STATUS; MySQL command, one of the lines I get is the following:

Innodb_buffer_pool_pages_dirty  28

When I look up the documentation, all I see is:

The number of pages currently dirty. Added in MySQL 5.0.2.

How can I fix this (I'm assuming having dirty pages isn't something good) ?

Solution

Having dirty pages is normal when there are changes (UPDATE/INSERT/DELETE) to the database.

When you change a row, MySQL updates it in the buffer pool, marking the page as “dirty”. The change is written in the binary log as well, so in case of crash, MySQL will replay the log and data won't be lost. Writting to the binary log is a fast append-only (sequential) operation, while the actual page update uses random writes which are slower. (this is done in the background)

MySQL flushes dirty pages to disk when it needs to load new data in the buffer pool. So, having dirty pages in InnoDB is something normal - it's how it works and it's done to improve the overall performance.

But if you really want to get rid of them, set innodb_max_dirty_pages_pct to 0

Comments

[Max]

So my understanding is that I can lower down innodb_max_dirty_pages_pct which may have an impact on performance (because InnoDB will write more often to disk) but will ensure queries are immediately written to the database files. That seems like the usual performance vs reliability discussion whereby the less often you write the better the performance, but the higher the chance to loose data. Have you ever had the need to lower down innodb_max_dirty_pages_pct to preserve data integrity? Should I bother with this setting at all?

[Maxim Krizhanovsky]

@user359650 The integrity is handled by transaction logs, so the only setting in effect is innodb_flush_log_at_trx_commit - it determines if the transaction log is flushed to disk on each transaction (full ACID compliance) or once every second (you can loose maximum 1 second of transactions in case of OS crash). The amount of dirty pages may affect time to shutdown MySQL, as well as time for crash recovery (I'm not sure for the second, but makes sense). If you have write-intensve application however, you may try if the innodb_max_dirty_pages_pct setting affect performance and how

[Max]

Indeed shutdown time is taking a long time and we were wondering why. These sort of settings look like the ones you shouldn't be messing with unless you do thorough testing before rolling out to production, unlike others we successfully changed with a positive impact on performance. We may look into this in the future. Thanks.

[Gunther Piez]

@Max Actually increasing the number of dirty pages may increase the performance of the database, at cost of memory. Did you know that you are surrounded by dirty pages? Not only databases use them, operating systems too. A lot on your PC, on your smartphone, everywhere... And you are free to disable them everywhere.

[Bill Karwin]

The more pleasant name for dirty pages is "modified pages." You can see this in a line of SHOW ENGINE INNODB STATUS\G where it gives a count of "Modified db pages." I don't know why they use two different terms for the same thing. I guess different software developers had different opinions.