1. 概要

SQLiteを使うと小さなBLOB（例：サムネイル画像など）を読み書きする場合、fread()やfwrite()を使って個別のファイル上に記録されたBLOBを読み書きするよりも35％も速く (*1) 読み書きができます。

さらに、10キロバイトのBLOBを扱うようなSQLiteデータベースを考えた場合、個別のファイルにそれぞれのBLOBを格納する場合に比べてディスク領域を約20％も節約可能です。

このようなパフォーマンスの差が生じる理由は、（私たちの考えでは）SQLiteデータベースの場合、open()やclose()システムコールが呼び出されるのが1回だけなのに対して、個別のファイルに格納されているBLOBを使用する場合は、open()やclose()がBLOBの数だけ呼び出されるためだと思われます。どうやらopen()とclose()を呼び出すオーバーヘッドは、データベースを使用するオーバーヘッドよりも大きいようです。また、個別ファイルの場合、ファイルシステムのブロックサイズの次の倍数にパディングされるのに対し、SQLiteデータベースではBLOBがコンパクトに格納されるため、結果的にサイズも小さくなります。

この記事の測定値は、2017年6月5日の週に、SQLiteのバージョン3.19.2から3.20.0を使って得られたものです。将来的なバージョンでは、パフォーマンスがさらに向上する可能性があります。

1.1. 注意事項

(*1) 上に挙げた35％の数値は概算値です。実際の時間は、ハードウェアやOSおよび実験の内容によって異なりますし、実世界におけるハードウェアのパフォーマンスの不安定性も変動の要因となり得ます。詳細は以下の文章を参照してください。また、ご自身でもぜひ試してみてください。もし、大きな偏差がある場合、 メーリングリスト を通じて報告していただければ幸いです。

35％の数値は、著者の手近にあったコンピュータで実行したテストの結果に基づいています。この記事の一部のレビュアーからは、SQLiteが、彼らのシステムで直接I/Oよりもレイテンシが高かったということが報告されていますが、私たちはまだそれを確認できていません。また、ファイルシステムのコールドキャッシュを使用して実験を実行した場合、SQLiteでは直接I/Oほどのパフォーマンスが出ないという兆候が見られました。

この記事で皆さんに注目してもらいたいのは、SQLiteの読み込み/書き込みレイテンシが、ディスク上の個々のファイルの読み込み/書き込みレイテンシと拮抗するということです。また、多くの場合で高速なのはSQLiteです。ただし、速度がさほど変わらない場合もあります。いずれにしても、リレーショナルデータベースがファイルシステムの直接I/Oよりも遅いという通説をここで反証したいと思っています。

1.2. 関連研究

Jim Gray氏 らがMicrosoft SQL ServerのファイルI/Oに対してBLOBの読み込みパフォーマンスを研究したところ、BLOBのサイズが250KiBから1MiB未満の場合、データベースからBLOBを読み込んだ方が速いということが分かりました（ 論文 ）。その研究によると、コンテンツが別のファイルに保持された場合でも、データベースはコンテンツのファイル名を依然として格納しているといいます。従って、データベースは、たとえそれがファイル名を抽出するだけであっても、全てのBLOBに関して参照されることになるということです。この記事では、BLOBのキーはファイル名であるため、事前のデータベースアクセスは必要ありません。個別のファイルからコンテンツを読み込む際、この記事ではデータベースが使用されることは一度もないため、直接ファイルI/Oがより速くなるしきい値は、Gray氏の論文のしきい値よりも小さくなります。

このWebサイトに掲載された以前の Internal Versus External BLOBs（内部BLOBと外部BLOB） の記事は、Jim Gray氏の論文と同じアプローチ（BLOBファイル名をデータベースのエントリとして格納）を採用した調査（2011年頃）です。ただし、SQL Serverの代わりにSQLiteを使用しています。

2. 測定の方法

I/Oパフォーマンス測定には、SQLiteソースツリーに格納されている kvtest.c プログラムを使用します。このテストプログラムをコンパイルするには、まずkvtest.cソースファイルを、 SQLite amalgamation ソースファイルの”sqlite3.c”と”sqlite3.h”を持つディレクトリに集めます。そしてUnixで以下のようなコマンドを実行します。

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

あるいはWindowsでMSVCを使います。

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

Android用にコンパイルする場合は4.1を参照してください。

