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

We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

beets.io

Post reply on HN
Print view View original

21–30 of 149 posts

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#21

Post by SwellJoe »

I never stop being impressed at how often people will jump to odd, unsupportable, conclusions like, "using MySQL will make this thing faster".

I've seen it so many times over the years regarding users and email configurations. I can't count the number of times I've dropped into someone's badly behaving mail configuration and found they had MySQL hosting the users, and explained it was for "performance" reasons. Somehow they didn't grasp that /etc/passwd fits entirely in memory, and the map files Postfix uses for various lookups and stuff are already a database (just one specifically designed for the task at hand) and also fit entirely in memory. Putting that MySQL layer in there is disastrous if performance matters; it is orders of magnitude slower for any case I've seen...still plenty fast for most cases, but it's ridiculous that this idea gets cargo-culted around that if you store your mail users in MySQL your mail server will be faster.

A little knowledge is a dangerous thing, is what I'm trying to say, and people who know MySQL is "fast" may not know enough to know that it's not the right tool for the job in a lot of cases...and is probably slower for many use cases. I'm pretty confident this is one of those cases. SQLite is wicked fast on small data sets, and being smaller means more of it will fit in memory; I can't think of any way MySQL could be a more performant choice for this workload.

Also, I don't even want to try to imagine shipping an installable desktop application for non-technical users that relies on MySQL!

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#22

Post by dietrichepp »

ben_jones wrote: post #8
petercooper wrote: post #4

Earlier quoted context omitted.

FWIW, I've run SQLite in a few production sites (low 6 figure pageviews per month) and it has worked fantastically. If you understand and work with the limitations, it really is amazing how much you can get out of it. I'm actually surprised WordPress hasn't ever moved over to it for ease of installation/deployment - WordPress and PHP seem more likely to trip over in most deployments I've seen before SQLite would.

Two questions: Wouldn't a "full" RDBMS like Mysql/Postgres offer a ton of benefits over SQLite (like the features to handle edge cases as they arise) to the point where, even if SQLite would work, SQLite still wouldn't be the ideal choice? Does wordpress completely abstract the database or do third-party plugins use their own interfaces to the database, in which case a migration to SQLite would break a lot of them?

Wordpress does not abstract the database, so migration to SQLite would break tons of plugins.

Wordpress is also a PHP application, and it's common to have multiple PHP processes running in parallel, which makes it much harder to use SQLite.

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#23

Post by petercooper »

StavrosK wrote: post #15
petercooper wrote: post #4

Earlier quoted context omitted.

FWIW, I've run SQLite in a few production sites (low 6 figure pageviews per month) and it has worked fantastically. If you understand and work with the limitations, it really is amazing how much you can get out of it. I'm actually surprised WordPress hasn't ever moved over to it for ease of installation/deployment - WordPress and PHP seem more likely to trip over in most deployments I've seen before SQLite would.

What are the limitations? I love SQLite, and have vaguely heard that it has issues with concurrency, but what , exactly? I use it on production for www.tithess.gr and it seems to be working beautifully, no concurrency problems whatsoever there. What problems should I be expecting in a multi-access scenario? I've never had that question answered adequately.

