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

41–50 of 166 posts

Re: 35% Faster Than The Filesystem (2017)

#41

Post by rathel »

quickquestion42 wrote: post #21
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…

EDIT: Deleted a controversial comment about the often praised SQLite test suite not actually being public and about locking behaviour in SQLite. References are [1] and [2]. [1] https://www.sqlite.org/th3.html [2] https://beets.io/blog/sqlite-nightmare.html

If I understand you correctly, there is another test suite in addition to the one you can download freely?

Re: 35% Faster Than The Filesystem (2017)

#42

Post by pedrocr »

hizanberg wrote: post #38
pedrocr wrote: post #35

Earlier quoted context omitted.

There's nothing about being embeddable that's a limitation. All I really want is a lib I can link to that gives me an entry point to launch the database against a given folder, with some of the tweaks the postgres config file has, and that gives me a connection back. That entry point can launch however many threads it wants and behave exactly like postgres itself. After that my app can connect to a standard postgres…

> There's nothing about being embeddable that's a limitation. An always-running in-memory server process is going to be a lot better at concurrency than multiple embeddable libraries trying to take locks out on the same file on disk.

Read what I wrote. What I want is always-running in-memory. Being embeddable doesn't mean you need to have the sqlite model where several processes access the same database. None of the sqlite uses I've had required that at all.

Re: 35% Faster Than The Filesystem (2017)

#43

Post by liability »

quickquestion42 wrote: post #21
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…

EDIT: Deleted a controversial comment about the often praised SQLite test suite not actually being public and about locking behaviour in SQLite. References are [1] and [2]. [1] https://www.sqlite.org/th3.html [2] https://beets.io/blog/sqlite-nightmare.html

D. Richard Hipp has always been pretty up front about his business model. He provides sqlite for free, public domain, and does not typically entertain third party contributions to keep the licensing clean. Additionally his business sells proprietary extensions and support for sqlite. Having that test suite uniquely positions them to do so, and due to this business model they expect to have the funding to continue providing a public domain sqlite to the world for decades to come.

Re: 35% Faster Than The Filesystem (2017)

#44

Post by zaroth »

Nevermindmine wrote: post #28
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…

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?

Re: 35% Faster Than The Filesystem (2017)

#45

Post by zaroth »

falcolas wrote: post #30
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…

Personal opinion: Those things (static assets) belong in a container with Nginx serving them (and that’s not even counting SAAS options like S3). Easy to integrate with CDNs, easy to update, easy to roll out and back. And you get to take advantage of layers to keep the network and disk usage minimal.

I don’t see how a container solves the backing store / source of truth problem?

How are changes getting put into the container? Are you versioning the container each time a file is changed or added? When files change are all instances of the containers being restarted to get the new/changed files?

Re: 35% Faster Than The Filesystem (2017)

#46

Post by liability »

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 config' arrangement is a nonstarter.

Re: 35% Faster Than The Filesystem (2017)

#47

Post by liability »

rathel wrote: post #41
quickquestion42 wrote: post #21

Earlier quoted context omitted.

EDIT: Deleted a controversial comment about the often praised SQLite test suite not actually being public and about locking behaviour in SQLite. References are [1] and [2]. [1] https://www.sqlite.org/th3.html [2] https://beets.io/blog/sqlite-nightmare.html

If I understand you correctly, there is another test suite in addition to the one you can download freely?

The TH3 test suite is proprietary:

>"The TH3 test harness is a set of proprietary tests, written in C that provide 100% branch test coverage (and 100% MC/DC test coverage) to the core SQLite library. The TH3 tests are designed to run on embedded and specialized platforms that would not easily support TCL or other workstation services. TH3 tests use only the published SQLite interfaces. TH3 consists of about 69.4 MB or 948.1 KSLOC of C code implementing 44753 distinct test cases. TH3 tests are heavily parameterized, though, so a full-coverage test runs about 1.7 million different test instances. The cases that provide 100% branch test coverage constitute a subset of the total TH3 test suite. A soak test prior to release does hundreds of millions of tests. Additional information on TH3 is available separately."

https://sqlite.org/testing.html

Re: 35% Faster Than The Filesystem (2017)

#48

Post by Tomte »

rathel wrote: post #41
quickquestion42 wrote: post #21

Earlier quoted context omitted.

EDIT: Deleted a controversial comment about the often praised SQLite test suite not actually being public and about locking behaviour in SQLite. References are [1] and [2]. [1] https://www.sqlite.org/th3.html [2] https://beets.io/blog/sqlite-nightmare.html

If I understand you correctly, there is another test suite in addition to the one you can download freely?

> The TH3 test harness is a set of proprietary tests

https://www.sqlite.org/testing.html#harnesses

Re: 35% Faster Than The Filesystem (2017)

#49

Post by notyourday »

dana321 wrote: post #37
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…

First thing you can do is just add more RAM, any free ram will be used to cache disk access. Varnish will decrease the latency only if you are using some scripting between the file and its access.

If you are at the point where you need to handle more IO, throwing additional memory onto a file origin will not in my experience do much because filesystems's idea of what to cache is vastly different from user's requests. Not doing any disk IO for hot files is vastly preferable than to doing some. This has been demonstrated in spades at LiveJournal which extensively stuffed files into memcache before Varnish became a thing.
Post reply on HN