小. 快速. 可靠.
选择任意三个.
比文件系统快35%

1. Summary

SQLite读写小块(例如缩略图)的速度比使用fread()或fwrite()从磁盘上的单个文件读取或写入相同的blob 快35%¹ .

此外,一个拥有10 KB blob的SQLite数据库所使用的磁盘空间比将blob存储在单个文件中的磁盘空间少约20%.

之所以会出现性能差异,是因为在SQLite数据库中工作时,仅使用一次调用open()和close()系统调用,而使用存储在其中的blob对每个blob调用一次open()和close()一次.单个文件. 看来,调用open()和close()的开销大于使用数据库的开销. 大小减小是由于以下事实:将单个文件填充到文件系统块大小的下一个倍数,而将斑点更紧密地打包到SQLite数据库中.

本文中的测量是在2017年6月5日那一周使用3.19.2和3.20.0之间的SQLite版本进行的. 您可能希望SQLite的将来版本能表现得更好.

1.1. Caveats

¹以上数字为35%. 实际时间取决于硬件,操作系统和实验的详细信息,以及实际硬件上随机的性能波动. 有关更多详细信息,请参见下面的文本. 自己尝试实验. 向邮件列表报告重大偏差.

35%的数字是基于作者容易掌握的每台计算机上运行的测试得出的. 本文的一些审阅者报告说,SQLite具有比其系统上直接I / O更高的延迟. 我们尚不了解区别. 当使用冷文件系统缓存运行实验时,我们还看到有迹象表明SQLite的性能不如直接I / O.

因此,让您获得一点收获:SQLite的读/写延迟与磁盘上单个文件的读/写延迟相比具有竞争力. 通常,SQLite速度更快. 有时,SQLite几乎一样快. 无论哪种方式,本文都不支持通常的假设,即关系数据库必须比直接文件系统I / O慢.

Jim Gray和其他人研究了Microsoft SQL Server的BLOB与文件I / O的读取性能,发现对于小于250KiB到1MiB的BLOB,从数据库中读取BLOB的速度更快. ( ). 在该研究中,即使内容保存在单独的文件中,数据库仍会存储内容的文件名. 因此,即使只是提取文件名,每个BLOB都会查询数据库. 在本文中,BLOB的密钥是文件名,因此不需要初步的数据库访问. 因为在本文中从单个文件读取内容时根本不使用数据库,所以直接文件I / O变得更快的阈值比Gray的论文中的阈值小.

该网站上的《 内部与外部BLOB》一文是一项较早的调查(大约在2011年),使用的方法与Jim Gray论文相同-将blob文件名存储为数据库中的条目-但适用于SQLite而不是SQL Server.

2. How These Measurements Are Made

使用SQLite源代码树中的kvtest.c程序测量I / O性能. 要编译此测试程序,请首先将kvtest.c源文件收集到包含SQLite合并源文件" sqlite3.c"和" sqlite3.h"的目录中. 然后在UNIX上,运行如下命令:

gcc -Os -I. -DSQLITE_DIRECT_OVERFLOW_READ \
  kvtest.c sqlite3.c -o kvtest -ldl -lpthread

或在具有MSVC的Windows上:

cl -I. -DSQLITE_DIRECT_OVERFLOW_READ kvtest.c sqlite3.c

Android的编译说明如下所示 .

使用所得的" kvtest"程序使用以下命令生成包含100,000个随机不可压缩的blob(每个随机大小在8,000至12,000字节之间)的测试数据库:

./kvtest init test1.db --count 100k --size 10k --variance 2k

如果需要,可以通过运行以下命令来验证新数据库:

./kvtest stat test1.db

接下来,使用以下命令将所有Blob的副本复制到目录中的单个文件中:

./kvtest export test1.db test1.dir

此时,您可以测量test1.db数据库使用的磁盘空间量以及test1.dir目录使用的磁盘空间及其所有内容. 在标准Ubuntu Linux桌面上,数据库文件的大小为1,024,512,000字节,而test1.dir目录将使用1,228,800,000字节的空间(根据" du -k"),大约比数据库大20%.

上面创建的" test1.dir"目录将所有Blob放入一个文件夹中. 据推测,当单个目录包含100,000个对象时,某些操作系统的性能将很差. 为了对此进行测试,kvtest程序还可以将blob存储在文件夹的层次结构中,每个文件夹最多包含100个文件和/或子目录. 可以使用"导出"命令的--tree命令行选项来创建Blob的备用磁盘表示形式,如下所示:

./kvtest export test1.db test1.tree --tree

test1.dir目录将包含100,000个文件,其名称类似于" 000000"," 000001"," 000002"等,但test1.tree目录将在子目录中包含相同的文件,例如" 00/00/00"," 00" / 00/01",依此类推. 尽管由于额外的目录条目,test1.test的空间略大,但test1.dir和test1.test目录占用的空间大致相同.

随后的所有实验都使用" test1.dir"或" test1.tree"进行相同的操作. 不管使用哪种操作系统,在两种情况下都测得的性能差异很小.

