April 19, 2014

What’s a good buffer pool read/write ratio?

At Percona Live last week, someone showed me a graph from their Cacti monitoring system, using the templates that I wrote. It was the buffer pool pages read, written, and created. He asked me if the graph was okay. Shouldn’t there be a lot more pages read than written, he asked? It’s a great question. […]

The write cache: Swap insanity tome III

Swapping has always been something bad for MySQL performance but it is even more important for HA systems. It is so important to avoid swapping with HA that NDB cluster basically forbids calling malloc after the startup phase and hence its rather complex configuration. Probably most readers of this blog know (or should know) about […]

Is your MySQL buffer pool warm? Make it sweat!

Today’s blog post diving into the waters of the MySQL buffer pool is a cross-post from Groupon’s engineering blog, and is Part 1 of 2. Thank you to Kyle Oppenheim at Groupon for contributing to this project and post. We’ll be posting Part 2 on Thursday. I’ll be at the Percona Live MySQL Conference and […]

Read/Write Splitting with PHP Webinar Questions Followup

Today I gave a presentation on “Read/Write Splitting with PHP” for Percona Webinars.  If you missed it, you can still register to view the recording and my slides. Thanks to everyone who attended, and especially to folks who asked the great questions.  I answered as many as I could during the session, but here are […]

Measuring the amount of writes in InnoDB redo logs

Choosing a good InnoDB log file size is key to InnoDB write performance. This can be done by measuring the amount of writes in the redo logs. You can find a detailed explanation in this post. To sum up, here are the main points: The redo logs should be large enough to store at most […]

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

Analyzing the distribution of InnoDB log file writes

I recently did a quick analysis of the distribution of writes to InnoDB’s log files. On a high-traffic commodity MySQL server running Percona XtraDB for a gaming workload (mostly inserts to the “moves” table), I used strace to gather statistics about how the log file writes are distributed in terms of write size. InnoDB writes […]

How is join_buffer_size allocated?

When examining MySQL configuration, we quite often want to know how various buffer sizes are used. This matters because some buffers (sort_buffer_size for example) are allocated to their full size immediately as soon as they are needed, but others are effectively a “max size” and the corresponding buffers are allocated only as big as needed […]

SSD, XFS, LVM, fsync, write cache, barrier and lost transactions

We finally managed to get Intel X25-E SSD drive into our lab. I attached it to our Dell PowerEdge R900. The story making it running is worth separate mentioning – along with Intel X25-E I got HighPoint 2300 controller and CentOS 5.2 just could not start with two RAID controllers (Perc/6i and HighPoint 2300). The […]

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