Memory-Mapped I/O Configuration #

When a SQLite deployment leaves mmap_size at its factory default of 0, every page the engine reads travels through the traditional read() syscall path: a kernel-to-user copy for each page, a context switch on every cache miss, and redundant buffering between the OS page cache and SQLite’s own page cache. On constrained ARM SoCs, sub-millisecond desktop UI threads, and high-throughput Python batch pipelines, that overhead surfaces as unpredictable read latency, inflated CPU-in-iowait, and accelerated flash wear from duplicated cache traffic. Memory-mapped I/O eliminates the copy by mapping database pages directly into the process virtual address space — but only for the main database file, and only when the value is calibrated against physical RAM and address-space limits. This configuration is part of the broader WAL Optimization & Concurrency Tuning discipline, and it must be coordinated with checkpoint cadence and page-cache sizing rather than tuned in isolation. Set it too high on a 32-bit gateway and you invite SIGBUS, OOM kills, and swap thrashing; leave it disabled on server-class hardware and you throttle analytical scans that would otherwise run at memory bandwidth.

Core Mechanism & Crash-Safety Defaults #

PRAGMA mmap_size=N instructs SQLite to mmap() up to N bytes of the main database file into the process address space. Once mapped, page reads become ordinary memory dereferences serviced by the kernel’s demand-paging machinery: the first touch of an unmapped page triggers a minor fault that maps the underlying file block, and subsequent accesses hit RAM directly with zero syscall overhead. Writes to mapped pages still go through the normal write path — SQLite treats the mapping as read-mostly and does not rely on dirty-page writeback for durability.

Critically, memory mapping never covers the write-ahead log. SQLite’s crash-recovery model depends on deterministic, ordered writes to the WAL followed by an fsync() before frames are considered durable. Mapping the log would introduce non-deterministic page-fault write ordering and break the atomicity guarantee that lets the engine recover a consistent database after abrupt power loss. That is why mmap_size accelerates read-heavy and analytical workloads but does nothing for commit latency. The crash-safety defaults you set elsewhere — PRAGMA journal_mode=WAL and PRAGMA synchronous=NORMAL, covered in the PRAGMA Optimization Guide and the synchronous crash-safety page — remain fully in force and are unaffected by the mapping.

Two subtleties govern safe operation. First, the OS may silently cap the requested size: a 32-bit process cannot map more than its fragmented address space allows, and the kernel enforces vm.max_map_count. SQLite honours whatever the kernel grants and reports the effective value back through PRAGMA mmap_size, so the requested and applied numbers can differ. Second, the map is a live window onto the file: if a concurrent checkpoint or a VACUUM shrinks the database while pages are mapped, dereferencing a now-truncated region raises SIGBUS. Both behaviours make read-back verification mandatory rather than optional.

Step-by-Step Implementation #

1. Verify Prerequisites and PRAGMA Baselines #

Memory mapping is a layer on top of an already-hardened connection. Confirm WAL mode is active, the page cache is sized deliberately, and inspect the mapping the engine is currently willing to grant before changing anything:

PRAGMA journal_mode;      -- expect: wal (mmap accelerates reads regardless, but WAL is the baseline)
PRAGMA page_size;         -- typically 4096; needed to reason about page counts vs bytes
PRAGMA cache_size;        -- negative = KiB of page cache; overlaps with mmap, so size them together
PRAGMA mmap_size;         -- current effective mmap window in bytes; default 0 = disabled

If page_size reports a non-default value (e.g. 8192 on some embedded images), factor it into the threshold math below. Because both cache_size and mmap_size hold copies of pages in RAM, calibrating them jointly avoids double-buffering — the interaction is detailed in Tuning cache_size for Embedded Linux.

2. Select the Target Value #

Pick the map size from the host’s physical RAM and address width, not from the database size. A useful starting formula is:

target_mmap_bytes = min(database_file_size, 0.25 × physical_RAM_bytes)

Cap at 25% of RAM on memory-constrained or swap-disabled devices; there is no benefit to mapping more of the file than fits alongside the working set. Use the decision table to pick a concrete value:

3. Apply Configuration with Explicit Verification #

mmap_size must be applied immediately after opening the connection and before any query warms the cache. Because the kernel may cap the value, always read it back and assert rather than assuming the request succeeded:

import os
import sqlite3
import logging

logger = logging.getLogger(__name__)

