When a cache scales the wrong way

Four small files sit in this service's cache folder. The largest is 35 bytes. Together they hold three counter numbers, and they are read on every single counter request.

They exist so the counting path does not have to ask the database four questions it almost always answers with "no". Does this counter exclude its owner's own visits? Track outbound clicks? Track paths through the site? Reload itself? For more than 99% of counters every answer is no, and a file holding the handful of numbers where the answer is yes is cheaper to consult than four indexed queries.

It is cheaper. It is also the kind of cheap that reverses.

What was measured

The same question — "is this counter in the list?" — asked two ways, at six list sizes. The file way: read it, decode the JSON, look for the number. The database way: one prepared statement against an indexed column. Two thousand repetitions each, five rounds, median taken.

At today's size the file wins by eight times: 0.0202 milliseconds against 0.1630. At a thousand numbers they are level. At ten thousand the file costs twelve times more, and at a hundred thousand it costs 135 times more, because by then it is 578 kilobytes that have to be read and parsed on every hit.

The database line does not move. 0.16 milliseconds at two rows and 0.16 milliseconds at a hundred thousand: that is what an index is for, and it is easy to forget how much work is hidden behind how boring that line looks.

The uncomfortable part

The crossover is somewhere between 100 and 1,000 numbers. This service has 2,218 counters that were active in the last thirty days.

So if the exclusion feature were a success — if everybody who has a counter switched on "don't count my own visits" — the file would hold 2,218 numbers, weigh 11 kilobytes, and cost 0.43 milliseconds per hit instead of 0.02. At yesterday's 18,423 hits that is eight seconds of work a day to avoid a query that would have taken three.

The optimisation is fastest when the feature is least used. It does not degrade gradually with load, the way a slow query does. It degrades with adoption, which is the one axis nobody watches, because adoption going up is supposed to be the good news.

Why it is still there

Because it is right today, and "right today" is allowed to be the reason for something, as long as somebody wrote down when it stops being true.

Three numbers in three files. Eight times cheaper than the alternative, on a machine where the counting path is the only thing that has to be fast. Replacing it now with the query it beats would be a worse system justified by a hypothetical.

What it needs is the tripwire, not the rewrite. The file is written from the database anyway, by the code that changes a setting — that is the natural place to notice that the list has grown past a few hundred entries and say so. A cache with a documented ceiling is a decision. A cache without one is a bet that nobody has agreed to.

The general shape

This is not an argument against caching in a file. It is an argument for knowing which way your cache scales.

Most caches get better under load: more requests, more hits, better ratio. This one is the other kind. Its cost per request is a function of how much data it holds, and what it holds grows with the thing the service is trying to encourage. Every read pays for every entry, including the 2,215 that have nothing to do with the visitor being counted right now.

The question to ask of any lookup table held in memory or in a file is not "how fast is it" but "what makes it grow, and what happens when that thing goes well". If the answer is "it gets slower", the size limit belongs in the code, next to the thing that writes the file, on the day you build it.