We told them not to buy more RAM.
A home-goods store had a morning of 'too many connections' errors. The obvious fix was more RAM. The data said don't spend it: the working set already fit in memory, and the answer was in the application's own rules.
In this article 3 sections
- A home-goods store's database started returning ERROR 1040: Too many connections one morning, right after its temp-table space filled up.
- The obvious fix was more RAM: a database larger than its buffer pool looks starved. Growing the pool would have cost money and fixed nothing.
- The measurement overruled it. The buffer pool was hitting its cache almost every time and still had room to spare. The working set was already in memory.
- The real cause was one discount-rule table that had grown far beyond what the store needed. The fix was in the application's rules, not in more RAM.
It started as a morning of connection errors. The temp-table space, on tmpfs, had filled, and seconds later the database began answering ERROR 1040: Too many connections. Our on-call engineer had it on screen within minutes, and we worked it together with the store's developers from the start.
temp-table space (tmpfs) ........ FULL
… seconds later …
mysqld: ERROR 1040 (HY000): Too many connectionsThe expensive answer, and why we set it aside
A database bigger than its buffer pool, and free memory on the machine. Every surface number points the same way: give the pool more RAM, done. It looks like progress and it costs real money. The measurement said it would fix nothing.
| THE EXPENSIVE ANSWER | WHAT THE DATA SAID |
|---|---|
| The database is bigger than the pool. Grow the pool. | The cache-hit ratio was near-perfect. The working set already lived in memory. |
| A bigger cache fixes the overflow. | The pool already had unused room. More RAM would cache data that was already cached. |
| THE DATA WONAdd hardware, problem solved. | The oversized table keeps growing behind the bigger pool until it overflows again. |
Two readings settled it. The buffer pool almost never missed, and it still had free pages left. Together they say one thing: the working set already fit entirely in memory. There was no data waiting on disk for a bigger cache to hold.
The real cause was in the table sizes
With memory ruled out, we went to the table sizes. One table stood out: the discount-rule combination table, by far the largest thing in the database.
That table is generated from the store's active discount rules, and combinations grow with the square of the rule count. Over time the rules had accumulated, and the table had grown with them. Queries against it were overflowing the temp-table space, and that overflow cascaded into connection exhaustion. The 1040 error was the last domino, not the first.
# table name is an illustrative rename.
> table sizes, largest first
discount_rule_combinations largest table by a wide margin
↳ generated from the active discount rules
↳ combinations grow with the SQUARE of the rule countThe disciplined answer and the expensive answer pointed opposite ways
A bigger pool would have bought time, and the table would have kept growing behind it. Only one answer touched the actual cause: tidy up the discount rules, cap the combinations, and keep the hardware as it was. The store's developers made the change, and the errors stopped.
The machine was never the problem. The data showed the working set already fit in memory, so the fix was in the application's rules, not more RAM. The most useful thing we could hand this customer wasn't more of our hardware. It was a reason not to buy it.
“Sometimes the most valuable thing a host can tell you is: don't spend the money.”
Straight answers
My database threw 'ERROR 1040: Too many connections'. Should I raise the connection limit?+
Usually no. A higher ceiling doesn't fix whatever is holding connections open, it lets more of them pile up before anything trips. A sensible limit fails fast and keeps the damage small. Find what is holding sessions before you raise the number.
Should I add more RAM if my database is bigger than my buffer pool?+
Not on that fact alone. The number that decides it is the buffer-pool hit rate, not the size on disk. If the pool is already hitting almost every time and has free pages, the working set fits in memory and more RAM would cache data that is already cached.
What is a normal InnoDB buffer pool hit ratio?+
Above 99 percent once the working set fits in memory. If the pool is already there and still has free pages, a bigger pool buys nothing; look at the queries instead.
Why does my MySQL temp-table space keep filling up?+
Usually a query producing far more intermediate rows than the result needs. Here a very large discount-rule table overflowed the temp-table space, which then cascaded into connection exhaustion. The fix was in the data, not the disk.
Will EuroVPS tell me not to buy an upgrade?+
Yes, when the data says the upgrade fixes nothing. We would rather trace the real cause than sell you hardware that hides it. In this case the honest answer was: don't grow the buffer pool, tidy up the rules.
Before you buy the upgrade, let us read the numbers.
If a bigger box would fix it, we'll say so. If it would only hide the real cause, we'll say that too, with the measurement.
Talk to an engineerYou’ve reached the end.
Put the reading to work
What are
you running?
Tell us about the workload, traffic and recovery requirements. Start with the server builder, or talk through the details with an engineer.
Written by the engineers who build and run the infrastructure it describes.Talk to an engineer