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

131–140 of 208 posts

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

#131

Post by Walf »

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…

Just don't let anyone mount with noatime, for performance. I found that little gem on a drive where paring down to used content would have been very helpful.

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

#132

Post by coldtea »

sheeshkebab wrote: post #119
coldtea wrote: post #108

Earlier quoted context omitted.

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

How about a 100TB database with 95TB being pdf's? It seems there are better ways of managing lots of immutable small files (including for backups/replication) than shoving them all into a database.

>How about a 100TB database with 95TB being pdf's?

How about it?

>It seems there are better ways of managing lots of immutable small files (including for backups/replication) than shoving them all into a database.

Depends on the business case. A database gives certain guarantees you'll have to replicate (usually badly) in any other way.

Conceptually, it's absolutely cleaner.

As for from a scientific or engineering standpoint, there are again no laws that dictate saying whether this is bad or good.

Even the performance characteristics depend on the implementation of the particular DB storage engine. They might be totally on par with storing in the filesystem (or close enough not to matter).

Not to mention that some filesystems might even have worse overhead depending on the type, size, etc. of files. In fact this very FA speaks of "SQLite small blob storage" being "35% Faster Than the Filesystem".

Plus filesystem storage and DBs are not that different in most cases -- they share algorithms for storage and indexing (logstorage, btrees, etc), and the main difference is the access layer. The FreeBSD filesystem, for one, was more like a DB storage layer than a 70s style Unix filesystem.

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

#133

Post by Chris2048 »

Quarrelsome wrote: post #83
wyc wrote: post #35

I know that SQLite performs exceptionally on embedded devices including most phones, IoT devices, and more. However, for better or worse, it's a flat file. Does anyone know of a TCP/IP-speaking SQL database that would work well on an embedded device? PostgreSQL/MariaDB seem kinda heavy, and the net couplers for SQLite look pretty unsupported.

and what exactly do you think any database is at the end of the day? It's not magic, it all ends up on the disk as a file. I mean why is a page file called a FILE?

Some dbs (innodb?) support raw block/partition access

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

#134

Post by Chris2048 »

notacoward wrote: post #28
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

FYI, in sqlite is still on disk.

Do you mean "stored on a traditional file-system"?

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

#135

Post by wyoung2 »

YZF wrote: post #38

What about mixed delete/write/read workloads? How well does SQLite deal with fragmentation?

> What about mixed delete/write/read workloads?

The source code to the test program is part of the SQLite source tree. It should be fairly easy to modify it to test the scheme you have in mind.

> How well does SQLite deal with fragmentation?

SQLite operates a lot like a modern filesystem: tree-based indexing structures, writes go to empty spaces, deletes leave holes that may later be filled by inserts, etc.

SQLite has active mechanisms to reduce fragmentation on the fly: b-tree page defragmentation (https://www.sqlite.org/fileformat.html) and auto-vaccuum (https://www.sqlite.org/pragma.html#pragma_auto_vacuum) being the main ones. If the DB file gets too fragmented, you can run a manual VACCUM (https://www.sqlite.org/lang_vacuum.html) on it.

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

#136

Post by Spooky23 »

wyoung2 wrote: post #125
Spooky23 wrote: post #111

Earlier quoted context omitted.

Great catch. The whole premise of the article is undermined by that.

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…

Thanks for the clarification.

In any case, I really take issue with that approach. Nearly every windows database vendor, including Microsoft, recommends or requires that you disable most types of scanning in databases. Not only is it ineffective and performance impacting, it potentially undermines integrity of the data.

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

#137

Post by wyoung2 »

ComodoHacker wrote: post #59

Can we draw a side conclusion from these measurements that open()/close() overhead on Win10 is significantly higher than on Win7? This seems doubtful.

See section 2.1 in the report: the Win10 and Win7 installations are on different hardware.

If you have a dual-boot Win10/7 box, it's easy to run the test yourself.

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

#138

Post by Chris2048 »

paulddraper wrote: post #23
jstimpfle wrote: post #17

Earlier quoted context omitted.

Opening a file means locating it physically on the disk by following references (usually at least one per directory level). This is a costly operation. Compare to the typical case of adding another row by appending to an already allocated disk block. No seek operation needed.

Prompting the question: why isn't the whole disk just a SQLite database?

Maybe it could be, there are a few different VFS for SQlite, and one of them ("one_file") directly writes to memory (for embedded devices):

https://sqlite.org/vfs.html

http://www.sqlite.org/src/doc/trunk/src/test_onefile.c

Maybe this can be turned into a vfs driver (fuse?).

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

#139

Post by Chris2048 »

wyoung2 wrote: post #128
mpweiher wrote: post #109

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

> The same effect will be reproducible with any format storing multiple objects in a single file Given unbounded developer time, any advantage SQLite has here can of course be matched or beaten by custom code. It's just another C library, not magic. The real issue is, how much work will it take you to do that? SQLite is billed as competing with `fopen()`. That's the proper way to compare it: given equal development t…

linking to a post in another article wrt tagging fs:

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

that thread began: "Now we just need a redesign of Unix tools and principles/practice"

This is perhaps one advantage of a sq-lite fs; the flexibility.

You could design concepts into the db structure, and have the driver translate this into the usual language of folders/files/read-write.

But as well as requiring new fsck tools etc, you might also need new ways to interact with the fs as it would have new capabilities, e.g. storing data non-hierarchically.

If the OS can be pushed forward wrt some feature, I care less about whether it would perform.

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

#140

Post by wyoung2 »

Spooky23 wrote: post #136
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…

Thanks for the clarification. In any case, I really take issue with that approach. Nearly every windows database vendor, including Microsoft, recommends or requires that you disable most types of scanning in databases. Not only is it ineffective and performance impacting, it potentially undermines integrity of the data.

My contention was that this article is about least-effort methods (i.e. fopen() vs default SQLite) so a lot of Windows SQLite users are going to have Defender (or heaven help them, something much worse) installed.

Once you start getting into optimizations, much of this article starts to sway back and forth. Put enough effort into optimization, and you can outdo SQLite for a specific application, simply because SQLite isn't magic, it's just C. Well-written and highly-optimized C, but purpose-written code always has the potential to do better than general-purpose code.

The point of SQLite in general and this report in specific is that you get a lot of performance, power, and safety for free with SQLite as opposed to `fopen()` and such. Good performance while antimalware interferes is just one way this manifests.

Post reply on HN