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

91–100 of 166 posts

Re: 35% Faster Than The Filesystem (2017)

#91

Post by bob1029 »

pedrocr wrote: post #84
bob1029 wrote: post #81

Earlier quoted context omitted.

Is there a reason you are managing locking at the application layer on the SQLite connection object? https://github.com/pedrocr/syncer/blob/24d70fd452aa6b1463090... This is going to absolutely destroy your throughput. I am not sure about go, but in .Net, the underlying SQLite connection instance is inherently thread-safe and can be shared across execution contexts. If you are unable to share a single SQLite connectio…

It's a simplification for the threading implementation. Spawning a new connection per thread would work but then I'd probably have to deal with failed transactions. The bottleneck wasn't there though, INSERT performance will just naturally have that behavior: https://stackoverflow.com/questions/1711631/improve-insert-p... Without batching several INSERTs into a single transaction you can't do very many of them which…

I would recommend giving it a try regardless. This is the exact approach we use today and we are seeing zero issues. I am not sure why you think you will see failed transactions. It is a perfectly legitimate/recommended approach to create a new connection per logical transaction.

Re: 35% Faster Than The Filesystem (2017)

#93

Post by liability »

zzzcpan wrote: post #66
liability wrote: post #40

Earlier quoted context omitted.

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.

I'm sure you can go faster, but how much faster? The rowid b-tree lookup, with the b-tree in memory, will almost certainly be blazing fast compared to the actual disk read.

Re: 35% Faster Than The Filesystem (2017)

#94

Post by c-smile »

Slightly off the topic.

In one of web applications (CRM alike) I was experimenting with SQLite-per-user approach. In that application user related data was clustered on per-user basis.

So instead of single-db-for-all-users I had one-db-per-user.

Performance gain was significant - around 25% for most of requests. I suspect that this is due the fact that index trees were significantly shorter for each particular user, data more local, etc.

Yet db schema update and other batch requests can be made without stopping the server, just handle db files one by one.

Just in case.

Re: 35% Faster Than The Filesystem (2017)

#95

Post by notyourday »

pdimitar wrote: post #90
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…

The main added value of sqlite3 when I work with it is mostly compartmentalisation of your data in one DB and one file. You not only store files as blobs, you can also store a lot of metadata on top of them, and then, when your needs inevitably grow, start using other SQL tables for the rest of your application configuration or state. And it's all in one neat tidy package. For the scenario you describe I'd generate a…

> You not only store files as blobs, you can also store a lot of metadata on top of them, and then, when your needs inevitably grow, start using other SQL tables for the rest of your application configuration or state. And it's all in one neat tidy package

That's a great solution in search of a problem for a small dataset.

For a large dataset it is a terrible solution, because disks fail and bit-rot happens. Ability to easily restore a tiny subset of data ( single file or multiple files ) using standard commands ( cp/rsync/etc ) is highly underappreciated.

Re: 35% Faster Than The Filesystem (2017)

#96

Post by pedrocr »

bob1029 wrote: post #91
pedrocr wrote: post #84

Earlier quoted context omitted.

It's a simplification for the threading implementation. Spawning a new connection per thread would work but then I'd probably have to deal with failed transactions. The bottleneck wasn't there though, INSERT performance will just naturally have that behavior: https://stackoverflow.com/questions/1711631/improve-insert-p... Without batching several INSERTs into a single transaction you can't do very many of them which…

I would recommend giving it a try regardless. This is the exact approach we use today and we are seeing zero issues. I am not sure why you think you will see failed transactions. It is a perfectly legitimate/recommended approach to create a new connection per logical transaction.

If the locking is moved to the database concurrent transactions can happen. If those touch the same rows one of them will have to fail to maintain consistency. The bottleneck wasn't there though. Even a single thread doing INSERTs is more than enough to slow down the database. You probably don't have a write-heavy workload.

Re: 35% Faster Than The Filesystem (2017)

#97

Post by notyourday »

fvdessen wrote: post #68
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.

> By putting all data in the db you have less moving parts, transactions, referential integrity, well defined behaviours, etc

If you are doing it for 100k files and below you are using a Ferrari to pick up eggs in a corner store. If you are using it at 100k files and above, you are using a Ferrari to move a pile of paving stones one by one.

Re: 35% Faster Than The Filesystem (2017)

#98

Post by derefr »

c-smile wrote: post #94

Slightly off the topic. In one of web applications (CRM alike) I was experimenting with SQLite-per-user approach. In that application user related data was clustered on per-user basis. So instead of single-db-for-all-users I had one-db-per-user. Performance gain was significant - around 25% for most of requests. I suspect that this is due the fact that index trees were significantly shorter for each particular user,…

The formalization of this approach is https://github.com/biokoda/actordb — which is essentially a sharded document store where one type of “document” is an SQL-addressable SQLite database.

Sadly, ActorDB seems to be inactive as a project—but the fundamental premise is sound.

I’d love to see the concept reimplemented as e.g. a Redis module (since a Redis cluster essentially has the same “shape” as ActorDB’s custom distributed K-V store.)

Re: 35% Faster Than The Filesystem (2017)

#99

Post by pdimitar »

notyourday wrote: post #95
pdimitar wrote: post #90

Earlier quoted context omitted.

The main added value of sqlite3 when I work with it is mostly compartmentalisation of your data in one DB and one file. You not only store files as blobs, you can also store a lot of metadata on top of them, and then, when your needs inevitably grow, start using other SQL tables for the rest of your application configuration or state. And it's all in one neat tidy package. For the scenario you describe I'd generate a…

> You not only store files as blobs, you can also store a lot of metadata on top of them, and then, when your needs inevitably grow, start using other SQL tables for the rest of your application configuration or state. And it's all in one neat tidy package That's a great solution in search of a problem for a small dataset. For a large dataset it is a terrible solution, because disks fail and bit-rot happens. Ability…

Why of course. This entire discussion should always be complemented with the sqlite3's intended use cases. Even the authors admit it's not every app.

So yes, definitely reach for a richer DB system when your needs grow beyond the point where sqlite3's weaknesses start to show.

I believe the discussion in the entire HN thread is on the general topic of "is sqlite3 really that good for small-to-mid-sized projects", to which I believe the answer is definitely a yes.

Re: 35% Faster Than The Filesystem (2017)

#100

Post by derefr »

notyourday wrote: post #97
fvdessen wrote: post #68

Earlier quoted context omitted.

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

> By putting all data in the db you have less moving parts, transactions, referential integrity, well defined behaviours, etc If you are doing it for 100k files and below you are using a Ferrari to pick up eggs in a corner store. If you are using it at 100k files and above, you are using a Ferrari to move a pile of paving stones one by one.

Show me a filesystem that can efficiently hold onto all the inode information for a blockchain represented as files and directories. (Hint: not even LevelDB can hold onto all the trie information efficiently; solutions are being sought that pack things tighter than LevelDB.)
Post reply on HN