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

201–208 of 208 posts

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

#201

Post by akx »

diroussel wrote: post #81
imron wrote: post #58

Earlier quoted context omitted.

> Oh goodness have we come a long way if interacting with a file is harder than interacting with a SQL database. It's not interacting with a file, it's interacting with a file that contains other files of varying sizes that need to be accessed randomly with good performance.

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.

Afaik, tar files do not contain a central directory, so finding a certain file in a tar file requires skimming through the entire archive.

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

#202

Post by fnord123 »

the8472 wrote: post #196
fnord123 wrote: post #194

Earlier quoted context omitted.

Are we talking about the POSIX interface to files or is there a new transactional API that allows for atomic writes (all pages are written to disk or none are), consistency, isolation (opening a file in another process which is currently being written won't show the pages that are being written), and makes the writes always durable (sync; not fsync)?

> Are we talking about the POSIX interface to files Those give you these atomic ops and optimistic or cooperative locking: https://rcrowley.org/2010/01/06/things-unix-can-do-atomicall... Multi-file transactions can be built by moving whole directories over symlinks to the previous version. The linux-specific RENAME_EXCHANGE flag can simplify this. > or is there a new transactional API CoW filesystems give you snapsho…

>For durability fsync is sufficient if you do it on the file and the directory. To combine atomic and durable you can do the write, fsync, rename, fsync dir dance.

Right. This allows for the correct behaviour but I wouldn't conclude that POSIX is a transactional API. I would regard this as being able to build a transactional API on top of it; but not transactional in itself. I mean, if you had to do the same dance in SQL (insert to dummy pkey and then update the entry) you would rightly dismiss it.

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

#203

Post by the8472 »

fnord123 wrote: post #202
the8472 wrote: post #196

Earlier quoted context omitted.

> Are we talking about the POSIX interface to files Those give you these atomic ops and optimistic or cooperative locking: https://rcrowley.org/2010/01/06/things-unix-can-do-atomicall... Multi-file transactions can be built by moving whole directories over symlinks to the previous version. The linux-specific RENAME_EXCHANGE flag can simplify this. > or is there a new transactional API CoW filesystems give you snapsho…

>For durability fsync is sufficient if you do it on the file and the directory. To combine atomic and durable you can do the write, fsync, rename, fsync dir dance. Right. This allows for the correct behaviour but I wouldn't conclude that POSIX is a transactional API. I would regard this as being able to build a transactional API on top of it; but not transactional in itself. I mean, if you had to do the same dance in…

Which is why I said

> You can build multi-step transactions on top of them with various operations.

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

#204

Post by chiph »

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…

Another reason to store them on the filesystem (especially if they're user-contributed) is that your anti-virus can see them there.

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

#205

Post by justinclift »

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…

As a data point, many of the links on that page to the mapbox.com website are 404. :(

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

#206

Post by stuaxo »

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…

Even with way fewer files than that you can't use wildcards in bash because it will expands to longer than the commandline allows.

This makes any work in the shell painful.

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

#207

Post by troutaway123 »

firethief wrote: post #171
troutaway123 wrote: post #158

Earlier quoted context omitted.

> preserving access to the toolchain which operates trivially with files was worth the additional performance overhead of working with the files Sounds like they're OK with that. In any case, couldn't you avoid the kernel trips with some dylib foolery (assuming the toolchain is dynamically linked)?

So: store the files in a database, expose them via FUSE, bypass FUSE and access the database?

Yep ;D

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

#208

Post by wyoung2 »

rusanu wrote: post #181
wyoung2 wrote: post #125

Earlier quoted context omitted.

The article may be unclear on this point: anti-virus was turned off for these tests. What that paragraph is pointing out is that performance tanks badly (roughly a factor of 10x) when you turn on Windows Defender, and presumably even worse with other anti-virus software. Credential: I was one of several people who vetted the article before it was published. I argued that Defender should be enabled, since it is a plat…

Can you clarify then wether the AV was simply not scanning the SQLite files (ie. the file extension or the file location was exempt in AV config) ?

As I understand it, drh simply disabled Windows Defender entirely for these tests.

SQLite gets an additional advantage with Defender enabled because Defender doesn't try to scan inside the SQLite DB file for the sub-files, whereas with separate files, Defender must at least look at each file's header to determine whether it's a potentially dangerous file type.

While I was vetting this article, I did some of my own tests, and I found that simply excluding the directory containing the test tree was sufficient to get the same benefit.

My point in other posts here is that you don't need to disable Defender to get this benefit when you use SQLite.

But let's be clear: the 35% claimed performance benefit has nothing to do with Windows Defender. That's a wholly separate issue. Defender pays more attention to a pile of individual files than to an amalgamation of that same data into a single file, and this magnifies SQLite's advantages, but it is not the source of the advantage the linked report talks about.

Post reply on HN