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

201–209 of 209 posts

Re: Lobste.rs is now running on SQLite

#201

Post by alfiedotwtf »

evanelias wrote: post #181
zinodaur wrote: post #162

Earlier quoted context omitted.

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

The query planners have diverged between MySQL and MariaDB; which one are you talking about? Both have received improvements in recent years, so the specific version is relevant as well. And at least both systems have supported index hints for decades (unlike another popular database that only very recently acknowledged their necessity in large production workloads...) The locking anomalies are a well-known and well-…

Citing semi-sync thinking everything would be automatic was a weird con against MySQL. I’m now trying to think how the hell other databases magically solve this without needing to handle edge cases

Re: Lobste.rs is now running on SQLite

#202

Post by dredmorbius »

cromka wrote: post #12

Ironically, this post resurfaced on HN with my comment here shown as if made 15 minutes ago, which I in reality did last week?

https://news.ycombinator.com/item?id=48740177> (dang, on 2CP & timestamps).

More: https://hn.algolia.com/?dateRange=all&page=0&prefix=false&qu...>

Re: Lobste.rs is now running on SQLite

#203

Post by zinodaur »

evanelias wrote: post #181
zinodaur wrote: post #162

Earlier quoted context omitted.

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

The query planners have diverged between MySQL and MariaDB; which one are you talking about? Both have received improvements in recent years, so the specific version is relevant as well. And at least both systems have supported index hints for decades (unlike another popular database that only very recently acknowledged their necessity in large production workloads...) The locking anomalies are a well-known and well-…

> The query planners have diverged between MySQL and MariaDB; which one are you talking about? Both have received improvements in recent years, so the specific version is relevant as well. And at least both systems have supported index hints

MySQL - we use the index hints 100% of the time because the query planner (still) doesn't work

> The real-world relevance is minor, especially since companies using external write-through caches are already making much worse inherent trade-offs in that area

I disagree - everyone knows that the cache value can be stale, and to be wary of it if your use case requires correctness. In my experience people tend to expect correct, up to date data from their database - which is why they usually have to opt in to read-replicas

> Do you have some novel academic solution for this that you can propose?

MySQL doesn't support synchronous replication - so semi sync is the next best thing. Then you wrestle with it to make it not crazy (e.g. - only count a tx as acked if it was acked by a replica that has all the other txes). Its not rocket science - maybe the big fanfare about synchronous replication made sense when a disk write was incredibly slow, but adding 2 ms of network latency and 5ms to persist it on another machine in order to not lose user data seems like a pretty straightforward choice to me. And if write performance matters that much to you - you shouldn't be using a dinosaur like MySQL anyways

Re: Lobste.rs is now running on SQLite

#205

Post by evanelias »

zinodaur wrote: post #203
evanelias wrote: post #181

Earlier quoted context omitted.

The query planners have diverged between MySQL and MariaDB; which one are you talking about? Both have received improvements in recent years, so the specific version is relevant as well. And at least both systems have supported index hints for decades (unlike another popular database that only very recently acknowledged their necessity in large production workloads...) The locking anomalies are a well-known and well-…

> The query planners have diverged between MySQL and MariaDB; which one are you talking about? Both have received improvements in recent years, so the specific version is relevant as well. And at least both systems have supported index hints MySQL - we use the index hints 100% of the time because the query planner (still) doesn't work > The real-world relevance is minor, especially since companies using external writ…

> MySQL

You still haven't indicated what version. For example there's a substantial join planner improvement in the latest LTS (MySQL 9.7). And in any case the original thread was about MariaDB, not MySQL.

> I disagree

OK, but again MariaDB literally solved the anomaly in the Jepsen report. So if this particular problem is so important to your use-case, why are you still using MySQL?

> In my experience people tend to expect correct, up to date data from their database

Then why do the vast majority of Postgres users stay with their default READ COMMITTED isolation, which provides weaker guarantees than MySQL's supposedly-problematic version of REPEATABLE READ?

> MySQL doesn't support synchronous replication - so semi sync is the next best thing

Despite not being fully synchronous, MySQL Group Replication has been available for years and is more synchronous than traditional semi-sync was. It solves the semi-sync problems you are describing. Ditto with Galera in MariaDB. They're just not a great fit for extremely high-write-volume OLTP, since they make the latency tradeoff you're describing.

> if write performance matters that much to you - you shouldn't be using a dinosaur like MySQL anyways

Properly-tuned InnoDB provides excellent write performance on par with competitors. Ditto for MyRocks. This is why many of the largest tech companies are still using MySQL as their primary database.

Re: Lobste.rs is now running on SQLite

#207

Post by pushcx »

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…

Thanks for highlighting this failure mode. We don't have a supervisor thread in a position to slow readers and writes like you described in your other thread, so I knocked up a job to force a checkpoint if needed. https://github.com/lobsters/lobsters/commit/cdf9397f8067fc21... Other things would fall over long before we reached 14k RPS so hopefully this suffices for us.
Post reply on HN