IT技术互动交流平台

SQLite性能

作者:它不是内存数据库,不要对IN-MEMORY望文生意。 - bbqz007  来源:IT165收集  发布日期:2016-11-17 20:00:59

SQLite创建的数据库有一种模式IN-MEMORY,但是它并不表示SQLite就成了一个内存数据库。IN-MEMORY模式可以简单地理解为,本来创建的数据库文件是基于磁盘的,现在整个文件使用内存空间来代替磁盘空间,其它操作保持一致。也就是数据库的设计没有根本改变。

提到内存,许多人就会简单地理解为,内存比磁盘速度快很多,所以内存模式比磁盘模式的数据库速度也快很多,甚至有人望文生意就把它变成等同于内存数据库。

它并不是为内存数据库应用而设计的,本质还是文件数据库。它的数据库存储文件有将近一半的空间是空置的,这是它的B树存储决定的,请参看上一篇SQLite存储格式。内存模式只是将数据库存储文件放入内存空间,但并不考虑最有效管理你的内存空间,其它临时文件也要使用内存,事务回滚日志一样要生成,只是使用了内存空间。它的作用应该偏向于临时性的用途。

我们先来看一下下面的测试结果,分别往memory和disk模式的sqlite数据库进行1w, 10w以及100w条数据的插入,采用一次性提交事务。另外使用commit_hook捕捉事务提交次数。

(注:测试场景为在新建的数据库做插入操作,所以回滚日志是很小的,并且无需要在插入过程中查找而从数据库加载页面,因此测试也并不全面)

内存模式

磁盘模式

在事务提交前的耗时 (事务提交后的总耗时):

  1w 10w 100w
内存模式 0.04s 0.35s 3.60s
磁盘模式 0.06s (0.27s) 0.47s (0.72s) 3.95s (4.62s)

可以看到当操作的数据越少时,内存模式的性能提高得越明显,事务IO的同步时间消耗越显注。

上图还有一组数据比较,就是在单次事务提交中,如果要为每条插入语句准备的话

  1w 10w 100w
内存模式 0.19s 1.92s 19.46s
磁盘模式 0.21s (0.35s) 2.06s (2.26s) 19.88s (20.41s)

我们从SQLite的设计来分析,一次插入操作,SQLite到底做了些什么。首先SQLite的数据库操作是以页面大小为单位的。在单条记录插入的事务中,回滚日志文件被创建。在B树中查找目标页面,要读入一些页面,然后将目标页面以及要修改的父级页面写出到回滚日志。操作目标页面的内存映像,插入一条记录,并在页面内重排序(索引排序,无索引做自增计数排序,参看上一篇《SQLite数据库存储格式》)。最后事务提交将修改的页面写出到数据库文件,成功后再删除日志文件。在这过程中显式进行了2次写磁盘(1次写日志文件,1次同步写数据库),还有2次隐式写磁盘(日志文件的创建和删除),这是在操作目录节点。以及为查找加载的页面读操作。更加详细可以参看官方文档的讨论章节《Atomic Commit In SQLite》。

如果假设插入100条记录,每条记录都要提交一次事务就很不划算,所以需要批量操作来减少事务提交次数。假设页面大小为4KB,记录长度在20字节内,每页可放多于200条记录,一次事务提交插入100条记录,假设这100条记录正好能放入到同一页面又没有产生页面分裂,这样就可以在单条记录插入事务的IO开销耗损代价中完成100条记录插入。

当我们的事务中,插入的数据越多,事务的IO代价就会摊得越薄,所以在插入100w条记录的测试结果中,内存模式和磁盘模式的耗时都十分接近。实际应用场合中也很少会需要一次插入100w的数据。有这样的需要就不要考虑SQLite。

(补充说明一下,事务IO指代同步数据库的IO,以及回滚日志的IO,只在本文使用)

除了IO外,还有没有其它地方也影响着性能。那就是语句执行。其实反观一切,都是在对循环进行优化。

for (i = 0; i < repeat; ++i)
{
    exec('BEGIN TRANS');
    exec('INSERT INTO ...');
    exec('END TRANS');
}

批量插入:

exec('BEGIN TRANS');
for (i = 0; i < repeat; ++i)
{
    exec('INSERT INTO ...');
}
exec('END TRANS');

当我们展开插入语句的执行

