登录

SQLite 上生产:三个硬限制和各自的解法

629
  • Rxo
    UID 4265楼主

    SQLite 能上生产,前提是先接受三条物理限制,别指望调参绕过去。

    1. 同一时刻只有一个写事务

    代价不是「慢」,是排队:写事务一长,读也会被 WAL 检查点拖住。做法:

    • 事务里只放写。网络请求、文件 IO、日志全挪到事务外。
    • PRAGMA busy_timeout = 5000; 让写冲突等待,而不是立刻 SQLITE_BUSY。
    • PRAGMA journal_mode = WAL; 读写不再互相阻塞;synchronous = NORMAL 在 WAL 下不会损坏数据库,断电最多丢最后一个事务。

    2. 不能放在网络文件系统上

    NFS、SMB、部分云盘的锁语义不可靠。锁失效时两个进程同时写同一个文件,结果是库损坏。这不是性能问题,是数据问题。必须共享存储,就换 Postgres。

    3. 备份不能用 cp

    WAL 模式下 cp db.sqlite 只拿到主文件,已提交但还在 -wal 里的事务全丢;拷贝过程中有写入,还会拿到半页。

    • 在线备份:sqlite3 db ".backup out.sqlite",或 VACUUM INTO 'out.sqlite'(只读快照,顺带整理)。
    • 要连续备份就上 litestream,按 WAL 帧复制。

    什么时候它是对的

    读多写少、单机、几十 GB 数据、写能串行。这些场景下它比 Postgres 省事得多:没有网络往返,单条读是微秒级。

    什么时候别用

    多台机器同时写、需要细粒度行锁、写入 QPS 上万。这几种调参救不回来。

    留一句迁移路径:表和 SQL 大多是标准 SQL,真正要改的是并发控制和备份脚本。早点把所有写操作收进一个入口,换库时只改那一处。

  • 收藏支持反对

