Streaming Live Video View Counts With Server-Sent Events in PHP 8.4

Our watch pages were lying. A clip trending in Japan would pick up 40,000 views in the twenty minutes between an aggregation run and the next page-cache purge, and the number under the player sat frozen at whatever LiteSpeed had baked into the HTML. People noticed — mostly uploaders watching their own numbers, but also anyone who kept two tabs open on the same video and got two different figures.

The naive fix is polling. We shipped it, watched the traffic graphs for a day, and pulled it back out. What replaced it is a Server-Sent Events endpoint that pushes coalesced view counts, survives Cloudflare, and — after a rewrite — stopped pinning one PHP worker per open tab. Here is what it actually took.

The polling bill we were about to pay

Numbers first, because they decide the architecture:

• ~380k watch-page views per day across our four regional front-ends
• median time on a watch page: 6.2 minutes
• a 5-second poll means ~74 extra requests per session
• that is roughly 28M extra requests per day

Each of those requests is not free. It is a Cloudflare cache MISS by definition (the payload is per-video and changes constantly), a full request through LiteSpeed, a PHP worker checkout, a SQLite read, and a response. And about 95% of them returned a number identical to the previous one. We were paying full request cost to say "nothing happened."

Backing the interval off to 30 seconds kills the feature. The entire point is that the number moves while you are looking at it. If it only moves twice a minute, you may as well leave the cached HTML alone.

Why SSE won over WebSockets here

I want to be specific about this, because "just use WebSockets" is the default answer and it is wrong for this shape of problem:

• The data flow is one-directional. After the handshake the client never sends anything. A full-duplex protocol buys us nothing and costs us a second transport to operate.
• It is plain HTTP. It goes through Cloudflare's normal proxy path with normal headers. No Upgrade negotiation, no separate routing, no worrying about which proxy layer speaks WebSocket.
• Reconnect is in the spec. EventSource retries automatically and replays Last-Event-ID back to you as a request header. With WebSockets you hand-write that state machine, and you write it badly the first time.
• It degrades cleanly. If the stream never connects, the page keeps the server-rendered count. Nothing breaks; the number is just stale, which is where we started.
• text/event-stream is UTF-8 by specification. We push channel titles in the same frames sometimes, and having encoding pinned by the protocol rather than by a header everyone forgets is worth something when half your strings are CJK.

The HTTP/1.1 six-connections-per-origin limit used to make SSE genuinely painful — one open stream ate a connection slot for the life of the page. Under HTTP/2 that is gone, and every browser we care about negotiates h2 through Cloudflare. We still cap ourselves to one stream per tab, because legacy clients exist and because it is the right design anyway.

WebSockets win the day we add live comments or watch-party sync. We do not have those. Don't pre-pay for them.

Step one, stop hammering SQLite on write

Counting a view is a write, and SQLite serializes writers. At our peak of roughly 90 views/sec across regions, an UPDATE per view means every single one takes the write lock. WAL mode means readers don't block behind the writer, which is essential, but it doesn't make the writes cheap — and your SSE readers will start collecting SQLITE_BUSY if you let write pressure climb.

So we buffer in APCu per worker and flush at a threshold. If a worker gets recycled mid-buffer we lose up to 24 counted views. That is completely fine. This is a view counter, not a ledger.

<?php
declare(strict_types=1);

final class ViewCounter
{
    public function __construct(
        private readonly PDO $db,
        private(set) int $flushAt = 25,
    ) {}

    public function hit(string $videoId, string $region): void
    {
        $key = "vc:{$videoId}:{$region}";
        $n = apcu_inc($key, 1, $ok, 3600);

        if ($n < $this->flushAt) {
            return;
        }
        // Atomic claim: exactly one worker wins the swap and owns the flush.
        if (!apcu_cas($key, $n, 0)) {
            return;
        }
        $this->persist($videoId, $region, $n);
    }

    private function persist(string $videoId, string $region, int $delta): void
    {
        static $stmt = null;
        $stmt ??= $this->db->prepare(<<<'SQL'
            INSERT INTO video_views (video_id, region, views, updated_at)
            VALUES (:vid, :region, :delta, :ts)
            ON CONFLICT(video_id, region) DO UPDATE SET
                views      = views + excluded.views,
                updated_at = excluded.updated_at
        SQL);

        $stmt->execute([
            'vid'    => $videoId,
            'region' => $region,
            'delta'  => $delta,
            'ts'     => time(),
        ]);
    }
}