Andrew has already spoken to the single writer matter. Another issue is it's not very easy to scale horizontally at all. You could shard, but you're not going to be doing replication very easily (although a project does exist to provide replicated SQLite - https://github.com/rqlite/rqlite - but then you might as well finally use Postgres or whatever).

My attitude to this is if my project can be working well and turning a profit within SQLite's limitations, we can worry about migrating to a different stack later on. The same reason I use Ruby for almost everything initially too.

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#24

Post by omarforgotpwd »

StavrosK wrote: post #15
petercooper wrote: post #4

Earlier quoted context omitted.

FWIW, I've run SQLite in a few production sites (low 6 figure pageviews per month) and it has worked fantastically. If you understand and work with the limitations, it really is amazing how much you can get out of it. I'm actually surprised WordPress hasn't ever moved over to it for ease of installation/deployment - WordPress and PHP seem more likely to trip over in most deployments I've seen before SQLite would.

What are the limitations? I love SQLite, and have vaguely heard that it has issues with concurrency, but what , exactly? I use it on production for www.tithess.gr and it seems to be working beautifully, no concurrency problems whatsoever there. What problems should I be expecting in a multi-access scenario? I've never had that question answered adequately.

Your entire database is in one file, so you can only write from one process at a time. You can read from multiple processes at a time though.

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#25

Post by johnny22 »

creshal wrote: post #7
chillacy wrote: post #2

> we’re read-heavy and write-light > we have almost no indices, no principled denormalization Sounds like an easy win. People are probably suggesting a database switch because they're finding issues with the current speed, but they're not using their current database to its full potential yet.

Is the bottleneck even the database in the first place? From the article, that doesn't seem to be the case: > The main case when beets writes to its database is on import, and import performance is dominated by I/O to the network and to music files. Small file I/O is universally slow, even on SSDs, and if you're hitting rate-limited MusicBrainz servers for each file, database performance is almost irrelevant.

lots of people use beets to query their music to build playlists. those cases don't require network access.

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#26

Post by zackelan »

StavrosK wrote: post #15
petercooper wrote: post #4

Earlier quoted context omitted.

FWIW, I've run SQLite in a few production sites (low 6 figure pageviews per month) and it has worked fantastically. If you understand and work with the limitations, it really is amazing how much you can get out of it. I'm actually surprised WordPress hasn't ever moved over to it for ease of installation/deployment - WordPress and PHP seem more likely to trip over in most deployments I've seen before SQLite would.

What are the limitations? I love SQLite, and have vaguely heard that it has issues with concurrency, but what , exactly? I use it on production for www.tithess.gr and it seems to be working beautifully, no concurrency problems whatsoever there. What problems should I be expecting in a multi-access scenario? I've never had that question answered adequately.

Only one process can have a SQLite database file open for writes at a time. Multiple processes/threads can read, however.

That's about it. I'm hesitant to describe it as an "issue" with concurrency because that has connotations that it's a problem with SQLite that the authors should address. Rather, it's part of the simplicity that is a key design feature of SQLite.

As SQLite's own documentation[0] says, it doesn't compete with client/server databases like Oracle/Postgres/MySQL - it competes with fopen() (edit: not a system call, see below), hand-maintained serialization/pickling formats, etc.

0: https://www.sqlite.org/whentouse.html

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#27

Post by kentonv »

dietrichepp wrote: post #20
tedmiston wrote: post #18

I have used SQLite for similar use cases, but occasionally it's led to a corrupted db. I had a cron task writing to it once a day, but an issue with the scheduler led to 2 tasks one day with the latter one finishing before the former. Of course I can add locking or something in my code, but I'd prefer to handle at a lower level — for example, have SQLite take the latest write without corrupting. I'm hoping someone ha…

SQLite is supposed to handle multiple concurrent writes by returning SQLITE_BUSY. I'm imagining hypothetical other causes for your corruption problem, like power loss at a bad moment.

SQLite is designed to handle power loss at bad moments (indeed, anything that calls itself a "database" ought to be resilient against power failure).

Relevant: https://www.sqlite.org/howtocorrupt.html

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#28

Post by 2muchcoffeeman »

dietrichepp wrote: post #20
tedmiston wrote: post #18

I have used SQLite for similar use cases, but occasionally it's led to a corrupted db. I had a cron task writing to it once a day, but an issue with the scheduler led to 2 tasks one day with the latter one finishing before the former. Of course I can add locking or something in my code, but I'd prefer to handle at a lower level — for example, have SQLite take the latest write without corrupting. I'm hoping someone ha…

SQLite is supposed to handle multiple concurrent writes by returning SQLITE_BUSY. I'm imagining hypothetical other causes for your corruption problem, like power loss at a bad moment.

I may be imagining things but I seem to recall that at one point you had to build your own SQLite to get safe concurrent writes, but that was made the default years ago.

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#29

Post by tracker1 »

cyberferret wrote: post #6

I'm a long time user and lover of SQLite, since way back when. Use it in a lot of our projects (web and Win32) that require local databases for logging etc. Sure for larger or concurrent user access to a db, we use other databases, but nothing beats the 'zero footprint' install of SQLite. I even enjoyed the fact that earlier versions of SQLite forced me to think optimise my queries due to the lack of nested SELECTs.…

I find that Firebird has been pretty nice as well, with the option to move from embedded to server.

Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS

#30

Post by Svenstaro »

StavrosK wrote: post #15
petercooper wrote: post #4

Earlier quoted context omitted.

FWIW, I've run SQLite in a few production sites (low 6 figure pageviews per month) and it has worked fantastically. If you understand and work with the limitations, it really is amazing how much you can get out of it. I'm actually surprised WordPress hasn't ever moved over to it for ease of installation/deployment - WordPress and PHP seem more likely to trip over in most deployments I've seen before SQLite would.

What are the limitations? I love SQLite, and have vaguely heard that it has issues with concurrency, but what , exactly? I use it on production for www.tithess.gr and it seems to be working beautifully, no concurrency problems whatsoever there. What problems should I be expecting in a multi-access scenario? I've never had that question answered adequately.

It only supports a subset of ALTER TABLE and the work around for the limitations is very annoying: https://www.sqlite.org/lang_altertable.html
Post reply on HN