31–40 of 208 posts
This is weird benchmarketing. They are comparing reading/writing 100,000 individual files vs writing 100,000 entries into a single file(sqlite database). For comparison one could concatenate the same data into a single big file even faster than into sqlite. They then do not offer logical analysis as to why things are faster. My understanding is that reads are probably faster due to operating system readahead being ab…
Earlier quoted context omitted.
Opening a file means locating it physically on the disk by following references (usually at least one per directory level). This is a costly operation. Compare to the typical case of adding another row by appending to an already allocated disk block. No seek operation needed.
Prompting the question: why isn't the whole disk just a SQLite database?
For example SQLlite vacuuming to free up deleted data can be slow, on large SQLite databases, we've found it much faster to rewrite the entire file than to vacuum it.
It also has some scalability limits, it uses locking to limit to a single concurrent writer (short duration locks), which only scales up to a point.
There're a lot of file system characteristics that differ from a database file format, SQLite could probably be more file-system like,but then it would diverge from being a fast and lightweight database format.
Microsoft tried the database as a filesystem once: https://en.wikipedia.org/wiki/WinFS (though this goes beyond just being a container)
Earlier quoted context omitted.
Opening a file means locating it physically on the disk by following references (usually at least one per directory level). This is a costly operation. Compare to the typical case of adding another row by appending to an already allocated disk block. No seek operation needed.
Prompting the question: why isn't the whole disk just a SQLite database?
Isn't this because writing to a SQLite database file bypasses most (or all) of the antivirus file scanning since it's can't see a complete file, and can only look at raw blocks of data?
So if you find value in having your antivirus scan all of your files, that's a disadvantage for using SQLite to store them?
> So let your take-away be this: read/write latency for SQLite is competitive with read/write latency of individual files on disk. Often SQLite is faster. Sometimes SQLite is almost as fast. Either way, this article disproves the common assumption that a relational database must be slower than direct filesystem I/O. This is under the 1.1 Caveats heading, which makes me feel the title is a little misleading (but only…
An append-only transaction log is probably going to perform better on a spinning disk than random writes. An intelligent app sorting and committing writes in order is probably going to perform better than an fs on a disk with no queue ordering facility. As they said in the post, block-aligned writes in bulk are going to be more efficient than spreading out a bunch of writes not block-aligned. And latency for a db write is going to be lower if you don't close an fd, assuming that closing an fd would always trigger an fsync.
But a properly tuned filesystem, with the right filesystem, with the right kernel driver, with the right disk, with the right bus, with the right control plane, etc etc may work fantastically better than an unoptimized database, and a given workload may fit that fs perfectly.
It's important to remember that a benchmark is only meaningful to the person who is running the benchmark. If you want it to be meaningful to you, you have to try it yourself (and then presumably blog about it).
Earlier quoted context omitted.
> have great difficulty retrieving Not that great, and the point is its comparing apples to oranges, pointless.
Writing your own methods to retrieve arbitrary chunks of data from a monolithic file would be a lot of work. Using map tiles as the example, how do you easily retrieve just the tiles in a specific region? Ok, how about all tiles that have the “hasLand” attribute set? Or the “containsCoastline” attribute? You’d end up rewriting your own version of a database.
Oh goodness have we come a long way if interacting with a file is harder than interacting with a SQL database.
(it really depends on what language and/or library(ies) you use and for what purpose)
For web map tiles (millions of tiny PNGs), everyone who's anyone stores their tiles in sqlite rather than on disk: https://www.mapbox.com/help/an-open-platform/#mbtiles
Hmm... Does SQLite have some geo data or 2d coordinate lookup capabilities as well?
Earlier quoted context omitted.
"Measure before optimizing" has limited application. There are many things that need not be measured. Appending to a file is definitely not slower than doing memory management inside the file to allocate a new chunk, seeking to that position and then writing out the chunk. Period. (And the serialization overhead is negligible compared to the disk I/O).
It's not only "writing" though. It's "writing" AND "reading". Can you read the correct line in in CSV faster? Can you find the row with specific critiria like SQLite faster? Can you handle concurrency correctly like SQLite? Can you make sure your CSV is always in consistent state like SQLite? The point of the article is that SQLite is still fast even with all the benefit of database . The idea of SQLite is that you c…