Sizing a Connection Pool for Edge CPU Limits

On a 1–2 core edge device — a single-board gateway, a battery-powered field node, a Python service pinned to two vCPUs by a cgroup quota — the instinct to “add more connections until throughput stops rising” is exactly wrong for SQLite. SQLite serializes every write through a single database-level write lock, so a pool holding four or eight writer handles does not write four or eight times faster; it queues them, and each queued attempt burns CPU spinning on busy_timeout retries and paying context-switch cost the scheduler cannot absorb on two cores. Readers do scale under Write-Ahead Logging, but only up to the number of cores that can actually run them in parallel and the RAM their per-connection page caches consume. This page sizes the pool to the CPU and memory the device really has, as one calibration within Connection Pooling Strategies, part of the wider WAL Optimization & Concurrency Tuning discipline. The failure it prevents is subtle: an oversized pool that looks healthy in a desktop benchmark and collapses into lock contention the moment it runs on the real two-core target.

Diagnosis

The symptom of an oversized pool is not a crash — it is throughput that falls as you add connections, while CPU sits pinned at 100%. Confirm it with three independent measurements before changing the pool size.

First, the SQLITE_BUSY rate. Every writer that cannot immediately take the write lock either blocks for busy_timeout or returns SQLITE_BUSY (error code 5). A rising busy rate as you add handles is the signature that the pool has more writers than SQLite’s single writer can serve:

import sqlite3

busy = 0
total = 0
try:
    conn.execute("INSERT INTO readings(sensor, value) VALUES (?, ?);", row)
    total += 1
except sqlite3.OperationalError as exc:
    if "database is locked" in str(exc):   # SQLITE_BUSY surfaced after busy_timeout expired
        busy += 1
    else:
        raise
# a busy ratio climbing with pool size == too many contending writers, not too few

Second, CPU pressure. On a 1–2 core device the scheduler cannot hide extra runnable threads. Read /proc/stat or, under a hypervisor, watch the steal column — time your vCPU was runnable but the host gave the core to someone else:

# %steal (st) climbing while throughput is flat == you are oversubscribed on CPU,
# not I/O-bound; more connections will only add context-switch overhead.
vmstat 1 5

Third, the RAM each connection reserves. cache_size is per-connection, so the pool’s real memory footprint is pool_size × cache_bytes, and on a constrained node that product — not throughput — is often the true ceiling:

PRAGMA cache_size;   -- per connection; negative = KiB (e.g. -2000 = ~2 MiB), positive = page count
PRAGMA page_size;    -- usually 4096; needed only when cache_size is a positive page count

If the busy ratio climbs with pool size, %steal or system CPU is high while rows/sec is flat, and cache_size × pool_size approaches the device’s memory budget, the pool is too big. The fix is to size it from the hardware, not from a benchmark run on a laptop.

Figure — On a two-core device the pool splits into a single serialized writer lane and a bounded reader lane sized to the cores; handles beyond that only add contention.

Sizing a SQLite pool to two CPU cores: one writer, readers equal to cores, extra handles wasted A two-core edge device on the left feeds a connection pool. The pool has one writer connection that holds SQLite's single write lock, and a reader lane with two reader connections matching the two cores. A third and fourth extra reader shown dashed sit past the core count and only add context-switch and lock-contention cost without raising throughput. 2-core device CPU + RAM budget writer × 1 holds the single write lock reader 1 runs on core 1 reader 2 runs on core 2 readers 3, 4 … no core to run on only add contention writes serialize: extra writers only queue + retry readers ≈ cores: WAL lets them run concurrently

Solution

Derive the pool from two numbers the device reports about itself — its usable core count and its memory ceiling — rather than a fixed constant. The rule is deliberately blunt because SQLite’s concurrency model is blunt: one writer, because writes serialize; readers up to the core count, because that is how many can run at once; and a hard cap so the combined page caches fit RAM. The factory below computes those bounds, builds a bounded pool, and reapplies the same PRAGMA baseline to every handle so a recycled connection can never revert to defaults:

import os
import queue
import sqlite3
import logging

logger = logging.getLogger("sqlite_pool_sizer")


def usable_cores() -> int:
    """Cores this process may actually run on (honours cgroup/affinity), min 1."""
    try:
        return max(1, len(os.sched_getaffinity(0)))   # respects taskset / cpuset, unlike cpu_count()
    except AttributeError:
        return max(1, os.cpu_count() or 1)


def pool_size(cores: int, writers: int = 1) -> int:
    """readers ~= cores (they run concurrently under WAL); writers fixed at 1 (writes serialize)."""
    readers = cores                                   # more readers than cores just time-slice
    return writers + readers


