最近, 一位程序员表示自己急需一个「也就」十亿行数据的测试数据库, 并且还得在一分钟之内生成.
于是, 他做了一个所有程序员都会做的事: 写一个 Python 脚本来生成数据库.
然而, 很不幸的是, 这个脚本非 常 慢.
于是, 他又做了一个所有程序员都会做的事: 进一步学习关于 SQLite,Python 以及不知道为什么还有 Rust 的知识.
项目已开源:
https://github.com/avinassh/fast-sqlite3-inserts
目标
作者需要在他 2019 年的 MacBook Pro(2.4GHz 四核 i5) 上, 一分钟内生成一个有 10 亿行的 SQLite 数据库.
表的模式
要求:
生成的数据是随机的;
「area」列将包含六位数的地区代码 (任何六位数都可以, 不需要验证);
「age」列是 5,10 或 15 中的任何一个;
「active」列是 0 或 1.
不过, 作者表示, 对脚本的要求也不用太高, 还是可以妥协的:
如果进程崩溃, 所有的数据都丢失也没有问题, 再次运行脚本就可以了;
允许充分利用电脑的资源: 100% 的 CPU,8GB 的内存和剩余的 SSD 储存;
不需要使用真正的随机方法, 来自 stdlib 的伪随机方法就可以.
Python 原型
在最开始的脚本中, 作者试图在一个 for 循环中逐一插入 1000 万条记录, 而这让用时直接达到了 15 分钟.
显然, 这太慢了.
在 SQLite 中, 每次插入都是一个事务, 每个事务都保证它被写入磁盘, 作者推断可能问题就来自这里.
于是作者开始尝试不同规模的批量插入, 发现 10 万是一个最佳点, 而运行时间减少到了 10 分钟.
SQLite 优化
作者认为自己写的代码已经很简练了, 并没有什么可以优化的空间.
于是他将下一个目标转到了数据库的优化.
根据各种关于 SQLite 优化的建议, 作者做了一些改进.
关闭「journal_mode」将禁用回滚日志, 也就是说, 如果任何事务失败, 都无法回滚.
关闭「synchronous」, 将使 SQLite 不再关心是否能可靠地写入磁盘, 而是把这个责任交给操作系统. 也就是说, 可能会出现 SQLite 并没有成功写入磁盘的情况.
「cache_size」指定了 SQLite 在内存中可以保留多少个内存页.
当「locking_mode」为「EXCLUSIVE」模式时, SQLite 锁住的连接将永远不会被释放.
将「temp_store」设置为「MEMORY」可以让其表现像一个内存数据库.
此处作者提醒, 请不要把这些操作用到生产上去.
重新审视 Python
作者再次重写了 Python 脚本, 这次包括了微调的 SQLite 参数, 这次带来了巨大的提升, 运行时间大幅减少:
原始的 for 循环版本用时大约 10 分钟.
批处理版本用时大约 8.5 分钟.
PyPy
PyPy 在其主页上强调它比 CPython 快 4 倍, 于是作者决定尝试一下.
令作者有些意外的是, 竟然不需要对现有的代码进行任何改动, 只需要在 PyPy 运行就可以了.
批处理版本只需要 2.5 分钟, 也就是速度快了接近 3.5 倍.
Busy Loop?
莫非是在 Python 的循环上耗费了太多时间? 于是作者删除了 SQL 指令之后再次跑了一遍代码:
批处理版本在 CPython 中用时 5.5 分钟.
批处理版本在 PyPy 中用时 1.5 分钟 (又是 3.5 倍的速度提升).
然而用 Rust 重写了相同的内容之后, 循环只需要 17 秒.
于是, 作者果断抛弃 Python, 转投 Rust 的怀抱.
Rust
像 Python 一样, 作者先写了一个原始的 Rust 版本, 一个循环执行一行数据的插入.
然而, 即便使用了所有 SQLite 的优化, 也依然消耗了大约 3 分钟. 于是作者进行了进一步的测试:
尝试把「rusqlite」换成异步运行的「sqlx」, 这让用时直接被拉到了 14 分钟. 作者表示, 这比自己迄今为止写的任何一个 Python 迭代都要差.
在执行原始 SQL 语句时, 使用准备好的语句. 这个版本的用时只有 1 分钟.
最优的版本
使用准备好的语句, 以 50 行为一个批次插入, 最终用时 34.3 秒.
作者又写了一个线程版本, 其中一个线程从通道接收数据, 还有四个线程向通道推送数据.
这个也是目前性能最好的版本, 最终用时大约 32.37 秒.
IO 时间
SQLite 论坛上的网友提出了一个有趣的想法: 测量内存数据库所需的时间.
于是作者又跑了一遍代码, 将数据库的位置设定为「:memory:」,rust 版本完成的时间少了两秒 (29 秒).
也就是说将 1 亿条记录写入到磁盘上需要 2 秒, 这个用时似乎也是合理的.
这也说明, 可能没有更多的 SQLite 优化可以以更快的方式写入磁盘, 因为 99% 的时间都花在生成和添加数据上.
排行榜
插入 1 亿行数据的用时:
Rust33 秒 PyPy126 秒 CPython210 秒
总结
尽可能使用 SQLite PRAGMA 语句
使用准备好的语句
进行分批插入
PyPy 确实比 CPython 快 4 倍
异步不一定更快
目前, 第二快的版本是单线程运行的, 而作者的电脑有 4 个核心, 于是他在一分钟内可以得到 8 亿行数据. 然后再经过几秒钟的数据合并, 时间仍然可以少于一分钟.
来源: http://news.51cto.com/art/202107/673136.htm