| 1 |
<!DOCTYPE html> |
| 2 |
<html><head> |
| 3 |
<meta name="viewport" content="width=device-width, initial-scale=1.0"> |
| 4 |
<meta http-equiv="content-type" content="text/html; charset=UTF-8"> |
| 5 |
<link href="sqlite.css" rel="stylesheet"> |
| 6 |
<title>35% Faster Than The Filesystem</title> |
| 7 |
<!-- path= --> |
| 8 |
</head> |
| 9 |
<body> |
| 10 |
<div class=nosearch> |
| 11 |
<a href="index.html"> |
| 12 |
<img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0"> |
| 13 |
</a> |
| 14 |
<div><!-- IE hack to prevent disappearing logo --></div> |
| 15 |
<div class="tagline desktoponly"> |
| 16 |
Small. Fast. Reliable.<br>Choose any three. |
| 17 |
</div> |
| 18 |
<div class="menu mainmenu"> |
| 19 |
<ul> |
| 20 |
<li><a href="index.html">Home</a> |
| 21 |
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a> |
| 22 |
<li class='wideonly'><a href='about.html'>About</a> |
| 23 |
<li class='desktoponly'><a href="docs.html">Documentation</a> |
| 24 |
<li class='desktoponly'><a href="download.html">Download</a> |
| 25 |
<li class='wideonly'><a href='copyright.html'>License</a> |
| 26 |
<li class='desktoponly'><a href="support.html">Support</a> |
| 27 |
<li class='desktoponly'><a href="prosupport.html">Purchase</a> |
| 28 |
<li class='search' id='search_menubutton'> |
| 29 |
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a> |
| 30 |
</ul> |
| 31 |
</div> |
| 32 |
<div class="menu submenu" id="submenu"> |
| 33 |
<ul> |
| 34 |
<li><a href='about.html'>About</a> |
| 35 |
<li><a href='docs.html'>Documentation</a> |
| 36 |
<li><a href='download.html'>Download</a> |
| 37 |
<li><a href='support.html'>Support</a> |
| 38 |
<li><a href='prosupport.html'>Purchase</a> |
| 39 |
</ul> |
| 40 |
</div> |
| 41 |
<div class="searchmenu" id="searchmenu"> |
| 42 |
<form method="GET" action="search"> |
| 43 |
<select name="s" id="searchtype"> |
| 44 |
<option value="d">Search Documentation</option> |
| 45 |
<option value="c">Search Changelog</option> |
| 46 |
</select> |
| 47 |
<input type="text" name="q" id="searchbox" value=""> |
| 48 |
<input type="submit" value="Go"> |
| 49 |
</form> |
| 50 |
</div> |
| 51 |
</div> |
| 52 |
<script> |
| 53 |
function toggle_div(nm) { |
| 54 |
var w = document.getElementById(nm); |
| 55 |
if( w.style.display=="block" ){ |
| 56 |
w.style.display = "none"; |
| 57 |
}else{ |
| 58 |
w.style.display = "block"; |
| 59 |
} |
| 60 |
} |
| 61 |
function toggle_search() { |
| 62 |
var w = document.getElementById("searchmenu"); |
| 63 |
if( w.style.display=="block" ){ |
| 64 |
w.style.display = "none"; |
| 65 |
} else { |
| 66 |
w.style.display = "block"; |
| 67 |
setTimeout(function(){ |
| 68 |
document.getElementById("searchbox").focus() |
| 69 |
}, 30); |
| 70 |
} |
| 71 |
} |
| 72 |
function div_off(nm){document.getElementById(nm).style.display="none";} |
| 73 |
window.onbeforeunload = function(e){div_off("submenu");} |
| 74 |
/* Disable the Search feature if we are not operating from CGI, since */ |
| 75 |
/* Search is accomplished using CGI and will not work without it. */ |
| 76 |
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){ |
| 77 |
document.getElementById("search_menubutton").style.display = "none"; |
| 78 |
} |
| 79 |
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */ |
| 80 |
function hideorshow(btn,obj){ |
| 81 |
var x = document.getElementById(obj); |
| 82 |
var b = document.getElementById(btn); |
| 83 |
if( x.style.display!='none' ){ |
| 84 |
x.style.display = 'none'; |
| 85 |
b.innerHTML='show'; |
| 86 |
}else{ |
| 87 |
x.style.display = ''; |
| 88 |
b.innerHTML='hide'; |
| 89 |
} |
| 90 |
return false; |
| 91 |
} |
| 92 |
var antiRobot = 0; |
| 93 |
function antiRobotGo(){ |
| 94 |
if( antiRobot!=3 ) return; |
| 95 |
antiRobot = 7; |
| 96 |
var j = document.getElementById("mtimelink"); |
| 97 |
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href"); |
| 98 |
} |
| 99 |
function antiRobotDefense(){ |
| 100 |
document.body.onmousedown=function(){ |
| 101 |
antiRobot |= 2; |
| 102 |
antiRobotGo(); |
| 103 |
document.body.onmousedown=null; |
| 104 |
} |
| 105 |
document.body.onmousemove=function(){ |
| 106 |
antiRobot |= 2; |
| 107 |
antiRobotGo(); |
| 108 |
document.body.onmousemove=null; |
| 109 |
} |
| 110 |
setTimeout(function(){ |
| 111 |
antiRobot |= 1; |
| 112 |
antiRobotGo(); |
| 113 |
}, 100) |
| 114 |
antiRobotGo(); |
| 115 |
} |
| 116 |
antiRobotDefense(); |
| 117 |
</script> |
| 118 |
<div class=fancy> |
| 119 |
<div class=nosearch> |
| 120 |
<div class="fancy_title"> |
| 121 |
35% Faster Than The Filesystem |
| 122 |
</div> |
| 123 |
<details class="fancy_toc"> |
| 124 |
<summary>Table Of Contents</summary> |
| 125 |
<div id="toc_sub"><div class="fancy-toc1"><a href="#summary">1. Summary</a></div> |
| 126 |
<div class="fancy-toc2"><a href="#caveats">1.1. Caveats</a></div> |
| 127 |
<div class="fancy-toc2"><a href="#related_studies">1.2. Related Studies</a></div> |
| 128 |
<div class="fancy-toc1"><a href="#how_these_measurements_are_made">2. How These Measurements Are Made</a></div> |
| 129 |
<div class="fancy-toc2"><a href="#read_performance_measurements">2.1. Read Performance Measurements</a></div> |
| 130 |
<div class="fancy-toc2"><a href="#write_performance_measurements">2.2. Write Performance Measurements</a></div> |
| 131 |
<div class="fancy-toc2"><a href="#variations">2.3. Variations</a></div> |
| 132 |
<div class="fancy-toc1"><a href="#general_findings">3. General Findings</a></div> |
| 133 |
<div class="fancy-toc1"><a href="#additional_notes">4. Additional Notes</a></div> |
| 134 |
<div class="fancy-toc2"><a href="#compiling_and_testing_on_android">4.1. Compiling And Testing on Android</a></div> |
| 135 |
</div> |
| 136 |
</details> |
| 137 |
</div> |
| 138 |
|
| 139 |
|
| 140 |
|
| 141 |
|
| 142 |
|
| 143 |
<h1 id="summary"><span>1. </span>Summary</h1> |
| 144 |
|
| 145 |
<p>SQLite reads and writes small blobs (for example, thumbnail images) |
| 146 |
<a href="#approx">35% faster¹</a> than the same blobs |
| 147 |
can be read from or written to individual files on disk using |
| 148 |
fread() or fwrite(). |
| 149 |
|
| 150 |
</p><p>Furthermore, a single SQLite database holding |
| 151 |
10-kilobyte blobs uses about 20% less disk space than |
| 152 |
storing the blobs in individual files. |
| 153 |
|
| 154 |
</p><p>The performance difference arises (we believe) because when |
| 155 |
working from an SQLite database, the open() and close() system calls |
| 156 |
are invoked only once, whereas |
| 157 |
open() and close() are invoked once for each blob |
| 158 |
when using blobs stored in individual files. It appears that the |
| 159 |
overhead of calling open() and close() is greater than the overhead |
| 160 |
of using the database. The size reduction arises from the fact that |
| 161 |
individual files are padded out to the next multiple of the filesystem |
| 162 |
block size, whereas the blobs are packed more tightly into an SQLite |
| 163 |
database. |
| 164 |
|
| 165 |
</p><p> |
| 166 |
The measurements in this article were made during the week of 2017-06-05 |
| 167 |
using a version of SQLite in between 3.19.2 and 3.20.0. You may expect |
| 168 |
future versions of SQLite to perform even better. |
| 169 |
|
| 170 |
</p><h2 id="caveats"><span>1.1. </span>Caveats</h2> |
| 171 |
|
| 172 |
<a name="approx"></a> |
| 173 |
<p> |
| 174 |
¹The 35% figure above is approximate. Actual timings vary |
| 175 |
depending on hardware, operating system, and the |
| 176 |
details of the experiment, and due to random performance fluctuations |
| 177 |
on real-world hardware. See the text below for more detail. |
| 178 |
Try the experiments yourself. Report significant deviations on |
| 179 |
the <a href="support.html#fx">SQLite forum</a>. |
| 180 |
</p> |
| 181 |
|
| 182 |
<p> |
| 183 |
The 35% figure is based on running tests on every machine |
| 184 |
that the author has easily at hand. |
| 185 |
Some reviewers of this article report that SQLite has higher |
| 186 |
latency than direct I/O on their systems. We do not yet understand |
| 187 |
the difference. We also see indications that SQLite does not |
| 188 |
perform as well as direct I/O when experiments are run using |
| 189 |
a cold filesystem cache. |
| 190 |
|
| 191 |
</p><p> |
| 192 |
So let your take-away be this: read/write latency for |
| 193 |
SQLite is competitive with read/write latency of individual files on |
| 194 |
disk. Often SQLite is faster. Sometimes SQLite is almost |
| 195 |
as fast. Either way, this article disproves the common |
| 196 |
assumption that a relational database must be slower than direct |
| 197 |
filesystem I/O. |
| 198 |
|
| 199 |
</p><h2 id="related_studies"><span>1.2. </span>Related Studies</h2> |
| 200 |
|
| 201 |
<p> |
| 202 |
A <a href="https://golangexample.com/an-unscientific-benchmark-of-sqlite-vs-the-file-system-btrfs/">2022 study</a> |
| 203 |
(<a href="https://github.com/chrisdavies/dbench">alternative link on GitHub</a>) found that |
| 204 |
SQLite is <i>roughly</i> twice as fast at real-world workloads compared to Btrfs and Ext4 on Linux. |
| 205 |
|
| 206 |
</p><p> |
| 207 |
<a href="https://www.microsoft.com/en-us/research/people/gray/">Jim Gray</a> |
| 208 |
and others studied the read performance of BLOBs |
| 209 |
versus file I/O for Microsoft SQL Server and found that reading BLOBs |
| 210 |
out of the |
| 211 |
database was faster for BLOB sizes less than between 250KiB and 1MiB. |
| 212 |
(<a href="https://www.microsoft.com/en-us/research/publication/to-blob-or-not-to-blob-large-object-storage-in-a-database-or-a-filesystem/">Paper</a>). |
| 213 |
In that study, the database still stores the filename of the content even |
| 214 |
if the content is held in a separate file. So the database is consulted |
| 215 |
for every BLOB, even if it is only to extract the filename. In this |
| 216 |
article, the key for the BLOB is the filename, so no preliminary database |
| 217 |
access is required. Because the database is never used at all when |
| 218 |
reading content from individual files in this article, the threshold |
| 219 |
at which direct file I/O becomes faster is smaller than it is in Gray's |
| 220 |
paper. |
| 221 |
|
| 222 |
</p><p> |
| 223 |
The <a href="intern-v-extern-blob.html">Internal Versus External BLOBs</a> article on this website is an |
| 224 |
earlier investigation (circa 2011) that uses the same approach as the |
| 225 |
Jim Gray paper — storing the blob filenames as entries in the |
| 226 |
database — but for SQLite instead of SQL Server. |
| 227 |
|
| 228 |
|
| 229 |
|
| 230 |
</p><h1 id="how_these_measurements_are_made"><span>2. </span>How These Measurements Are Made</h1> |
| 231 |
|
| 232 |
<p>I/O performance is measured using the |
| 233 |
<a href="https://sqlite.org/src/file/test/kvtest.c">kvtest.c</a> program |
| 234 |
from the SQLite source tree. |
| 235 |
To compile this test program, first gather the kvtest.c source file |
| 236 |
into a directory with the <a href="amalgamation.html">SQLite amalgamation</a> source |
| 237 |
files "sqlite3.c" and "sqlite3.h". Then on unix, run a command like |
| 238 |
the following: |
| 239 |
|
| 240 |
</p><div class="codeblock"><pre>gcc -Os -I. -DSQLITE_DIRECT_OVERFLOW_READ \ |
| 241 |
kvtest.c sqlite3.c -o kvtest -ldl -lpthread |
| 242 |
</pre></div> |
| 243 |
|
| 244 |
<p>Or on Windows with MSVC: |
| 245 |
|
| 246 |
</p><div class="codeblock"><pre>cl -I. -DSQLITE_DIRECT_OVERFLOW_READ kvtest.c sqlite3.c |
| 247 |
</pre></div> |
| 248 |
|
| 249 |
<p>Instructions for compiling for Android |
| 250 |
are <a href="#compile-android">shown below</a>. |
| 251 |
|
| 252 |
</p><p> |
| 253 |
Use the resulting "kvtest" program to |
| 254 |
generate a test database with 100,000 random uncompressible |
| 255 |
blobs, each with a random |
| 256 |
size between 8,000 and 12,000 bytes |
| 257 |
using a command like this: |
| 258 |
|
| 259 |
</p><div class="codeblock"><pre>./kvtest init test1.db --count 100k --size 10k --variance 2k |
| 260 |
</pre></div> |
| 261 |
|
| 262 |
<p> |
| 263 |
If desired, you can verify the new database by running this command: |
| 264 |
|
| 265 |
</p><div class="codeblock"><pre>./kvtest stat test1.db |
| 266 |
</pre></div> |
| 267 |
|
| 268 |
<p> |
| 269 |
Next, make copies of all the blobs into individual files in a directory |
| 270 |
using a command like this: |
| 271 |
|
| 272 |
</p><div class="codeblock"><pre>./kvtest export test1.db test1.dir |
| 273 |
</pre></div> |
| 274 |
|
| 275 |
<p> |
| 276 |
At this point, you can measure the amount of disk space used by |
| 277 |
the test1.db database and the space used by the test1.dir directory |
| 278 |
and all of its content. On a standard Ubuntu Linux desktop, the |
| 279 |
database file will be 1,024,512,000 bytes in size and the test1.dir |
| 280 |
directory will use 1,228,800,000 bytes of space (according to "du -k"), |
| 281 |
about 20% more than the database. |
| 282 |
|
| 283 |
</p><p> |
| 284 |
The "test1.dir" directory created above puts all the blobs into a single |
| 285 |
folder. It was conjectured that some operating systems would perform |
| 286 |
poorly when a single directory contains 100,000 objects. To test this, |
| 287 |
the kvtest program can also store the blobs in a hierarchy of folders with no |
| 288 |
more than 100 files and/or subdirectories per folder. The alternative |
| 289 |
on-disk representation of the blobs can be created using the --tree |
| 290 |
command-line option to the "export" command, like this: |
| 291 |
|
| 292 |
</p><div class="codeblock"><pre>./kvtest export test1.db test1.tree --tree |
| 293 |
</pre></div> |
| 294 |
|
| 295 |
<p> |
| 296 |
The test1.dir directory will contain 100,000 files |
| 297 |
with names like "000000", "000001", "000002" and so forth but the |
| 298 |
test1.tree directory will contain the same files in subdirectories like |
| 299 |
"00/00/00", "00/00/01", and so on. The test1.dir and test1.test |
| 300 |
directories take up approximately the same amount of space, though |
| 301 |
test1.test is very slightly larger due to the extra directory entries. |
| 302 |
|
| 303 |
</p><p> |
| 304 |
All of the experiments that follow operate the same with either |
| 305 |
"test1.dir" or "test1.tree". Very little performance difference is |
| 306 |
measured in either case, regardless of operating system. |
| 307 |
|
| 308 |
</p><p> |
| 309 |
Measure the performance for reading blobs from the database and from |
| 310 |
individual files using these commands: |
| 311 |
|
| 312 |
</p><div class="codeblock"><pre>./kvtest run test1.db --count 100k --blob-api |
| 313 |
./kvtest run test1.dir --count 100k --blob-api |
| 314 |
./kvtest run test1.tree --count 100k --blob-api |
| 315 |
</pre></div> |
| 316 |
|
| 317 |
<p> |
| 318 |
Depending on your hardware and operating system, you should see that reads |
| 319 |
from the test1.db database file are about 35% faster than reads from |
| 320 |
individual files in the test1.dir or test1.tree folders. Results can vary |
| 321 |
significantly from one run to the next due to caching, so it is advisable |
| 322 |
to run tests multiple times and take an average or a worst case or a best |
| 323 |
case, depending on your requirements. |
| 324 |
|
| 325 |
</p><p>The --blob-api option on the database read test causes kvtest to use |
| 326 |
the <a href="c3ref/blob_read.html">sqlite3_blob_read()</a> feature of SQLite to load the content of the |
| 327 |
blobs, rather than running pure SQL statements. This helps SQLite to run |
| 328 |
a little faster on read tests. You can omit that option to compare the |
| 329 |
performance of SQLite running SQL statements. |
| 330 |
In that case, the SQLite still out-performs direct reads, though |
| 331 |
by not as much as when using <a href="c3ref/blob_read.html">sqlite3_blob_read()</a>. |
| 332 |
The --blob-api option is ignored for tests that read from individual disk |
| 333 |
files. |
| 334 |
|
| 335 |
</p><p> |
| 336 |
Measure write performance by adding the --update option. This causes |
| 337 |
the blobs to be overwritten in place with another random blob of |
| 338 |
exactly the same size. |
| 339 |
|
| 340 |
</p><div class="codeblock"><pre>./kvtest run test1.db --count 100k --update |
| 341 |
./kvtest run test1.dir --count 100k --update |
| 342 |
./kvtest run test1.tree --count 100k --update |
| 343 |
</pre></div> |
| 344 |
|
| 345 |
<p> |
| 346 |
The writing test above is not completely fair, since SQLite is doing |
| 347 |
<a href="transactional.html">power-safe transactions</a> whereas the direct-to-disk writing is not. |
| 348 |
To put the tests on a more equal footing, add either the --nosync |
| 349 |
option to the SQLite writes to disable calling fsync() or |
| 350 |
FlushFileBuffers() to force content to disk, or using the --fsync option |
| 351 |
for the direct-to-disk tests to force them to invoke fsync() or |
| 352 |
FlushFileBuffers() when updating disk files. |
| 353 |
|
| 354 |
</p><p> |
| 355 |
By default, kvtest runs the database I/O measurements all within |
| 356 |
a single transaction. Use the --multitrans option to run each blob |
| 357 |
read or write in a separate transaction. The --multitrans option makes |
| 358 |
SQLite much slower, and uncompetitive with direct disk I/O. This |
| 359 |
option proves, yet again, that to get the most performance out of |
| 360 |
SQLite, you should group as much database interaction as possible within |
| 361 |
a single transaction. |
| 362 |
|
| 363 |
</p><p> |
| 364 |
There are many other testing options, which can be seen by running |
| 365 |
the command: |
| 366 |
|
| 367 |
</p><div class="codeblock"><pre>./kvtest help |
| 368 |
</pre></div> |
| 369 |
|
| 370 |
<h2 id="read_performance_measurements"><span>2.1. </span>Read Performance Measurements</h2> |
| 371 |
|
| 372 |
<p>The chart below shows data collected using |
| 373 |
<a href="https://sqlite.org/src/file/test/kvtest.c">kvtest.c</a> on five different |
| 374 |
systems: |
| 375 |
|
| 376 |
</p><ul> |
| 377 |
<li><b>Win7</b>: A circa-2009 Dell Inspiron laptop, Pentium dual-core |
| 378 |
at 2.30GHz, 4GiB RAM, Windows7. |
| 379 |
</li><li><b>Win10</b>: A 2016 Lenovo YOGA 910, Intel i7-7500 at 2.70GHz, |
| 380 |
16GiB RAM, Windows10. |
| 381 |
</li><li><b>Mac</b>: A 2015 MacBook Pro, 3.1GHz intel Core i7, 16GiB RAM, |
| 382 |
MacOS 10.12.5 |
| 383 |
</li><li><b>Ubuntu</b>: Desktop built from Intel i7-4770K at 3.50GHz, 32GiB RAM, |
| 384 |
Ubuntu 16.04.2 LTS |
| 385 |
</li><li><b>Android</b>: Galaxy S3, ARMv7, 2GiB RAM |
| 386 |
</li></ul> |
| 387 |
|
| 388 |
<p>All machines use SSD except Win7 which has a |
| 389 |
hard-drive. The test database is 100K blobs with sizes uniformly |
| 390 |
distributed between 8K and 12K, for a total of about 1 gigabyte |
| 391 |
of content. The database page size |
| 392 |
is 4KiB. The -DSQLITE_DIRECT_OVERFLOW_READ compile-time option was |
| 393 |
used for all of these tests. |
| 394 |
Tests were run multiple times. |
| 395 |
The first run was used to warm up the cache and its timings were discarded. |
| 396 |
|
| 397 |
</p><p> |
| 398 |
The chart below shows average time to read a blob directly from the |
| 399 |
filesystem versus the time needed to read the same blob from the SQLite |
| 400 |
database. |
| 401 |
The actual timings vary considerably from one system to another |
| 402 |
(the Ubuntu desktop is much |
| 403 |
faster than the Galaxy S3 phone, for example). |
| 404 |
This chart shows the ratio of the |
| 405 |
times needed to read blobs from a file divided by the time needed to read |
| 406 |
them from the database. The left-most column in the chart is the normalized |
| 407 |
time to read from the database, for reference. |
| 408 |
|
| 409 |
</p><p> |
| 410 |
In this chart, an SQL statement ("SELECT v FROM kv WHERE k=?1") |
| 411 |
is prepared once. Then for each blob, the blob key value is bound |
| 412 |
to the ?1 parameter and the statement is evaluated to extract the |
| 413 |
blob content. |
| 414 |
|
| 415 |
</p><p> |
| 416 |
The chart shows that on Windows10, content can be read from the SQLite |
| 417 |
database about 5 times faster than it can be read directly from disk. |
| 418 |
On Android, SQLite is only about 35% faster than reading from disk. |
| 419 |
|
| 420 |
</p><center> |
| 421 |
<div class="imgcontainer"> |
| 422 |
<img src="images/faster-read-sql.jpg"> |
| 423 |
</div> |
| 424 |
<br> |
| 425 |
Chart 1: SQLite read latency relative to direct filesystem reads.<br> |
| 426 |
100K blobs, avg 10KB each, random order using SQL |
| 427 |
</center> |
| 428 |
|
| 429 |
<p> |
| 430 |
The performance can be improved slightly by bypassing the SQL layer |
| 431 |
and reading the blob content directly using the |
| 432 |
<a href="c3ref/blob_read.html">sqlite3_blob_read()</a> interface, as shown in the next chart: |
| 433 |
|
| 434 |
</p><center> |
| 435 |
<div class="imgcontainer"> |
| 436 |
<img src="images/faster-read-blobapi.jpg"> |
| 437 |
</div> |
| 438 |
<br> |
| 439 |
Chart 2: SQLite read latency relative to direct filesystem reads.<br> |
| 440 |
100K blobs, avg size 10KB, random order<br> |
| 441 |
using sqlite3_blob_read(). |
| 442 |
</center> |
| 443 |
|
| 444 |
<p> |
| 445 |
Further performance improvements can be made by using the |
| 446 |
<a href="mmap.html">memory-mapped I/O</a> feature of SQLite. In the next chart, the |
| 447 |
entire 1GB database file is memory mapped and blobs are read |
| 448 |
(in random order) using the <a href="c3ref/blob_read.html">sqlite3_blob_read()</a> interface. |
| 449 |
With these optimizations, SQLite is twice as fast as Android |
| 450 |
or MacOS-X and over 10 times faster than Windows. |
| 451 |
|
| 452 |
</p><center> |
| 453 |
<div class="imgcontainer"> |
| 454 |
<img src="images/faster-read-mmap.jpg"> |
| 455 |
</div> |
| 456 |
<br> |
| 457 |
Chart 3: SQLite read latency relative to direct filesystem reads.<br> |
| 458 |
100K blobs, avg size 10KB, random order<br> |
| 459 |
using sqlite3_blob_read() from a memory-mapped database. |
| 460 |
</center> |
| 461 |
|
| 462 |
<p> |
| 463 |
The third chart shows that reading blob content out of SQLite can be |
| 464 |
twice as fast as reading from individual files on disk for Mac and |
| 465 |
Android, and an amazing ten times faster for Windows. |
| 466 |
|
| 467 |
</p><h2 id="write_performance_measurements"><span>2.2. </span>Write Performance Measurements</h2> |
| 468 |
|
| 469 |
<p> |
| 470 |
Writes are slower. |
| 471 |
On all systems, using both direct I/O and SQLite, write performance is |
| 472 |
between 5 and 15 times slower than reads. |
| 473 |
|
| 474 |
</p><p> |
| 475 |
Write performance measurements were made by replacing (overwriting) |
| 476 |
an entire blob with a different blob. All of the blobs in these |
| 477 |
experiments are random and incompressible. Because writes are so much |
| 478 |
slower than reads, only 10,000 of the 100,000 blobs in the database |
| 479 |
are replaced. The blobs to be replaced are selected at random and |
| 480 |
are in no particular order. |
| 481 |
|
| 482 |
</p><p> |
| 483 |
The direct-to-disk writes are accomplished using fopen()/fwrite()/fclose(). |
| 484 |
By default, and in all the results shown below, the OS filesystem buffers are |
| 485 |
never flushed to persistent storage using fsync() or |
| 486 |
FlushFileBuffers(). In other words, there is no attempt to make the |
| 487 |
direct-to-disk writes transactional or power-safe. |
| 488 |
We found that invoking fsync() or FlushFileBuffers() on each file |
| 489 |
written causes direct-to-disk storage |
| 490 |
to be about 10 times or more slower than writes to SQLite. |
| 491 |
|
| 492 |
</p><p> |
| 493 |
The next chart compares SQLite database updates in <a href="wal.html">WAL mode</a> |
| 494 |
against raw direct-to-disk overwrites of separate files on disk. |
| 495 |
The <a href="pragma.html#pragma_synchronous">PRAGMA synchronous</a> setting is NORMAL. |
| 496 |
All database writes are in a single transaction. |
| 497 |
The timer for the database writes is stopped after the transaction |
| 498 |
commits, but before a <a href="wal.html#ckpt">checkpoint</a> is run. |
| 499 |
Note that the SQLite writes, unlike the direct-to-disk writes, |
| 500 |
are <a href="transactional.html">transactional</a> and <a href="transactional.html">power-safe</a>, though because the synchronous |
| 501 |
setting is NORMAL instead of FULL, the transactions are not durable. |
| 502 |
|
| 503 |
</p><center> |
| 504 |
<div class="imgcontainer"> |
| 505 |
<img src="images/faster-write-safe.jpg"> |
| 506 |
</div> |
| 507 |
<br> |
| 508 |
Chart 4: SQLite write latency relative to direct filesystem writes.<br> |
| 509 |
10K blobs, avg size 10KB, random order,<br> |
| 510 |
WAL mode with synchronous NORMAL,<br> |
| 511 |
exclusive of checkpoint time |
| 512 |
</center> |
| 513 |
|
| 514 |
<p> |
| 515 |
The android performance numbers for the write experiments are omitted |
| 516 |
because the performance tests on the Galaxy S3 are so random. Two |
| 517 |
consecutive runs of the exact same experiment would give wildly different |
| 518 |
times. And, to be fair, the performance of SQLite on android is slightly |
| 519 |
slower than writing directly to disk. |
| 520 |
|
| 521 |
</p><p> |
| 522 |
The next chart shows the performance of SQLite versus direct-to-disk |
| 523 |
when transactions are disabled (<a href="pragma.html#pragma_journal_mode">PRAGMA journal_mode=OFF</a>) |
| 524 |
and <a href="pragma.html#pragma_synchronous">PRAGMA synchronous</a> is set to OFF. These settings put SQLite on an |
| 525 |
equal footing with direct-to-disk writes, which is to say they make the |
| 526 |
data prone to corruption due to system crashes and power failures. |
| 527 |
|
| 528 |
</p><center> |
| 529 |
<div class="imgcontainer"> |
| 530 |
<img src="images/faster-write-unsafe.jpg"> |
| 531 |
</div> |
| 532 |
<br> |
| 533 |
Chart 5: SQLite write latency relative to direct filesystem writes.<br> |
| 534 |
10K blobs, avg size 10KB, random order,<br> |
| 535 |
journaling disabled, synchronous OFF. |
| 536 |
</center> |
| 537 |
|
| 538 |
<p> |
| 539 |
In all of the write tests, it is important to disable anti-virus software |
| 540 |
prior to running the direct-to-disk performance tests. We found that |
| 541 |
anti-virus software slows down direct-to-disk by an order of magnitude |
| 542 |
whereas it impacts SQLite writes very little. This is probably due to the |
| 543 |
fact that direct-to-disk changes thousands of separate files which all need |
| 544 |
to be checked by anti-virus, whereas SQLite writes only changes the single |
| 545 |
database file. |
| 546 |
|
| 547 |
</p><h2 id="variations"><span>2.3. </span>Variations</h2> |
| 548 |
|
| 549 |
<p>The <a href="compile.html#direct_overflow_read">-DSQLITE_DIRECT_OVERFLOW_READ</a> compile-time option causes SQLite |
| 550 |
to bypass its page cache when reading content from overflow pages. This |
| 551 |
helps database reads of 10K blobs run a little faster, but not all that much |
| 552 |
faster. SQLite still holds a speed advantage over direct filesystem reads |
| 553 |
without the SQLITE_DIRECT_OVERFLOW_READ compile-time option. |
| 554 |
|
| 555 |
</p><p>Other compile-time options such as using -O3 instead of -Os or |
| 556 |
using <a href="compile.html#threadsafe">-DSQLITE_THREADSAFE=0</a> and/or some of the other |
| 557 |
<a href="compile.html#rcmd">recommended compile-time options</a> might help SQLite to run even faster |
| 558 |
relative to direct filesystem reads. |
| 559 |
|
| 560 |
</p><p>The size of the blobs in the test data affects performance. |
| 561 |
The filesystem will generally be faster for larger blobs, since |
| 562 |
the overhead of open() and close() is amortized over more bytes of I/O, |
| 563 |
whereas the database will be more efficient in both speed and space |
| 564 |
as the average blob size decreases. |
| 565 |
|
| 566 |
|
| 567 |
</p><h1 id="general_findings"><span>3. </span>General Findings</h1> |
| 568 |
|
| 569 |
<ol type="A"> |
| 570 |
<li> |
| 571 |
<p>SQLite is competitive with, and usually faster than, blobs stored in |
| 572 |
separate files on disk, for both reading and writing. |
| 573 |
|
| 574 |
</p></li><li> |
| 575 |
<p>SQLite is much faster than direct writes to disk on Windows |
| 576 |
when anti-virus protection is turned on. Since anti-virus software |
| 577 |
is and should be on by default in Windows, that means that SQLite |
| 578 |
is generally much faster than direct disk writes on Windows. |
| 579 |
|
| 580 |
</p></li><li> |
| 581 |
<p>Reading is about an order of magnitude faster than writing, for all |
| 582 |
systems and for both SQLite and direct-to-disk I/O. |
| 583 |
|
| 584 |
</p></li><li> |
| 585 |
<p>I/O performance varies widely depending on operating system and hardware. |
| 586 |
Make your own measurements before drawing conclusions. |
| 587 |
|
| 588 |
</p></li><li> |
| 589 |
<p>Some other SQL database engines advise developers to store blobs in separate |
| 590 |
files and then store the filename in the database. In that case, where |
| 591 |
the database must first be consulted to find the filename before opening |
| 592 |
and reading the file, simply storing the entire blob in the database |
| 593 |
gives much faster read and write performance with SQLite. |
| 594 |
See the <a href="intern-v-extern-blob.html">Internal Versus External BLOBs</a> article for more information. |
| 595 |
</p></li></ol> |
| 596 |
|
| 597 |
|
| 598 |
<h1 id="additional_notes"><span>4. </span>Additional Notes</h1> |
| 599 |
|
| 600 |
<a name="compile-android"></a> |
| 601 |
<h2 id="compiling_and_testing_on_android"><span>4.1. </span>Compiling And Testing on Android</h2> |
| 602 |
|
| 603 |
<p> |
| 604 |
The kvtest program is compiled and run on Android as follows. |
| 605 |
First install the Android SDK and NDK. Then prepare a script |
| 606 |
named "android-gcc" that looks approximately like this: |
| 607 |
|
| 608 |
</p><div class="codeblock"><pre>#!/bin/sh |
| 609 |
# |
| 610 |
NDK=/home/drh/Android/Sdk/ndk-bundle |
| 611 |
SYSROOT=$NDK/platforms/android-16/arch-arm |
| 612 |
ABIN=$NDK/toolchains/arm-linux-androideabi-4.9/prebuilt/linux-x86_64/bin |
| 613 |
GCC=$ABIN/arm-linux-androideabi-gcc |
| 614 |
$GCC --sysroot=$SYSROOT -fPIC -pie $* |
| 615 |
</pre></div> |
| 616 |
|
| 617 |
<p>Make that script executable and put it on your $PATH. Then |
| 618 |
compile the kvtest program as follows: |
| 619 |
|
| 620 |
</p><div class="codeblock"><pre>android-gcc -Os -I. kvtest.c sqlite3.c -o kvtest-android |
| 621 |
</pre></div> |
| 622 |
|
| 623 |
<p>Next, move the resulting kvtest-android executable to the Android |
| 624 |
device: |
| 625 |
|
| 626 |
</p><div class="codeblock"><pre>adb push kvtest-android /data/local/tmp |
| 627 |
</pre></div> |
| 628 |
|
| 629 |
<p>Finally use "adb shell" to get a shell prompt on the Android device, |
| 630 |
cd into the /data/local/tmp directory, and begin running the tests |
| 631 |
as with any other unix host. |
| 632 |
</p><p align="center"><small><i>This page was last updated on 2025-11-13 07:12:58Z </small></i></p> |
| 633 |
|