On May 13, 2018, at 10:15 PM, Rowan Worth <row...@dug.com> wrote:
> ...I wouldn't call the extra i/o imposed by sqlite "very very
> cheap" either - it doubles writes (once to the rollback journal, once to
> the DB), forces syncs, and likely results in a more seek heavy i/o pattern
> (this depends a bit on schema design and whether the app requires/fully
> takes advantage of relational operations).
fopen() can be simpler than SQLite when you’ve got a problem that isn’t taking
advantage of SQLite’s power.
If you ignore the sizes of SQLite and libc, so that you only consider the size
of end-user-written code, the same up-front cost gets you a fully
ACID-compliant RDBMS on the one hand vs. flat C structures written linearly to
disk on the other. If you can express most or all of your operations on the
data in terms of linear table scans or linearly-indexed accesses — e.g. data[i]
is always at sizeof(data) * i bytes into the file — then the flat file is
probably faster and cheaper. On top of that, the binary size of stdio is
probably a lot smaller than that of SQLite.
The interesting cases happen when you write your “simple” C code using fopen()
and then find yourself writing O(N) algorithms where faster algorithms would
work if only you had indexed access or JOIN abilities, or… Then you have to
do a classic tradeoff evaluation: will the complexity of SQLite pay for itself?
I recommend reading “Avoid SQLite in Your Next Firefox Feature,” an article
written by the Mozilla developers after they found many features creating
SQLite DBs for tiny little things better handled another way:
Note that their recommendation to use plain-text JSON or lz4-compressed JSON
instead of small amounts of data comes out of the fact that Mozilla had to have
those mechanisms in Firefox anyway, so they’re “free,” and they already had to
be heavily-optimized. It isn’t great as general-purpose advice.
> My point is simply that it's unwise to think of any DB query as having "no
> latency" even when dealing with an sqlite DB.
ObWarStory: I once wrote an application using SQLite that tripped a 20-second
watchdog timer by having an unexpectedly high amount of real-world traffic: a
mere hundreds of DB updates per second. How?
It was running on a system with a 5400 RPM hard disk, which translates to a
theoretical maximum of 45 transactions per second: 5400 RPM / 60 seconds per
minute / 2 disk rotations per transaction = 45. Since each DB update was done
independently, as long as the update rate was over 45 TPS, the application
would fall further and further behind real time until that watchdog timer
Immediate solution: wrap a couple of key “for” loops in SQLite transactions to
batch the disk updates.
Future solution: stop using spinning rust. :)
sqlite-users mailing list