def make_connection(db_path: str, cache_kb: int, timeout: float) -> sqlite3.Connection:
    conn = sqlite3.connect(db_path, timeout=timeout, isolation_level=None, check_same_thread=False)
    try:
        conn.execute("PRAGMA journal_mode=WAL;")        # readers concurrent with one writer
        conn.execute("PRAGMA synchronous=NORMAL;")      # fsync at checkpoint, not every commit
        conn.execute(f"PRAGMA busy_timeout={int(timeout * 1000)};")  # ms: block instead of instant SQLITE_BUSY
        conn.execute(f"PRAGMA cache_size={cache_kb};")  # negative = KiB; PER connection, so keep it small
        # Read back every value; a pooled/recycled handle that skipped init reverts to defaults silently.
        for pragma, want in (("cache_size", cache_kb),):
            got = conn.execute(f"PRAGMA {pragma};").fetchone()[0]
            if got != want:
                logger.warning("%s mismatch: requested=%d applied=%d", pragma, want, got)
        return conn
    except sqlite3.Error:
        conn.close()
        logger.exception("connection init failed for %s", db_path)
        raise


def build_pool(db_path: str, ram_budget_mib: int = 64, timeout: float = 15.0) -> queue.Queue:
    cores = usable_cores()
    size = pool_size(cores)
    # Split the RAM budget across handles so combined caches never exceed the device ceiling.
    cache_kb = -max(1024, (ram_budget_mib * 1024) // size)   # negative KiB, >=1 MiB floor per conn
    pool: queue.Queue = queue.Queue(maxsize=size)
    for _ in range(size):
        pool.put(make_connection(db_path, cache_kb, timeout))
    logger.info("pool sized: cores=%d size=%d cache_kb/conn=%d", cores, size, cache_kb)
    return pool

Two decisions carry the weight. os.sched_getaffinity(0) is used instead of os.cpu_count() because a cgroup or taskset can pin a Python service to two cores on a sixteen-core host — sizing to the physical count would build an eight-times-too-large pool. And the per-connection cache is ram_budget / pool_size, so adding a handle shrinks each handle’s cache rather than growing total footprint; the pool cannot silently overrun RAM as it fills. Keep the single writer routed through one dedicated handle and let the readers share the rest; funnelling writes through one connection is also what the companion Reducing Lock Contention in Multi-Threaded Apps leans on.

Verification

Prove the pool is sized to the hardware, then prove it under load.

First, assert the derived size matches the cores the process can actually use — not the host’s physical count:

cores = usable_cores()
assert pool_size(cores) == 1 + cores, "pool must be one writer plus readers-equal-to-cores"
# on a 2-core cgroup this must be 3, even on a 16-core host
assert cores <= (os.cpu_count() or 1), "affinity should never exceed physical cores"

Second, confirm every handle really carries the baseline, so a recycled connection is not silently running at the default cache or without a busy_timeout:

conn = pool.get()
try:
    assert conn.execute("PRAGMA cache_size;").fetchone()[0] < 0, "cache_size reverted to a page count"
    assert conn.execute("PRAGMA busy_timeout;").fetchone()[0] > 0, "busy_timeout not applied to this handle"
    assert conn.execute("PRAGMA journal_mode;").fetchone()[0].lower() == "wal", "handle not in WAL mode"
finally:
    pool.put(conn)

Third, run the real workload and watch the busy ratio hold flat as concurrency rises. If throughput climbs from one to cores readers and then plateaus while SQLITE_BUSY stays near zero, the pool is right-sized; if the busy ratio climbs, you have more writers contending than the single write lock can serve, and the extra writer handles must go.

Failure Modes & Gotchas

Every connection’s page cache multiplies the RAM you thought you set. cache_size is per-connection, so a pool of six handles each at a 16 MB cache reserves ~96 MB of pager memory before Python object overhead — enough to OOM a 128 MB node that benchmarked fine with a single connection. Always size the per-connection cache as budget / pool_size, and read it back on every handle: a connection that skipped the initializer reverts to the ~2 MB default and quietly breaks your accounting. The same per-handle-budget discipline is spelled out in Connection Pooling Strategies.

Idle connections pin an old WAL snapshot and block checkpoint truncation. A pooled reader that opened a transaction and never finished holds a read mark against the WAL; SQLite cannot reset or truncate the WAL past the oldest live reader, so on a device with more handles than active work the WAL grows without bound even though writes are modest. Keep readers short-lived, set isolation_level=None so a stray SELECT does not silently open a long transaction, and prefer a small pool that finishes and returns handles promptly over a large one full of idle readers holding snapshots.

Sizing to os.cpu_count() on a shared or virtualized host oversubscribes CPU. Under a hypervisor or a cgroup CPU quota the physical core count is a fiction — the scheduler may only ever give you a fraction of it. A pool sized to sixteen cores on a two-vCPU allotment spends its time context-switching and shows up as rising %steal with flat throughput. Always derive the reader count from os.sched_getaffinity(0) (or the cgroup quota) so the pool tracks the CPU the process can actually consume, not the CPU the box advertises.