exec('BEGIN TRANS');
for (i = 0; i < repeat; ++i)
{
    // unwind exec('INSERT INTO ...');
    prepare('INSERT INTO ...');
    bind();
    step();
    finalize();
}
exec('END TRANS');

又发现循环内可以移出部分语句

exec('BEGIN TRANS');
// unwind exec('INSERT INTO ...');
prepare('INSERT INTO ...');
for (i = 0; i < repeat; ++i)
{
    bind();
    step();
}
finalize();
exec('END TRANS');

这样就得到了批量插入的最终优化模式。

所以对sql语句的分析,编译和释放是直接在损耗CPU,而同步IO则是在饥饿CPU。

请看下图

分别为内存模式1w和10w两组测试,每组测试包括4项测试

1.只编译一条语句,只提交一次事务

2.每次插入编译语句,只提交一次事务

3.只编译一条语句,但使用自动事务。

4.每次插入编译语句,并使用自动事务。

可以看到测试项目4基本上就是测试项目2和测试项目3的结果的和。

测试项目1就是批量插入优化的最终结果。

下面是探讨内存模式的使用:

经过上面的分析,内存模式在批量插入对比磁盘模式提升不是太显注的,请现在开始关注未批量插入的结果。

下面给出的是磁盘模式0.1w和0.2w两组测试,每组测试包括4项测试

可以看到在非批量插入情况,sqlite表现很差要100秒来完成1000次单条插入事务,但绝非sqlite很吃力,因为cpu在空载,IO阻塞了程序。

再来看内存模式20w测试

可以看到sqlite在内存模式,即使在20w次的单条插入事务,其耗时也不太逊于磁盘模式100w插入一次事务。

  0.1w 0.2w 20w
内存模式(非批量插入)     15.87s
磁盘模式(非批量插入) 97.4s 198.28s  
  编译1次插入语句 每次插入编译1次语句
内存模式(20w,20w次事务) 11.10s 15.87s
磁盘模式(100w,1次事务) 4.62s 20.41s

 不能给出内存模式100w次事务的测试结果是因为程序运行出问题。

在100w的插入一次性事务测试结果,内存模式和磁盘模式相差不到1秒,这1秒就是最后大量数据库同步到数据库的IO时间。

再回到上面两图两表的测试结果,磁盘模式在执行多事务显得偏瘫,每秒不多于10个单条插入的事务。而内存模式下执行事务的能力仍然坚挺,每秒1w次单条插入的事务也不在话下。

在实际应用中,数据随机实时,你又不想做批量插入控制,就可以考虑用内存模式将现有的数据马上用事务提交,不管事务提交的数据是多是少。你只要定制计划,将内存模式的数据库同步到你的外部数据库。因为每个内存模式的数据库是独立的,你同步一个内存模式的数据库到外部的期间,就可以同时使用另一个内存模式的数据库缓冲数据。

(上面删除段落是根据MinGW系统的测试结果。在真机环境测试了win 7 32bit和win 7 64bit,以及在它们之上使用mingw系统,测试结果是sqlite处理1000个单条插入事务总耗时在100秒级别。而在vm环境,vm虚拟磁盘上测试了xp,linux和macosx,测试结果是sqlite处理1000个单条插入事务总耗时在10秒级别内。值得注意的是,vm虚拟磁盘不是直接操作磁盘,所以我还要另找磁盘,挂接真实的磁盘对虚拟机环境进行测试。)


在经过慎重考虑后,在linux和mac环境下进行了测试,验证了一句“数据库都构建在痛苦的操作系统之上”。上面的测试环境是MinGw,痛苦的不是windows而是在windows之上加上的一层MinGw系统,磁盘操作十分痛苦。根据在linux和mac环境的测试结果,内存模式和磁盘模式在单条插入自动事务的性能更加接近,相差只有10倍左右,由于不用在MinGW这样的适配系统痛苦地操作磁盘,所以在其它批量插入事务的测试项目中,两种模式的测试结果更加趋于接近。

至于你想用sqlite的内存模式作持久用途或者去媲美内存数据库,可能不是正确的选择。sqlite是一个体积轻巧,可以帮你管理关系型数据的嵌入式数据库。它适应嵌入式的空间小,耗电低和占用内存有限的特殊环境。它的高效是不因为它的简单,而在基本的数据库查询功能上有打折扣。它在设计上有针对性的取舍,使它更适合某些应用场合,也必然在舍的部分蹩足。

本篇至此结束,谢谢观看。

