Next week on Show HN: `sqlitefs`, the SQLite-backed FUSE filesystem?
Something similar exists: https://www.youtube.com/watch?v=wN6IwNriwHc
35% Faster Than The Filesystem (2017)
51–60 of 166 posts
Re: 35% Faster Than The Filesystem (2017)
#52Earlier quoted context omitted.
EDIT: Deleted a controversial comment about the often praised SQLite test suite not actually being public and about locking behaviour in SQLite. References are [1] and [2]. [1] https://www.sqlite.org/th3.html [2] https://beets.io/blog/sqlite-nightmare.html
D. Richard Hipp has always been pretty up front about his business model. He provides sqlite for free, public domain, and does not typically entertain third party contributions to keep the licensing clean. Additionally his business sells proprietary extensions and support for sqlite. Having that test suite uniquely positions them to do so, and due to this business model they expect to have the funding to continue pro…
[deleted]
Re: 35% Faster Than The Filesystem (2017)
#53In the past I've had huge speedups by moving simple single-table databases that had grown a bit (e.g., time series data) from sqlite to postgres. Insert performance is also quite bad forcing you to write applications with extra caching layers to be able to do a bunch of inserts at once. Sqlite is great for many applications but it's speed is somewhat oversold. I really wish the postgres engine was embeddable into app…
I would be curious about your particular implementation with SQLite. When enabling WAL, I am able to saturate a NVMe disk on which the the database file resides. In the single-node case, I have yet to see SQLite (when optimized) run slower than any other SQL implementation. I do agree that SQLite with 100% defaults will easily be overtaken by Postgres, and that if your use case requires that more than 1 node persist transactional data you should definitely be using Postgres.
We are currently using SQLite (C#/System.Data.Sqlite.Core/WAL=on/synchronous=normal) in production for multiple customers as the principal store of transactional business data and application state. We have had literally zero downtime or performance concerns as a consequence of SQLite being used for managing this data. The longer this goes on, the more I am led to believe that using a client-server database system for any single-node persistence scenario is never a good idea. The part that I really like is that I don't have to install anything on the customer's box along with our software. We use a self-contained deployment of .NET Core, and it brings the SQLite engine along for the ride.
Re: 35% Faster Than The Filesystem (2017)
#54Really depends on which filesystem too, you'll notice here that Microsoft's NTFS implementation is horrendously slow, HFS+ is several times faster, but still slower. Since they don't bother explaining which Linux filesystem they used, I suspect they mean EXT4, and the particular version of EXT4 in the kernel in that particular version of Ubuntu. The real news is: ext4 is about as fast as SQLite for small objects.
Which is why for decades many dB offerings offered raw or cooked storage. Cooked being that they used the file system and raw being using the device directly. I'm not sure of the upsides and downsides today as I last worked in that area about 20 years ago, but was very much a case of raw a noticeable performance gain. But then you are equally tied into the vendors backup mechanisms.
I ran a large at the time Informix implementation in the late 90s/early 2000s.
Raw disk vs file system (Solaris UFS) performance was 35% faster on our transaction systems.
Re: 35% Faster Than The Filesystem (2017)
#55Wow, so maybe instead of a node_modules folder, npm should use a node_modules.db?
This sounds like a fantastic idea. You would go from 4000 directories and 50000 files to a single file with b-tree indices, hashes, etc. You could probably get a 100x speedup over the current approach.
Re: 35% Faster Than The Filesystem (2017)
#56Earlier quoted context omitted.
EDIT: Deleted a controversial comment about the often praised SQLite test suite not actually being public and about locking behaviour in SQLite. References are [1] and [2]. [1] https://www.sqlite.org/th3.html [2] https://beets.io/blog/sqlite-nightmare.html
D. Richard Hipp has always been pretty up front about his business model. He provides sqlite for free, public domain, and does not typically entertain third party contributions to keep the licensing clean. Additionally his business sells proprietary extensions and support for sqlite. Having that test suite uniquely positions them to do so, and due to this business model they expect to have the funding to continue pro…
Resp to deleted comment:
sqlite is considered such because it's open source and well tested. That doesn't seem to imply to me that the tests are also open source. I don't think Hipp is lying about his proprietary test suite, so I just can't convince myself to lose sleep over this.
Re: 35% Faster Than The Filesystem (2017)
#57Earlier quoted context omitted.
D. Richard Hipp has always been pretty up front about his business model. He provides sqlite for free, public domain, and does not typically entertain third party contributions to keep the licensing clean. Additionally his business sells proprietary extensions and support for sqlite. Having that test suite uniquely positions them to do so, and due to this business model they expect to have the funding to continue pro…
Resp to deleted comment: sqlite is considered such because it's open source and well tested. That doesn't seem to imply to me that the tests are also open source. I don't think Hipp is lying about his proprietary test suite, so I just can't convince myself to lose sleep over this.
Yes, fair enough. Note that I never accused anybody of not telling the truth. My point was about how useful sqlite can be as the posterchild example of a good test suite when we can't see the secret sauce. Not that it doesn't exist. I deleted the other comment because after reading yours I realised it was needlessly controversial. I think you're right in that SQLite can be considered an example of having a good test suite when looked at from a different perspective, just not from the point of it (the test suite) being open source.
Re: 35% Faster Than The Filesystem (2017)
#58What if you create something that is optimized for performance without offering the full SQL language? Just a container to store small files with padding to avoid the expensive open/close calls.
As sibling comments stated it's common in games. A well known open source VFS library is PhysicsFS¹, previously it was called PhysFS AFAIR.
Re: 35% Faster Than The Filesystem (2017)
#59Beware opening sqlite files from untrusted sources. https://research.checkpoint.com/select-code_execution-from-u...
That's true for everything.
Re: 35% Faster Than The Filesystem (2017)
#60For small- to mid-sized projects, I’ve always realized huge gains in simplicity by haves “Files” tables to store various assets. It means instances in a web-farm can pull the files down when they initialize easily, it means files are automatically versioned, it provides an obvious place to put the files when they are being uploaded on the Admin panel. It means all the files are getting backed up as part of the databa…
I though, one of the advantage here is, you are able to store/read metadata along the file content easy without worrying the metadata and file may desync in some way or corrupted.
This makes serve something like Image (photo dimension) or video(length) metadata way easier. Some filesystem (like ntfs) do have place to save metadata along files, but most filesystem don't have the way to done that properly