Hotdry.
归档
systems-engineering

SQLite 单表十亿行实现 10 万 TPS：WAL 模式与 PRAGMA 优化详解

通过 WAL 模式、索引、ANALYZE 和关键 PRAGMA 参数，SQLite 单表十亿行轻松实现 10 万 TPS 混合读写，避开 ORM 直击极致吞吐，适用于单机高性能场景。

SQLite 嵌入式单文件数据库在高 TPS 场景下的潜力远超预期。Anders Murphy 在 M1 Pro MacBook（16GB 内存）基准测试中，使用单一 account 表（id INT 主键、balance INT），填充 10 亿行数据，通过 WAL 模式、读写分离连接池和动态批处理，实现混合读写 102k TPS，纯写批处理高达 186k TPS。这对比 Postgres（10ms 延迟 + 可序列化隔离仅 348 TPS），凸显无网络零延迟 + 单写者批优化的 “非理性有效”。

基准模拟真实 web 负载：交互事务（UPDATE + 业务逻辑 + UPDATE），Pareto 分布（99.95% 事务集中 0.05% 热点用户，模拟 10 万活跃），虚拟线程突发 1M 并发。SQLite 基线无批 44k TPS，引入 SAVEPOINT 嵌套事务隔离后批处理 121k TPS，3 读 + 1 写仍超 100k。

核心观点：配置即性能，参数落地清单

  1. PRAGMA 参数配置（连接初始化全执行）
    这些参数针对缓存、日志、同步优化，直接决定 TPS 上限：

    参数 推荐值 作用与阈值
    journal_mode WAL 读写并发，写日志不锁主 DB
    synchronous NORMAL 减 fsync，平衡安全（OFF 风险高）
    cache_size 15625 ~64MB 页缓存（内存 10%）
    page_size 4096 匹配 FS 页，I/O 高效（建前设）
    temp_store MEMORY 临时 / 排序内存化
    busy_timeout 5000 写锁 5s 等待
    mmap_size 0 或大 内存映射随机读（SSD 优）

    Python 示例：

    conn.execute("""
PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL;
PRAGMA cache_size=15625; PRAGMA page_size=4096;
PRAGMA temp_store=MEMORY; PRAGMA busy_timeout=5000;
""")

  2. 索引与统计维护

    • 热点字段覆盖索引：CREATE INDEX idx_balance ON account(balance);。
    • ANALYZE; 更新规划器统计，避免低估大表。
    • 禁 COUNT(*)（O (n)），维护影子计数。

  3. 连接池与批处理实践

    • writer：单连接，CPU 池异步批（阈值 32k）。
    • readers：核心数（如 8）池，并发 WAL 读。
    • 批事务：SAVEPOINT sp; UPDATE; [逻辑]; RELEASE/ROLLBACK sp; 失败隔离。
      示例（伪 Clojure）：
    (tx! (fn [tx] (SAVEPOINT "inner"; UPDATE account SET balance=...; UPDATE ...; RELEASE "inner")))

    避 ORM：ORM Prepare / 反射损耗大，直原生 SQL。

可落地 Checklist

  1. 空库：PRAGMA page_size=4096; CREATE TABLE account (id INT PK, balance INT); VACUUM;
  2. 填充：事务批 100k INSERT INTO account VALUES(?,1000000000) 1B 行。
  3. 优化：索引 + ANALYZE;。
  4. 池部署：1 writer + 8 readers。
  5. 压测：1M 虚拟线程 Pareto 负载，TPS >100k。
  6. 生产运维：litestream 备份；cron VACUUM; REINDEX; ANALYZE;；警 WAL>1GB/CPU>80%。

风险与回滚

  • 单写：纯写瓶颈 10k+ TPS，用批缓解。
  • 无 MVCC：长事务慎，序列化隔离。
  • 崩溃：NORMAL 安全，OFF + WAL checkpoint。
    扩展：rqlite 多机，projections 投影。

此参数组合基准实证，单机日志 / 缓存 / 分析 TPS 王者。

资料来源
Anders Murphy 文章：“SQLite batch + SAVEPOINT 实现 121k TPS。”（https://andersmurphy.com/2025/12/02/100000-tps-over-a-billion-rows-the-unreasonable-effectiveness-of-sqlite.html）
HN 讨论：https://news.ycombinator.com/item?id=42217092

（正文 1128 字）

查看归档