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. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
fb0299e43a47ab73ce13f3655c3064eb |
User & Date: | drh 2017-06-06 17:29:23.720 |
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
Changes to pages/fasterthanfs.in.
︙ | ︙ | |||
43 44 45 46 47 48 49 | 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> | | | | 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | 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> |
︙ | ︙ | |||
197 198 199 200 201 202 203 204 205 | <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> | > > | > > | > > | | 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 | <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. |
︙ | ︙ | |||
270 271 272 273 274 275 276 277 278 | 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> | > > | | > > > | | | 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 | 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 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> <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 |
︙ | ︙ |