Documentation Source Text

Check-in [3aac5ad40e]
Login

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

Overview
Comment:In the 35%-faster report, invert the ratios on the charts, as this seems to make them easier to understand. Check-in the spreadsheet used to construct the charts.
Timelines: family | ancestors | descendants | both | branch-3.20
Files: files | file ages | folders
SHA3-256: 3aac5ad40e92240b1888325cd56fa0aa1951204c721f9cd313fc942ba87fec1b
User & Date: drh 2017-08-08 03:49:22
Context
2017-08-11
00:55
Fix a typo in the fileformat document check-in: faacaa1d1b user: drh tags: branch-3.20
2017-08-08
03:49
In the 35%-faster report, invert the ratios on the charts, as this seems to make them easier to understand. Check-in the spreadsheet used to construct the charts. check-in: 3aac5ad40e user: drh tags: branch-3.20
03:33
In the 35%-faster report, invert the ratios on the charts, as this seems to make them easier to understand. Check-in the spreadsheet used to construct the charts. check-in: 4869b508d9 user: drh tags: trunk
2017-08-07
16:53
Update links to the source code repositories to be HTTPS and update the location of the WWW3 server from Fremont to San Francisco. check-in: aa6b786823 user: drh tags: branch-3.20
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to images/faster-read-blobapi.jpg.

cannot compute difference between binary files

Changes to images/faster-read-mmap.jpg.

cannot compute difference between binary files

Changes to images/faster-read-sql.jpg.

cannot compute difference between binary files

Changes to images/faster-write-safe.jpg.

cannot compute difference between binary files

Changes to images/faster-write-unsafe.jpg.

cannot compute difference between binary files

Added misc/kvtest-timings.ods.

cannot compute difference between binary files

Changes to pages/fasterthanfs.in.

259
260
261
262
263
264
265
266
267
268
269

270
271
272
273
274
275
276
277
278
279
280
281
282





283
284
285
286
287
288
289
...
304
305
306
307
308
309
310
311


312
313
314
315
316
317
318
...
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
of content.  The database page size
is 4KiB.  The -DSQLITE_DIRECT_OVERFLOW_READ compile-time option was
used for all of these tests.
Tests were run multiple times.
The first run was used to warm up the cache and its timings were discarded.

<p>
The chart below shows average time to read a blob from the SQLite database,
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, for example).  
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>
<div class='imgcontainer'>
<img src="images/faster-read-sql.jpg">
</div>
<br>
Chart 1:  SQLite read latency relative to direct filesystem reads.<br>
100K blobs, avg 10KB each, random order using SQL
................................................................................
using sqlite3_blob_read().
</center>

<p>
Further 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:  SQLite read latency relative to direct filesystem reads.<br>
................................................................................
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.








|
|
|
|
>


|
|
<
|







>
>
>
>
>







 







|
>
>







 







|
|
|







259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274

275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
...
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
...
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
of content.  The database page size
is 4KiB.  The -DSQLITE_DIRECT_OVERFLOW_READ compile-time option was
used for all of these tests.
Tests were run multiple times.
The first run was used to warm up the cache and its timings were discarded.

<p>
The chart below shows average time to read a blob directly from the
filesystem versus the time needed to read the same blob from the SQLite 
database.
The actual timings vary considerably from one system to another 
(the Ubuntu desktop is much
faster than the Galaxy S3 phone, for example).  
This chart shows the ratio of the
times needed to read blobs from a file divided by the time needed to
from the database.  The left-most column in the chart is the normalized

time to read from the database, for reference.

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

<p>
The chart shows that on Windows10, content can be read from the SQLite
database about 5 times faster than it can be read directly from disk.
On Android, SQLite is only about 35% faster than reading from disk.

<center>
<div class='imgcontainer'>
<img src="images/faster-read-sql.jpg">
</div>
<br>
Chart 1:  SQLite read latency relative to direct filesystem reads.<br>
100K blobs, avg 10KB each, random order using SQL
................................................................................
using sqlite3_blob_read().
</center>

<p>
Further 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.
With these optimizations, SQLite is twice as fast as Android
or MacOS-X and over 10 times faster than Windows.

<center>
<div class='imgcontainer'>
<img src="images/faster-read-mmap.jpg">
</div>
<br>
Chart 3:  SQLite read latency relative to direct filesystem reads.<br>
................................................................................
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 SQLite database updates in [WAL mode]
against raw direct-to-disk overwrites of separate files on disk.
The [PRAGMA synchronous] setting is 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.