使用以下命令测量从数据库和单个文件读取Blob的性能:

./kvtest run test1.db --count 100k --blob-api
./kvtest run test1.dir --count 100k --blob-api
./kvtest run test1.tree --count 100k --blob-api

根据您的硬件和操作系统,您应该看到从test1.db数据库文件读取的速度比从test1.dir或test1.tree文件夹中的各个文件的读取速度快35%. 由于缓存,结果在一次运行与下一次运行之间可能会有很大差异,因此建议多次运行测试,并根据您的要求选择平均,最差情况或最佳情况.

数据库读取测试中的--blob-api选项使kvtest使用SQLite的sqlite3_blob_read()功能来加载blob的内容,而不是运行纯SQL语句. 这有助于SQLite在读取测试上更快地运行. 您可以省略该选项以比较运行SQL语句的SQLite的性能. 在那种情况下,尽管与使用sqlite3_blob_read()相比 ,SQLite的性能仍然好于直接读取. 从单个磁盘文件读取的测试将忽略--blob-api选项.

通过添加--update选项来评估写入性能. 这会导致斑点被另一个大小完全相同的随机斑点覆盖到位.

./kvtest run test1.db --count 100k --update
./kvtest run test1.dir --count 100k --update
./kvtest run test1.tree --count 100k --update

上面的写入测试并不完全公平,因为SQLite正在执行电源安全事务,而直接磁盘写入则不是. 为了使测试更加平等,可以在SQLite写入中添加--nosync选项以禁止调用fsync()或FlushFileBuffers()来将内容强制写入磁盘,或者使用--fsync选项进行直接定向.磁盘测试,以强制他们在更新磁盘文件时调用fsync()或FlushFileBuffers().

默认情况下,kvtest在单个事务中全部运行数据库I / O测量. 使用--multitrans选项可以在单独的事务中运行每个blob读取或写入操作. --multitrans选项使SQLite慢得多,并且与直接磁盘I / O不具有竞争性. 该选项再次证明,要使SQLite发挥最大性能,您应该在单个事务中将尽可能多的数据库交互分组.

还有许多其他测试选项,可以通过运行以下命令来查看:

./kvtest help

2.1. Read Performance Measurements

下图显示了在五个不同系统上使用kvtest.c收集的数据:

除Win7带有硬盘驱动器外,所有计算机均使用SSD. 测试数据库是10万个Blob,大小均匀地分布在8K和12K之间,总共约有1 GB的内容. 数据库页面大小为4KiB. 所有这些测试都使用了-DSQLITE_DIRECT_OVERFLOW_READ编译时选项. 测试进行了多次. 第一次运行用于预热高速缓存,并且其定时被丢弃.

下表显示了直接从文件系统读取blob的平均时间与从SQLite数据库读取同一blob所需的时间. 从一个系统到另一个系统的实际时间差异很大(例如,Ubuntu桌面比Galaxy S3手机要快得多). 此图表显示了从文件读取Blob所需的时间除以从数据库读取所需的时间的比率. 图表中最左侧的列是从数据库读取的标准化时间,以供参考.

在此图表中,仅准备了一条SQL语句(" SELECT v FROM kv WHERE k =?1"). 然后,对于每个Blob,将Blob键值绑定到?1参数,并评估该语句以提取Blob内容.

The chart shows that on Windows10, content can be read from the SQLite database about 5 times faster than it can be read directly from disk. On Android, SQLite is only about 35% faster than reading from disk.


图表1:相对于直接文件系统读取的SQLite读取延迟.
100K Blob,平均每个10KB,使用SQL随机排序

通过绕过SQL层并使用sqlite3_blob_read()接口直接读取blob内容,可以稍微提高性能,如下图所示:


图表2:相对于直接文件系统读取的SQLite读取延迟.
100K Blob,平均大小10KB,随机顺序
使用sqlite3_blob_read().

通过使用SQLite的内存映射I / O功能,可以进一步提高性能. 在下一张图表中,整个1GB数据库文件都进行了内存映射,并使用sqlite3_blob_read()接口(随机顺序)读取了blob. 通过这些优化,SQLite的速度是Android或MacOS-X的两倍,是Windows的十倍以上.


图表3:相对于直接文件系统读取的SQLite读取延迟.
100K Blob,平均大小10KB,随机顺序
使用内存映射数据库中的sqlite3_blob_read().

第三张图表显示,对于Mac和Android,从SQLite中读取blob内容的速度是从磁盘上的单个文件读取速度的两倍,而对于Windows,则是惊人的十倍.

2.2. Write Performance Measurements

写比较慢. 在所有使用直接I / O和SQLite的系统上,写入性能比读取速度慢5到15倍.

写入性能测量是通过将整个Blob替换为另一个Blob来进行的. 这些实验中的所有斑点都是随机且不可压缩的. 由于写入比读取慢得多,因此数据库中100,000个Blob中只有10,000个被替换. 要替换的斑点是随机选择的,并且没有特定的顺序.

