October 24, 2014

InnoDB adaptive flushing in MySQL 5.6: checkpoint age and io capacity

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; […]

Adaptive flushing in MySQL 5.6

As you may know, flushing in MySQL is an area of my interest, I wrote about it several times, i.e. http://www.percona.com/blog/2011/09/18/disaster-mysql-5-5-flushing/ http://www.percona.com/blog/2011/03/31/innodb-flushing-a-lot-of-memory-and-slow-disk/ http://www.percona.com/blog/2011/01/03/mysql-5-5-8-in-search-of-stability/ In MySQL 5.6 there was implemented a new flushing logic, so I decided to check what do we have now.

The relationship between Innodb Log checkpointing and dirty Buffer pool pages

This is a time-honored topic, and there’s no shortage of articles on the topic on this blog. I wanted to write a post trying to condense and clarify those posts, as it has taken me a while to really understand this relationship. Some basic facts Most of us know that writing into Innodb updates buffer […]

Disaster: MySQL 5.5 Flushing

We raised topic of problems with flushing in InnoDB several times, some links: InnoDB Flushing theory and solutions MySQL 5.5.8 in search of stability This was not often recurring problem so far, however in my recent experiments, I observe it in very simple sysbench workload on hardware which can be considered as typical nowadays.

InnoDB Flushing: Theory and solutions

I mentioned problems with InnoDB flushing in a previous post. Before getting to ideas on a solution, let’s define some terms and take a look into theory. The two most important parameters for InnoDB performance are innodb_buffer_pool_size and innodb_log_file_size. InnoDB works with data in memory, and all changes to data are performed in memory. In […]

InnoDB Flushing: a lot of memory and slow disk

You may have seen in the last couple of weekly news posts that Baron mentioned we are working on a new adaptive flushing algorithm in InnoDB. In fact, we already have three such algorithms in Percona Server (reflex, estimate, keep_average). Why do we need one more? Okay, first let me start by showing the current […]

Different flavors of InnoDB flushing

In my recent benchmarks, such as this one about the Virident TachIon card, I used different values for innodb_buffer_pool_size, like 13GB, 52GB, and 144GB, for testing the tpcc-mysql database with size 100G. This was needed in order to test different memory/dataset size ratios. But why is it important, and how does it affect how InnoDB works […]

Why delayed flushing can result in less work

I can think of at least two major reasons why systems delay flushing changes to durable storage: 1. So they can do the work when it’s more convenient. 2. So they can do less work in total. Let’s look at how the second property can be true.

Effect of adaptive_flushing

I recently had the chance to witness the effects of innodb_adaptive_flushing on the performance of InnoDB Plugin 1.0.5 in the wild, which Yasufumi wrote about previously here and here. The server in question was Solaris 10 with 8 disk RAID10 and 2 32GB SSDs used for ZIL and L2ARC, 72G RAM and 40G buffer pool. […]

A technical WebScaleSQL review and comparison with Percona Server

The recent WebScaleSQL announcement has made quite a splash in the MySQL community over the last few weeks, and with a good reason. The collaboration between the major MySQL-at-scale users to develop a single code branch that addresses the needs of, well, web scale, is going to benefit the whole community. But I feel that […]