Billing-grade telemetry from 15,000 devices

A log here is an invoice line

The network sells ad time on screens in residential elevators and in malls. Every time a player shows an ad it writes one line: which order, which content, which screen, start and end. Advertisers pay for what really aired, and the audit reports they receive come from these lines. So the logs are billing evidence, and they come from devices spread in thousands of buildings, each one with the internet that building has.

I built this pipeline from the first screen, and it changed a lot until we got to about 15,000 devices. What follows is the shape it has today and the steps that got it there.

On the device: memory first, then batches

Players were cheap hardware, many of them with old SD cards. Writing to the card on every play kills it in months, so the player keeps the lines in memory and dumps them to a file every 5 minutes. Every 15 minutes a routine joins the pending files, compresses and uploads them.

Only after the server confirms it received a file does the device delete it, and that is the rule that matters most. If the connection drops in the middle, nothing happens, the file goes again in the next cycle. A screen that stays six hours offline arrives with six hours of logs in the queue, and they go in order.

Each line also gets a UUID generated on the device. That costs bytes, and it pays back later, because a retry is always possible and the server needs a way to know it already has that play.

The ingestion server

Uploads come by HTTP to a PHP endpoint. Today it writes the whole upload as one Kafka message and answers the player only after the broker accepted it. If the broker accepted and the answer got lost on the way back, the player sends the same file again. That is at-least-once from the screen to Kafka, and I prefer it this way: a duplicate I can remove later, a play that never left the device I can't recover.

Before Kafka the same endpoint wrote files to disk, and a batch job picked them up. That is where the most of the engineering went for years.

The MySQL years: sort before insert

LOAD DATA INFILE is very fast if you give it a good file. We had the opposite: thousands of small files from different players, different days, out of order. So I built a shell pipeline where every step was chosen by benchmark, timing each command in milliseconds. tr to strip characters is much faster than any search and replace, sort is hard to beat, and there was one step in Perl. The output was files of 100,000 to 500,000 lines, already sorted in the order of the table's index, and inserting presorted data was orders of magnitude faster than inserting it mixed.

This is a later version of that script, still in the repository in 2022, with generic names. The MarkLap function is there because I measured every run:

import, MySQL era (simplified)

LOCKFILE=/var/tmp/lock_import_logs
( set -o noclobber; echo "$$" > "$LOCKFILE" ) 2>/dev/null || exit 0   # one run at a time
trap 'rm -f "$LOCKFILE"' INT TERM EXIT

MarkLap() {                      # benchmark of each step
  t1=$(date +%s.%N); echo "$1 ($(echo "$t1 - $t0" | bc)s)"; t0=$t1
}

# keep only data characters, drop broken lines, sort by boot time and sequence
cat $PENDING | \
  tr -c -d '[:alnum:][= =]-:;\n' | \
  grep '[[:blank:]]' | \
  sort -t';' -k1,1n -k2,2n > "$dir/plays.log"

mysql --local-infile -e "
  LOAD DATA LOCAL INFILE '$dir/plays.log'
  IGNORE INTO TABLE audit.play_log
  FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n'
  (@boot, @seq, @order_id, @content_id, @type, @start, @end, @device_id)
  SET device_id = @device_id, order_id = @order_id, content_id = @content_id,
      boot_at = @boot, seq = @seq, start_at = @start, end_at = @end;"
MarkLap "play_log $(wc -l < "$dir/plays.log") rows"

IGNORE was the dedup of that era, so a line with a key that is already there gets skipped. It works, and the price is that MySQL checks the unique index in every row, which hurt more as the table grew.

By then the audit table had passed 2 billion rows. It was partitioned by week, so a query for one campaign touched only the weeks it needed, and the summary tables were updated incrementally, because a full recalculation took days. I only ran a full one when there was no other way. I tuned MySQL a lot in those years; I also tried Percona, and the difference came from the configuration much more than from the distribution.

Kafka and ClickHouse

In 2019 I started testing ClickHouse: about 30 test servers of different sizes, a cluster of three in the cloud first, later a bare metal machine. In 2022 it became the main store for the logs. MySQL kept receiving the same data for about one more year as a backup, in case something went wrong, and around 2023 it stopped getting logs for good. Kafka came in the same period, between the upload and the database. We designed that part together in the team, and a teammate wrote and shipped the final consumer.

Here is how a play travels today, from the screen until the reports:

proof-of-play path

player (memory → file every 5 min → upload every 15 min, UUID per line)
  → HTTP upload → one Kafka message per upload (topic retention: 3 days)
  → "OK" to the player only after the broker accepted
  → importer: reads a batch of messages, removes repeated UUIDs inside the batch
  → stage table with the last 3 days → main play tables (ReplacingMergeTree)
  → summaries every 10 minutes → audit reports, billing, occupancy

Three days shows up twice, and it's on purpose. The topic keeps 3 days, so if the importer stops, the uploads wait in Kafka and nothing is lost; once it stopped for about 39 hours and the queue just grew and came down after. The stage table also keeps 3 days, so a screen that was offline over the weekend and sends Friday's plays on Monday still meets the rows that arrived before and the duplicates get caught there.

Sorting before insert still pays here, in another form. ClickHouse wants few big inserts, so the importer works in large batches. Recent runs inserted 155,000 to 643,000 rows in 7 to 20 seconds, around 20,000 to 33,000 rows per second per run, and a full day is about 200 million rows193.9M + 5.4M rows in 24 h, system.part_log, 2026-10-05.

We put a TTL of one month on the UUID column. A full UUID on billions of rows is expensive, and after a month the chance of a late retry is close to zero, so the key expires and the disk stays reasonable.

What "billing-grade" means here

I used to say we never lost a log. It isn't true, and I stopped saying it. Over ten years and 15,000 devices in the field we had duplicates and we had gaps, and some of them were hard to even measure at the source. What we always had was the map. For any gap we could say which screen and which hours, and when the fault was ours, the advertiser got compensated. Nothing got lost without a trace, and every invoice had the lines behind it.

All of this comes from boring decisions, like deleting only after the ack, a key per line made at the edge, a buffer with days of slack between each stage, and summaries that you can recalculate from the raw data.

What I'd reuse anywhere

On flaky devices, write to memory and ship in batches. Delete after the ack, never before.
Make the dedup key at the source. The server can't invent it later.
Leave days of buffer between stages (topic retention, stage table) so a stop becomes a queue.
Sort before inserting, in any database with an index or a sort key, and measure each step of the pipeline.
Say the honest guarantee. "Traced and reconciled" survives an interview question that "zero loss" doesn't.

If you take one thingDesign for the screen that stays six hours offline. When the pipeline handles that case without anyone noticing, the normal day takes care of itself.

Filed under the problem it solves