Skip to content

InnoDB buffer pool

Your queries are written well, the tables have their indexes, and the site is still slow. In a lot of cases the cause isn't the queries at all: the database no longer fits in memory, so it's reading from disk.

Wondering how much memory your database has, how much data it's trying to keep in it, and what to do when the second number is bigger than the first? This article explains what the InnoDB buffer pool is, why it should be at least as large as your data, and where to see both numbers in Core.

What the buffer pool is

InnoDB is the storage engine MariaDB uses for virtually every table. It stores your tables on disk in fixed-size chunks called pages. A page holds rows of a table, or part of an index.

The buffer pool is a block of memory MariaDB reserves at startup to hold copies of those pages. Every read and every write goes through it:

  • A query needs a page. If the page is already in the buffer pool, MariaDB uses it straight away. No disk involved.
  • If the page isn't there, MariaDB reads it from disk into the buffer pool first, then uses it. This is the slow path.
  • A write changes the page in the buffer pool, and MariaDB writes it out to disk afterwards, in the background.

Reading a page from memory takes microseconds. Reading it from disk takes far longer, even on the NVMe SSDs Cyberfusion uses. That gap is the whole reason the buffer pool exists.

Why it should be at least as large as your data

The buffer pool has a fixed size. When it's full and a query needs a page that isn't in it, MariaDB has to throw out a page that is in it to make room. That's called eviction.

If all of your InnoDB data fits in the buffer pool, this almost never happens. After the database has been running for a while, every page has been read once and is now in memory, and it stays there. Queries run at memory speed.

If your data is larger than the buffer pool, it can't all be in memory at the same time. Pages get evicted to make room for other pages, and the evicted pages get read back from disk when a query needs them again, which evicts something else. The database ends up moving pages between disk and memory constantly instead of answering queries.

What this looks like from the outside:

  • Pages that used to render quickly now take seconds, and the delay isn't consistent. The same page is fast one minute and slow the next, because it depends on whether the pages it needs happen to be in memory at that moment.
  • Slowness appears without you having changed anything. The data simply grew past the pool over time.
  • Everything on the cluster gets slower at once, not one specific site. All databases on a node share one buffer pool, so a database that grew too large pushes other databases' pages out of memory as well.
  • Traffic peaks hurt much more than they used to. More queries at the same time means more competition for the same too-small pool.

The fix isn't a better query. A query that reads from disk is slow no matter how it's written.

You can't reserve the pool for specific tables

A common reaction to the previous section is: most of my data doesn't need to be in memory anyway, so let me keep the tables that matter in the pool and leave the rest out.

You can't. There is one buffer pool per database server, shared by every table in every database on that node. InnoDB has no setting to keep a specific table in memory, and no setting to keep a specific table out of it. Some other database engines do offer this; InnoDB doesn't.

Pages also don't enter the pool because someone decided they should. Reading a page into the buffer pool is how a query gets answered. There's no such thing as reading a table without caching it.

So the size of a table you never query still counts, because "never" is rarely true.

Worked example: the audit log

This pattern comes up constantly in SaaS applications:

  • users, subscriptions and orders: 2 GB in total. Every page load queries them.
  • audit_log: 60 GB. Written to on every action, read by a human roughly once a week, when someone opens the audit trail in the admin panel.
  • The buffer pool is 8 GB.

On a normal day this works fine. The 2 GB of tables that get queried constantly are in memory, and the audit log sits on disk where nobody has to think about it.

Then someone opens the audit trail and filters it by last month. MariaDB has to read those pages from disk into the buffer pool to answer the query — there is no other way to answer it. The pool is full, so to make room it evicts pages. The pages it evicts are your users, subscriptions and orders data, because that's what was in there.

The report finishes and the person closes the tab. The problem starts there:

  • Your hot data is no longer in memory. It's back on disk.
  • Every ordinary request now has to read it in again, one page at a time.
  • For as long as that takes, every customer's requests are slow — not just the person who ran the report.

One query against a table you don't care about made the whole application slow for everyone, and nothing about the application changed.

Why the algorithm can't help you here