$db = new PDO('sqlite:/var/www/data/views.sqlite', options: [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$db->exec('PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL; PRAGMA busy_timeout=2000;');

(new ViewCounter($db))->hit($videoId, $region);

Two notes. apcu_cas() is doing real work here — without it, two workers crossing the threshold in the same millisecond both flush and you double-count. And we keep rows per (video_id, region) because our trending pipeline already shards that way; summing at read time costs nothing on a 40k-row table with the right unique index.

The stream endpoint

Things people get wrong about the SSE wire format, in the order they bit me:

• Every frame ends with a blank line — \n\n. A single \n and the browser buffers your event forever waiting for the terminator.
• Lines beginning with : are comments. That is your heartbeat mechanism, and it is not optional behind a CDN.
• id: sets Last-Event-ID, which the browser echoes back as a request header on reconnect.
• retry: sets the client's reconnect delay in milliseconds. Send it once, early.
• Any output buffering anywhere in the stack will silently destroy the whole thing.

<?php
declare(strict_types=1);
// public/stream/views.php

while (ob_get_level() > 0) { ob_end_clean(); }
ini_set('zlib.output_compression', '0');
ini_set('output_buffering', '0');
ini_set('implicit_flush', '1');
ob_implicit_flush(true);
set_time_limit(0);
ignore_user_abort(false);

header('Content-Type: text/event-stream; charset=utf-8');
header('Cache-Control: no-cache, no-store, no-transform');
header('X-Accel-Buffering: no');
header('X-LiteSpeed-Cache-Control: no-cache');

$ids = array_slice(array_values(array_filter(
    explode(',', $_GET['ids'] ?? ''),
    static fn (string $v): bool => (bool) preg_match('/^[A-Za-z0-9_-]{11}$/', $v),
)), 0, 12);

if ($ids === []) { http_response_code(400); exit; }

$db = new PDO('sqlite:/var/www/data/views.sqlite', options: [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$db->exec('PRAGMA journal_mode=WAL; PRAGMA busy_timeout=2000; PRAGMA query_only=ON;');

$in = implode(',', array_fill(0, count($ids), '?'));
$q  = $db->prepare(
    "SELECT video_id, SUM(views) AS views, MAX(updated_at) AS rev
       FROM video_views WHERE video_id IN ($in) GROUP BY video_id"
);

echo "retry: 3000\n\n";
flush();

$sent     = [];
$lastBeat = time();
$deadline = time() + 300;   // recycle this worker on our terms

while (time() < $deadline && !connection_aborted()) {
    $q->execute($ids);
    $changed = [];
    $rev = 0;

    foreach ($q->fetchAll(PDO::FETCH_ASSOC) as $row) {
        $v = (int) $row['views'];
        if (($sent[$row['video_id']] ?? -1) === $v) { continue; }
        $sent[$row['video_id']] = $v;
        $changed[$row['video_id']] = $v;
        $rev = max($rev, (int) $row['rev']);
    }

    if ($changed !== []) {
        echo "id: {$rev}\n";
        echo "event: views\n";
        echo 'data: ' . json_encode($changed, JSON_THROW_ON_ERROR) . "\n\n";
        $lastBeat = time();
        flush();
    } elseif (time() - $lastBeat >= 15) {
        echo ": ping\n\n";      // comment frame: keeps the edge from timing us out
        $lastBeat = time();
        flush();
    }

    usleep(2_000_000);
}

echo "event: rotate\ndata: {}\n\n";
flush();

The design choices worth defending:

• A 2-second tick, not a push per write. Coalescing to a fixed tick still reads as "live" to a human and cuts frame count by about 95% versus per-increment pushes.
• Only changed IDs, only when they differ from what this connection last sent. A video nobody is watching costs one 8-byte comment every 15 seconds.
• Absolute counts, never deltas. We tried deltas. One dropped frame desynced the display permanently. Absolute values are idempotent — a lost frame self-heals on the next tick. Send state, not events.
• A hard 5-minute deadline plus a rotate event. Long-lived PHP processes accumulate garbage. Ending on our schedule beats being killed on someone else's.
• 12 IDs max. A watch page is one main video plus up to eleven sidebar recommendations, all on one connection.
• connection_aborted() only reports the truth after a write attempt, which is why the heartbeat doubles as dead-peer detection.

Getting LiteSpeed and Cloudflare out of the way

Three separate layers wanted to buffer our stream.

PHP itself. The ob_end_clean() loop plus zlib.output_compression=0. Compression is the subtle one: a gzip encoder holds bytes until it has a block worth emitting, so your 40-byte frame arrives whenever the tenth one does. On a 2-second tick that is a 20-second lag with no error anywhere.

LiteSpeed. It honours X-Accel-Buffering: no. Add X-LiteSpeed-Cache-Control: no-cache too, or the page cache will cheerfully attempt to store an infinite response. We also excluded the path in .htaccess with a RewriteRule ^stream/ - [E=Cache-Control:no-cache] guard inside the <IfModule LiteSpeed> block — and only inside it, because Apache's parser chokes on comma-separated values in E= flags.

Cloudflare. It does not buffer text/event-stream, but two things still bite. Edge compression will re-introduce the gzip problem, and Cache-Control: no-transform is the header that reliably stops it. And there is an idle timeout in the ~100 second range — any stream quieter than that dies without the heartbeat. We also added a Cache Rule matching /stream/* set to bypass, because a broad "cache everything" rule elsewhere on the zone would otherwise try to swallow it.

The verification that actually settles arguments:

curl -N --compressed -H 'Accept: text/event-stream' https://example.com/stream/views?ids=...

Frames should land one at a time, two seconds apart. If they arrive in a single burst at the end, something is buffering. Run it against the origin IP with --resolve first to work out whether the problem is yours or the edge's.

The browser side is the easy part

const ids = [...document.querySelectorAll('[data-vid]')].map(el => el.dataset.vid).slice(0, 12);
const lang = document.documentElement.lang || 'en';
const compact = new Intl.NumberFormat(lang, { notation: 'compact' });
let es = null;

function connect() {
  es = new EventSource(`/stream/views?ids=${ids.join(',')}`);

  es.addEventListener('views', (ev) => {
    for (const [id, views] of Object.entries(JSON.parse(ev.data))) {
      const el = document.querySelector(`[data-vid="${id}"] .vw-count`);
      if (!el) continue;
      el.textContent = compact.format(views);
      el.title = views.toLocaleString(lang);
    }
  });

  // Server-initiated recycle: reconnect with jitter so 5k tabs don't sync up.
  es.addEventListener('rotate', () => {
    es.close();
    setTimeout(connect, 250 + Math.random() * 1000);
  });
}

document.addEventListener('visibilitychange', () => {
  if (document.hidden) es?.close();
  else if (!es || es.readyState === EventSource.CLOSED) connect();
});

connect();

Two details that matter more for our audience than they would for an English-only site.

Never format numbers server-side. 1,200,000 is "1.2M" in English, "120万" in Japanese and Chinese, and "120만" in Korean. Those are not decorations, they are how the number is read. Push raw integers over the wire and let Intl.NumberFormat with notation: 'compact' resolve it against the document language. This also means our LiteSpeed page cache can hold one HTML variant per locale and the live layer stays locale-agnostic.

Close the stream on visibilitychange. Before we did, background tabs were roughly 60% of our open connections. People open six videos, watch one, and leave the rest parked for an hour.

The PHP worker problem, and the Go broker that fixed it

Here is what killed version one. Every open SSE connection occupies one PHP worker for its entire life. Our LiteSpeed plan gives a bounded process pool. Three hundred concurrent watchers meant three hundred workers held hostage, and every other request on the site queued behind them. We found this out at 21:00 JST, which is exactly when you would expect to find it out.

There is no way around this inside PHP-FPM or LSAPI. PHP cannot hand a connection back to the server and go do something else. The options are: run streams on a separate pool with its own cap (buys time, still 1:1), adopt Swoole coroutines (a second runtime and a second deploy path), or put the connections somewhere cheap.

We went with cheap. About 120 lines of Go: one goroutine per connection at roughly 4 KB each, a single SQLite reader for the whole process, ticking once for everybody.

package main

import (
    "database/sql"
    "encoding/json"
    "fmt"
    "log"
    "net/http"
    "strings"
    "sync"
    "time"

    _ "modernc.org/sqlite"
)

type sub struct {
    ids  []string
    seen map[string]int64
    out  chan []byte
}

type hub struct {
    mu   sync.RWMutex
    subs map[*sub]struct{}
}

func (h *hub) poll(db *sql.DB) {
    rows, err := db.Query(`SELECT video_id, SUM(views) FROM video_views GROUP BY video_id`)
    if err != nil {
        log.Println("poll:", err)
        return
    }
    defer rows.Close()

    totals := map[string]int64{}
    for rows.Next() {
        var id string
        var n int64
        if err := rows.Scan(&id, &n); err == nil {
            totals[id] = n
        }
    }

    h.mu.RLock()
    subs := make([]*sub, 0, len(h.subs))
    for s := range h.subs {
        subs = append(subs, s)
    }
    h.mu.RUnlock()

    for _, s := range subs {
        changed := map[string]int64{}
        for _, id := range s.ids {
            if n, ok := totals[id]; ok && s.seen[id] != n {
                s.seen[id] = n
                changed[id] = n
            }
        }
        if len(changed) == 0 {
            continue
        }
        buf, _ := json.Marshal(changed)
        select {
        case s.out <- buf:
        default: // slow consumer: drop it, the next tick carries full state anyway
        }
    }
}

func (h *hub) serve(w http.ResponseWriter, r *http.Request) {
    flusher, ok := w.(http.Flusher)
    if !ok {
        http.Error(w, "streaming unsupported", http.StatusInternalServerError)
        return
    }
    ids := strings.Split(r.URL.Query().Get("ids"), ",")
    if len(ids) == 0 || ids[0] == "" {
        http.Error(w, "ids required", http.StatusBadRequest)
        return
    }
    if len(ids) > 12 {
        ids = ids[:12]
    }

    hdr := w.Header()
    hdr.Set("Content-Type", "text/event-stream; charset=utf-8")
    hdr.Set("Cache-Control", "no-cache, no-transform")
    hdr.Set("X-Accel-Buffering", "no")
    w.WriteHeader(http.StatusOK)
    fmt.Fprint(w, "retry: 3000\n\n")
    flusher.Flush()

    s := &sub{ids: ids, seen: map[string]int64{}, out: make(chan []byte, 4)}
    h.mu.Lock()
    h.subs[s] = struct{}{}
    h.mu.Unlock()
    defer func() {
        h.mu.Lock()
        delete(h.subs, s)
        h.mu.Unlock()
    }()

    beat := time.NewTicker(15 * time.Second)
    defer beat.Stop()

    for {
        select {
        case <-r.Context().Done():
            return
        case buf := <-s.out:
            fmt.Fprintf(w, "event: views\ndata: %s\n\n", buf)
            flusher.Flush()
        case <-beat.C:
            fmt.Fprint(w, ": ping\n\n")
            flusher.Flush()
        }
    }
}

func main() {
    db, err := sql.Open("sqlite", "file:/var/www/data/views.sqlite?mode=ro&_pragma=busy_timeout(2000)")
    if err != nil {
        log.Fatal(err)
    }
    db.SetMaxOpenConns(1)

    h := &hub{subs: map[*sub]struct{}{}}
    go func() {
        for range time.Tick(2 * time.Second) {
            h.poll(db)
        }
    }()

    // WriteTimeout MUST stay zero. Any non-zero value kills long-lived streams.
    srv := &http.Server{Addr: "127.0.0.1:8081", Handler: http.HandlerFunc(h.serve)}
    log.Fatal(srv.ListenAndServe())
}

Deployment is a systemd unit on 127.0.0.1:8081 with LiteSpeed reverse-proxying /stream/ to it. PHP still renders the page and still counts the views; it just stopped holding the sockets. Memory at 2,000 concurrent connections: 61 MB RSS. The PHP version had run out of workers at 300.

If you cannot run a daemon — which is the situation on three of our four hosts — the PHP version is still workable. Cap connections per IP, keep the recycle short, and know your exact worker count. Just don't discover the ceiling in production.

Load testing it honestly

The metric is not requests per second. It is propagation delay: the wall-clock gap between a view being counted and the number changing in a browser. And it has to be measured with N connections already open, because the failure mode is queueing, not throughput.

import asyncio, json, sqlite3, statistics, time
import httpx

URL     = "http://127.0.0.1:8081/?ids={ids}"
CANARY  = "dQw4w9WgXcQ"
IDS     = [CANARY, "kJQP7kiw5Fk", "9bZkp7q19f0"]
bumped: dict[int, float] = {}   # observed total -> perf_counter() when written


async def watcher(lat: list[float], ready: asyncio.Event) -> None:
    async with httpx.AsyncClient(timeout=None) as c:
        url = URL.format(ids=",".join(IDS))
        async with c.stream("GET", url, headers={"Accept": "text/event-stream"}) as r:
            ready.set()
            evt = None
            async for line in r.aiter_lines():
                if line.startswith("event:"):
                    evt = line[6:].strip()
                elif line.startswith("data:") and evt == "views":
                    v = json.loads(line[5:]).get(CANARY)
                    if v in bumped:
                        lat.append(time.perf_counter() - bumped[v])


async def bumper(db_path: str, n: int = 20) -> None:
    con = sqlite3.connect(db_path)
    for _ in range(n):
        con.execute(
            "UPDATE video_views SET views = views + 1 WHERE video_id = ? AND region = 'JP'",
            (CANARY,),
        )
        con.commit()
        (total,) = con.execute(
            "SELECT SUM(views) FROM video_views WHERE video_id = ?", (CANARY,)
        ).fetchone()
        bumped[total] = time.perf_counter()
        await asyncio.sleep(3)


async def main(db_path: str, clients: int = 1000) -> None:
    lat: list[float] = []
    ready = asyncio.Event()
    tasks = [asyncio.create_task(watcher(lat, ready)) for _ in range(clients)]
    await ready.wait()
    await asyncio.sleep(2)          # let every client settle before we bump
    await bumper(db_path)
    await asyncio.sleep(3)
    for t in tasks:
        t.cancel()
    lat.sort()
    print(f"n={len(lat)} "
          f"p50={statistics.median(lat) * 1000:.0f}ms "
          f"p99={lat[int(len(lat) * 0.99)] * 1000:.0f}ms")


asyncio.run(main("/var/www/data/views.sqlite", clients=1000))

Raise ulimit -n before running this or you will spend an hour debugging your load generator instead of your server. Results on a 2 vCPU box:

• 100 connections — p50 1.1s, p99 2.3s, 18 MB RSS
• 1,000 connections — p50 1.2s, p99 2.6s, 39 MB RSS
• 5,000 connections — p50 1.4s, p99 4.9s, 118 MB RSS

p50 sits near half the tick interval, which is exactly what the maths predicts. The p99 blowup at 5,000 is the JSON marshalling running once per subscriber instead of once per changed ID. That is the next fix, and it is a small one.

What I would do differently

• Ship the heartbeat first. Half a day went into "Cloudflare drops the connection at 100 seconds" that a four-byte comment frame would have prevented.
• Absolute values from the start. The delta version looked more elegant and was wrong in a way that only shows up on flaky mobile networks.
• Cap the ID list server-side on day one. Somebody will pass 400 IDs, and it will be a crawler, not an attacker.
• Instrument connections, not requests. Every dashboard we had was request-rate based, so all of them looked healthy while the worker pool was exhausted and the site was effectively down.

Conclusion

SSE turned out to be the right size of tool: one HTTP response, one text format, forty lines of client code, and no new protocol to operate. The genuinely hard parts had nothing to do with the protocol — they were output buffering across three layers that each thought they were helping, and the one-worker-per-connection model that PHP cannot escape. If you are on a bounded worker pool, solve the second problem before you ship rather than at 21:00 on a Tuesday. The version running on TopVideoHub today is the Go broker in front of the same SQLite file PHP already writes to, and it has been boring for four months, which is the highest compliment I have for infrastructure.