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

61–70 of 208 posts

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

#61

Post by jitl »

songzme wrote: post #56

During my first week at American Express I did not have the credentials to install any applications. My manager wanted me to build a prototype and I couldn't install a database. Firebase and other third party was impossible because I had to work on an internal server. I thought SQLite would save my day, but I remembered it being really hard to install and set up. So I had to write my own DB that I called 'stupid-db'.…

???

SQLite is a library, not a database server. There's no install step - it's a single .C file (and a header, if you're into that). Most of the major scripting languages have rock-solid bindings that don't require any additional system libraries or software installs.

Python (in the stdlib!): https://docs.python.org/2/library/sqlite3.html

Nodejs (prebuilt): https://www.npmjs.com/search?q=sqlite

Ruby (needs libs, which are preinstalled on macOS): https://rubygems.org/search?utf8=&query=sqlite3

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

#62

Post by subway »

copenja wrote: post #60

I mean, is SQLite not also using the filesystem? Maybe I'm confused here. Seems like saying using C is 15% faster than ASM. But that includes user error, right?

3rd paragraph

The performance difference arises (we believe) because when working from an SQLite database, the open() and close() system calls are invoked only once, whereas open() and close() are invoked once for each blob when using blobs stored in individual files. It appears that the overhead of calling open() and close() is greater than the overhead of using the database.

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

#63

Post by mcpherrinm »

copenja wrote: post #60

I mean, is SQLite not also using the filesystem? Maybe I'm confused here. Seems like saying using C is 15% faster than ASM. But that includes user error, right?

The article is basically entirely an answer to that question.

The third sentence in particular is:

The performance difference arises (we believe) because when working from an SQLite database, the open() and close() system calls are invoked only once, whereas open() and close() are invoked once for each blob when using blobs stored in individual files. It appears that the overhead of calling open() and close() is greater than the overhead of using the database. The size reduction arises from the fact that individual files are padded out to the next multiple of the filesystem block size, whereas the blobs are packed more tightly into an SQLite database.

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

#64

Post by bigger_cheese »

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…

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).

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

#65

Post by qznc »

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.

Use sqlite and send SQL queries and answers over the network.

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

#66

Post by AstralStorm »

bigger_cheese wrote: post #64
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…

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.

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

#67

Post by AstralStorm »

qznc wrote: post #65
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.

Use sqlite and send SQL queries and answers over the network.

Or you can switch to the big daddy Postgres or Oracle. They will generally accept SQL that SQLite accepts with few modifications.

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

#68

Post by copenja »

mcpherrinm wrote: post #63
copenja wrote: post #60

I mean, is SQLite not also using the filesystem? Maybe I'm confused here. Seems like saying using C is 15% faster than ASM. But that includes user error, right?

The article is basically entirely an answer to that question. The third sentence in particular is: The performance difference arises (we believe) because when working from an SQLite database, the open() and close() system calls are invoked only once, whereas open() and close() are invoked once for each blob when using blobs stored in individual files. It appears that the overhead of calling open() and close() is grea…

The advice to use a single open/close call instead of multiple is great, but that is advice on how to effectively use the filesystem. It isn't in anyway, shape, or form beating the filesystem.

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

#69

Post by elcritch »

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.

ActorDB could get you there, it's a distributed DB based on SQLite [1].

[1]: http://www.actordb.com

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

#70

Post by rusanu »

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 write intensive the workload is," but keep in mind this is spinning media from 2006. Modern SSDs change the equation quite a bit, as they are much more friendly to random IO and benefit less from database write-ahead log and buffer pool behavior.

Also, when deciding between blob vs. filesystem, blobs bring transactional and recovery consistency. The DB is self contained, and all blobs are contained in it. A restore of the DB on a different system yields a consistent system, it won't have links to missing files, and there won't be orphaned files left over (files not referenced by records in DB).

Despite all this, my practical experience is that filesystem is better than blobs for things like uploaded content, images, pngs and jps etc. Blobs bring additional overhead, require bigger DB storage (more expensive usually, think AWS RDS) and the increased size cascades in operational overhead (bigger backups, slower restore etc).

[0] https://www.microsoft.com/en-us/research/publication/to-blob...

Post reply on HN