CODEWITHSUNDEEP
mysqlinnodb
Max
Max

Reputation: 13334

How to fix InnoDB dirty pages?

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

Upvotes: 10

Views: 22638

Answers (1)

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26719

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

Upvotes: 21

Related Questions