Documentation Source Text

Check-in [fb0299e43a]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Tweaks to the faster-than-filesystem document to make it more mobile-friendly.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: fb0299e43a47ab73ce13f3655c3064eba283d36730c5cbac33707aadc8574b7e
User & Date: drh 2017-06-06 17:29:23
Context
2017-06-06
20:24
Minor tweak to the aff_short.html document. check-in: ede2d703a0 user: drh tags: trunk
17:29
Tweaks to the faster-than-filesystem document to make it more mobile-friendly. check-in: fb0299e43a user: drh tags: trunk
15:16
New hyperlinks to the faster-than-filesystem paper. check-in: 1539b18574 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/fasterthanfs.in.

43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
...
197
198
199
200
201
202
203

204

205
206
207
208
209
210
211
212
213
214
215

216

217
218
219
220
221
222
223
224
225
226
227
228

229

230
231
232
233
234
235
236
237
238
...
270
271
272
273
274
275
276

277

278
279
280

281
282
283
284
285
286
287
...
291
292
293
294
295
296
297

298

299
300
301
302
303
304
305
306
307
308
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>

<p>Or on Windows with MSVC:

<codeblock>
cl -I. -DSQLITE_DIRECT_OVERFLOW_READ kvtest.c sqlite3.c
</codeblock>
................................................................................
<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.
................................................................................
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
................................................................................
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







|
|







 







>

>

|









>

>

|










>

>

|







 







>

>

|
|
>







 







>

>

|
|







43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
...
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
...
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
...
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
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 &#92;
  kvtest.c sqlite3.c -o kvtest -ldl -lpthread
</codeblock>

<p>Or on Windows with MSVC:

<codeblock>
cl -I. -DSQLITE_DIRECT_OVERFLOW_READ kvtest.c sqlite3.c
</codeblock>
................................................................................
<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>
<div class='imgcontainer'>
<img src="images/faster-read-sql.jpg">
</div>
<br>
Chart 1:  100K BLOBs, avg size 10KB, 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>
<div class='imgcontainer'>
<img src="images/faster-read-blobapi.jpg">
</div>
<br>
Chart 2:  100K BLOBs, avg size 10KB, 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>
<div class='imgcontainer'>
<img src="images/faster-read-mmap.jpg">
</div>
<br>
Chart 3:  100K BLOBs, avg size 10KB, 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.
................................................................................
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>
<div class='imgcontainer'>
<img src="images/faster-write-safe.jpg">
</div>
<br>
Chart 4:  10K BLOBs, avg size 10KB, random order<br>
in WAL mode with synchronous NORMAL,<br>
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
................................................................................
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>
<div class='imgcontainer'>
<img src="images/faster-write-unsafe.jpg">
</div>
<br>
Chart 5:  10K BLOBs, avg size 10KB, random order<br>
in journaling disabled, 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