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

51–60 of 137 posts

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

#51

Post by hughrr »

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.

This is actually a problem with the design of NTFS. Small files are stored in the MFT (Master File Table) which results in heavy lock contention and slowness if you're doing lots of small files which is what most people tend to do these days.

NTFS really does quite well on large files but small Unixy things and source code it is hopeless on. I first learned about this trying to make Subversion fast about a decade ago.

You can tune some of this out with fsutil but it gives marginal gains. It's depressing though when you have a VM on windows that is faster than the native OS filesystem :(

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

#52

Post by gameswithgo »

chungy wrote: post #37
mikestew wrote: post #33

Earlier quoted context omitted.

The only reason I'm posting on HN right now is because I dared to right-click a folder on a network share. MyFolderName (not responding) , whelp, guess I'll go fuck off for 60 seconds while every item in that shortcut menu opens a network connection, navigates to...or whatever the hell it's doing. Yeah, I know, use the CLI. OTOH, I argue: fix your broken UI. I worked at MSFT for eight years as a full-time, Flavorade-…

> rarely a day goes by that I don't question how Microsoft manages to get anyone to buy that steaming pile they call an operating system. "But muh games!"

yeah maybe instead of making fun of that major industry other operating systems should treat it as an opportunity to gain market share.

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

#53

Post by harikb »

bsaul wrote: post #48
vincent-toups wrote: post #3

I'd be shocked if this weren't the case.

I guess you must be a low-level developer then, because to application developer it would seem that sqlite write speed is actually bound by the file system performance (which it depends on).

The correct comparison is “SQLite index” is faster compared to “file system inode index”

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

#54

Post by tantalor »

bsaul wrote: post #48
vincent-toups wrote: post #3

I'd be shocked if this weren't the case.

I guess you must be a low-level developer then, because to application developer it would seem that sqlite write speed is actually bound by the file system performance (which it depends on).

The title is a joke, right? It's not actually "faster than the filesystem" unless it's not using the filesystem; it's faster than something else that also uses the filesystem because they use different system calls (context switching).

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

#55

Post by kissgyorgy »

xxs wrote: post #34

No idea what's with the sqlite articles making the front page every other day. This one is pretty old as well. The measurements in this article were made during the week of 2017-06-05 using a version of SQLite in between 3.19.2 and 3.20.0.

I think it's just simply a lot of folks on HN (including me) just like SQLite very much and instantly voting up an article about SQLite (even when we already saw it :D).

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

#56

Post by dexterhaslem »

modeless wrote: post #24
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.

If you're doing a bunch of I/O, it's often worth it to go into Windows Defender settings and turn off "real time protection" temporarily. It can make a big difference.

absolutely, and for directories for your projects /places where lots of files thrash about, add those dirs as exceptions in the options.

i mean i added c:\ as an exception because i do not need realtime av but i dont think it will actually work like that

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

#57

Post by outworlder »

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.

Known issue? Yes. One of the reasons many games have faster load times under Wine when compared with the actual OS.

No clue on what the actual underlying issue is. And it's not like we can easily switch filesystems to compare.

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

#58

Post by passivate »

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.

Personally, I have not seen that. Which operations did you think were slow? Small-block/large-block?, sequential/random I/O ? etc, etc. Or did you just mean general productivity?

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

#59

Post by masklinn »

naikrovek wrote: post #32
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.

Yes. NTFS filesystem performance when accessing many small files rapidly is slow. It's an NTFS limitation, not any specific version of Windows. I believe it is caused by some per-file overhead that NTFS incurs, though I'm not 100% sure.

> It's an NTFS limitation, not any specific version of Windows.

I would guess their surprise is because the charts specifically show Win10 as being 100% slower than Win7 in the first chart, and still a good 30% slower in the best case (third chart).

That's not NTFS v other, that's Win7 v Win10, both on NTFS.

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

#60

Post by jamal-kumar »

This gets posted a lot here and I really wonder if anyone's bothered to try and replicate these results or test them with different sizes of data on different filesystems - You can see that the greatest disparity is with NTFS/windows while linux is pretty close in performance, but they don't bother to mention if they've got ubuntu formatted for ext4 or whatever. I can't really seem to find anything looking around, and this article is like 5 years old now. I remember showing this very article to a supervisor at one point and he scoffed it off as unnecessary overhead when all I needed was a plain file store and no relational queries at all.

It looks like this is the benchmarking code, I'll have to go over it for curiosity sometime: https://www.sqlite.org/src/file/test/kvtest.c

Post reply on HN