9 条回复

  • mozi
    UID 809
    #1

    三个限制说得对,生产上我还会把「事务怎么开」单独拎出来。默认是 DEFERRED,冲突要到真正写的那一刻才报 SQLITE_BUSY,前面读和计算都白做了;写路径用 BEGIN IMMEDIATE,把锁争用提前到进事务时,busy_timeout 才真能等到。

    另外 PRAGMA 里只有 journal_mode=WAL 是持久的,foreign_keys、busy_timeout 每个新连接都要重设,连接池里漏一次就等于规则只对部分连接生效。VACUUM INTO 之前还得留出一份完整副本的磁盘,空间已经紧的时候这条备份路径会直接失败。

  • 嘴哥
    UID 590
    #2

    WAL + synchronous=NORMAL 那句 “断电最多丢最后一个事务” 别当保证。官方说的是系统崩溃或断电后,已经提交的事务仍可能回滚,并没有承诺只丢一个。库保持一致与提交结果能否持久保存是两回事;如果业务不能接受已确认写入消失,就得按持久性要求选配置。SQLite 官方说明

  • hi2hi
    UID 170
    #3

    主服务设了 foreign_keys 和 busy_timeout,不代表临时脚本、管理工具的新连接也设了。连接级配置要统一初始化;最容易破坏约定的,往往是那段「只跑一次」的脚本。

  • Lvan
    UID 1112
    #4

    三条限制都认。备份那条其实有个更省事的解法:litestream 这类工具直接盯着 WAL 文件往 S3 里流,等于把 “拍照备份” 换成 “录像回放”,库炸了还能按时间点倒带。比定时 cp 多花的只是一个进程的功夫,换回来的是“上周三下午三点那版数据还在” 的底气——对小团队来说,这比再加一台 Postgres 便宜得多。

  • 煤球大王
    UID 12612
    #5

    三条之外还有一条同源的限制:WAL 退不回去,往往不是写太长,是读太长。

    WAL 的检查点不能越过最老的那个读快照。一条跑几分钟的报表查询、一个忘了关的读事务,甚至连接池里一条闲置但持有快照的连接,都会把检查点钉在原地:后面的写事务照常提交,-wal 文件却只涨不退,最后磁盘被一个看起来「没什么写入量」的库吃满,排查方向还很容易先去怀疑写并发。

    所以生产上我会单独盯两个数:-wal 文件大小和检查点推进情况,而不是只看数据库主文件。报表和分析类读取不要直接压在主库上,哪怕它只是读——用在线备份出的快照或副本去跑;应用侧则保证读事务短、连接归还前没有悬挂的快照。主帖说写要串行,我补的是:读可以并发,但不能长占。

  • uvexz
    UID 1158
    #6

    三条限制之外,补一个实操层面的解法:别拿 busy_timeout 当并发控制用。它只是在锁冲突时等一等,写一多照样往外冒 SQLITE_BUSY。真正管用的是在应用层把写收敛到一个写入口——单个写线程或写进程排队串行,busy_timeout 只当最后一道保险。主帖说的「把所有写操作收进一个入口」不只是为了以后好换库,它现在就能让锁争用基本消失。

    另外一个可验证的点:压测别只看平均写延迟,看 p99。单写者模型下写延迟的长尾全来自排队,p99 写进验收指标,否则上线后第一个长写事务拖垮的就是排在后面的所有写。

  • mozi
    UID 809
    #7

    #5 说的长读钉住 WAL 检查点,这点比写并发更容易在生产上踩到,而且经常不是那条「跑了几分钟的报表」。

    更常见的是连接池或 ORM 的隐式事务:一次 SELECT 就开启了读快照,业务逻辑接着做 HTTP、渲染、甚至等锁,连接还没 commit/rollback 就被放回池里,快照却还挂着。Python 的 sqlite3 默认 isolation_level 就是这种路子,不少「我只是读了一下」的请求,其实在持有一个不会自己结束的读事务。主库上的分析查询只是最显眼的那一种,池子里这种悬挂才是 -wal 只涨不退时优先该查的。

    落地的话,连接借出时确保没有未结束事务,归还前强制结束快照(或直接丢掉脏连接),并且把 -wal 体积做成告警,而不是等磁盘满了再回头猜写太多。#6 说的应用层单写入口和 p99,我同意;但 WAL 胀死这件事,读路径的生命周期要单独管,busy_timeout 和写排队都管不到它。

  • kinghost
    UID 355
    #8

    三条限制都认,但生产上还缺第四条,而且是最容易被这三条掩盖的一条:可用性。

    WAL、busy_timeout、litestream 解决的都是「库本身别坏、数据别丢」,没解决「这台机器挂了怎么办」。SQLite 是单文件单机,litestream 给的是备份和时间点恢复,不是高可用——恢复要手动演练,RTO 是小时级不是秒级。主帖说「读多写少、单机」是对的场景,但上生产之前得先把 RPO 和 RTO 写下来:能丢多少分钟的数据、挂了多久能恢复回来,并且真的演练过一次从 litestream 的 S3 副本起库。没演练过的备份只是个心理安慰,演练那天才发现缺了 -wal 或者恢复脚本跑不通的,见得太多了。

    另外补一个运维上的小点:定期跑 PRAGMA integrity_check,并且把结果写进监控。前面几楼都在讲怎么不写坏库,但库坏了能不能第一时间发现,同样是生产要求——等用户报错才发现,备份链再完整也晚了一拍。

  • 煤球大王
    UID 12612
    #9

    @mozi #7 你把排查顺序改对了:显眼的长报表反而好抓,池子里挂着快照的闲置连接才是常态——它不出现在任何慢查询日志里,-wal 涨起来的时候第一反应才会错怪到写上。

    补一个能直接落地的查法:-wal 异常增长时,先别看正在跑的 SQL,先逐条查连接池里每条连接的事务状态,有闲置连接还挂着事务,基本就是它;再看它的快照年龄。钉住检查点的是快照的年龄,不是某条查询的时长,所以池子最好单独给读事务设一个年龄上限:超龄的快照强制结束,并打一条带借用方信息的日志,不然下次还是只能猜。归还前结束事务是纪律,借出时断言「无未结束事务」才是闸——纪律靠人记,闸靠代码拦。

发表回复