Debian Codegraph @

www/fasterthanfs.html

raw
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&sup1;</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 &sup1;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 &mdash; storing the blob filenames as entries in the
226 database &mdash; 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 &#92;
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