結果として得られた”kvtest”プログラムを使用して、100,000個の圧縮されていない無作為なBLOBを含むテストデータベースを生成します。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ディレクトリおよびそのコンテンツの全てが使用する領域を測定できます。標準的なLinuxのUbuntuデスクトップでは、データベースファイルのサイズは1,024,512,000バイトで、test1.dirディレクトリは1,228,800,000バイト（データベースよりも約20％多い）の領域を（”du -k”によると）使用しています。

上で作成された”test1.dir”ディレクトリは、全てのBLOBを単一のフォルダに入れます。ただ、一部のOSでは、1つのディレクトリに100,000個のオブジェクトが含まれていたりすると、パフォーマンスが低下する場合が考えられます。kvtestプログラムでは、フォルダごとに100個以下のファイルやサブディレクトリを持つことができるフォルダの階層にもBLOBを格納することができるため、両者を比較テストすることが可能です。BLOBのディスク上の代替表現は、以下のように、”export”コマンドに–treeコマンドラインオプションを使用して作成します。

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

test1.dirディレクトリに含まれるのは100,000個のファイルで、それぞれ”000000″、”000001″、”000002″などのような名前を持ちます。一方、test1.treeディレクトリでは、同じファイルが”00/00/00″、”00/00/01″などの名前でサブディレクトリに格納されます。占有領域は、test1.dirとtest1.treeディレクトリでほとんど変わりませんが、test1.treeの方が余分なディレクトリエントリがある分、わずかに多くなっています。

上記に続く全ての実験でも、”test1.dir”と”test1.tree”は同じような結果となり、どのOSを利用するかによらず、パフォーマンスの差はほとんど見られませんでした。

データベースおよび個々のファイルから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

お使いのハードウェアとOSにもよりますが、test1.dbデータベースファイルからの読み込みは、test1.dirまたはtest1.treeフォルダの個別ファイルからの読み込み比べて、35％ほど速くなるということが分かると思います。テストの結果は、キャッシュによって実行の度ごとに大きく異なる可能性があるため、テストは複数回実行し、必要に応じて、平均値、最悪の場合または最良の場合を取ることをお勧めします。

データベース読み込みテストで–blob-apiオプションを指定すると、kvtestは、純粋なSQL文を実行するのではなく、SQLiteの sqlite3_blob_read() 機能を使ってBLOBの内容を読み込みます。これによってSQLiteによる読み込みテストの実行が少し速くなってしまうのですが、もしSQL文を実行したSQLiteのパフォーマンスで比較したい場合は、このオプションを外してください。このオプションを外しても、SQLiteは依然として直接読み込みよりも優れたパフォーマンスを見せますが、 sqlite3_blob_read() を使った時と比べると、速度の差は小さくなります。なお、–blob-apiオプションは、個別のディスクファイルからの読み込みテストでは無視されます。

書き込みパフォーマンスを測定する際には、–updateオプションを追加します。これによってBLOBは、サイズが全く同じの別のランダムなBLOBで上書きされます。

./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測定の全てを1回のトランザクションで実行しますが、それぞれのBLOBの読み書きを個別のトランザクションで実行したい場合には、–multitransオプションを使用してください。この–multitransオプションを適用すると、SQLiteの処理速度は大幅に低下するため、直接ディスクI/Oとの比較において対抗できる状態ではなくなります。つまりこのオプションは、SQLiteのパフォーマンスを最大限に引き出したい場合には、できるだけ多くのデータベースをグループ化して単一のトランザクションにまとめる必要があるということを証明しているというわけです。

これら以外にもテスト用のオプションは数多くあり、以下のコマンドで確認できます。

./kvtest help

2.1. 読み込みパフォーマンスの測定

以下のグラフは、5つの異なるシステム上で、kvtest.cを使用して収集したデータを示しています。
* Win7：circa-2009 Dell Inspiron、2.30GHz Pentiumデュアルコア、4GiB RAM、Windows7
* Win10：2016 Lenovo YOGA 910、Intel i7-7500（2.70GHz）、16GiB RAM、Windows10
* Mac：2015 MacBook Pro、3.1GHz Intel Core i7、16GiB RAM、MacOS 10.12.5
* Ubuntu：Intel i7-4770K（3.50GHz）、32GiB RAMの自作PC、Ubuntu 16.04.2 LTS
* Android：Galaxy S3、ARMv7、2GiB RAM

Win7だけはハードドライブですが、その他のPCはSSDを使っています。テストデータベースは、サイズが8Kから12Kの間で均等に分散された100KのBLOBで、合計約1ギガバイトのコンテンツです。データベースのページサイズは4KiB。全てのテストには、-DSQLITE_DIRECT_OVERFLOW_READコンパイル時オプションが適用されています。テストは複数回実行しました。初回はキャッシュをなじませるためのもので、その時間は測定対象に含めていません。

