SQLite small blob storage: 35% Faster Than the Filesystem • Hacker News Board

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

#161

Post by Twirrim » Wed, Jun 14, 2017, 3:42 PM UTC

patio11 wrote: post #114

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…

My experience (for an application which had a working set of under 1 GB of files in the 50kb to N MB range, and approximately 50 GB persisted at any given time) was that preserving access to the toolchain which operates trivially with files was worth the additional performance overhead of working with the files and, separately, occasionally having to retool things to e.g. not have 10e7 files in a single folder, which…

> e.g. not have 10e7 files in a single folder, which is something that Linux has some opinions (none of them good) about.

I keep running in to this as a common anti-pattern in software, even with software developers that should know better (having fallen afoul of it before).

I ran in to it with some semi-bespoke software once, used by a remarkable number of state departments across the country. It was one of many, many things wrong with that godawful thing. Worse, not only were they dumping files in a single directory, almost any time they went to do something with the directory they'd request a directory listing too. Like many of the bizarre things the app did, it never actually used the information it acquired that way.

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

#162

Post by 45h34jh53k4j » Wed, Jun 14, 2017, 3:58 PM UTC

Dylan16807 wrote: post #110

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.

Except when they are not. Just because malcode is encapsulated doesn't mean it is neutralised. The issue is untrusted data, not how it is stored.

Word Docs with embedded content or can trigger code execution on parsing, data structures that break AV parsers, image or font data that causes system libraries to overflow/UAF/etc; There all could potentially be a security issue in a DB, the same as in a FS.

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

#163

Post by eric_the_read » Wed, Jun 14, 2017, 4:00 PM UTC

fb03 wrote: post #124

Aaargh20318 wrote: post #118

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. If you store them on a filesystem, how do you deal with redundancy / failover / scaling ? At a previous job we did a bit of experimenting with using a clustered FS but they all introduced a lot of problems. However, this was a couple of years ago so the situation may be diffe…

> If you store them on a filesystem, how do you deal with redundancy / failover / scaling ? In most cases, using a solution like Amazon S3 will have all these three sorted out for you pretty good. And while it's not a full mountable filesystem, from a system development perspective it has a really good API for retrieving and storing files.

Technically there is s3fs, but it's fairly terrible and I actively recommend against it. Still, it does come handy from time to time, in a limited set of cases.

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

#164

Post by [deleted] » Wed, Jun 14, 2017, 4:27 PM UTC

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…

[deleted]

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

#165

Post by sbov » Wed, Jun 14, 2017, 4:33 PM UTC

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…

It's pretty easy to use a CDN with blob storage, which reduces the impact of serving it from your application.

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

#166

Post by cm2187 » Wed, Jun 14, 2017, 4:33 PM UTC

Twirrim wrote: post #161

patio11 wrote: post #114

Earlier quoted context omitted.

My experience (for an application which had a working set of under 1 GB of files in the 50kb to N MB range, and approximately 50 GB persisted at any given time) was that preserving access to the toolchain which operates trivially with files was worth the additional performance overhead of working with the files and, separately, occasionally having to retool things to e.g. not have 10e7 files in a single folder, which…

> e.g. not have 10e7 files in a single folder, which is something that Linux has some opinions (none of them good) about. I keep running in to this as a common anti-pattern in software, even with software developers that should know better (having fallen afoul of it before). I ran in to it with some semi-bespoke software once, used by a remarkable number of state departments across the country. It was one of many, ma…

I never understood why it mattered that files would be in a single directory or split over multiple directories. At the end of the day it is the same file system with the same number of files. The directories are just a logical structure. Why would there be a difference?

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

#167

Post by internalfx » Wed, Jun 14, 2017, 4:41 PM UTC

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 personally prefer the database...

But I think for it to work well you need a better strategy than just large BLOBs.

GridFS for MongoDB - https://docs.mongodb.com/manual/core/gridfs/

ReGrid for RethinkDB - https://github.com/internalfx/rethinkdb-regrid

I'm sure the same concept could apply to other databases.

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

#168

Post by incanus77 » Wed, Jun 14, 2017, 5:15 PM UTC

craigds wrote: post #12

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

Originator of MBTiles format here. The initial intentions were moving 100,000s of raster map tiles between computer and mobile device, either over USB or network. Main benefit was avoiding per-file transaction overhead, as well as checksumming potential. Side benefits included a small space savings over filesystem because of block overhead, plus later iterations allowed for de-duplication of e.g. all-blue water map tiles. These days, we still use the format for transport between local machine and the Mapbox backend, despite having moved to "vector tiles", which essentially are protocol buffer-based encoded geometries directly.

Original post: https://developmentseed.org/blog/2010/oct/08/portable-map-ti...

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

#169

Post by incanus77 » Wed, Jun 14, 2017, 5:22 PM UTC

incanus77 wrote: post #168

craigds wrote: post #12

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

Originator of MBTiles format here. The initial intentions were moving 100,000s of raster map tiles between computer and mobile device, either over USB or network. Main benefit was avoiding per-file transaction overhead, as well as checksumming potential. Side benefits included a small space savings over filesystem because of block overhead, plus later iterations allowed for de-duplication of e.g. all-blue water map t…

Also, to clarify a bit on why spatial capabilities weren't part of the MBTiles spec: when rendering maps, you assemble tiles for a given zoom scale in the vertical and horizontal directions, like a checkerboard. The unique tuple defining a particular square on the map is z/x/y — zoom level, horizontal, vertical. So stuffing these into SQLite and uniquely keying across z/x/y there makes for fast retrieval at render time.

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

#170

Post by jamie_ca » Wed, Jun 14, 2017, 5:22 PM UTC

cm2187 wrote: post #166

Twirrim wrote: post #161

Earlier quoted context omitted.

> e.g. not have 10e7 files in a single folder, which is something that Linux has some opinions (none of them good) about. I keep running in to this as a common anti-pattern in software, even with software developers that should know better (having fallen afoul of it before). I ran in to it with some semi-bespoke software once, used by a remarkable number of state departments across the country. It was one of many, ma…

I never understood why it mattered that files would be in a single directory or split over multiple directories. At the end of the day it is the same file system with the same number of files. The directories are just a logical structure. Why would there be a difference?

Spinning disks.

If you want raw file access, your ideal filesystem is a giant key-value store that keys on the full path to the file. This choice means doing a directory listing will involve a lot of random access reads across the disk, and in the days before SSDs this would be a performance killer.

So instead, a directory would have a little section of disk to write its file entries to. These would be fairly small, because most directories are small, and if there were more files added than there was room, another block would be allocated. Doing a file listing would be reasonably quick as you're just iterating over a block and following the link to the next block, but random file access would get slower the more files are added.

It's probably a solvable problem in filesystem design, but because current filesystems fall down hard on the million-files-in-a-directory use case, nobody actually does that, so there's no drive to make a filesystem performant in that case.