Lobste.rs Successfully Migrates to SQLite with Major Performance Gains
Lobste.rs is now running on SQLite
After years of discussion and multiple deployment attempts, I successfully migrated Lobste.rs from MariaDB to SQLite. The switch resulted in lower CPU and memory usage, reduced hosting costs, and a snappier site experience. Despite initial performance hurdles caused by full table scans, careful debugging and community collaboration ensured a smooth transition that passed the Monday traffic spike with flying colors.
Migrating the underlying database without having access to the production database is really hard to get right.
- kgeist
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 documentation, and almost no one switching to Sqlite seems to be aware of it because no one discusses it in blog posts like these. I guess most projects switching to SQLite have very low traffic and no malicious users (yet)
- homebrewer
It's been fairly unstable recently, pages sometimes render for several seconds which I've never seen under MariaDB. Used to be instantaneous, always.
Sometimes (maybe 5% or less) the request won't render at all, and you get a browser error page.
Today they ran into this bug, lost a bunch of voting data, and went into read-only mode for several hours:
https://github.com/rails/rails/pull/57128
I wonder how much of this is usual bugs which crop up during major database migrations, and how much is caused by choice of SQLite.
- evanelias
Some of the cited reasons for moving off MariaDB [1] seem misguided, in my opinion. Especially the part about "K1 are very enterprise-focused, so the database is likely to focus its work on features that are not relevant to us. There's increased risk they drop the free/open source version we use"
K1 acquired the commercial entity behind MariaDB Enterprise, but that's separate from the non-profit MariaDB Foundation. And there's literally zero risk of the MariaDB server suddenly going closed-source; as a fork of MySQL (which is GPL), this is not even legally possible!
[1] https://github.com/lobsters/lobsters/issues/539#issuecomment...
- chasil
Some observations:
1. Stats are used by "Cost-Based Optimizers."
I think this is the most appropriate wiki:
https://en.wikipedia.org/wiki/Query_optimization
On my Oracle databases, I run the dbms_stats.gather_database_stats package procedure once a week. SQL Server also has stats for sure, and I would be surprised by a relational database that did not.
SQLite will quietly gather stats with each database connection. Running an explicit analyze devotes full attention to an otherwise piecemeal group effort.
2. I don't think rows are physically removed when deleted, instead they are marked. Removal will definitely happen at vacuum.
3. There is a backup command in the CLI that will make a transaction consistent copy of the database file, and there are lower-level C functions behind it that can be used with object code.
4. It is important to remember the many restrictions of WAL mode, including the impact on ATTACHing multiple database files: ACID consistency is lost.
https://sqlite.org/lang_attach.html
'Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL.'
- cromka
Ironically, this post resurfaced on HN with my comment here shown as if made 15 minutes ago, which I in reality did last week?
- varun_ch
Does HN still use simple files for storing its data?
- dcmatt
Wow, lobsters seems refreshing! Invite only to contribute must be why they can run on sqlite so effectively. I've run into so many issues with WAL (real time market data) unless I'm painfully deliberate with reads and writes. Don't feel like jumping on the "ohhh invite me" bandwagon, but definitely subscribing to their feed.
- kenforthewin
The site is performing poorly for me. For example, the login page took 9 seconds to load, same with the home page.