SQLite small blob storage: 35% Faster Than the Filesystem
1–10 of 208 posts
Re: SQLite small blob storage: 35% Faster Than the Filesystem
#2> 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 in the way most benchmark headings probably are, I guess).
Incidentally, can someone more experienced with filesystem and database I/O confirm or contest the assertion here? Specifically, I'm not sure it's fair to generalize these results (even if valid) to categorical relational databases. But this is not a special area of expertise for me.
Re: SQLite small blob storage: 35% Faster Than the Filesystem
#3Interesting. We built a couple of systems many years ago using SQLite as the storage engine for binary files - one was a kind of mini version control utility for storing report files for a legacy ERP system.
I always thought that the SQLite system was far quicker to search and retrieve files from the dataset than it was doing so in our previous version, using hierarchical file folders.
Interestingly, we canned the project because the ERP vendor themselves release a similar tool using - wait for it - individual files in a folder system... (Addendum - much later on they switched to - wait for it - CVS for version control.... in 2010!!)
Re: SQLite small blob storage: 35% Faster Than the Filesystem
#4Consistently happy with SQLite, just a lovely little database. Performance is basically a cherry on top of the simplicity it provides.
Re: SQLite small blob storage: 35% Faster Than the Filesystem
#5This 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 able to predict reads better when they are within a single file. Writes are faster because they do one bulk COMMIT instead of many individual fsyncs.
Re: SQLite small blob storage: 35% Faster Than the Filesystem
#6> 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…
For small files, I could see this being true, but not in a way that would scale to large files.
For example, instead of a database, you could make a folder (instead of a table) and then a bunch of 4 byte files with ints in them. Doing a table scan would be like cat *.
Obviously this uses a lot more open/close/read calls, with more seeks and iops, because those files may or may not be clustered together on disk. If you packed it all into the same file, it would go faster because of the locality and less system calls. You can also be smarter about caching and other things that the file system does well, but probably isn't optimized for the small file use case.
Re: SQLite small blob storage: 35% Faster Than the Filesystem
#7This 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…
The third paragraph might offer the logical analysis you're looking for:
> The performance difference arises (we believe) because when working from an SQLite database, the open() and close() system calls are invoked only once, whereas open() and close() are invoked once for each blob when using blobs stored in individual files. It appears that the overhead of calling open() and close() is greater than the overhead of using the database. The size reduction arises from the fact that individual files are padded out to the next multiple of the filesystem block size, whereas the blobs are packed more tightly into an SQLite database.
Re: SQLite small blob storage: 35% Faster Than the Filesystem
#8This 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…
> For comparison one could concatenate the same data into a single big file even faster than into sqlite.
One could, but then one would have great difficulty retrieving the individual files back when needed.
The point is not about what has the greatest raw speed, the point is that that for applications that read lots of small files from the filesystem, they'll possibly get better performance and almost certainly use less disk space by sticking the files in SQLite instead.
Re: SQLite small blob storage: 35% Faster Than the Filesystem
#9This 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…
[deleted]
Re: SQLite small blob storage: 35% Faster Than the Filesystem
#10[deleted]