We Inherited a Laravel ERP From 2018. The MySQL Config Was Set and Forgotten.
A story about discovering 8.6 million stalled queries, understanding what InnoDB is actually doing under the hood, and fixing a misconfiguration that nobody knew existed…
A story about discovering 8.6 million stalled queries, understanding what InnoDB is actually doing under the hood, and fixing a misconfiguration that nobody knew existed.
When I joined my current company, We inherited a Laravel ERP that had been in production since 2018.
The application was built mostly by a third-party development team before we came on board. When they handed it over, we got the code, the server credentials, and not much else. No documentation on infrastructure decisions. No explanation of why certain config values were set the way they were. Just a running application and the implied expectation that we keep it running.
For a while, that was fine. The app worked. Users complained here and there about slowness, but in a small team that’s always building new features and putting out fires, slow-but-functional rarely makes it to the top of the priority list. You chalk it up to “the app is getting bigger” and move on.
Then one day (umm… Yesterday actually) our team (two devs btw) stumbled upon this line in my-innodb.cnf :)
innodb_buffer_pool_size = 512M
512 megabytes. On a server with 16GB of RAM. Running a 91GB database.
Let me remind you that… this one line had been sitting there since 2018…set by someone who either configured it for a much smaller server, or just never changed the default.
Nobody questioned it.
Nobody noticed.
It just quietly degraded performance for years while we blamed everything else.
This is that story.
Some Background: What Is the Buffer Pool?
Before I get into the investigation, let me explain the one concept that makes everything else make sense because I wish someone had explained it to me early on.
Your database lives on disk. Disk is slow. Compared to RAM, it is really slow.
So MySQL doesn’t go directly to disk on every query. Instead, it loads chunks of your tables and indexes into a region of memory called the buffer pool, and serves reads from there.
Think of it like a chef’s workbench. Your pantry (disk) holds everything. But the chef doesn’t run back to the pantry for every single ingredient…they keep the most-used things on the counter (buffer pool) within arm’s reach. The bigger the counter, the more you can keep close, and the faster the cooking goes.
innodb_buffer_pool_size is literally the size of that counter.
Ours was 512MB for a 91GB pantry. The chef was running to the pantry constantly.
How We Found It
It started with a simple question We hadn’t thought to ask before: is the buffer pool sized correctly?
The standard way to check is to calculate the buffer pool hit rate… what percentage of read requests were served from memory (fast) versus fetched from disk (slow). A healthy server should be above 99%.
You run these two queries in your MySQL console:
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
- read_requests → total times MySQL looked for a page
- reads → times it couldn't find it in the buffer pool and had to go to disk
hit_rate = 1 - (reads / read_requests)
Our numbers came back:
Innodb_buffer_pool_read_requests → 1,379,229,806,450
Innodb_buffer_pool_reads → 9,297,636,798
hit_rate = 1 - (9,297,636,798 / 1,379,229,806,450) = 99.3%
99.3%. Looks completely fine, right?
We almost stopped there. But something felt off… how could a 512MB buffer pool serving a 91GB database have a 99% hit rate? So we kept digging.
Turns out, those are lifetime cumulative counters… they’ve been adding up since the last server restart. On a server that’s been running for months, the hit rate almost always looks healthy because the same small set of frequently-used pages gets read millions of times, making the denominator enormous and completely burying the actual misses.
The hit rate metric lies on long-running servers. I learned this the hard way.
The Real Diagnostics
Instead of lifetime counters, you need to look at what’s happening to the pool right now:
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_free';
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_total';
SHOW STATUS LIKE 'Innodb_buffer_pool_wait_free';
SHOW STATUS LIKE 'Innodb_data_reads';
Here’s what each one actually means:
pages_free: empty slots available in the buffer pool right now. If this is 0, the pool is completely full and InnoDB has to evict something every single time it needs to load a new page. Zero free pages = zero breathing room.
pages_total : total number of slots. MySQL stores data in 16KB pages, so a 512MB pool holds 32,768 pages total.
wait_free: this is the one that matters most. It increments every time InnoDB needs to load a page but the pool is full, so it has to forcibly evict a dirty page (a page with changes not yet written to disk), wait for that page to flush to disk, and then load the new page. This is a complete stop the query is frozen waiting for disk I/O just to make room for itself.
Innodb_data_reads : total physical disk reads since startup. The higher this is relative to uptime, the more your database is living on disk.
Our results:
Innodb_buffer_pool_pages_free → 0
Innodb_buffer_pool_pages_total → 32,768
Innodb_buffer_pool_wait_free → 8,638,760
Innodb_data_reads → 14,166,222,090
I stared at these numbers for a moment.
Pages free = 0. Always. The pool was 100% full at all times, no exceptions.
wait_free = 8,638,760. Eight point six million times, InnoDB had to stop mid-query, evict a dirty page, wait for disk, and then continue. Eight point six million frozen moments. Every user who ever loaded a slow page, every report that took too long — at least some of that was this.
data_reads = 14 billion. Fourteen billion physical disk reads. Not cache hits. Actual I/O.
Then I checked the database size:
SELECT
table_schema AS db,
ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;
our_app_db → 93,642 MB (~91 GB)
91GB. With a 512MB buffer pool. Running since 2018.
Nobody had changed this config in years. We just kept building features on top of a database that was essentially running without a proper memory cache.
What InnoDB Is Actually Doing With That Memory
Before I get to the fix, I want to explain something that genuinely surprised me…
InnoDB is smarter about memory management than I expected. Understanding this also explains why the hit rate looked fine even when things were broken.
InnoDB caches but Why a Simple LRU Would Break Everything
The obvious approach is a plain LRU (Least Recently Used) cache: keep the most recently accessed pages, evict the least recently used ones. Simple.
But this breaks badly for databases, and here’s a concrete example.
Imagine a manager runs a monthly sales report that scans the entire orders table… let’s say, 20GB of data.
With a plain LRU, MySQL loads all those pages into the buffer pool, kicking out everything else to make room. The report finishes in a few minutes. That data is never touched again.
Now the buffer pool is full of cold report data, and all the genuinely hot pages… the ones your app uses for every user request, have been evicted. One report query just wiped your entire cache.
InnoDB solves this with something called the Midpoint Insertion Strategy.
The Two-Zone Buffer Pool
InnoDB splits the buffer pool into two zones:
┌──────────────────────────────────────────────────────────┐
│ YOUNG (HOT) — ~63% │ OLD — ~37% │
│ │ │
│ Pages proven to be useful │ All new pages land │
│ live here │ HERE first │
└──────────────────────────────────────────────────────────┘
▲
midpoint
new pages enter here
The key rule is this: new pages don’t go straight to the hot zone. Every page… regardless of what it is… enters at the midpoint, into the “old” zone first. It only gets promoted to the hot zone if it gets accessed again at least 1 second after being loaded.
This one rule is what protects hot data from table scans. A full table scan reads a page once, moves on within milliseconds, never comes back. Since the page never gets a second access after the 1-second threshold, it never qualifies for promotion. It slides toward the tail of the old zone and gets evicted — without touching your hot data.
New page read from disk
│
▼
Enters OLD zone (midpoint)
│
├── accessed again after >1000ms? ──YES──► promoted to YOUNG (hot) zone
│
└── never accessed again? ──────────────► slides to tail, evicted
You can check these thresholds on your server:
-- What % of the pool is the "old" zone (default: 37%)
SHOW VARIABLES LIKE 'innodb_old_blocks_pct';
-- How long before a page qualifies for promotion (default: 1000ms)
SHOW VARIABLES LIKE 'innodb_old_blocks_time';
What Actually Stays Hot in an ERP
With this system, InnoDB naturally figures out your working set… the subset of data your app genuinely needs regularly. For an ERP, that tends to be:
- Index pages for frequently queried columns… hit on almost every query, permanently hot
- Master data products, customers, suppliers, chart of accounts…referenced across every module constantly
- Active records open purchase orders, unpaid invoices, current fiscal year transactions, live inventory movements
- Small lookup tables tax codes, units of measure, currencies, warehouses… tiny in size but queried on nearly every operation
- InnoDB system pages internal bookkeeping MySQL always needs
What gets evicted: closed fiscal years, archived orders, old journal entries, historical audit logs. In an ERP, this historical data is enormous. It’s the bulk of that 91GB. InnoDB loads it when needed, it doesn’t get re-accessed regularly, and it gets evicted quietly.
This is why you don’t need 91GB of buffer pool for a 91GB database… and it’s why our 99.3% hit rate looked fine. The hot working set fit within 512MB and was genuinely served from cache. The problem was everything just outside that slice… every slightly less-frequent query that hit disk, bumped something else out, and contributed to 8.6 million wait_free stalls.
You can inspect exactly which tables are winning the buffer pool right now:
SELECT TABLE_NAME, COUNT(*) AS pages_in_pool,
SUM(DATA_SIZE)/1024/1024 AS mb_in_pool
FROM information_schema.INNODB_BUFFER_PAGE
WHERE TABLE_NAME IS NOT NULL
GROUP BY TABLE_NAME
ORDER BY pages_in_pool DESC
LIMIT 20;
The Fix
We can’t fit 91GB into 16GB of RAM.
The goal is different: give InnoDB enough space to cache the working set so most queries hit memory, and disk I/O drops from “constant” to “occasional.”
On a monolith where the app and database share one server, you first need to budget memory honestly across every process:
Process What it does Estimated
RAM OS + kernel Linux itself ~1G
Apache Web server ~200MB
PHP-FPM Laravel app workers ~1–2G
Redis Cache and queues ~300MB
Queue workers Background jobs ~300MB
InnoDB buffer pool What we’re fixing ~10G
Headroom So you don’t hit swap ~1G
The config we landed on:
[mysqld]
innodb_buffer_pool_size = 10G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_chunk_size = 128M
What are instances? The buffer pool can be divided into multiple independent regions. When many queries run simultaneously, they don’t all fight for a single lock — each instance has its own. One instance per 1–2GB is a reasonable rule of thumb.
What are chunks? The unit MySQL uses when resizing the buffer pool at runtime without a full restart. Smaller chunks = finer control. Aim for 2–5% of your total buffer pool size.
There’s a formula MySQL requires you to satisfy:
buffer_pool_size = chunk_size × instances × N
(N = any whole number)
With our config:
128M × 8 = 1G
10G / 1G = 10 ✓ clean whole number
Total chunks = 80 ✓ well under the 1000 limit
If your config doesn’t satisfy this formula, MySQL silently rounds innodb_buffer_pool_size up to the nearest valid value. On a tight server that silent rounding can push memory usage past physical RAM and cause the OS to kill MySQL. Always verify the math.
Apply the config and restart (on AlmaLinux/RHEL):
sudo systemctl restart mysqld
A few more settings worth adding:
# Flush transaction log every second instead of every commit.
# Tiny durability tradeoff (up to 1 second of data on a hard crash)
# but noticeably better write performance for frequent operations.
innodb_flush_log_at_trx_commit = 2
# Larger redo log = fewer forced checkpoint flushes = fewer sudden perf spikes.
innodb_log_file_size = 512M
# More I/O threads to take advantage of available CPU cores.
innodb_read_io_threads = 6
innodb_write_io_threads = 6
# How aggressively InnoDB flushes dirty pages in the background.
# Default (200) was tuned for HDDs in 2005. Use 1000 for HDD, 2000 for SSD.
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000
What to Watch After the Change
The buffer pool starts empty after every restart. For the first 10–15 minutes performance feels similar to before… that’s normal. InnoDB is pulling the working set back into memory. Give it time and real traffic.
Watch the pool fill up:
SELECT
ROUND((pages_data / pages_total) * 100, 1) AS fill_pct
FROM (
SELECT variable_value AS pages_data
FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data'
) d,
(
SELECT variable_value AS pages_total
FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total'
) t;
Run it every minute or two. Watch it climb from 0% toward 80–90%.
Most importantly, check that wait_free stops growing:
SHOW STATUS LIKE 'Innodb_buffer_pool_wait_free';
Run this a few times a minute apart. If the number has stopped increasing, you’re done — the pool is large enough for the working set. If it keeps climbing, your working set is still larger than the pool and you’ll need to look at archiving historical data, adding a read replica for reports, or upgrading hardware.
What I Took Away From This
1. The hit rate metric lies on long-running servers. Lifetime counters look healthy even when the pool is under severe pressure. wait_free and pages_free are what actually tell the truth.
2. Inherited configs are a hidden risk. When you join a team and take over an existing system, the infrastructure config is easy to overlook. Everyone assumes someone else already reviewed it. Often nobody did.
3. “It’s always been like this” is not the same as “it’s fine.” The app ran for years with this misconfiguration. That doesn’t mean it was running well. It means nobody had compared it to what it could be.
4. You don’t need the whole dataset in RAM… just the working set. ERPs accumulate years of historical data that rarely gets queried. InnoDB is smart enough to figure out what to keep hot, but only if you give it enough room.
5. On a shared server, budget memory honestly. “Give MySQL 80% of RAM” only applies to a dedicated DB server. On a box also running PHP-FPM, Redis, and queue workers, the math changes completely.
6. Default configs are written for old hardware. The MySQL default for innodb_buffer_pool_size is 128MB. It hasn't been updated to reflect that modern servers routinely have 16GB, 32GB, or 64GB of RAM. Never assume defaults are appropriate for your setup.
We didn’t build this system. We inherited it, we maintained it, and eventually we dug deep enough to find this. The fix itself was five minutes. Understanding why took much longer.
If you’ve joined a team that maintains an application you didn’t build… go check your innodb_buffer_pool_size. Check wait_free. Run the diagnostics. You might find something that's been quietly costing you for years.
I did.
This is part of an ongoing series where I write about things I discover while working as a software engineer in a small team… mostly by breaking things and figuring out why.
Previous: Things You Should Know About How a Database Works
The MySQL docs on InnoDB Buffer Pool Configuration are worth bookmarking. All diagnostic queries in this article work on MySQL 5.7+ and 8.x.