Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLite small blob storage: 35% Faster Than the Filesystem

sqlite.org

Post reply on HN
Print view View original

91–100 of 208 posts

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#91

Post by tetha »

rusanu wrote: post #70

I must point out Jim Gray's paper To Blob or Not To Blob[0]. His team considered NTFS vs. SQL Server, but most rationale applies to any filesystem vs. database decision. The summary was "The study indicates that if objects are larger than one megabyte on average, NTFS has a clear advantage over SQL Server. If the objects are under 256 kilobytes, the database has a clear advantage. Inside this range, it depends on how…

> Despite all this, my practical experience is that filesystem is better than blobs for things like uploaded content, images, pngs and jps etc.

This is especially true if you want to deliver them back to the users in the context of a web application. If you place this kind of content in a database, you'll need to serve them with your application.

If you use files for this content, you get two interesting options. For one, you can use any stock web server like nginx to serve these files - and nginx will outperform your application in this context. On top of that, it's easy to push this content onto a CDN in order to further cut the latency to the user.

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#92

Post by geon »

visarga wrote: post #27
tarasglek wrote: post #5

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…

No, no, if it is faster than the filesystem it must mean that it has a faster storage medium. Maybe it is writing to memory, or the aether. /s Now, seriously, how can the foot be faster than the shoe?

A reading/writing a single file can be faster than multiple files. Also querying a db can be faster than querying the fs.

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#93

Post by rusanu »

mpweiher wrote: post #90
rusanu wrote: post #70

I must point out Jim Gray's paper To Blob or Not To Blob[0]. His team considered NTFS vs. SQL Server, but most rationale applies to any filesystem vs. database decision. The summary was "The study indicates that if objects are larger than one megabyte on average, NTFS has a clear advantage over SQL Server. If the objects are under 256 kilobytes, the database has a clear advantage. Inside this range, it depends on how…

> Also, when deciding between blob vs. filesystem, blobs bring transactional and recovery consistency. Interesting. I would have thought the other way around, at least for crash-resistance: the I/O stack (including disk hardware) has a tendency to reorder writes and so updates that live within a single file will corrupt fairly easily. Separate files not so much. I vaguely remember a paper on that (Usenix?) and sqlite…

You have two consystency issues with storing the files in the filesystem:

- rollbacks in the DB can lead to orphaned files on disk. One can try to add logic in the app (eg. a catch block that removes the file if the DB rolled back) but that is not gonna help on a crash

- it is impossible to obtain a consistent backup of both the DB and the filesystem. You can backup the filesystem and the DB, but the two will not be consistent between them unless you froze the app during the backup. When you restore the two backups (filesystem, DB) you may encounter any anomaly: orphaned files (exists on filesystem but no entry in DB), broken links (entry in DB referencing a non-existent file) etc. This is because the moment at which the backup 'views' the file and the DB record referencing it are distinct in time.

As for write reordering: write-ahead log systems relies on correct write order. All DBs worth their name enforce this one way or another (via special API, via config requirements etc etc)

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#94

Post by rusanu »

tetha wrote: post #91
rusanu wrote: post #70

I must point out Jim Gray's paper To Blob or Not To Blob[0]. His team considered NTFS vs. SQL Server, but most rationale applies to any filesystem vs. database decision. The summary was "The study indicates that if objects are larger than one megabyte on average, NTFS has a clear advantage over SQL Server. If the objects are under 256 kilobytes, the database has a clear advantage. Inside this range, it depends on how…

> Despite all this, my practical experience is that filesystem is better than blobs for things like uploaded content, images, pngs and jps etc. This is especially true if you want to deliver them back to the users in the context of a web application. If you place this kind of content in a database, you'll need to serve them with your application. If you use files for this content, you get two interesting options. For…

Amen to that. Fastest database query is the one you never run.

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#95

Post by dkersten »

nimchimpsky wrote: post #15
imron wrote: post #8

Earlier quoted context omitted.

> 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…

> have great difficulty retrieving Not that great, and the point is its comparing apples to oranges, pointless.

Nothing wrong with comparing apples and oranges. They have a lot more in common than people give them credit for (both are fruit, shape, flavour, nutrient content, where they grow, who eats them etc etc). You can even compare things that aren't similar at all and its not necessarily pointless.

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#96

Post by dom0 »

mpweiher wrote: post #90
rusanu wrote: post #70

I must point out Jim Gray's paper To Blob or Not To Blob[0]. His team considered NTFS vs. SQL Server, but most rationale applies to any filesystem vs. database decision. The summary was "The study indicates that if objects are larger than one megabyte on average, NTFS has a clear advantage over SQL Server. If the objects are under 256 kilobytes, the database has a clear advantage. Inside this range, it depends on how…

> Also, when deciding between blob vs. filesystem, blobs bring transactional and recovery consistency. Interesting. I would have thought the other way around, at least for crash-resistance: the I/O stack (including disk hardware) has a tendency to reorder writes and so updates that live within a single file will corrupt fairly easily. Separate files not so much. I vaguely remember a paper on that (Usenix?) and sqlite…

Databases use journaling to avoid these issues. The difference between RDBMS' journaling and file system's journaling is the RDBMS journals application data, the file system does not (with few exceptions). Thus, a RDBMS gives you consistent application state, while the file system does not (with fewer exceptions, basically only TxF).

Another pain point is backup; it's not possible to create a consistent snapshot of (rdbms state, other state on the file system). This is avoided entirely if you only need to snapshot (rdbms state,).

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#97

Post by aruggirello »

tarasglek wrote: post #5

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…

Exactly my thought. A possibly fairer comparison could be SQLite blobs vs. some archive format (with and without compression).

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#98

Post by dkersten »

imron wrote: post #85
diroussel wrote: post #81

Earlier quoted context omitted.

If you only want to read then both zip and tar are good for this purpose. It's commonly used and known to work well. There are many formats that are really just a renamed zip file. Like jar, and epub. File entries don't have to be compressed so you can mmap. If you want to do random writes then more consideration is needed.

> If you only want to read then both zip and tar are good for this purpose And programmatically accessing them is significantly more complicated than using SQLite which involves dropping a single header in your project and about 10 lines of code. > If you want to do random writes then more consideration is needed. Which you almost certainly what you want to do for the sorts of use cases where SQLite is also under con…

which involves dropping a single header in your project and about 10 lines of code.

10 lines of user code + the sqlite library. Using other formats like zip is hardly any more user code (+ some external library).

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#99

Post by imron »

dkersten wrote: post #98
imron wrote: post #85

Earlier quoted context omitted.

> If you only want to read then both zip and tar are good for this purpose And programmatically accessing them is significantly more complicated than using SQLite which involves dropping a single header in your project and about 10 lines of code. > If you want to do random writes then more consideration is needed. Which you almost certainly what you want to do for the sorts of use cases where SQLite is also under con…

which involves dropping a single header in your project and about 10 lines of code. 10 lines of user code + the sqlite library. Using other formats like zip is hardly any more user code (+ some external library).

The sqlite 'library' is a single header file and a single c file that you can just drop in your project and use.

Re: SQLite small blob storage: 35% Faster Than the Filesystem

#100

Post by vonhugendong »

rusanu wrote: post #70

I must point out Jim Gray's paper To Blob or Not To Blob[0]. His team considered NTFS vs. SQL Server, but most rationale applies to any filesystem vs. database decision. The summary was "The study indicates that if objects are larger than one megabyte on average, NTFS has a clear advantage over SQL Server. If the objects are under 256 kilobytes, the database has a clear advantage. Inside this range, it depends on how…

Thanks for this clarification
Post reply on HN