April 20, 2014

How well does your table fits in innodb buffer pool ?

Understanding how well your tables and indexes fit to buffer pool are often very helpful to understand why some queries are IO bound and others not – it may be because the tables and indexes they are accessing are not in cache, for example being washed away by other queries. MySQL Server does not provide […]

What is stored InnoDB buffer pool

Ever wonder what is stored in InnoDB buffer pool at the moment ? It is not so hard actually – we made a short patch for MySQL 5.0 which show innodb buffer pool content

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

Some little known facts about Innodb Insert Buffer

Despite being standard Innodb feature forever Insert Buffers remains some kind of mysterious thing for a lot of people, so let me try to explain thing a little bit. Innodb uses insert buffer to “cheat” and not to update index leaf pages when at once but “buffer” such updates so several updates to the same […]

Quickly preloading Innodb tables in the buffer pool

In the previous post I mentioned a way I use to preload Clustered Index (data) for Innodb tables. Though I thought this topic would benefit from a bit more information. But lest first start with feature request for Innodb Team: All ways I mention here are hacks and they can’t be as efficient as native […]

Choosing innodb_buffer_pool_size

My last post about Innodb Performance Optimization got a lot of comments choosing proper innodb_buffer_pool_size and indeed I oversimplified things a bit too much, so let me write a bit better description. Innodb Buffer Pool is by far the most important option for Innodb Performance and it must be set correctly. I’ve seen a lot […]

Innodb Recovery – Is large buffer pool always better ?

How does Buffer Pool size affects Innodb Performance ? I always expected the effect to be positive, Innodb with large buffer pool to performing better. Including Recovery of course. I even blogged about it. It turns out it is not always the case. Last week I was called to help with Innodb crash recovery on […]

Innodb redo log archiving

Percona Server 5.6.11-60.3 introduces a new “log archiving” feature. Percona XtraBackup 2.1.5 supports “apply archived logs.” What does it mean and how it can be used? Percona products propose three kinds of incremental backups. The first is full scan of data files and comparison the data with backup data to find some delta. This approach […]

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

InnoDB scalability issues due to tables without primary keys

Each day there is probably work done to improve performance of the InnoDB storage engine and remove bottlenecks and scalability issues. Hence there was another one I wanted to highlight: Scalability issues due to tables without primary keys This scalability issue is caused by the usage of tables without primary keys. This issue typically shows […]