下のグラフは、ファイルシステムのファイルにおけるBLOB読み込み時間と対比させて、SQLiteデータベースにおける同じBLOBの読み込み時間の平均で示したものです。小さいほうが良好で、実際の時間はシステムによって大きく異なります（例えば、UbuntuデスクトップはGalaxy S3よりもはるかに高速です）。このグラフでは、ディスクから直接あるBLOBを読み込む際に掛かった時間を、同じBLOBをSQLiteから読み込む際に掛かった時間で割った比率を示しています。グラフの一番左側の列は、SQLiteからの読み込み時間をそれ自体で割ったもので、常に値は1.00です（この列は視覚的な参照のためだけに置かれています）。

ここでは、SQL文（”SELECT v FROM kv WHERE k=?1″）が一度準備されます。次いで、それぞれのBLOBに対して、BLOBキー値が？1パラメータにバインドされ、BLOBコンテンツを抽出するためにステートメントが評価されます。

この図から、Windows10ではファイルからの直読み込みと比較した場合、SQLiteからの読み込みの方が5倍ほど早く読み込める可能性があることが分かります。一方でAndroid環境では、SQLiteはほんの35%ほどの性能向上のみが確認できました。

グラフ1：ファイルシステムの直接読み込みに対するSQLiteの読み込みレイテンシ
100KBLOB、それぞれの平均サイズ10KB、順序はランダムでSQLを使用

次のグラフで示すように、 sqlite3_blob_read() インターフェースを使い、SQLレイヤーをバイパスしてBLOBコンテンツを直接読み込むと、パフォーマンスはわずかに向上します。

グラフ2：ファイルシステムの直接読み込みに対するSQLiteの読み込みレイテンシ
100KBLOB、平均サイズ10KB、順序はランダム
sqlite3_blob_read()を使用

SQLiteの メモリマップドI/O 機能を使用すると、パフォーマンスはさらに向上します。次のグラフが示しているのは、1GBデータベースファイルの全体がメモリマップされ、 sqlite3_blob_read() インターフェースを使って（ランダムな順序で）BLOBが読み込まれた結果です。

グラフ3：ファイルシステムの直接読み込みに対するSQLiteの読み込みレイテンシ
100KBLOB、平均サイズ10KB、順序はランダム
メモリマップされたデータベースでsqlite3_blob_read()を使用

3つ目のグラフで分かるのは、SQLiteからのBLOBコンテンツの読み込みが、MacおよびAndroidディスク上の個別ファイルからの読み込みの2倍、Windowsに至っては10倍の速さであるということです。

2.2. 書き込みパフォーマンスの測定

書き込みはそれ自体が遅いものです。全てのシステムにおいて、直接I/OとSQLiteの両方で、書き込みパフォーマンスは読み込みよりも5～15倍遅い結果となりました。

書き込みパフォーマンスの測定は、BLOB全体を別のBLOBに置き換える（上書きする）ことで行います。実験に使われる全てのBLOBは、ランダムで未圧縮のデータです。読み込みよりもはるかに遅いため、データベース内の100,000個のBLOBのうち置き換えられるのはわずか10,000個のみです。BLOBは順不同でランダムに選択され、置き換えられます。

ディスクへの直接書き込みには、fopen()/fwrite()/fclose()を使用します。デフォルトの状態、または以下に示す全ての結果によれば、OSのファイルシステムのバッファは、fsync()またはFlushFileBuffers()を使用して永続ストレージにフラッシュされることはありません。つまり、ディスクへの直接書き込みを、トランザクションまたはパワーセーフにする試みはないということです。この実験を通じて、書き込みされる各ファイルに対してfsync()やFlushFileBuffers()が呼び出されることによって、ディスクへの直接記憶領域がSQLiteへの書き込みより約10倍以上遅くなることが分かりました。

以下のグラフでは、 WALモード におけるSQLiteデータベースのデータベース更新処理と、ディスクへの直接上書きとを比較しています。 PRAGMA同期 はNORMALに設定し、全てのデータベース書き込みは単一のトランザクションで行います。データベース書き込みのタイマーは、トランザクションがコミットされた後、 チェックポイント が実行される前に停止されます。ちなみに、ディスクへの直接書き込みとは異なり、SQLiteの書き込みは トランザクショナル で パワーセーフ であることに注意してください。ただし、同期設定がFULLではなくNORMALであるため、トランザクションに持続性はありません。