后续会有':memory:','file:whereIsDb?mode=memory'以及'disk.db'这三种模式的对比。

测试代码在https://github.com/bbqz007/xw/test.sqlite.in-memory.zip。(不支持VC,需要自行下载编译sqlite。支持VC编译的测试代码未上传。)

mingw测试插入1000条数据使用自动事务,即一共提交1000次事务:

运行在      总耗时

xp (vm11)            9s

win 7 64bit        200s

win 7 32bit        100s

最后补上Linux (vm11)和MacOSX (vm11)的测试结果:

Linux 2.6.32-358.el6.x86_64
cpu MHz        : 3591.699
cpu MHz        : 3591.699
----- 10000 in memory ----
repeat insert 10000 times, in 1 trans, with 1 stmt prepared
 0.04s
 0.04s
commit: 1
repeat insert 10000 times, in 1 trans, with each stmt prepared
 0.06s
 0.06s
commit: 1
repeat insert 10000 times, in auto trans(s), with 1 stmt prepared
 0.02s
 0.02s
commit: 10000
repeat insert 10000 times, in auto trans(s), with each stmt prepared
 0.06s
 0.06s
commit: 10000
---- 100000 in memory ----
repeat insert 100000 times, in 1 trans, with 1 stmt prepared
 0.11s
 0.11s
commit: 1
repeat insert 100000 times, in 1 trans, with each stmt prepared
 0.40s
 0.40s
commit: 1
repeat insert 100000 times, in auto trans(s), with 1 stmt prepared
 1.28s
 1.28s
commit: 100000
repeat insert 100000 times, in auto trans(s), with each stmt prepared
 1.76s
 1.76s
commit: 100000
---- 200000 in memory ----
repeat insert 200000 times, in 1 trans, with 1 stmt prepared
 0.23s
 0.23s
commit: 1
repeat insert 200000 times, in 1 trans, with each stmt prepared
 0.87s
 0.87s
commit: 1
repeat insert 200000 times, in auto trans(s), with 1 stmt prepared
 7.35s
 7.35s
commit: 200000
repeat insert 200000 times, in auto trans(s), with each stmt prepared
 9.10s
 9.10s
commit: 200000
--- 1000000 in memory ----
repeat insert 1000000 times, in 1 trans, with 1 stmt prepared
 1.23s
 1.23s
commit: 1
repeat insert 1000000 times, in 1 trans, with each stmt prepared
 4.39s
 4.39s
commit: 1
 
 
------ 1000 in disk ----
rm: 无法删除'test.db': 没有那个文件或目录
repeat insert 1000 times, in 1 trans, with 1 stmt prepared
 0.00s
 0.00s
commit: 1
repeat insert 1000 times, in 1 trans, with each stmt prepared
 0.00s
 0.00s
commit: 1
repeat insert 1000 times, in auto trans(s), with 1 stmt prepared
 0.80s
 0.80s
commit: 1000
repeat insert 1000 times, in auto trans(s), with each stmt prepared
 0.87s
 0.87s
commit: 1000
------ 2000 in disk ----
repeat insert 2000 times, in 1 trans, with 1 stmt prepared
 0.00s
 0.00s
commit: 1
repeat insert 2000 times, in 1 trans, with each stmt prepared
 0.01s
 0.02s
commit: 1
repeat insert 2000 times, in auto trans(s), with 1 stmt prepared
 1.60s
 1.60s
commit: 2000
repeat insert 2000 times, in auto trans(s), with each stmt prepared
 2.27s
 2.27s
commit: 2000
----- 10000 in disk ----
repeat insert 10000 times, in 1 trans, with 1 stmt prepared
 0.01s
 0.02s
commit: 1
repeat insert 10000 times, in 1 trans, with each stmt prepared
 0.04s
 0.04s
commit: 1
---- 100000 in disk ----
repeat insert 100000 times, in 1 trans, with 1 stmt prepared
 0.11s
 0.11s
commit: 1
repeat insert 100000 times, in 1 trans, with each stmt prepared
 0.45s
 0.45s
commit: 1
--- 1000000 in disk ----
repeat insert 1000000 times, in 1 trans, with 1 stmt prepared
 1.27s
 1.34s
commit: 1
repeat insert 1000000 times, in 1 trans, with each stmt prepared
 4.51s
 4.57s
commit: 1
Linux测试结果

MacOSX的测试结果:

Tag标签: 性能  
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规