Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

35% Faster Than The Filesystem (2017)

sqlite.org

Post reply on HN
Print view View original

61–70 of 166 posts

Re: 35% Faster Than The Filesystem (2017)

#61

Post by pedrocr »

liability wrote: post #46
pedrocr wrote: post #10

In 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 once tried out some RSS aggregator/reader that required me to install and set up postgres for no good reason other than it's what the developer was accustomed to. Huge pain in the ass, particularly since simply using sqlite would have been perfectly adaquate (does a RSS reader really need concurrent writes? Really? ) For any software meant to be used on a non-technie's desktop, anything other than an embedded 'zero…

This is exactly true, which is why embedding postgres would be great. It's very easy to hit the performance limitations of sqlite and then be stuck with the choice of either poor performance or forcing users to setup postgres like you experienced.

Re: 35% Faster Than The Filesystem (2017)

#62

Post by Const-me »

The fact that SQLite is much faster than file systems is not surprising at all. But there’s one surprising result there.

> All machines use SSD except Win7 which has a hard-drive.

And yet Win10 is much slower than Win7 in all tests (note they have latency on the graphs).

How’s that possible? For a drive spinning at 5400 RPM, average random read latency can’t be smaller than 2.2ms, this is how long it takes to rotate the disks 180° which is an average. An SSD should do much better than that.

I have a couple possible explanation. Either that particular Win10 machine, 2016 Lenovo YOGA 910, has _extremely_ slow SSD. Or there’s something wrong with software/drivers/config of the PC, like an antivirus, or wrong chipset drivers.

Re: 35% Faster Than The Filesystem (2017)

#63

Post by pedrocr »

bob1029 wrote: post #53
pedrocr wrote: post #10

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

The code for my most recent case is here:

https://github.com/pedrocr/syncer/

The db code itself is here:

https://github.com/pedrocr/syncer/blob/24d70fd452aa6b1463090...

WAL is being used but that's not enough to get a mixed INSERT/SELECT to not completely starve reads without first caching INSERT and batching it.

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

This is single node with WAL. If there's something I've missed I'd love to see it (or get a PR). But the research I did when I wrote the code seemed to indicate this was a known limitation of sqlite.

Re: 35% Faster Than The Filesystem (2017)

#64

Post by demosito666 »

bob1029 wrote: post #55
wayneftw wrote: post #50

Wow, 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.

Even better would be not to use it entirely - it'd solve far bigger problems with node.

Re: 35% Faster Than The Filesystem (2017)

#65

Post by jon-wood »

zaroth wrote: post #44
Nevermindmine wrote: post #28

Earlier quoted context omitted.

What kind of versioning are your referring to? I know some databases has it but not sure if I understand it. Personally I use S3 or similar for this. At the number of files I'm working with storing it in a relational database would be crazy expensive.

Scheme could be something like; FileId, VersionId, Name, Data, UploadedTimestamp, UploadedBy, ... If you’re on AWS, pushing and pulling from an S3 bucket is probably a great solution, and then of course there’s nothing to worry about in terms of backups. Do you still need to keep an index of the files / metadata in a DB, or can you tag everything you need directly on the S3 objects and just pull the whole bucket?

Even if you’re not in AWS S3 works great, especially where you need to serve content direct to client’s browsers. S3 supports either making a file public and available to all, or using pre-signed URLs to provide short term authorisation to access a file to specific clients. No need to pull the bucket at all.

Re: 35% Faster Than The Filesystem (2017)

#66

Post by zzzcpan »

liability wrote: post #40
Tepix wrote: post #11

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

I'm not sure how much better you'd get than sqlite if you were using a straight forward rowid select with a prepared statement. Sqlite is pretty damn snappy for something like that and using a prepared statement amortizes the cost of compiling the SQL.

You can get a point read with just a single aligned random I/O operation on disk with this. I don't see how a B-tree based storage can compete.

And even more optimizations are possible, like grouping objects often accessed together into a single batch to retrieve the whole group in just one random operation too.

Re: 35% Faster Than The Filesystem (2017)

#67

Post by karmakaze »

Const-me wrote: post #62

The fact that SQLite is much faster than file systems is not surprising at all. But there’s one surprising result there. > All machines use SSD except Win7 which has a hard-drive. And yet Win10 is much slower than Win7 in all tests (note they have latency on the graphs). How’s that possible? For a drive spinning at 5400 RPM, average random read latency can’t be smaller than 2.2ms, this is how long it takes to rotate…

There's the other obvious explanation that because the Win7 machine has slower storage it automatically uses a larger cache than the Win10 machine that more than offsets the difference.

Re: 35% Faster Than The Filesystem (2017)

#68

Post by fvdessen »

notyourday wrote: post #18

As someone who has spent time consulting for different kinds of "we do stuff on the internet" companies, I can confidently say that this is a premature optimization for 99.999% of the companies/projects. The companies simply don't have enough IO traffic to need it. Is it cool? Sure. Is it sexy? Maybe. Is it needed ? Nope. Do boring stuff. Use files. If you send your small files over the internet via web and you need…

> Do boring stuff. Use files.

I have the opposite experience. Files are far from 'boring', they have too many gotchas to count. Putting files in the db is for many use-cases actually easier than putting them on the filesystem. By putting all data in the db you have less moving parts, transactions, referential integrity, well defined behaviours, etc. The speed increase is just a nice side effect.

Re: 35% Faster Than The Filesystem (2017)

#69

Post by Filligree »

messe wrote: post #9
zaroth wrote: post #6

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

> was that it bloats the database backups. Having a deduplicating backup solution might help with that. Simplest way to do that (in terms of ease-of-implementation), would be to keep backups on a ZFS or btrfs filesystem. Note that (AFAIK) with btrfs, deduplication must be triggered manually, so you'd need to run `btrfs filesystem defragment` on the backups to take advantage of it.

Note for other readers: The parent post probably means incremental ZFS snapshots, which won't store multiple copies of any given block unless the piece of data has been moved.

ZFS also has a block deduplication scheme, but turning it on is a poor idea. The deduplication table needs to be stored in memory for decent performance, and this takes a lot of memory. Even then, performance is rarely as good as with dedup turned off. It doesn't even save as much space as you'd hope -- it's per-record, and ZFS storage records are 128KiB by default. If you're trying to deduplicate 10KiB files with that, you're going to have a bad time.

(Note also: The ZFS record size is dynamic; the 128KiB default is the maximum record size it'll allow, i.e. the most data that has to be read and rewritten (somewhere else) if you modify one byte in a larger file. If you just write a single 10KiB file and force it to disk, then you'll get a 10KiB record. It's complicated; try not to read more into this than "dedup is rarely a good idea," though if I'm allowed a second line, I'll add "compression is always a good idea; enable zle at a minimum.")

Re: 35% Faster Than The Filesystem (2017)

#70

Post by zzzcpan »

perspective1 wrote: post #22
zaroth wrote: post #6

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

My (naive?) view is that Nginx caches static files by default after the first hit so the inconvenience isn't worth it in my web apps. Would I really see an improvement switching to a SQLite-esque file system?

Nginx doesn't cache files by default, but it has a file based caching module and a file based store module that can be used to retrieve files from backends and cache on local filesystem.
Post reply on HN