def configure_mmap(db_path: str, requested_bytes: int = 268435456) -> sqlite3.Connection:
    """Open a connection with deterministic memory-mapped I/O and verify the map."""
    if not os.path.exists(db_path):
        raise FileNotFoundError(f"Database file not found: {db_path}")

    conn = sqlite3.connect(db_path, timeout=10.0)
    try:
        conn.execute("PRAGMA journal_mode=WAL;")        # concurrent readers; log excluded from mmap
        conn.execute("PRAGMA synchronous=NORMAL;")      # fsync at checkpoint, not every commit
        conn.execute(f"PRAGMA mmap_size={requested_bytes};")  # map up to N bytes of the MAIN db file

        # Read back: the OS/engine may silently cap the value (32-bit space, vm.max_map_count).
        applied = conn.execute("PRAGMA mmap_size;").fetchone()[0]
        if applied != requested_bytes:
            logger.warning(
                "mmap_size capped: requested=%d applied=%d", requested_bytes, applied
            )
        if applied == 0 and requested_bytes > 0:
            # Filesystem (NFS/FUSE/squashfs) may lack mmap semantics — reads fell back to read().
            logger.error("mmap disabled by platform; falling back to cache_size tuning")
        return conn
    except sqlite3.OperationalError:
        conn.close()
        logger.exception("PRAGMA configuration failed")
        raise

The read-back is not defensive boilerplate: a mapping that silently reports back 0 tells you the underlying filesystem rejected mmap(), and you should reallocate that RAM budget to cache_size instead.

Workload Profiles & Threshold Reference #

For write-dominated targets, the map size matters less than keeping the WAL small: coordinate with Checkpoint Frequency Tuning and Threshold Tuning for High-Write Workloads so mapped readers are never blocked behind an oversized checkpoint. When the database is reached through pooled or async handles, remember that mapping state is per-connection — see Connection Pooling Strategies and Async Execution Patterns for applying the PRAGMA uniformly across every handle.

Failure Documentation & Edge Cases #

Silent value capping #

Trigger: A 32-bit process or a kernel with a low vm.max_map_count cannot satisfy the requested map, so SQLite grants a smaller window or none at all. Diagnosis: PRAGMA mmap_size; returns a value below the request, or the applied value is 0. Cross-check the kernel ceiling with cat /proc/sys/vm/max_map_count. Fallback: Log the discrepancy (the code above does this), lower the request to fit the address space, and redirect the unused RAM budget into cache_size per Tuning cache_size for Embedded Linux.

SIGBUS on file truncation #

Trigger: A concurrent checkpoint, VACUUM, or bulk schema migration shrinks the database file while a reader is dereferencing a now-truncated mapped page. Diagnosis: The process dies with Bus error (SIGBUS); dmesg shows the faulting address inside the mapped region. Correlate with maintenance windows or VACUUM runs. Fallback: Serialize maintenance behind exclusive access, avoid VACUUM while mapped readers are live, and use PRAGMA wal_autocheckpoint to keep truncation incremental rather than abrupt.

OOM killer activation #

Trigger: An aggressive mmap_size on a low-RAM ARM SoC with vm.overcommit_memory permissive enough to accept the reservation, then real memory pressure forces the kernel to reap the process. Diagnosis: dmesg shows Out of memory: Killed process; /proc/<pid>/status shows VmRSS climbing as pages fault in. Compare Mapped vs Cached in /proc/meminfo. Fallback: Cap mmap_size at 25% of physical RAM, prefer cache_size on swap-disabled devices, and never map more than the working set.

Platform lacks mmap semantics #

Trigger: The database lives on NFS, a FUSE mount, or a read-only squashfs image where mmap() is unsupported or unsafe. Diagnosis: PRAGMA mmap_size; reports 0 despite a positive request; read latency shows no improvement. Fallback: Treat mapping as unavailable, keep the database on a local ext4/btrfs volume, and lean on cache_size for the read path.

Production Hardening Checklist #

• Confirm PRAGMA journal_mode=WAL and PRAGMA synchronous=NORMAL are applied before enabling mapping
• Set mmap_size immediately after opening the connection, before any query warms the cache
• Read back PRAGMA mmap_size and assert the applied value matches (or log the cap)
• Cap mmap_size at 25% of physical RAM on constrained or swap-disabled devices
• Size cache_size and mmap_size jointly to avoid double-buffering the same pages
• Apply the PRAGMA on every pooled and async connection — mapping state is per-handle
• Guard VACUUM and schema migrations behind exclusive access to prevent SIGBUS
• Verify the target filesystem supports mmap (local ext4/btrfs, not NFS/FUSE/squashfs)
• Monitor /proc/meminfo Mapped vs Cached and vm.max_map_count on 32-bit targets

Related Pages #

• Tuning cache_size for Embedded Linux — how the internal page cache interacts with the OS mapping
• PRAGMA Optimization Guide — the full connection-initialization ordering
• Checkpoint Frequency Tuning — keep truncation incremental so mapped readers never stall
• Connection Pooling Strategies — apply mmap_size uniformly across pooled handles
• Threshold Tuning for High-Write Workloads — prioritise a small WAL over a large map on write-heavy targets

Part of the WAL Optimization & Concurrency Tuning series.