グラフ4：ファイルシステムの直接書き込みに対するSQLiteの書き込みレイテンシ
10KBLOB、平均サイズ10KB、順序はランダム
同期をNORMALに設定したWALモード
チェックポイント時を除く

書き込み実験のAndroidのパフォーマンス数値は、Galaxy S3のテスト結果にバラツキが目立ったため省略しています。まったく同じ実験を2回連続で実行しても、結果の時間は大きく異なりました。なお、公平を期すために言っておくと、Android上のSQLiteのパフォーマンスは、ディスクに直接書き込むよりもわずかに遅い結果でした。

以下のグラフは、トランザクションが無効（ PRAGMA journal_mode=OFF ）で、 PRAGMA同期 がOFFに設定されている時の、SQLiteとディスクへの直接書き込みのパフォーマンスを示しています。これらの設定によりシステムクラッシュや停電によってデータが破損しやすくなるため、SQLiteの条件はディスクへの直接書き込みの条件により近くなります。

グラフ5：ファイルシステムの直接書き込みに対するSQLiteの書き込みレイテンシ
10KBLOB、平均サイズ10KB、順序はランダム
journal_mode=OFF、同期OFF

全ての書き込みテストにおいて重要なのは、ディスクへの直接書き込みテストを実行する前にウイルス対策ソフトウェアを無効にするということです。ウイルスソフトの存在はSQLiteではさほど大きな問題となりませんでしたが、直接書き込みの場合は、ウイルスソフトの有無で一桁ほど速度に違いが出ました。その理由は恐らく、SQLiteでは書き込みによる変更が単一のデータベースファイルのみなのに対し、直接書き込みでは何千もの個別ファイルが変更され、ウイルスソフトがそれを逐一チェックするためだろうと思われます。

2.3. バリエーション

-DSQLITE_DIRECT_OVERFLOW_READ コンパイル時オプションを適用すると、SQLiteはオーバーフローページからコンテンツを読み込む際に、そのページキャッシュをバイパスするようになります。これにより、データベースが10KのBLOBを読み込む速度が多少は速くなりますが、劇的というわけではありません。SQLITE_DIRECT_OVERFLOW_READコンパイル時オプションなしでも、ファイルシステムの直接読み込みに対するSQLiteの速度的な優位性は維持されます。

-Osの代わりに-O3を使用したり、 -DSQLITE_THREADSAFE=0 やその他の 推奨されるコンパイル時オプション を使用したりすることで、ファイルシステムの直接読み込みに対して、SQLiteがさらに速く動作するようになる場合もあります。

テストデータ内のBLOBサイズはパフォーマンスに影響します。一般的に、BLOBサイズが大きいほど、open()やclose()のオーバーヘッドが、I/Oのより大きなバイトで償却されるため、ファイルシステムは高速になります。それに対してデータベースは、BLOBの平均サイズが減少するにつれて、速度と領域のいずれにおいてもより効率的となります。

3. 結果のまとめ

A. SQLiteでは、BLOBがディスク上の別々のファイルに格納された場合と比べると、読み書きの両方において速度は同等か、通常はより速くなります。
B. ウイルス対策ソフトウェアが有効になっている場合、SQLiteは、Windows上のディスクへの直接書き込みよりもはるかに高速になります。Windowsではウイルスソフトがデフォルトで有効になっている場合がほとんどであり、またその必要もあるため、一般的にWindowsでは、SQLiteの方がディスクへの直接書き込みよりもはるかに高速です。
C. SQLiteおよびディスクへの直接I/Oの両方において、全てのシステムを通じて、読み込みは書き込みより約一桁高速です。
D. I/Oのパフォーマンスは、OSおよびハードウェアによって大きく異なります。結論を出す前に、まずはご自身の環境で測定してください。
E. 一部のSQLデータベースエンジンでは、BLOBを別々のファイルに格納し、ファイル名をデータベースに格納するよう推奨しています。ただし、ファイルを開いて読み込む前に、データベースを参照してファイル名を見つける必要があるような場合、単純にデータベースにBLOB全体を格納すると、SQLiteを使った読み書きのパフォーマンスは大幅に向上します。詳細については、 Internal Versus External BLOBs（内部BLOBと外部BLOB） を参照してください。

4. 補足事項

4.1. 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デバイスでシェルプロンプトを表示し、/data/local/tmpディレクトリにcdして、他のUnixホストと同じようにテストを実行します。