Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Lobste.rs is now running on SQLite

lobste.rs

Post reply on HN
Print view View original

171–180 of 209 posts

Re: Lobste.rs is now running on SQLite

#171

Post by victorbjorklund »

inigyou wrote: post #141
wild_egg wrote: post #118

Earlier quoted context omitted.

> SQLite definitely seems like a poor choice for dealing with many concurrent requests Can you qualify "many"? SQLite easily handles 100k+ writes per second and it's not hard to have app layer code serialize and batch writes to take advantage of that speed. Concurrent writes require a ton of overhead and your system and code can be quite a lot faster and simpler if you just skip the idea of them altogether.

SQLite serialises all writes - only one can proceed at a time and others must wait until that one is finished

Writes are fast unless you are doing some complex transaction stuff.

Re: Lobste.rs is now running on SQLite

#172

Post by eterm »

torginus wrote: post #166
eterm wrote: post #43

Earlier quoted context omitted.

SQLite definitely seems like a poor choice for dealing with many concurrent requests. Maybe it's improved since I last used it, but to my knowledge SQLite essentially forces all writes to be serialised, at risk of data corruption otherwise. There are tricks for improving the performance such as WALs, but that is merely a performance boost rather than genuine concurrency with things like row-level locks that you might…

Can't you do infinity read replicas? I don't think the write load on lobster.rs is severe - maybe 1 comment/sec, with an acceptable replication delay of 60 sec? If you can do that, you can have infinity instances.

With WAL, you don't need read replicas, you can have lots of readers and they don't get blocked by writers.

As an aside, on replication, eventual consistency is not a panacea:

If I make this comment, it's absolutely fine if you don't see it for a minute.

If I make this comment, it's absolutely broken if I then don't see it on my subsequent requests. I'll think the site has broken, and try to resubmit my comment.

You can relatively easily work around that one by pinning people to instances, but that's still yet another thing to consider, and those kinds of considerations add up when you're dealing with distributed systems.

However, this a single instance, it's way too early to talk about replication.

Given that just hitting their front-page is taking 6+ seconds, they've got a performance problem that needs fixing, and my hunch is that they have some kind of "last accessed" database entry, which turns every request into a write.

Re: Lobste.rs is now running on SQLite

#173

Post by inigyou »

victorbjorklund wrote: post #171
inigyou wrote: post #141

Earlier quoted context omitted.

SQLite serialises all writes - only one can proceed at a time and others must wait until that one is finished

Writes are fast unless you are doing some complex transaction stuff.

Each one includes a few fsyncs

Re: Lobste.rs is now running on SQLite

#175

Post by ksec »

zinodaur wrote: post #162
alfiedotwtf wrote: post #133

Earlier quoted context omitted.

Any reason besides trust me bro?

- Its query planner is ancient and broken - it comes up with very bad plans. Every time we rely on it, we regret it eventually - you have to use locks to prevent mangled write transactions, instead of the db handling it. Jepsen report is scathing - its pretty hard to set it up in semi-sync replication mode, a.k.a. "only return from a transaction commit when the transaction is present on at least one other replica". O…

>I don't know why you would choose it

Vitess. Also I often wonder if these system are on latest mySQL? I have read plenty of mySQL opinions that still based their experience during 5.x era.

Re: Lobste.rs is now running on SQLite

#176

Post by dzonga »

my take to benefit from SQLite:

make sure you're not doing a bunch of migrations. that's the SQLite weak point that I know of & have experienced.

use SQLite - as if you're using a KV or better yet maybe a CQRS approach.

Re: Lobste.rs is now running on SQLite

#177

Post by inigyou »

inigyou wrote: post #147
OutOfHere wrote: post #56

Earlier quoted context omitted.

That's nonsense wrt invitation-only being the future. I can equally say that free speech is the future. For comparison, Reddit does very well without being invitation only.

Reddit is literally the dead internet right now. It's also not open access. You have to run an invisible tightrope so its algorithms won't automatically shadowban you and then you have to run a barely visible tightrope to get enough karma to not have your posts automatically removed from every subreddit.

Also, Reddit now requires you to verify your ID with Persona.

Re: Lobste.rs is now running on SQLite

#178

Post by gregoryl »

chrismarlow9 wrote: post #138
kgeist wrote: post #136

Earlier quoted context omitted.

On the page you linked: >However, if a database has many concurrent overlapping readers and there is always at least one active reader, then no checkpoints will be able to complete and hence the WAL file will grow without bound. >This scenario can be avoided by ensuring that there are "reader gaps": times when no processes are reading from the database and that checkpoints are attempted during those times. Dunno, may…

https://github.com/openclaw/openclaw/issues/72774 Seems to indicate a collection cron with a manual query to truncate can fix it. This is fun little things I like to learn. SRE Easter eggs.

Those SRE people live on excitement, its basically a gift ;)

Re: Lobste.rs is now running on SQLite

#179

Post by shirol »

kgeist wrote: post #130

They use WAL in SQLite. If I continuously perform reads/writes so that they overlap with no gaps, I can make their VM go down because SQLite will not have time to initiate a checkpoint to trim the WAL file. SQLite waits for a time window without any active reads/writes before starting a WAL checkpoint. If there isn't one, the WAL will grow indefinitely, eating up all the disk space on the VM. It's in SQLite's documen…

That's only true in theory. In practice, you'll be rate-limited first. Even if you aren't, the database won't be the bottleneck because SQLite is so fast. Their Ruby on Rails stack will suffer thread starvation way before the WAL grows. Rapid requests still leave microsecond gaps between context switches for SQLite to run an automatic checkpoint. To actually make SQLite stall, you'd need an endpoint that lets you hold a massive write transaction open indefinitely, which lobste.rs doesn't expose anyway (I hope).

Re: Lobste.rs is now running on SQLite

#180

Post by mikae1 »

kgeist wrote: post #130

They use WAL in SQLite. If I continuously perform reads/writes so that they overlap with no gaps, I can make their VM go down because SQLite will not have time to initiate a checkpoint to trim the WAL file. SQLite waits for a time window without any active reads/writes before starting a WAL checkpoint. If there isn't one, the WAL will grow indefinitely, eating up all the disk space on the VM. It's in SQLite's documen…

> I guess most projects switching to SQLite have very low traffic and no malicious users (yet)

Lobsters is invite only. That helps. :)

Post reply on HN