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

101–110 of 208 posts

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

#101

Post by icebraining »

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…

If you place this kind of content in a database, you'll need to serve them with your application.

Well, not necessarily: https://github.com/FRiCKLE/ngx_postgres/

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

#102

Post by ianamartin »

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…

I swear to god, I don't understand why people don't think about this.

total sadness about having to deal with a 150GB SQL database, of which 148 GBs are blob storage for PDFs

"We have big data!! We need enterprise scale!"

Nope. You have big stupid.

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

#103

Post by majewsky »

AstralStorm wrote: post #66
bigger_cheese wrote: post #64

Earlier quoted context omitted.

From this old stack overflow topic: https://stackoverflow.com/questions/6916011/how-do-i-get-win... Conclusion seems to be that filesystems on windows are not well optimized for the usecase of reading and writing from many small files at once. Which could explain poor windows performance in the benchmark (writing to 100,000 individual files).

Which is why before making such broad statements they should check on the other platforms, which have much less broken file systems. (Check Ext4, ZFS, UFS2, HPFS+ and APFS at least.) What they did is check an untuned Ext4, UBIFS, HPFS+ and NTFS vs a tuned database in a single thread scenario. Especially misleading in the mmap case where synchronization would just kill the performance dead.

I find that comparison justified: How many people are even aware of the fact that filesystems can be tuned (except for, maybe, noatime and discard for SSDs)?

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

#104

Post by justinclift »

icebraining wrote: post #101
tetha wrote: post #91

Earlier quoted context omitted.

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

If you place this kind of content in a database, you'll need to serve them with your application. Well, not necessarily: https://github.com/FRiCKLE/ngx_postgres/

Interesting project. Although that one seems pretty dead (2 years since last commit), this fork of it seems actively developed:

https://github.com/konstruxi/ngx_postgres

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

#105

Post by justinclift »

shawn-butler wrote: post #79
asmosoinio wrote: post #39

Earlier quoted context omitted.

Interesting and very relevant to the discussion! Hmm... Does SQLite have some geo data or 2d coordinate lookup capabilities as well?

Try SpatialLite[0] if you need advanced geometry capabilities. Has R-Tree spatial indexes if you use SQLite 3.6+, and rudimentary MBR index for prior versions. Has rudimentary support for curves as well. [0]: https://www.gaia-gis.it/fossil/libspatialite/index

As a data point, one of our users figured out how to load the SpatiaLite extension for DB Browser for SQLite (on Windows), then wrote up step by step instructions:

https://github.com/sqlitebrowser/sqlitebrowser/wiki/SpatiaLi...

(people had been asking previously, but we had no idea. ^\o/^)

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

#106

Post by justinclift »

ori_b wrote: post #18
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…

A big part of it is because they're repeatedly walking the same path while reading and writing it. I got a 20% performance improvement on FreeBSD+ZFS with this change, which merely opens an fd, and uses that to stat and write, cutting the number of path traversals in half: https://eigenstate.org/paste/d41e3b69d258e3b52f7cee7a9921f02...

Cool, have you pointed them at it? :)

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

#107

Post by justinclift »

Const-me wrote: post #86

SQLite is good when you’re mostly reading. For writing, the major drawback of SQLite is it doesn’t support concurrent writes. All filesystems do (at least when writing different files), all full-fledged RDBMS-es do, even some embedded databases do (like ESENT). Yet, in SQLite only a single thread can write. Even embedded chips are multicore these days…

Some work seems to be ongoing for improving SQLite concurrency:

https://www.sqlite.org/cgi/src/timeline?n=100&r=begin-concur...

Also mentioned here:

https://news.ycombinator.com/item?id=12742943

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

#108

Post by coldtea »

ianamartin wrote: post #102
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…

I swear to god, I don't understand why people don't think about this. total sadness about having to deal with a 150GB SQL database, of which 148 GBs are blob storage for PDFs "We have big data!! We need enterprise scale!" Nope. You have big stupid.

I don't see the sadness -- or the stupid.

There are several very valid business cases to store files as blobs in a DB.

What's the problem is the DB is 150GB? It's not like the working set (which for the files will just be the metadata) will be that big for storing file blobs.

10GB Database + 140GB of pdfs on the filesystem are not any different to a 150GB DB with everything in.

And you have other issues (consistency, transactional issues, backups, etc).

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

#109

Post by mpweiher »

Not surprised by the results, very surprised by the explanation given, the overhead of open/close calls.

As far as I know (from many measurements and talking to kernel people and researching the mechanism involved), the difference is due to the fact that buffers are shared between all pages of a single file, and not between files.

So for reads, the filesystem will do read-ahead of significantly more data than requested and keep that in buffers, and future reads will profit. Similar for buffers shared when writing.

The same effect will be reproducible with any format storing multiple objects in a single file, it has virtually nothing to do with "SQLite" or "Databases".

One tradeoff is the greater potential for inconsistencies, which despite all the measures taken is much greater when you modify a file rather than writing only completely new files. Another is the inconvenience and duplication of effort, because all your default file-system management tools aren't available.

It would be interesting to see if a pseudo-filesystem that is mapped to a single underlying file would show the same effects (preferably implemented in user space to avoid overheads of multiple kernel round trips).

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

#110

Post by Dylan16807 »

rusanu wrote: post #72

From OP: "SQLite is much faster than direct writes to disk on Windows when anti-virus protection is turned on. Since anti-virus software is and should be on by default in Windows, that means that SQLite is generally much faster than direct disk writes on Windows." I don't get this. If scanning the content is important (as acknowledged by the author), then bypassing the scan via blob storage is a security issue and th…

Data chunks inside another file are effectively quarantined by default. There is no exempting or security issue, it's safe by design. It will get scanned occasionally but not on every write.
Post reply on HN