News for hackers. Stuff that matters.
Re:The Winamp Skin Museum is powered by a SQLite3 database w... by bazmattaz on Saturday June 11, 2022 @04:42PM (#31708179)
I thought that SQLlite databases are not suitable for large multi user websites. Something got to do with only being able to handles one transaction at a time. Isn’t that right?
Re:The Winamp Skin Museum is powered by a SQLite3 database w... by colechristensen on Saturday June 11, 2022 @04:57PM (#31708323)
Sure, but you can have raised millions with large numbers of customers before this is an issue. Having multiple customers using your platform at the same time to the extent that their transactions actually end up having to wait a meaningful amount of time requires a lot of usage that you often don’t reach for a long long time.
Re:The Winamp Skin Museum is powered by a SQLite3 database w... by derefr on Saturday June 11, 2022 @07:01PM (#31709321)
Or just having any long OLAP queries mixed into your OLTP workload. A readers-writer lock can often end up with tons of writers waiting on one last long read to drain.
Re:The Winamp Skin Museum is powered by a SQLite3 database w... by manigandham on Saturday June 11, 2022 @07:38PM (#31709569)
SQLite WAL mode means readers and writer don't block each other: https://www.sqlite.org/wal.html

Since it's SQLite, it's also trivial to just copy the database file as a snapshot for separate OLAP queries if necessary.

Re:The Winamp Skin Museum is powered by a SQLite3 database w... by derefr on Sunday June 12, 2022 @02:19PM (#31716860)
Not all OLAP queries are for reporting. Sometimes they drive OLTP processes — really complex, separate-statements but same-transaction equivalents to "INSERT/UPDATE/DELETE ... WHERE" queries.

Of course, you can still drive those using a separate snapshot for the read side, to generate an ID set for a follow-up write on the business layer. But now you're doing CQRS — less trivial. You're not getting any of the ACID benefits of doing the OLAP and OLTP queries together in a single transaction; so your data model has to be designed for that (by e.g. having all tables be temporal tables.)

Re:The Winamp Skin Museum is powered by a SQLite3 database w... by xhrpost on Sunday June 12, 2022 @12:05AM (#31711327)
This is sounds pretty great. Is there though that much of a dev ramp up increase from using SQLite over MySQL/PGSQL though?
Re:The Winamp Skin Museum is powered by a SQLite3 database w... by themerone on Saturday June 11, 2022 @04:55PM (#31708304)
SQLlite can do handle concurrent reads, writes are a problem.

A site like this which is mostly a read-only archive is a perfect use case for SQLite.

Re:The Winamp Skin Museum is powered by a SQLite3 database w... by ww520 on Saturday June 11, 2022 @06:09PM (#31708935)
With the amount of memory these days, the whole SQLite db would be cached in memory. Read heavy application is not a problem at all.
Re:The Winamp Skin Museum is powered by a SQLite3 database w... by qbasic_forever on Sunday June 12, 2022 @01:44AM (#31711783)
Yeah at that point you're basically as fast as redis or any other good binary tree datastructure store.
Re:The Winamp Skin Museum is powered by a SQLite3 database w... by hedora on Saturday June 11, 2022 @05:53PM (#31708810)
Define large. I'd guess sqlite can do 10-100K TPS, no problem.

You'll want a read cache to avoid repeatedly rendering the same HTML, etc in the frontend, where most CPU is spent. That'll accidentally reduce database load.

Assuming 10% writes (which is really, really write heavy), that'll get you to 1M page views per second. After that, you'll need to rearchitect.

(MySQL and Postgres are probably better choices, but I'd evaluate all three if I was setting this up for real.)

Re:The Winamp Skin Museum is powered by a SQLite3 database w... by Closi on Saturday June 11, 2022 @08:08PM (#31709791)
Just because you can use something doesn’t mean you should use it.

If you have 10k writes a second and 90k reads a second then SQLite is an awful choice.

Re:The Winamp Skin Museum is powered by a SQLite3 database w... by kasey_junk on Saturday June 11, 2022 @08:38PM (#31710022)
Have you benchmarked it?

Given modern commodity servers and no network overhead I’d buy it not being a problem.

My intuition is you are right but given the operational simplicity improvements it would be worth exploring.

Re:The Winamp Skin Museum is powered by a SQLite3 database w... by hedora on Sunday June 12, 2022 @12:21AM (#31711407)
This. Like I said, postgres or mysql are probably better choices. However, modern hardware (especially small sync disk writes) is really fast,and sqlite has its own strong points.
Re:The Winamp Skin Museum is powered by a SQLite3 database w... by teirce on Saturday June 11, 2022 @04:44PM (#31708198)
I think this is kind of their point. If you're starting out, you may not need to meet the same considerations as a "large multi user website". So you don't get stuck trying to do that - you start with what you need (appropriately) and adjust down the line.

It is hard to do right.

Re:The Winamp Skin Museum is powered by a SQLite3 database w... by Gigachad on Saturday June 11, 2022 @11:54PM (#31711259)
The problem is you have SQLite which works some of the time and Postgres which works all of the time. Postgres is a great option for websites of all sizes. There is vanishingly few reasons to ever pick SQLite for a website. When the only real advantage is not having to run a second process which only really matters for local apps.
Re:The Winamp Skin Museum is powered by a SQLite3 database w... by chasil on Saturday June 11, 2022 @05:09PM (#31708433)
SQLite only allows one writer at a time. Attempts at multiple DML logins will not block until successful, but simply fail.

The allowed writer can also impact readers, depending on the WAL mode setting.

For DSS uses, where a data store is published once a day, SQLite is wonderful. For OLTP, look elsewhere.

Re:The Winamp Skin Museum is powered by a SQLite3 database w... by sgbeal on Sunday June 12, 2022 @08:24AM (#31713968)
> Attempts at multiple DML logins will not block until successful, but simply fail.

sqlite has a "busy handler" mechanism which tells it how to respond to blocks and most apps use the builtin one with which they can tell it "wait for X milliseconds before failing on a lock."

The Fossil SCM (https://fossil-scm.org), the SCM in which sqlite itself is hosted, is an sqlite application which serves thousands of users per day, many of them in write transactions. Its forum is a separate sqlite3 instance. sqlite3's own forum is another... all of those serve many users, many of whom are writing, and in 14 years of using/contributing to fossil-scm.org, almost on a daily basis, i've hit exactly two locking errors.

Re:The Winamp Skin Museum is powered by a SQLite3 database w... by dundarious on Saturday June 11, 2022 @05:16PM (#31708511)
The Single Writer Principle is probably the most important principle for concurrent systems design. Often it’s best to design wholly around it.

In this case, I doubt there are any writers at all.

Re:The Winamp Skin Museum is powered by a SQLite3 database w... by hinkley on Sunday June 12, 2022 @01:44AM (#31711784)
I haven't gotten much past the planning phase but I'm thinking it would be a pretty good fit to use Erlang (Elixir in my case) and sqlite together, which should make SWP pretty straightforward, which in addition to offloading some of the concurrency work off of sqlite, also should make it fairly straightforward to swap out for postgres. If in fact I ever hit that point.
Re:The Winamp Skin Museum is powered by a SQLite3 database w... by dugmartin on Sunday June 12, 2022 @07:38AM (#31713751)
Phoenix now has sqlite3 as an option when running phx.new. I just played with it yesterday to also checkout litespeed syncing the database to backblaze. It was very easy to setup and test by running phx.gen.auth and monitoring the data being synced to b2. There is a blog post about running SQLite in dev and Postgres in production using Phoenix, much like rails does. It’s worth spending a few minutes checking it all out. I have a project in mind where I think it would be a good fit.
Re:The Winamp Skin Museum is powered by a SQLite3 database w... by hinkley on Saturday June 11, 2022 @08:09PM (#31709797)
Somewhere along the way, we seem to have forgotten that we have processors that can do 3 billion mathematical operations per second.

I'm currently trying to shame some people at work into acting on the fact that they wrote some code that should take about 1µs per call that is instead taking over 100. If you're stupid with cycles then you get to be stupid with cores too, and then you get to be stupid with locking semantics.

Re:The Winamp Skin Museum is powered by a SQLite3 database w... by leksak on Sunday June 12, 2022 @08:30AM (#31713993)
Unless you are working in aerospace, military, or similar shaming people seems uncouth and perhaps not the best way to inspire meaningful change?
Re:The Winamp Skin Museum is powered by a SQLite3 database w... by hinkley on Tuesday June 14, 2022 @01:57AM (#31735207)
Shame is somewhere around #5 on a list, and never for subordinates.

Also I’ve worked on commercial aviation software, and maybe three of us even knew the meaning of the word, so I’m curious what domain you saw it in.

Re:The Winamp Skin Museum is powered by a SQLite3 database w... by easytiger on Sunday June 12, 2022 @06:32AM (#31713455)
If its mostly read then it's fine