京公网安备11010502063487号用 sqlite 保存抓取结果很方便,但多个爬虫线程同时写会撞上 locked。WAL 模式、busy 等待、单写线程加队列分别解决什么,代价是什么?这篇用 sqlite 3.53.4 一项项测。
sqlite 版本 3.53.4,Python sqlite3 模块,数据库文件在本机磁盘的临时目录 journal_mode=delete synchronous=FULL (PRAGMA 读回 2):中位 3476 次提交/秒(1462–3645) journal_mode=delete synchronous=NORMAL(PRAGMA 读回 1):中位 3347 次提交/秒(2453–4087) journal_mode=wal synchronous=FULL (PRAGMA 读回 2):中位 21166 次提交/秒(5058–21767) journal_mode=wal synchronous=NORMAL(PRAGMA 读回 1):中位 36454 次提交/秒(3172–46450) journal_mode=wal synchronous=OFF (PRAGMA 读回 0):中位 64743 次提交/秒(63177–65096)
每行一个事务的写入主要花在每次提交的磁盘同步上。同样 2000 行,WAL 模式的提交速度比默认的 delete 日志高一个数量级左右;synchronous 降低还能更快,但 OFF 在掉电时可能损坏数据,NORMAL 在 WAL 下的风险要看文档(这里没有测掉电)。单次测量波动很大,表里给的是 5 次中位数与范围。
journal_mode=delete busy 等待 timeout=0:中位 3000 行/秒(811–3587),遇到 "database is locked" 并重试的次数中位 1505(1063–5613),每次最后表里都是 2000 行 journal_mode=delete busy 等待 timeout=0.05:中位 3535 行/秒(510–3682),遇到 "database is locked" 并重试的次数中位 13(12–50),每次最后表里都是 2000 行 journal_mode=delete busy 等待 timeout=5:中位 2804 行/秒(1402–2855),遇到 "database is locked" 并重试的次数中位 0(0–0),每次最后表里都是 2000 行 journal_mode=wal busy 等待 timeout=0:中位 8292 行/秒(7292–20722),遇到 "database is locked" 并重试的次数中位 158(157–287),每次最后表里都是 2000 行 journal_mode=wal busy 等待 timeout=0.05:中位 13324 行/秒(7806–18322),遇到 "database is locked" 并重试的次数中位 1(1–2),每次最后表里都是 2000 行 journal_mode=wal busy 等待 timeout=5:中位 9584 行/秒(5086–12079),遇到 "database is locked" 并重试的次数中位 0(0–0),每次最后表里都是 2000 行
多个线程各开连接写同一个库,sqlite 同一时刻只允许一个写者。连接的 busy 等待为 0 时,抢不到锁就立刻报 “database is locked”(delete 模式中位重试 1505 次);设了等待(Python 里是 connect 的 timeout 参数,默认 5 秒)就由 sqlite 在内部重试,实验里重试次数为 0。WAL 下吞吐更高。
4 个生产线程各放 500 行,批大小 1:中位 8310 行/秒(5360–11995),写线程提交 2000 次,表里 2000 行,没有 locked 错误 4 个生产线程各放 500 行,批大小 10:中位 49717 行/秒(2952–52716),写线程提交 200 次,表里 2000 行,没有 locked 错误 4 个生产线程各放 500 行,批大小 100:中位 90207 行/秒(78879–114349),写线程提交 20 次,表里 2000 行,没有 locked 错误 4 个生产线程各放 500 行,批大小 500:中位 117097 行/秒(97004–122708),写线程提交 4 次,表里 2000 行,没有 locked 错误
爬虫线程把结果放进 queue.Queue,由一个写线程持有唯一的连接、攒一批再提交。实验里批大小 1 时 8310 行/秒,批大小 100 时 90207 行/秒;提交次数从 2000 降到 20。没有 locked 错误,因为只有一个写者。队列无上限时写线程跟不上会占用内存(队列满时让生产者等待,见第 22 篇)。
journal_mode=delete 未提交的写事务里有 10 行(约 0.0 MiB):读连接 读到 0 行,用时 0.0 ms;写提交之后再读:10 行 journal_mode=delete 未提交的写事务里有 1000 行(约 2.0 MiB):读连接 错误 "database is locked",用时 0.1 ms;写提交之后再读:1000 行 journal_mode=wal 未提交的写事务里有 10 行(约 0.0 MiB):读连接 读到 0 行,用时 0.1 ms;写提交之后再读:10 行 journal_mode=wal 未提交的写事务里有 1000 行(约 2.0 MiB):读连接 读到 0 行,用时 0.2 ms;写提交之后再读:1000 行
WAL 下读连接读到的是写事务提交前的状态,不会被正在进行的写事务挡住;delete 模式下实验里 1000 行(约 2 MiB)的未提交写事务让读连接报 locked,而 10 行的没有。我只是观察到这个差别,没有去查原因。
默认自动 checkpoint(1000 页):过程中 -wal 最大 4.1 MiB;写完时文件大小(KiB){'db': 38296, '-wal': 4217};此时手动 checkpoint(TRUNCATE) 返回 (0, 0, 0)(busy, 日志页数, 已移回页数)
读事务结束后再 checkpoint(TRUNCATE):(0, 0, 0),-wal 现在 0 KiB
关掉自动 checkpoint:过程中 -wal 最大 41.9 MiB;写完时文件大小(KiB){'db': 8, '-wal': 42910};此时手动 checkpoint(TRUNCATE) 返回 (0, 0, 0)(busy, 日志页数, 已移回页数)
读事务结束后再 checkpoint(TRUNCATE):(0, 0, 0),-wal 现在 0 KiB
默认自动 checkpoint,但有一个读连接一直开着读事务:过程中 -wal 最大 41.9 MiB;写完时文件大小(KiB){'db': 8, '-wal': 42910};此时手动 checkpoint(TRUNCATE) 返回 (1, 10665, 0)(busy, 日志页数, 已移回页数)
读事务结束后再 checkpoint(TRUNCATE):(0, 0, 0),-wal 现在 0 KiB默认每累积 1000 页自动做 checkpoint,-wal 最大 4.1 MiB。关掉自动 checkpoint,或者有一个读事务一直不结束,checkpoint 就无法把数据移回主库,-wal 长到 41.9 MiB(主库只有 8 KiB),手动 checkpoint(TRUNCATE) 返回 busy=1。读事务结束后再 checkpoint,-wal 回到 0 KiB。长时间开着的读事务(比如迭代一个很大的结果集边遍历边处理)是个隐患。
默认 isolation_level='';in_transaction(连接刚建好)=False;执行一条 INSERT 后 in_transaction=True(Python 自动开了事务,没 commit 前别的连接看不到) 另一个连接此时 SELECT count(*) = 0;commit 后 = 1 连接默认 check_same_thread=True,在另一个线程里用同一个连接:ProgrammingError: SQLite objects created in a thread can only be used in that same threa
Python sqlite3 默认在第一条 INSERT 时隐式开事务,忘了 commit 别的连接就看不到数据;连接默认不能跨线程使用。写线程模式天然符合这个限制:连接只在写线程里创建和使用。