News for hackers. Stuff that matters.
Re:SQLite – The “server-process-edition” branch by quinthar on Wednesday August 15, 2018 @01:14PM (#17767768)
It's scales so perfectly we are deploying it across 3 datacenters, each with four 384 core machines with 3 terabytes of RAM. I can't speak highly enough about sqlite and the team behind it.
Re:SQLite – The “server-process-edition” branch by da_chicken on Wednesday August 15, 2018 @05:05PM (#17769781)
I don't understand why you'd use SQLite for a deployment this heavy over PostgreSQL or MySQL. Yes, SQLite is fantastic, but why would you choose to do it this way?
Re:SQLite – The “server-process-edition” branch by andrewguenther on Wednesday August 15, 2018 @06:00PM (#17770151)
Because SQLite is often the right tool for the job, more so than Postgres and MySQL. It scales insanely well and requires minimal configuration and near zero administration. I try to use SQLite for as long as is feasible in my projects purely because it "just works".

Don't let the "Lite" fool you. Depending on your needs, you can scale to 10s of thousand of users using just SQLite. Or not! It is all about knowing your system and properly evaluating your options. I choose to use SQLite because it often fits the use case of small to medium projects the best.

Re:SQLite – The “server-process-edition” branch by chubot on Wednesday August 15, 2018 @07:20PM (#17770585)
Honest question: where do Postgres and MySQL fall down for such deployments? What is the administration required? Backups? Users and permissions? Indices?

FWIW I use sqlite for a personal wiki, i.e. a website with exactly one user :)

Re:SQLite – The “server-process-edition” branch by codefined on Thursday August 16, 2018 @05:15PM (#17778470)
I think the idea is that if your service does more, it necessarily needs more work to maintain. From a simple point of view, there are just more moving parts to go wrong.
Re:SQLite – The “server-process-edition” branch by eismcc on Wednesday August 15, 2018 @08:31PM (#17770951)
The lite doesn’t refer to light, it refers to a “mineral”.

https://changelog.com/podcast/201

Re:SQLite – The “server-process-edition” branch by oblio on Thursday August 16, 2018 @03:40AM (#17772696)
I was confused by this, so I read the transcript. In case anyone else reads this, the name is supposed to be read as:

SQL (S Q L or sequel, your call) - ite

So it does sound like the name of a mineral: bauxite, boehmite, hematite, etc. Heck, kryptonite :D

Re:SQLite – The “server-process-edition” branch by TeMPOraL on Thursday August 16, 2018 @04:10AM (#17772823)
> Jerod Santo:

> Hm, like a mineral. Were you playing on the word "light", or were you just playing on mineral...?

> Richard Hipp:

> I was, I was.

It's pronounced as a mineral. It's still a pun on "light". And an obvious one, given what SQLite is compared to other RDBMSes.

Re:SQLite – The “server-process-edition” branch by api on Wednesday August 15, 2018 @11:09PM (#17771594)
If someone put a good fast raft replication engine in process in front of sqlite you'd have a pretty killer db for a lot of things.
Re:SQLite – The “server-process-edition” branch by captn3m0 on Wednesday August 15, 2018 @11:14PM (#17771612)
Re:SQLite – The “server-process-edition” branch by oconnor663 on Wednesday August 15, 2018 @11:14PM (#17771615)
Today is your lucky day: https://github.com/rqlite/rqlite
Re:SQLite – The “server-process-edition” branch by gcbw2 on Wednesday August 15, 2018 @07:28PM (#17770640)
I think it is a tradeoff. If you want more data resilience on disk, SQLite will require all sorts of hacks to (and more difficult, when) to dump to disk.

On the other hand, if you need every ounce of performance and do not care so much about data resiliency on disk, then the other sql applications will be too much hassle to configure in a way you do not miss any of the many safeguards to save data they ship with by default, which will kill your performance when you least expect.

Re:SQLite – The “server-process-edition” branch by emn13 on Wednesday August 15, 2018 @05:38PM (#17770005)
It's faster? Simpler?

Conversely, why would you choose one of those alternatives?

(I don't actually think there aren't any reasons to do so, mind you, but I can well imagine that if you really KISS, well - even at impressive sizes sqlite makes sense).

Re:SQLite – The “server-process-edition” branch by vesak on Thursday August 16, 2018 @12:29AM (#17771891)
SQLite's typing is rather weak, is it not? That might be a tad scary for someone used who relies on that part of PostgreSQL.
Re:SQLite – The “server-process-edition” branch by kjax on Thursday August 16, 2018 @01:42AM (#17772214)
It sure is. After recently porting a large application from SQLite to Postgres, much of my time was spent casting types and ensuring code was updated to input the correct types into Postgres. All in all, the changes were for the better, as many bugs were discovered and resolved in the process.
Re:SQLite – The “server-process-edition” branch by da_chicken on Thursday August 16, 2018 @12:52PM (#17776363)
Well, the historic one is, "because you need multiple concurrent data writers."

But, that's what this branch is supposed to be for. So now the reason is: "because you need multiple concurrent data writers from multiple processes." And it might also be "you need multiple concurrent data writers and care about your data enough to not risk loss in the event of a system crash or power failure" since it uses PRAGMA synchronous=OFF.

Re:SQLite – The “server-process-edition” branch by le-mark on Wednesday August 15, 2018 @02:12PM (#17768331)
You are using this particular branch in production, deployed as you mention, or sqlite in general? Some more info about this use case would be very interesting if you can share!
Re:SQLite – The “server-process-edition” branch by gregmac on Wednesday August 15, 2018 @01:24PM (#17767871)
What sort of volume is this handling? Is the dataset in memory, or if not, what size and type of I/O is backing this? It seems like a ton of CPU, whereas in my experience typically I/O is the primary bottleneck for database loads.
Re:SQLite – The “server-process-edition” branch by basilgohar on Wednesday August 15, 2018 @02:40PM (#17768569)
Note the second comment in the following thread:

https://news.ycombinator.com/item?id=12739771

quinthar posted in quite a lot of detail there as well, so it may provide some context.

Re:SQLite – The “server-process-edition” branch by charleslmunger on Wednesday August 15, 2018 @03:39PM (#17769040)
The link says that it requires turning synchroous off, which means that you won't be waiting for real I/O on transactions, since no fsync calls are emitted. Add that to a huge amount of RAM for cache, and it's very reasonable to be CPU bound... So long as you don't mind data loss or corruption on power loss or kernel panic...
Re:SQLite – The “server-process-edition” branch by latch on Wednesday August 15, 2018 @11:58PM (#17771759)
Doesn't a BBU Raid controllers largely address the power loss concern? And, I always thought BBU Raid controllers were just absolute common sense for any serious database (until everyone went cloud and suddenly basics like dual network card, dual PSU and raid controllers didn't fit Amazon's desire to sell complexity).
Re:SQLite – The “server-process-edition” branch by nh2 on Thursday August 16, 2018 @12:54AM (#17771988)
Well, data will be lost if anything below the OS buffer cache crashes (source http://sqlite.1065341.n5.nabble.com/How-dangerous-is-PRAGMA-...).

The BBU protects from power loss of the HDs, but not power loss or general failure of the mainboard or any other important component.

PAlso BBUs can run out of battery so a flash backed BBU is generally recommended.

Re:SQLite – The “server-process-edition” branch by singron on Thursday August 16, 2018 @12:52AM (#17771975)
Without fsync, the dirty page can sit in RAM and not even be sent to the disk, so a battery backup for the disk wouldn't solve data loss.
Re:SQLite – The “server-process-edition” branch by latch on Thursday August 16, 2018 @03:12AM (#17772570)
Right..but you leave fsync on in this case, no? This might be incompatible with the locking required by this server-process edition though (I guess, no clue). But more generally speaking, fsync=on with a proper raid controller gives massive performance boost (like orders of magnitude) while being relatively resilient to power loss.
Re:SQLite – The “server-process-edition” branch by nutjob2 on Wednesday August 15, 2018 @02:48PM (#17768633)
"in my experience typically I/O is the primary bottleneck for database loads"

Not when you have potentially neatly 3TB of memory cache.

Of course it all depends on the dataset.

Re:SQLite – The “server-process-edition” branch by viburnum on Wednesday August 15, 2018 @09:38PM (#17771222)
So is there one SQLite process running on each of the 384 cores?
Re:SQLite – The “server-process-edition” branch by est on Wednesday August 15, 2018 @11:20PM (#17771639)