Documentation Source Text

Check-in [5f95a7df66]
Login

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

Overview
Comment:Add a chart of raw data to the faster-than-filesystem page.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:5f95a7df6617e4f0af730b6a062b8c6787e37ac662cfb7302a7429ebbc5fa10e
User & Date: drh 2017-05-22 19:23:20
Context
2017-05-22
19:28
Fix typo in the 3.19.0 release notes. check-in: 76f5154a37 user: drh tags: trunk
19:23
Add a chart of raw data to the faster-than-filesystem page. check-in: 5f95a7df66 user: drh tags: trunk
14:02
Version 3.19.0 check-in: 9ae6f97cab user: drh tags: trunk, release, version-3.19.0
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/fasterthanfs.in.

161
162
163
164
165
166
167








































































experiment we have not yet run.

<p>Remember that the relative performance of database reads and reads from
the filesystem will depend on both the hardware and the operating system.
Please try the tests above on your own system.  If you encounter cases
there database reads do not perform favorably in comparison to filesystem
reads, please report your findings in the [mailing lists|SQLite mailing list].















































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
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
experiment we have not yet run.

<p>Remember that the relative performance of database reads and reads from
the filesystem will depend on both the hardware and the operating system.
Please try the tests above on your own system.  If you encounter cases
there database reads do not perform favorably in comparison to filesystem
reads, please report your findings in the [mailing lists|SQLite mailing list].

<h1>Update as of 2017-05-22</h1>

<p>The table below shows data collected using 
[https://www.sqlite.org/src/file/test/kvtest.c|kvtest.c] on five different
systems:  An old Dell laptop running Windows7, a new Lenovo laptop running
Windows10, a Mac-Pro, an Ubuntu desktop machine, and an older Android
phone (a Galaxy S3). All machines use SSD except the Dell which has a
hard-drive. The test database is 100K BLOBs uniformly
distributed between 8K and 12K in size, each.  The database page size
is 4KiB.  The chart shows average BLOB access time in microseconds.

<center>
<table border=1>
<tr>
<th>kvtest arguments
<th>Win7
<th>Win10
<th>MacPro
<th>Ubuntu
<th>Android
</tr>
<tr>
<td>(direct file access)
<td>100<td>46<td>7.9<td>3.2<td>145
<tr>
<td>(plain database access)
<td>22<td>6.9<td>4.4<td>2.4<td>128
<tr>
<td>--blob-api
<td>18<td>5.8<td>3.6<td>1.9<td>105
<tr>
<td>--blob-api --random
<td>28<td>8.4<td>4.8<td>2.6<td>135
<tr>
<td>--mmap 1G
<td>9<td>3.4<td>5.2<td>2.1<td>45
<tr>
<td>--mmap 1G --blob-api --random
<td>13<td>4.4<td>4.7<td>2.2<td>78
</table>
</center>

<p>
Every run of kvtest gives a slightly different time, of course.  The
numbers above are averages over between three and five runs and are
rounded to reflect the fact that they are imprecise.
Always remember:  <i>Your mileage may vary</i>.
Rerun these tests yourself on your own hardware using data that is
a close match to your production data before drawing conclusions.

<p>
Notice that the individual file access times on Windows are dramatically
slower than on the unix systems.  This might be because the 100K BLOBs are
all stored ina single directory and Windows is inefficient at searching
directories with large numbers of files.  The Windows file access times
might be improved if the BLOBs were stored in a hierarchy of directories,
rather than dumping them all into a single big directory.

<h2>Key Points</h2>

<ol>
<li><p>
Do not assume that directory file I/O is faster than using an SQLite database.
Reading from SQLite can be faster, sometimes much faster, than reading
separate files from disk.

<li><p>
The relative performance between direct file access and database access
depends a lot on the operating system, the hardware, and what database
access method is being used.  Make your own measurements.
</ol>