CODEWITHSUNDEEP
mysqlsqldatabaseinnodb
blacktie24
blacktie24

Reputation: 5075

What is INNODB "Pages to be Flushed"? And can I restart mysqld while there are still pages to be flushed?

I've tried reading some of the material out there but they are a bit over my head. However, from what I understand, if you have a lot of memory allocated to the buffer pool, then the writes to memory are happening faster than the disk can keep up with, and therefore, there are "pages to be flushed" still? Additionally, if I restart the mySQL server, will that cause any issues?

Upvotes: 0

Views: 1247

Answers (1)

Mahesh Patil
Mahesh Patil

Reputation: 1551

  • InnoDB performs certain tasks in the background, including flushing of dirty pages (those pages that have been changed but are not yet written to the database files) from the buffer pool, a task performed by the master thread. For more information you can refer: http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html#innodb-performance-adaptive_flushing

  • Having dirty pages is something normal. When you update 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 append-only operation, while the actual update involve random writes, and random write is slower. 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 value to 0

  • If you are using MySQL v5.6 then you can enable this variable innodb_buffer_pool_dump_at_shutdown which Specifies whether to record the pages cached in the InnoDB buffer pool when the MySQL server is shut down, to shorten the warmup process at the next restart. you must use this variable in conjunction with innodb_buffer_pool_load_at_startup.

Upvotes: 1

Related Questions