As a junior once I asked a seasoned MySQL DBA (Abuelo) “How do you stay so calm in critical situations?”Abuelo DBA then uttered golden words: “Son, I keep my dirty…
The post InnoDB Flushing is simple – explained first appeared on Change Is Inevitable.
In MySQL 5.6 InnoDB has a dedicated thread (page_cleaner) that’s
responsible for performing flushing operations. Page_cleaner
performs flushing of the dirty pages from the buffer pool based
on two factors:
- access pattern - the least recently used
pages will be flushed by LRU flusher from LRU_list when buffer
pool has no free pages anymore;
- age – the oldest modified non-flushed pages are part of
flush_list structure and will be flushed by flush_list flusher
based on several heuristics.
There is a good overview of the page_cleaner and also here you may find some details about flushing in MySQL 5.6. Below I describe several additional aspects of the flush_list flushing that was not really …
[Read more]InnoDB’s checkpoint algorithm is not well documented. It is too complex to explain in even a long blog post, because to understand checkpoints, you need to understand a lot of other things that InnoDB does. I hope that explaining how InnoDB does checkpoints in high-level terms, with simplifications, will be helpful. A lot of the simplifications are because I do not want to explain the complexities of how the simple rules can be tweaked for optimization purposes, while not violating the ACID guarantees they enforce.
A bit of background: Gray and Reuter’s classic text on transaction processing introduced two types of checkpoints beginning on page 605. There is a sharp checkpoint, and there is a fuzzy checkpoint.
A sharp checkpoint is accomplished by flushing all modified pages for committed transactions to disk, and …
[Read more]