The buffer pool decides what to evict based on what was used recently. A page that was just read is, as far as it can tell, the most useful page to keep. It has no idea that the audit log doesn't matter to you and the orders table does. There's nothing in a page that says how important it is, and nothing you can attach to a table to say it.

InnoDB does have a partial defence against exactly this situation, called midpoint insertion. It exists because a plain "throw out whatever was used least recently" rule would let one table scan wipe the entire pool. The basics:

  • The list of cached pages is split into two parts. The new part holds pages that have proven useful, and is 63% of the list. The old part holds pages that have only been read once, and is the remaining 37%. The boundary between them is the midpoint.
  • A page read from disk is inserted at the midpoint — at the top of the old part — instead of at the top of the whole list. It starts out as a candidate for eviction, not as protected data.
  • A page moves up into the new part only when something reads it again, and only if that second read happens more than about a second after the page arrived. Reads within that first second don't count.
  • A page nobody reads again drifts down through the old part and is evicted, without ever having pushed anything out of the new part.

The reason for the one-second rule is that a table scan reads a page, uses it a few times in quick succession, and then never touches it again. Without the rule, those rapid repeat reads would look like proof the page is valuable. InnoDB also reads pages ahead of what a scan asked for, guessing the scan will want them next; those pages land in the old part too, so a guess that turns out wrong costs nothing.

So a single pass over the audit log churns through the old part of the list rather than immediately throwing out everything valuable. That softens the problem. It doesn't remove it:

  • The old part is still 37% of the pool. A large scan evicts more than a third of what was cached, and that third is real data your application was using.
  • The one-second rule only protects against reads in quick succession. A report that reads the same pages more than once over a longer period — which a join, a sort or a GROUP BY over a big result does — promotes them into the protected part, which is the outcome the split was meant to prevent.
  • The two settings that control this behaviour, innodb_old_blocks_pct (how large the old part is) and innodb_old_blocks_time (the one-second window), apply to the whole server. There is no per-table version of either, so tuning them to protect against the audit log changes how caching works for every other table on the node too.

Those two settings are the closest thing to a lever here, and MySQL's own guidance for a workload that mixes normal traffic with occasional big reports is to shrink the old part, or to widen the time window while the report runs. Both are part of the MariaDB configuration Cyberfusion manages, and both affect every database on the node, so contact support if you think your cluster is a case for it. The options in the next section are usually the better fix. For the full mechanics, see MySQL's documentation on midpoint insertion.

And the audit log isn't fully cold to begin with. It's written to constantly, and every insert updates the end of the table and its indexes, so that part is permanently in memory whether you query the table or not.

What to do with a large cold table

Since you can't exclude it from the pool, the options are to keep it out of the database server, keep it small, or make the rare query read fewer pages:

  • Put it on another node or cluster. A separate database node, or a separate cluster, has its own buffer pool. A report against the audit log then can't evict anything your application needs.
  • Don't keep it in MariaDB at all. If the data isn't relational and you never join against it, a relational database is the wrong place for it. Cyberfusion clusters can run Meilisearch and Elasticsearch as node groups, and a lot of customers use them for exactly this: a large body of records that is written constantly and searched occasionally. Both are built for searching and filtering large volumes of documents, which is what an audit trail actually is, and both index the data for that up front instead of reading through rows to find matches. For this article the important part is that they run as their own service, with their own memory and their own storage, so searching them can't evict anything from the MariaDB buffer pool. Moving the audit log out of MariaDB removes its 60 GB from the 'Total Data' figure entirely.
  • Keep only recent rows in the table. Move anything older than your retention period to an archive table, a file, or object storage. The everyday table stays small, and the archive is only touched by the rare query that genuinely needs history.
  • Give it the indexes the report needs. This is the difference between reading millions of pages and reading a few hundred. If the audit page filters by date and user, index those columns. If the query only selects columns that are in the index, MariaDB can answer it from the index alone and never touch the row data, which is where nearly all of the 60 GB is. SELECT * prevents this.
  • Limit what the report can ask for. Paginate it, and cap the date range it accepts. A screen that lets someone request "everything, all time" will eventually have someone request it.

Where to see your numbers

