Fun MySQL fact of the day: the buffer pool

Since February, we've been intentionally working off a half-formed mental model about InnoDB. In just about all cases, we considered a model wherein InnoDB always reads from and writes to the on-disk index files to serve each query. And while considering a mental model like this can certainly simplify understanding certain things, it can also confound other things. So, let's start building on this half-formed mental model by considering the InnoDB buffer pool, which we've only casually discussed before.

Since reading from and writing to disk is several orders of magnitude slower than reading from and writing to memory, in an ideal situation, all of our data would be stored in memory. Of course, given the cost and volatile nature of modern, commodity RAM, our ideal situation would turn pretty bleak if the server ever crashed or had to be restarted: we'd lose all of our data. In attempts of striking a balance, InnoDB tries its best to keep as much data as it can in memory while, at the same time, still guaranteeing durability without sacrificing performance.

So, how much data is "as much data as it can"? Well, it depends. It depends on the value of innodb_buffer_pool_size, which defaults to 134217728 (or 128MB). What does this mean for an out-of-the-box experience? Simply, at most, 128MB of your database will be held in memory managed by MySQL. That's not a lot; especially if you have wide records and large tables in the multi-gigabyte or multi-terabyte range. Generally speaking, a decent starting size for innodb_buffer_pool_size is roughly 80% of the available system memory, which should leave enough room for other processes and the additional memory MySQL itself requires for each thread (remember join_buffer_size, sort_buffer_size, and read_rnd_buffer_size, for example?).

Over the next little while, we're going to start a journey into the InnoDB buffer pool before circling all the way back to the redo log. But, for today, that's enough. Still, if you're eager, I encourage you to go look at how your buffer pool is sized compared to the amount of memory available on the server. Is it close to 80%?