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

SQLite is 35% Faster Than The Filesystem (2017)

sqlite.org

Post reply on HN
Print view View original

11–20 of 137 posts

Re: SQLite is 35% Faster Than The Filesystem (2017)

#12

Post by sixothree »

mjevans wrote: post #8

I would love for directories of small files to be stored this way under ZFS; like infrequently updated tar or zip (no compression) files. (Though the filesystem layer of compression might operate on the whole file, or maybe as two streams, one for the file and one for the index.)

I was under the impression that in NTFS files under 4k are stored in the MFT. I feel like they might have chosen 10k to break this barrier and end up in filespace land.

edit: https://en.wikipedia.org/wiki/NTFS#Resident_vs._non-resident...

Re: SQLite is 35% Faster Than The Filesystem (2017)

#14

Post by shawnz »

laurent123456 wrote: post #9

Is it a known issue that the filesystem on Windows 10 is so slow? Being 5 times slower than macOS was roughly my experience but I thought there was just something wrong with my Windows laptop. I can't find any benchmark or explanation about this.

See here for some notes from the WSL team why certain filesystem operations that would be fast on Linux are slow on Windows:

https://github.com/microsoft/WSL/issues/873#issuecomment-391...

https://github.com/microsoft/WSL/issues/873#issuecomment-425...

Re: SQLite is 35% Faster Than The Filesystem (2017)

#16

Post by simcop2387 »

mjevans wrote: post #8

I would love for directories of small files to be stored this way under ZFS; like infrequently updated tar or zip (no compression) files. (Though the filesystem layer of compression might operate on the whole file, or maybe as two streams, one for the file and one for the index.)

I think this is kind of what reiserfs did back in the day for small files. Keep them in the tree and share pages with lots of small files. It worked rather well, it's just that the file system had plenty of other issues with reliability and recovery at the time. It was vitally impotant that you didn't store any plain text disk images that contained a reiserfs partition on a reiserfs mount, fsck could decide to merge thentwo together causing unknown amounts of corruption.

Re: SQLite is 35% Faster Than The Filesystem (2017)

#17

Post by nightfly »

Already__Taken wrote: post #7
mirekrusin wrote: post #5

I wondered the other day how things would behave in node if dependencies were in node_modules.sqlite3 (with posibility to eject to edit if needed).

Shouldn't it be possible to make an sql structure and mount it like an FS to find out?

That would be the worst of both worlds: The overhead of storing your data in SQL, plus the overhead of filesystem search and access.

The better way to do this would be to have node access the modules in SQLite directly.

Re: SQLite is 35% Faster Than The Filesystem (2017)

#18

Post by daenz »

Somewhat related, I wrote a fuse-based file system in Rust recently that used SQLite as the backing store for file records, though not the file contents. I imagine I could use it for file content as well, so it's good to know more about its performance.

https://amoffat.github.io/supertag/

Re: SQLite is 35% Faster Than The Filesystem (2017)

#19

Post by mpweiher »

Hmmm.

SQLite is 35% faster reading and writing within a large file than the filesystem is at reading and writing small files.

Most filesystems I know are very, very slow at reading and writing small files and much, much faster at reading and writing within large files.

For example, for my iOS/macOS performance book[1], I measured the difference writing 1GB of data in files of different sizes, ranging from 100 files of 10MB to 100K files of 10K each.

Overall, the times span about an order of magnitude, and even the final step, from individual file sizes of 100KB each to 10KB each was a factor of 3-4 different.

[1] https://www.amazon.com/gp/product/0321842847

Re: SQLite is 35% Faster Than The Filesystem (2017)

#20

Post by jjoonathan »

laurent123456 wrote: post #9

Is it a known issue that the filesystem on Windows 10 is so slow? Being 5 times slower than macOS was roughly my experience but I thought there was just something wrong with my Windows laptop. I can't find any benchmark or explanation about this.

It isn't just Windows 10, as far as I know every version of windows has always wanted to spend like a minute (or multiple minutes!) slowly counting the files before it proceeds to slowly delete them one by one... until it finds one that's open in a background process and nixes the whole operation in a half complete state. 5 times slower sounds conservative. It's awful.

Hey, maybe they could put rounded corners on the file deletion window, I bet that would fix it!

Post reply on HN