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[0]) * 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

Reply via email to