Documentation Source Text

   {35% Faster Than The Filesystem}</tcl>

<table_of_contents>

<h1>Summary</h1>

<p>Small blobs (for example, thumbnail images)
can be read out of an SQLite database about 35% faster

than they can be read from individual files on disk.

<p>Furthermore, a single SQLite database holding
10-kilobyte blobs uses about 20% less disk space than
storing the blobs in individual files.

<p>The performance difference arises (we believe) because when
reading from an SQLite database, the open() and close() system calls
are invoked only once, whereas
open() and close() are invoked once for each blob
when reading the blobs from individual files.  It appears that the
overhead of calling open() and close() is greater than the overhead
of using the database.  The size reduction arises from the fact that
individual files are padded out to the next multiple of the filesystem
block size, whereas the blobs are packed more tightly into an SQLite
database.











<h1>How These Measurements Are Made</h1>

<p>The performance comparison is accomplished using the
[https://www.sqlite.org/src/file/test/kvtest.c|kvtest.c] program
found in the SQLite source tree.
To compile the test program, first gather the kvtest.c source file
into a directory with the [amalgamation|SQLite amalgamation] source
files "sqlite3.c" and "sqlite3.h".  Then on unix, run a command like
the following:

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

<codeblock>
cl -I. -DSQLITE_DIRECT_OVERFLOW_READ kvtest.c sqlite3.c
</codeblock>

<p>
Use the resulting "kvtest" program to
generate a test database with 100,000 random blobs, each 10,000 bytes in

size using a command like this:

<codeblock>
./kvtest init test1.db --count 100k --size 10k







</codeblock>

<p>
Next, make copies of all the blobs into individual files in a directory
using commands like this:

<codeblock>
mkdir test1.dir
./kvtest export test1.db test1.dir
</codeblock>

<p>
At this point, you can measure the amount of disk space used by
the test1.db database and the space used by the test1.dir directory
and all of its content.  On a standard Ubuntu Linux desktop, the
database file will be 1,024,512,000 bytes in size and the test1.dir
directory will use 1,228,800,000 bytes of space (according to "du -k"),
about 20% more than the database.

<p>


























Measure the performance for reading blobs from the database and from
individual files using these commands:

<codeblock>
./kvtest run test1.db --count 100k --blob-api
./kvtest run test1.dir --count 100k

</codeblock>

<p>
Depending on your platform, you should see that reads from the test1.db
database file are about 35% faster than reads from individual files in
the test1.dir folder.





















































































































































































<h2>Variations</h2>

<p>The [-DSQLITE_DIRECT_OVERFLOW_READ] compile-time option causes SQLite
to bypass its page cache when reading content from overflow pages.  This
helps database reads of 10K blobs run a little faster, but not all that much
faster.  SQLite still holds a speed advantage over direct filesystem reads
................................................................................
without the SQLITE_DIRECT_OVERFLOW_READ compile-time option.

<p>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.

<p>When constructing the test data, trying varying the size of the blob.
The performance advantage will shift toward direct filesystem reads as
the size of blobs increase, since the cost of invoking open() and close()
will be amortized over more bytes transferred using read().  The break-even
point, the point where it becomes faster to read directly from the filesystem,
will vary from one system to another.  In the other direction, reducing the
blob size provide more advantage to database reads.  With a 5 KB blob size,
reading from the database is twice as fast and uses 60% less space than
blobs stored as individual files.

<p>The --blob-api option causes database reads to occur using the
[sqlite3_blob_open()], [sqlite3_blob_reopen()], and [sqlite3_blob_read()]
interfaces instead of using SQL statements.  Without the --blob-api
option, a separate SQL statement is run to read each blob and
the performance of reading from the database is approximately
the same as the performance from reading directly from files.
This is still a significant finding, since few people would
expect a [full-featured SQL] database to run as fast as direct file reads,
and yet SQLite does.

<p>The --random option on the "run" command causes the
blobs to be read in a random order.  This causes the performance of database
reads to decrease.  The reason is that the blobs are tightly packed in
the database, rather than being padded out to the next block size as when
they are stored in the filesystem.  Some pages contain parts of
adjacent blobs.  When the blobs are read sequentially, those pages are
only read into memory once and cached and then used to reconstruct
adjacent blobs, but when blobs are read in a random order, those pages
that share parts of two or more blobs tend to be read multiple times,
leading to decreased performance.


<p>The "--mmap SIZE" option on the "run" command causes the database file
to be accessed using mmap() instead of via read().  The SIZE argument is
the size of the memory mapped region, and should be the size of the database
file for maximum performance.  Using "--mmap 1G" causes the database reads
to be almost twice as fast as disk reads even when the --random
option is used.





<p>When --random is used and both --blob-api and --mmap are omitted,
reading directly from files on disk is generally a little faster, but
reads from the database are still competitive.




<h1>Other Considerations</h1>





<p>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 is
gives much faster read performance with SQLite.
See the [Internal Versus External BLOBs] article for more information.

<p>This report only looks at the performance of reads, not writes.
Because SQLite implements [atomic commit|power-safe ACID transactions]
we expect that write performance into SQLite will be slower than writing
directly to individual files.  However, if ACID transactions are disabled
via [PRAGMA journal_mode|PRAGMA journal_mode=OFF]
(thus putting SQLite on equal footing with the filesystem) and the
[sqlite3_blob_write()] interface is used, SQLite might well be competitive
or even faster than writes to separate files on disk.  That is an
experiment we have not yet run.

<p>Remember that the relative performance of database reads and reads from
the filesystem will depend on both the hardware and the operating system.
Please try the tests above on your own system.  If you encounter cases
there database reads do not perform favorably in comparison to filesystem
reads, please report your findings in the [mailing lists|SQLite mailing list].

<h1>Update as of 2017-05-22</h1>

<p>The table below shows data collected using 
[https://www.sqlite.org/src/file/test/kvtest.c|kvtest.c] on five different
systems:  An old Dell laptop running Windows7, a new Lenovo laptop running
Windows10, a Mac-Pro, an Ubuntu desktop machine, and an older Android
phone (a Galaxy S3). All machines use SSD except the Dell which has a
hard-drive. The test database is 100K BLOBs uniformly
distributed between 8K and 12K in size, for a total of about 1 gigabyte
of content.  The database page size
is 4KiB.  The chart shows average BLOB access time in microseconds.

<center>
<table border=1>
<tr>
<th>kvtest arguments
<th>Win7
<th>Win10
<th>MacPro
<th>Ubuntu
<th>Android
</tr>
<tr>
<td>(direct file access)
<td>100<td>46<td>7.9<td>3.2<td>145
<tr>
<td>(plain database access)
<td>22<td>6.9<td>4.4<td>2.4<td>128
<tr>
<td>--blob-api
<td>18<td>5.8<td>3.6<td>1.9<td>105
<tr>
<td>--blob-api --random
<td>28<td>8.4<td>4.8<td>2.6<td>135
<tr>
<td>--mmap 1G
<td>9<td>3.4<td>5.2<td>2.1<td>45
<tr>
<td>--mmap 1G --blob-api --random
<td>13<td>4.4<td>4.7<td>2.2<td>78
</table>
</center>

<p>
Every run of kvtest gives a slightly different time, of course.  The
numbers above are averages over between three and five runs and are
rounded to reflect the fact that they are imprecise.
Always remember:  <i>Your mileage may vary</i>.
Rerun these tests yourself on your own hardware using data that is
a close match to your production data before drawing conclusions.

<p>
Notice that the individual file access times on Windows are dramatically
slower than on the unix systems.  This might be because the 100K BLOBs are
all stored in a single directory and Windows is inefficient at searching
directories with large numbers of files.  The Windows file access times
might be improved if the BLOBs were stored in a hierarchy of directories,
rather than dumping them all into a single big directory.

<h2>Key Points</h2>

<ol>
<li><p>
Do not assume that directory file I/O is faster than using an SQLite database.
Reading from SQLite can be faster, sometimes much faster, than reading
separate files from disk.

<li><p>
The relative performance between direct file access and database access
depends a lot on the operating system, the hardware, and what database
access method is being used.  Make your own measurements.
</ol>

   {35% Faster Than The Filesystem}</tcl>

<table_of_contents>

<h1>Summary</h1>

<p>Small blobs (for example, thumbnail images)
can be read from and written to an SQLite database about 
<a href="#approx">35% faster&sup1;</a>
than they can be read from or written to individual files on disk.

<p>Furthermore, a single SQLite database holding
10-kilobyte blobs uses about 20% less disk space than
storing the blobs in individual files.

<p>The performance difference arises (we believe) because when
working from an SQLite database, the open() and close() system calls
are invoked only once, whereas
open() and close() are invoked once for each blob
when using blobs stored in individual files.  It appears that the
overhead of calling open() and close() is greater than the overhead
of using the database.  The size reduction arises from the fact that
individual files are padded out to the next multiple of the filesystem
block size, whereas the blobs are packed more tightly into an SQLite
database.

<a name="approx"><p>
&sup1;The 35% figure above is approximate.  Actual timings vary
depending on hardware, operating system, and the
details of the experiment, and due to random performance fluctuations
on real-world hardware.  See the text below for more detail.
Try the experiments yourself.  Report significant deviations to
the [mailing lists].
</p></a>


<h1>How These Measurements Are Made</h1>

<p>I/O performance is measured using the
[https://www.sqlite.org/src/file/test/kvtest.c|kvtest.c] program
from the SQLite source tree.
To compile this test program, first gather the kvtest.c source file
into a directory with the [amalgamation|SQLite amalgamation] source
files "sqlite3.c" and "sqlite3.h".  Then on unix, run a command like
the following:

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

<codeblock>
cl -I. -DSQLITE_DIRECT_OVERFLOW_READ kvtest.c sqlite3.c
</codeblock>

<p>
Use the resulting "kvtest" program to
generate a test database with 100,000 random blobs, each with a random
size between 8,000 and 12,000 bytes
using a command like this:

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

<p>
If desired, you can verify the new database by running this command:

<codeblock>
./kvtest stat test1.db
</codeblock>

<p>
Next, make copies of all the blobs into individual files in a directory
using a command like this:

<codeblock>

./kvtest export test1.db test1.dir
</codeblock>

<p>
At this point, you can measure the amount of disk space used by
the test1.db database and the space used by the test1.dir directory
and all of its content.  On a standard Ubuntu Linux desktop, the
database file will be 1,024,512,000 bytes in size and the test1.dir
directory will use 1,228,800,000 bytes of space (according to "du -k"),
about 20% more than the database.

<p>
The "test1.dir" directory created above puts all the blobs into a single
folder.  It was conjectured that some operating systems would perform 
poorly when a single directory contains 100,000 objects.  To test this,
the kvtest program can also store the blobs in a hierarchy of folders with no
more than 100 files and/or subdirectories per folder.  The alternative
on-disk representation of the blobs can be created using the --tree
command-line option to the "export" command, like this:

<codeblock>
./kvtest export test1.db test1.tree --tree
</codeblock>

<p>
The test1.dir directory will contain 100,000 files
with names like "000000", "000001", "000002" and so forth but the
test1.tree directory will contain the same files in subdirectories like
"00/00/00", "00/00/01", and so on.  The test1.dir and test1.test
directories take up approximately the same amount of space, though
test1.test is very slightly larger due to the extra directory entries.

<p>
All of the experiments that follow operate the same with either 
"test1.dir" or "test1.tree".  Very little performance difference is
measured in either case, regardless of operating system.

<p>
Measure the performance for reading blobs from the database and from
individual files using these commands:

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

<p>
Depending on your hardware and operating system, you should see that reads 
from the test1.db database file are about 35% faster than reads from 

individual files in the test1.dir or test1.tree folders.  Results can vary
significantly from one run to the next due to caching, so it is advisable
to run tests multiple times and take an average or a worst case or a best
case, depending on your requirements.

<p>The --blob-api option on the database read test causes kvtest to use
the [sqlite3_blob_read()] feature of SQLite to load the content of the
blobs, rather than running pure SQL statements.  This helps SQLite to run
a little faster on read tests.  You can omit that option to compare the
performance of SQLite running SQL statements.
In that case, the SQLite still out-performs direct reads, though
by not as much as when using [sqlite3_blob_read()].
The --blob-api option is ignored for tests that read from individual disk
files.

<p>
Measure write performance by adding the --update option.  This causes
the blobs are overwritten in place with another random blob of
exactly the same size.

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

<p>
The writing test above is not completely fair, since SQLite is doing
[power-safe transactions] whereas the direct-to-disk writing is not.
To put the tests on a more equal footing, add either the --nosync
option to the SQLite writes to disable calling fsync() or
FlushFileBuffers() to force content to disk, or using the --fsync option
for the direct-to-disk tests to force them to invoke fsync() or
FlushFileBuffers() when updating disk files.

<p>
There are many other testing options, which can be seen by running
the command:

<codeblock>
./kvtest help
</codeblock>

<h2>Read Performance Measurements</h2>

<p>The chart below shows data collected using 
[https://www.sqlite.org/src/file/test/kvtest.c|kvtest.c] on five different
systems:  An old Dell laptop running Windows7, a new Lenovo laptop running
Windows10, a Mac-Pro, an Ubuntu desktop machine, and an older Android
phone (a Galaxy S3). All machines use SSD except the Dell which has a
hard-drive. The test database is 100K BLOBs uniformly
distributed between 8K and 12K in size, for a total of about 1 gigabyte
of content.  The database page size
is 4KiB.  The -DSQLITE_DIRECT_OVERFLOW_READ compile-time option was
used for all of these tests.
The chart shows average time to read a BLOB from the SQLite database,
in a random order, relative to the time needed to read the same BLOB
from a file in the filesystem.  Smaller is better.  The actual timings
vary considerably from one system to another (the Ubuntu desktop is much
faster than the Galaxy S3 phone).  This chart shows the ratio of the
times needed to read blobs from SQLite divided by the times needed to
read the same blob directly from disk.  The left-most column in the chart
is the time needed to read from disk divided by itself, and thus always
has a value of 1.00.  That column is included for visual reference only.

<p>
In this chart, an SQL statement ("SELECT v FROM kv WHERE k=?1") 
is prepared once.  Then for each blob, the blob key value is bound 
to the ?1 parameter and the statement is evaluated to extract the
blob content.

<center>
<img src="images/faster-read-sql.jpg">
<br>
Chart 1:  100K BLOBs, avg size 10KB, read in random order
using SQL
</center>

<p>
The performance can be improved slightly by bypassing the SQL layer
and reading the blob content directly using the
[sqlite3_blob_read()] interface, as shown in the next chart:

<center>
<img src="images/faster-read-blobapi.jpg">
<br>
Chart 2:  100K BLOBs, avg size 10KB, read in random order<br>
using sqlite3_blob_read()
</center>

<p>
Futher performance improves can be made by using the
[memory-mapped I/O] feature of SQLite.  In the next chart, the
entire 1GB database file is memory mapped and blobs are read
(in random order) using the [sqlite3_blob_read()] interface:

<center>
<img src="images/faster-read-mmap.jpg">
<br>
Chart 3:  100K BLOBs, avg size 10KB, read in random order<br>
using sqlite3_blob_read() from a memory-mapped database.
</center>

<p>
The third chart shows that reading blob content out of SQLite can be
twice as fast as reading from individual files on disk for Mac and
Android, and an amazing ten times faster for Windows.

<h2>Write Performance Measurements</h2>

<p>
Writes are slower.
On all systems, using both direct I/O and SQLite, write performance is
between 5 and 15 times slower than reads.

<p>
Write performance measurements were made by replacing (overwriting)
an entire blob with a different blob.  All of the blobs in these
experiment are random and incompressible.  Because writes are so much
slower than reads, only 10,000 of the 100,000 blobs in the database
are replaced.  The blobs to be replaced are selected at random and
are in no particular order.

<p>
The direct-to-disk writes are accomplished using fopen()/fwrite()/fclose().
By default, and in all the results shown below, the OS filesystem buffers are
never flushed to persistent storage using fsync() or
FlushFileBuffers().  In other words, there is no attempt to make the
direct-to-disk writes transactional or power-safe.
We found that invoking fsync() or FlushFileBuffers() on each file
written causes direct-to-disk storage
to be about 10 times or more slower than writes to SQLite.

<p>
The next chart compares raw direct-to-disk overwrites against
database updates on the SQLite database in [WAL mode] with
[PRAGMA synchronous] set to NORMAL.
All database writes are in a single transaction.
The timer for the database writes is stopped after the transaction
commits, but before a [checkpoint] is run.
Note that the SQLite writes, unlike the direct-to-disk writes,
are [transactional] and [power-safe], though because the synchronous
setting is NORMAL instead of FULL, the transactions are not durable.

<center>
<img src="images/faster-write-safe.jpg">
<br>
Chart 4:  10K BLOBs, avg size 10KB, written in random order<br>
in WAL mode with synchronous NORMAL, exclusive of checkpoint time.
</center>

<p>
The android performance numbers for the write experiments are omitted
because the performance tests on the Galaxy S3 are so random.  Two
consecutive runs of the exact same experiment would give wildly different
times.  And, to be fair, the performance of SQLite on android is slightly
slower than writing directly to disk.

<p>
The next chart shows the performance of SQLite versus direct-to-disk
when transactions are disabled ([PRAGMA journal_mode|PRAGMA journal_mode=OFF])
and [PRAGMA synchronous] is set to OFF.  These settings put SQLite on an
equal footing with direct-to-disk writes, which is to say they make the
data prone to corruption due to system crashes and power failures.

<center>
<img src="images/faster-write-unsafe.jpg">
<br>
Chart 5:  10K BLOBs, avg size 10KB, written in random order<br>
in journaling disabled and with synchronous OFF.
</center>

<p>
In all of the write tests, it is important to disable anti-virus software
prior to running the direct-to-disk performance tests.  We found that
anti-virus software slows down direct-to-disk by an order of magnitude
whereas it impacts SQLite writes very little.  This is probably due to the
fact that direct-to-disk changes thousands of separate files which all need
to be checked by anti-virus, whereas SQLite writes only changes the single
database file.

<h2>Variations</h2>

<p>The [-DSQLITE_DIRECT_OVERFLOW_READ] compile-time option causes SQLite
to bypass its page cache when reading content from overflow pages.  This
helps database reads of 10K blobs run a little faster, but not all that much
faster.  SQLite still holds a speed advantage over direct filesystem reads
................................................................................
without the SQLITE_DIRECT_OVERFLOW_READ compile-time option.

<p>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.

<p>The size of the blobs in the test data affects performance.
The filesystem will generally be faster for larger blobs, since
the overhead of open() and close() is amortized over more bytes of I/O,
whereas the database will be more efficient in both speed and space
as the average blob size decreases.

























<h1>General Findings</h1>







<ol type="A">
<li>
<p>SQLite is competitive with, and usually faster than, blobs stored in
separate files on disk, for both reading and writing.




<li>
<p>Reading is about an order of magnitude faster than writing, for both
SQLite and direct-to-disk I/O.


<li>
<p>I/O performance varies widely depending on operating system and hardware.
Make your own measurements before drawing conclusions.

<li>
<p>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.
























































































</ol>

<title>SQLite Is Transactional</title>


<h2>SQLite is Transactional</h2>

<p>A transactional database is one in which all changes and queries
appear to be
Atomic, Consistent, Isolated, and Durable
([http://en.wikipedia.org/wiki/ACID | ACID]).

<title>SQLite Is Transactional</title>
<tcl>hd_keywords {power-safe transactions} {transactional} {power-safe}</tcl>

<h2>SQLite is Transactional</h2>

<p>A transactional database is one in which all changes and queries
appear to be
Atomic, Consistent, Isolated, and Durable
([http://en.wikipedia.org/wiki/ACID | ACID]).