Why keep counters
Once an application is live you want to know what it's doing. How many pages were served, and which ones. How many API calls each customer made. How many emails went out and how many failed. How long the payment provider takes to answer, and how long the slowest request took this morning.
These are all the same shape: a name, maybe an argument to split it further (the route, or the customer), and a number. For a count the number is 1, and for a timing it's how many seconds something took. What you want to keep is the total, the count, the smallest and the largest, for each minute, so the minutes can be added up into hours, days and months for charts. The average is just the total divided by the count.
The table
One row holds one counter for one minute:
CREATE TABLE counter (
id BIGINT NOT NULL AUTO_INCREMENT,
hash CHAR(32) NOT NULL,
name VARCHAR(64) NOT NULL,
argument VARCHAR(250) NULL,
timeslot BIGINT NOT NULL,
total FLOAT NOT NULL,
minimum FLOAT NOT NULL,
maximum FLOAT NOT NULL,
count INT NOT NULL DEFAULT 0,
PRIMARY KEY (id),
UNIQUE INDEX (hash),
INDEX (timeslot),
INDEX (name)
);The timeslot is the time rounded down to the start of its minute, as a Unix timestamp. The hash is the row's identity: the name, the timeslot and the argument, hashed together. A unique index across those three columns would almost do, except that MySQL treats every NULL as different from every other NULL in a unique index, so a counter with no argument would get a new row each time instead of adding to the one that's there.
function counterSlot(): int {
$now = time();
return $now - ($now % 60);
}
function counterHash(string $name, ?string $argument, int $slot): string {
$key = $argument === null ? "$name:$slot" : "$name:$slot:$argument";
return substr(hash('sha256', $key), 0, 32);
}Writing every value
The simple way is to write each value as it happens. MySQL can insert the row if it isn't there yet, or add to it if it is, in one statement:
function counterValue(PDO $db, string $name, ?string $argument, float $value): void
{
$slot = counterSlot();
$q = $db->prepare("
INSERT INTO counter
(hash, name, argument, timeslot, total, minimum, maximum, count)
VALUES
(:hash, :name, :argument, :timeslot, :total, :minimum, :maximum, 1)
ON DUPLICATE KEY UPDATE
total = total + VALUES(total),
minimum = LEAST(minimum, VALUES(minimum)),
maximum = GREATEST(maximum, VALUES(maximum)),
count = count + 1
");
$q->execute([
':hash' => counterHash($name, $argument, $slot),
':name' => $name,
':argument' => $argument,
':timeslot' => $slot,
':total' => $value,
':minimum' => $value,
':maximum' => $value,
]);
}(MySQL 8.0.20 and later warn that VALUES() here is deprecated; the replacement is to name the new row with AS new and write new.total. Either works.)
Counting is a call with 1, and timing is a call with the seconds something took:
counterValue($db, 'page.view', $route, 1);
$start = microtime(true);
$result = $payments->charge($order);
counterValue($db, 'payment.time', null, microtime(true) - $start);Reading them back
Because every row is a minute, any longer period is a GROUP BY. Here's an hour at a time:
SELECT timeslot - timeslot % 3600 AS hour,
SUM(total) / SUM(count) AS average,
MIN(minimum) AS fastest,
MAX(maximum) AS slowest,
SUM(count) AS calls
FROM counter
WHERE name = 'payment.time' AND timeslot >= :since
GROUP BY hour
ORDER BY hour;What goes wrong
This works, and on a quiet site it's all you need. The trouble starts when the site gets busy, which is exactly when you most want the numbers.
Every value is a write. A request that counts the page view, times two queries and counts an email makes four round trips to the database, and each one is a write that MySQL has to make durable before it answers. The counters start to cost more than the work they're measuring.
Worse, the busiest counters all land on the same row. Every request in the same minute updates the one page.view row for that route, and InnoDB locks a row while it's being changed, so those updates queue up behind each other. The more traffic there is, the longer each request waits to record that it happened.
What we'd like is to add up the minute somewhere fast, and write it once when the minute is over.
Counting in shared memory
PHP requests don't share any memory of their own, but APCu gives every request running under the same PHP-FPM server a shared key-value store, and it has atomic operations: apcu_inc adds to a number without any other request being able to get in between the read and the write. So we can keep the minute's totals there.
Each counter for each minute becomes four keys, one for each of the total, the count, the minimum and the maximum:
counter␟t␟payment.time␟1768906800␟N total
counter␟c␟payment.time␟1768906800␟N count
counter␟n␟payment.time␟1768906800␟N minimum
counter␟x␟payment.time␟1768906800␟N maximum
counter␟t␟page.view␟1768906800␟S/products with an argumentThe separator is the ASCII unit separator, character 31, which never turns up in a name. The argument goes last, with N for none or S in front of a real one, so splitting the key into five parts still works if the argument happens to contain a separator.
apcu_inc only adds integers, so values are stored as fixed point, multiplied by a million. A timing of 0.0347 seconds goes in as 34700. Six decimal places is already more than the FLOAT columns keep, and as the keys only ever hold one minute's worth before they're written out and deleted, there's no danger of running past the 64-bit limit.
final class Counters {
const SCALE = 1000000; // six decimal places, as integers
const SEP = "\x1f"; // the ASCII unit separator
const SEEN = 'counter.seen'; // the newest minute we know has started
const LOCK = 'counter.lock'; // held by whichever request is flushing
static PDO $db;
private static ?bool $available = null;
static function available(): bool {
return self::$available ??=
(function_exists('apcu_enabled') && apcu_enabled());
}
static function add(string $name, ?string $argument, float $value): void {
if (!self::available()) {
counterValue(self::$db, $name, $argument, $value);
return;
}
$slot = counterSlot();
$scaled = (int) round($value * self::SCALE);
$encoded = $argument === null ? 'N' : 'S' . $argument;
$tail = self::SEP . $name . self::SEP . $slot . self::SEP . $encoded;
$prefix = 'counter' . self::SEP;
apcu_inc($prefix . 't' . $tail, $scaled); // total
apcu_inc($prefix . 'c' . $tail, 1); // count
self::bump($prefix . 'n' . $tail, $scaled, true); // minimum
self::bump($prefix . 'x' . $tail, $scaled, false); // maximum
self::maybeFlush($slot);
}Smallest and largest
There's no atomic minimum in APCu, but there is compare-and-swap: apcu_cas replaces a value only if it still holds what you last read. So we read the current minimum, give up if ours isn't smaller, and otherwise try to swap ours in. If another request changed it in between, the swap fails and we go round again with the new value.
private static function bump(string $key, int $value, bool $isMin): void {
while (true) {
$current = apcu_fetch($key, $found);
if (!$found) {
// create it, or if someone else just did, compare with theirs
if (apcu_add($key, $value)) return;
continue;
}
// not a new extreme
if ($isMin ? $value >= $current : $value <= $current) return;
// swap ours in, or if it changed under us, read it again
if (apcu_cas($key, $current, $value)) return;
}
}Nothing ever waits here. The loop only goes round again when another request has just made progress, and most of the time the value isn't a new extreme at all, so it returns after one read.
Flushing a finished minute
Here's the part that makes the whole thing simple. All the values for a minute go into keys with that minute's timeslot in them. Once the clock moves on to the next minute, nothing will ever write to the old keys again: they're frozen. So writing out a finished minute needs no atomic reset and has no window where an increment could go missing. It's just read the keys, write them to the database, and delete them.
Every call to add checks whether a new minute has started since the last flush. That's one fetch of a shared key, and almost always the answer is no. When the minute has rolled over, the first request to notice takes a lock and does the flush; any others that notice at the same moment see the lock and carry on with their work.
private static function maybeFlush(int $currentSlot): void {
$seen = apcu_fetch(self::SEEN, $found);
if (!$found) {
apcu_add(self::SEEN, $currentSlot);
return;
}
// no minute has finished since the last flush
if ($currentSlot <= $seen) return;
// another request is flushing
if (!apcu_add(self::LOCK, 1, 30)) return;
try {
// check again now we hold the lock
if ($currentSlot > apcu_fetch(self::SEEN)) {
self::flushBefore($currentSlot);
apcu_store(self::SEEN, $currentSlot);
}
} finally {
apcu_delete(self::LOCK);
}
}
static function flush(): void {
if (self::available()) {
self::maybeFlush(counterSlot());
}
}The lock is an apcu_add, which only succeeds for whoever creates the key. It's given a time to live of 30 seconds, so if the request holding it dies part way through, the lock goes away by itself and the next minute's flush picks up what was left.
The flush walks every counter key, skips the ones for the current minute, gathers the four parts of each counter together, and deletes the keys as it goes:
private static function flushBefore(int $before): void {
$counters = [];
foreach (new APCUIterator('/^counter' . self::SEP . '/') as $entry) {
$parts = explode(self::SEP, $entry['key'], 5);
if (count($parts) !== 5) continue;
[, $field, $name, $slot, $argument] = $parts;
// the current minute is still being written to
if ((int) $slot >= $before) continue;
$group = $name . self::SEP . $slot . self::SEP . $argument;
$counters[$group] ??= [
'name' => $name,
'argument' => $argument === 'N' ? null : substr($argument, 1),
'slot' => (int) $slot,
't' => 0, 'c' => 0, 'n' => null, 'x' => null,
];
// a finished minute, so this value is final
$counters[$group][$field] = $entry['value'];
apcu_delete($entry['key']);
}
if ($counters) {
self::write(array_values($counters));
}
}Then the whole lot goes to MySQL as one statement. It's the same upsert as before, with many rows instead of one, and it adds to whatever is already there rather than replacing it:
private static function write(array $counters): void {
$tuples = [];
$values = [];
foreach ($counters as $c) {
$tuples[] = '(?, ?, ?, ?, ?, ?, ?, ?)';
array_push($values,
counterHash($c['name'], $c['argument'], $c['slot']),
$c['name'],
$c['argument'],
$c['slot'],
$c['t'] / self::SCALE,
($c['n'] ?? $c['t']) / self::SCALE,
($c['x'] ?? $c['t']) / self::SCALE,
$c['c'],
);
}
$q = self::$db->prepare("
INSERT INTO counter
(hash, name, argument, timeslot, total, minimum, maximum, count)
VALUES " . implode(', ', $tuples) . "
ON DUPLICATE KEY UPDATE
total = total + VALUES(total),
minimum = LEAST(minimum, VALUES(minimum)),
maximum = GREATEST(maximum, VALUES(maximum)),
count = count + VALUES(count)
");
$q->execute($values);
}
}Adding rather than replacing matters when there's more than one server. Each server has its own APCu and flushes its own minute, and the rows simply add up in the database.
Starting up
The class needs the database connection, and it's worth a flush at the end of each request too, so a finished minute gets written out as soon as possible:
Counters::$db = $db;
register_shutdown_function(function () {
try {
Counters::flush();
} catch (Throwable $e) {
// counting must never break a page
}
});
Counters::add('page.view', $route, 1);Where APCu isn't available, add falls back to writing each value straight to the database, so the same calls work everywhere. That includes command-line scripts: APCu is switched off on the command line by default (apc.enable_cli=0), so apcu_enabled() says no even though the functions exist, and a long import script writes its counters directly.
What it costs
Several writes per request become about one batched write per minute per server, and the busy rows no longer queue up. The hot path is two atomic adds, two compare-and-swaps that nearly always stop after one read, and one fetch to see whether the minute has changed.
In return:
- If PHP-FPM restarts, whatever was in shared memory and not yet written is lost: at most a minute of counts. That's fine for metrics. For anything that must never lose a value, such as money, keep the direct write.
- A finished minute is written by the next request that comes along after it. On a site that goes quiet, the last minute waits in memory until the next visitor.
- Each server keeps its own counts until it flushes, so the database is up to a minute behind.
For counting and timing a live application, that's a very good trade.