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.

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 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 being read from disk over and over, and it's why the site is slow.

There are two directions to fix it: make the data smaller, or make the pool larger. What you can't do is keep specific tables in or out of the pool; see You can't reserve the pool for specific tables.

Reading the report

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

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

  • '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.

The 'InnoDB' page only exists on clusters with MariaDB. PostgreSQL has its own cache, not covered here.

The matching health check

'InnoDB buffer pool size sufficient' compares the same two numbers every 24 hours and flags the cluster when the data is larger than the pool. Severity 'recommendation'; its wrench button opens this page. See All about health checks.

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:

  • Page already in the pool → MariaDB uses it straight away. No disk involved.
  • Page not there → MariaDB reads it from disk into the pool first, then uses it. The slow path.
  • A write changes the page in the 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 pool has a fixed size. When it is full and a query needs a page that is not in it, MariaDB throws out a page that is, to make room. That is eviction.

All your data fits → after the database has been running a while, every page has been read once and stays in memory. Queries run at memory speed.

Your data is larger than the pool → 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 spends its time moving pages between disk and memory instead of answering queries.

What that looks like from the outside:

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

The fix is not a better query. A query that reads from disk is slow no matter how it is written.

You can't reserve the pool for specific tables

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 none to keep a specific table out of it. Some other database engines offer this; InnoDB does not.

Pages also do not enter the pool because someone decided they should. Reading a page into the buffer pool is how a query gets answered. There is 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

A pattern that 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.
  • The buffer pool is 8 GB.

On a normal day this works fine. The 2 GB that gets queried constantly is in memory, and the audit log sits on disk where nobody thinks about it.

Then someone opens the audit trail and filters by last month. MariaDB must read those pages from disk into the buffer pool to answer the query — there is no other way. The pool is full, so it evicts pages to make room. The pages it evicts are your users, subscriptions and orders data, because that is 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 is 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 do not 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 the audit log does not matter to you and the orders table does. There is 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, 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 list of cached pages is split in two. The new part holds pages that have proven useful (63% of the list). The old part holds pages read only once (the remaining 37%). The boundary is the midpoint.
  • A page read from disk is inserted at the midpoint — the top of the old part — not at the top of the whole list. It starts as a candidate for eviction, not as protected data.
  • A page moves into the new part only when something reads it again, and only if that second read is more than about a second after the page arrived. Reads within that first second do not count.
  • A page nobody reads again drifts down through the old part and is evicted, without ever pushing anything out of the new part.

The one-second rule exists because a table scan reads a page, uses it a few times in quick succession, 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; those land in the old part too, so a wrong guess costs nothing.

So a single pass over the audit log churns through the old part rather than immediately throwing out everything valuable. That softens the problem. It does not 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, 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, so tuning them to protect against the audit log changes caching for every other table on the node.

Those two settings are the closest thing to a lever here. MySQL's own guidance for a workload mixing normal traffic with occasional big reports is to shrink the old part, or 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 below are usually the better fix.

Full mechanics: MySQL's documentation on midpoint insertion.

And the audit log is not fully cold to begin with: it is 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

You cannot exclude it from the pool, so: keep it out of the database server, keep it small, or make the rare query read fewer pages.

  1. 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 cannot evict anything your application needs.
  2. Don't keep it in MariaDB at all. If the data is not 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 many customers use them for exactly this: a large body of records written constantly and searched occasionally. Both are built for searching and filtering large volumes of documents — which is what an audit trail is — and both index the data up front instead of reading through rows to find matches. They run as their own service, with their own memory and storage, so searching them cannot evict anything from the MariaDB buffer pool. Moving the audit log out removes its 60 GB from 'Total Data' entirely.
  3. 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.
  4. 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 — where nearly all of the 60 GB is. SELECT * prevents this.
  5. 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.

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 do not remove them when uninstalled. So do old migrations from another host or 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. These grow forever unless something deletes old rows. Most 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 belonging 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 does not 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 in the report. See All about databases.

Make the pool larger

The buffer pool size is not a setting in Core. It is part of the MariaDB configuration Cyberfusion manages, 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.

So a larger pool means more memory on the node. Contact Cyberfusion support for advice 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, 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 does not need every page in memory at once — only the pages queries actually touch, called the working set. A 40 GB database where visitors only 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 cannot be measured reliably, and it changes. A nightly report, a search crawler, or an export job reads pages normal traffic never touches, and each pushes the pages your visitors need out of memory. That is the audit-log problem again, and it is why "the cold data doesn't count" does not 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 5% over the pool behaves very differently from one at four times the pool.