使用fopen()/ fwrite()/ fclose()完成对磁盘的直接写入. 默认情况下,在下面显示的所有结果中,从不使用fsync()或FlushFileBuffers()将OS文件系统缓冲区刷新到持久性存储中. 换句话说,没有尝试使直接磁盘写入事务或电源安全. 我们发现,在每个写入的文件上调用fsync()或FlushFileBuffers()导致直接磁盘存储的速度比写入SQLite的速度慢10倍左右或更多.

下图比较了WAL模式下的 SQLite数据库更新与磁盘上单独文件的原始直接磁盘覆盖. PRAGMA同步设置为NORMAL. 所有数据库写操作都在单个事务中. 在事务提交之后但在运行检查点之前,数据库写计时器将停止. 请注意,与直接磁盘写入不同,SQLite写入具有事务性电源安全性 ,但由于同步设置为NORMAL而不是FULL,因此事务不持久.


图4:相对于直接文件系统写入的SQLite写入延迟.
10K Blob,平均大小10KB,随机顺序,
具有同步NORMAL的WAL模式
不包括检查点时间

省略了用于写入实验的android性能数字,因为Galaxy S3的性能测试是如此随机. 连续两次完全相同的实验将给出截然不同的时间. 而且,公平地说,Android上SQLite的性能比直接写入磁盘要慢一些.

下一张图表显示了禁用事务( PRAGMA journal_mode = OFF )并将PRAGMA sync设置为OFF时,SQLite与直接磁盘的性能. 这些设置使SQLite在磁盘直接写入方面处于平等的地位,也就是说,由于系统崩溃和电源故障,它们使数据易于损坏.


图5:相对于直接文件系统写入的SQLite写入延迟.
10K Blob,平均大小10KB,随机顺序,
禁用日记功能,同步关闭.

在所有写测试中,在运行直接磁盘性能测试之前,禁用防病毒软件很重要. 我们发现,防病毒软件将直接引导到磁盘的速度降低了一个数量级,而对SQLite写入的影响却很小. 这可能是由于直接磁盘更改了成千上万个需要防病毒检查的单独文件,而SQLite只写了一个数据库文件.

2.3. Variations

-DSQLITE_DIRECT_OVERFLOW_READ编译时选项使SQLite从溢出页面读取内容时绕过其页面缓存. 这可以帮助数据库读取10K Blob的速度更快一些,但并没有那么快. 与不使用SQLITE_DIRECT_OVERFLOW_READ编译时选项的直接文件系统读取相比,SQLite仍具有速度优势.

Other compile-time options such as using -O3 instead of -Os or using -DSQLITE_THREADSAFE=0 and/or some of the other recommended compile-time options might help SQLite to run even faster relative to direct filesystem reads.

测试数据中斑点的大小会影响性能. 对于更大的Blob,文件系统通常会更快,因为open()和close()的开销将在更多I / O字节上摊销,而随着平均Blob大小的减小,数据库在速度和空间上都将更加高效.

3. General Findings

  1. 在读取和写入方面,SQLite与存储在磁盘上单独文件中的Blob具有竞争性,并且通常比它们更快.

  2. 启用防病毒保护后,SQLite的速度比Windows上直接写入磁盘要快得多. 由于防病毒软件在Windows中默认是并且应该默认打开,因此这意味着SQLite通常比Windows上的直接磁盘写入要快得多.

  3. 对于所有系统以及SQLite和直接磁盘I / O,读取都比写入快一个数量级.

  4. I / O性能因操作系统和硬件而异. 在得出结论之前进行自己的测量.

  5. Some other SQL database engines advise developers to store blobs in separate files and then store the filename in the database. In that case, where the database must first be consulted to find the filename before opening and reading the file, simply storing the entire blob in the database gives much faster read and write performance with SQLite. See the Internal Versus External BLOBs article for more information.

4. Additional Notes

4.1. Compiling And Testing on Android

kvtest程序如下编译并在Android上运行. 首先安装Android SDK和NDK. 然后准备一个名为" android-gcc"的脚本,该脚本大致如下所示:

#!/bin/sh
#
NDK=/home/drh/Android/Sdk/ndk-bundle
SYSROOT=$NDK/platforms/android-16/arch-arm
ABIN=$NDK/toolchains/arm-linux-androideabi-4.9/prebuilt/linux-x86_64/bin
GCC=$ABIN/arm-linux-androideabi-gcc
$GCC --sysroot=$SYSROOT -fPIC -pie $*

使该脚本可执行,并将其放在$ PATH中. 然后按如下所示编译kvtest程序:

android-gcc -Os -I. kvtest.c sqlite3.c -o kvtest-android

接下来,将生成的kvtest-android可执行文件移动到Android设备:

adb push kvtest-android /data/local/tmp

最后,使用" adb shell"在Android设备上获取shell提示,将CD插入/ data / local / tmp目录,然后像其他任何Unix主机一样开始运行测试.

by  ICOPY.SITE