An unscientific benchmark of SQLite vs. the file system (btrfs) • Hacker News Board

Re: An unscientific benchmark of SQLite vs. the file system (btrfs)

#11

Post by christophilus » Sat, Feb 12, 2022, 6:19 PM UTC

kevingadd wrote: post #5

If you're using the same file, it would make sense if the db 'warms up' by growing enough to comfortably hold all your data.

Good point. That’s the more probable explanation, actually. I’d imagine the execution plan would be cached within the first batch of requests. I hadn’t considered the file resizing, but it’s a good hunch.

Re: An unscientific benchmark of SQLite vs. the file system (btrfs)

#12

Post by formerly_proven » Sat, Feb 12, 2022, 6:21 PM UTC

For SQLite in this test writes in particular means transactions, where each transaction contains exactly one write.

Re: An unscientific benchmark of SQLite vs. the file system (btrfs)

#13

Post by leeoniya » Sat, Feb 12, 2022, 6:26 PM UTC

christophilus wrote: post #2

I’m the author. Out of curiosity, how did you find and post this before I did?

i follow you on github :)

sorry!

Re: An unscientific benchmark of SQLite vs. the file system (btrfs)

#14

Post by christophilus » Sat, Feb 12, 2022, 6:34 PM UTC

leeoniya wrote: post #13

christophilus wrote: post #2

I’m the author. Out of curiosity, how did you find and post this before I did?

i follow you on github :) sorry!

Ha! No worries.

Re: An unscientific benchmark of SQLite vs. the file system (btrfs)

#15

Post by christophilus » Sat, Feb 12, 2022, 6:36 PM UTC

formerly_proven wrote: post #12

For SQLite in this test writes in particular means transactions, where each transaction contains exactly one write.

Yeah. It’s simulating the behavior of a CRUD server I’m thinking about writing. I wouldn’t be able to batch writes very easily there, so it would be a transaction per write.

Re: An unscientific benchmark of SQLite vs. the file system (btrfs)

#16

Post by christophilus » Sat, Feb 12, 2022, 6:47 PM UTC

TL;DR: yes, probably for a real-world use case.

Anyway, the perf is good enough for what I need, and the flexibility of querying makes it well worth it. There is something nice about plain text files, though.

Re: An unscientific benchmark of SQLite vs. the file system (btrfs)

#17

Post by CalmStorm » Sat, Feb 12, 2022, 6:50 PM UTC

The article doesn’t mention whether SQLite WAL mode is on. That could make SQLite significantly faster: https://sqlite.org/wal.html

Re: An unscientific benchmark of SQLite vs. the file system (btrfs)

#18

Post by ape4 » Sat, Feb 12, 2022, 6:50 PM UTC

A quick google shows there are few FUSE SQLite implementations. Then you can use grep, ls, etc

Re: An unscientific benchmark of SQLite vs. the file system (btrfs)

#19

Post by bungle » Sat, Feb 12, 2022, 6:54 PM UTC

CalmStorm wrote: post #17

The article doesn’t mention whether SQLite WAL mode is on. That could make SQLite significantly faster: https://sqlite.org/wal.html

It mentions it at the end: ./tmp.db?_timeout=5000&_journal=WAL&_sync=1

Not sure if it was updated after your comment.

Re: An unscientific benchmark of SQLite vs. the file system (btrfs)

#20

Post by tyho » Sat, Feb 12, 2022, 6:55 PM UTC

Go is perhaps not the best tool to use for this as calling sqlite via Cgo will incur a penalty. Might be significant in a very hot loop.