Core reports both numbers per cluster.

  1. Navigate to 'Clusters'.
  2. Select the cluster.
  3. Navigate to 'InnoDB' under 'Health' in the sidebar.

The report is generated by asking the database server directly when you open the page, so it takes a moment to load and always shows the current state.

The page opens with a bar comparing two values:

  • 'InnoDB Buffer Pool': how much memory the database has for caching, on this cluster.
  • 'Total Data': how much InnoDB data there is, across every database on the cluster. This is the data in your tables plus the indexes on them.

When 'Total Data' is larger than 'InnoDB Buffer Pool', the bar turns orange and an 'excess' label shows how much data doesn't fit. That is the amount that has to be read from disk over and over.

Below the bar is a list of every database on the cluster with its total size. Click a database to expand it into a table-by-table breakdown, sorted largest first, with three columns:

  • 'Data': the size of the rows.
  • 'Index': the size of the indexes on that table.
  • 'Total': the two added together, which is what the buffer pool has to hold.

The largest tables at the top of that list are where to start if you want to bring the total down.

The 'InnoDB' page is only available on clusters with MariaDB. PostgreSQL has a separate cache of its own, which this page doesn't cover.

The matching health check

You don't have to check this by hand. The 'InnoDB buffer pool size sufficient' health check compares the same two numbers automatically and flags the cluster when the data is larger than the pool. It runs every 24 hours, has severity 'recommendation', and its wrench button takes you straight to the 'InnoDB' page. See All about health checks.

What to do when your data doesn't fit

There are two directions: make the data smaller, or make the pool larger.

Make the data smaller

Start with the largest tables in the report. Common causes of a database that grew larger than anyone expected:

  • Tables left behind by software you no longer use. Plugins, extensions and modules create their own tables and usually don't remove them when you uninstall them. So do old migrations from another host or another CMS. These tables are never queried, but they still take up space that has to be counted.
  • Logs and history stored in the database. Activity logs, security-plugin logs, order or payment history, and similar tables grow forever unless something deletes old rows. Most of these have a retention setting; if the software has one, set it.
  • WordPress specifically: post revisions in wp_posts, expired transients and autoloaded options in wp_options, and orphaned rows in wp_postmeta that belong to posts that no longer exist. These three account for the majority of oversized WordPress databases.
  • Indexes you don't need. The 'Index' column counts towards the total exactly like the data does. An index nothing queries costs memory permanently. This is not an argument against indexes: a missing index usually costs much more than an unused one. See WordPress database indexes.

After deleting a lot of rows, the table file on disk doesn't shrink by itself. The freed space stays inside the file as gaps. Enable 'Optimising' on the database so OPTIMIZE TABLE runs weekly and rebuilds the tables into densely packed files, which lowers the number shown in the report. See All about databases.

Make the pool larger

The buffer pool size isn't a setting in Core. It's part of the MariaDB configuration Cyberfusion manages, and it's sized against the memory of the node the database server runs on, since the pool has to fit in that node's RAM alongside everything else running there.

So a larger buffer pool means more memory on the node. Contact Cyberfusion support, and we'll advise on what the cluster needs.

If the database currently shares a node with nginx and PHP, moving MariaDB to its own node is often the better change. The database then gets that node's memory to itself instead of competing with PHP processes for it, and you can grow that node without paying for a larger web server. See Separating services across nodes.

About the "fits in memory" target

Comparing total data against the buffer pool is a deliberately strict test. In practice, a database doesn't need every page in memory at once — only the pages queries actually touch, which is called the working set. A 40 GB database where visitors only ever read the most recent 5 GB can run perfectly well on a smaller pool.

Total data is used as the target anyway, for two reasons:

  • The working set can't be measured reliably, and it changes. A nightly report, a search crawler, or an export job reads pages that normal traffic never touches, and each of those pushes the pages your visitors need out of memory. That's the audit-log problem again, and it's why "the cold data doesn't count" doesn't hold up.
  • Once everything fits, performance stops depending on which pages happen to be cached. You get the same speed at 3 AM and during your busiest hour.

Treat an over-target cluster as something to look into, not as an outage. A cluster where the data is 5% over the pool behaves very differently from one where it's four times the pool.