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

41–50 of 149 posts

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

#41

Post by chjj »

Premature optimization is evil, but preemptive optimization is necessary unless you want to paint yourself into a corner. I realized this after implementing a bitcoin full node.

In my bitcoin implementation, as an experiment, I tried storing the blockchain in sqlite, postgres, and leveldb. I gathered up a bunch of data from the first ~200k blocks of the blockchain and benchmarked all three databases. I queried for something like 30,000 utxos out of a set of a couple million. What took 300-400ms in leveldb took 1.6 seconds in postgres (on the repl. in my actual node it would have taken longer due to deserialization of the utxos). What took 1.6 seconds in postgres took over 30 seconds in SQlite.

Now, you can tell me I did the benchmarks wrong, and "oh, if you just did this it would be faster!", but 30+ seconds is slower to an absolutely insane level. Needless to say, I went the key-value store route, but I was still astounded at how slow sqlite was once it got a few million records in the database.

I actually like sqlite, but when you know you're going to be dealing with 70gb of data and over 10 million records, preemptive optimization is the key. If I were the author, I would consider switching to postgres if there are over 500k-1m records to be expected. That being said, if they're partial to sqlite, SQLightning (https://github.com/LMDB/sqlightning) looks pretty interesting (SQLite with an LMDB backend).

edit: To clarify, these weren't particularly scientific benchmarks. This was me timing a very specific query to get an idea of the level of data management I was up against. Don't take my word for it.

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

#42

Post by spilk »

dietrichepp wrote: post #22
ben_jones wrote: post #8

Earlier quoted context omitted.

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.

Wordpress does attempt some abstraction of the database via its various wrapper classes/functions like wpdb, WP_Query, etc. It's definitely not ORM-level abstraction by any stretch of the imagination, though.

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

#43

Post by asveikau »

zackelan wrote: post #26
StavrosK wrote: post #15

Earlier quoted context omitted.

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

> it competes with the fopen() system call

Nitpick: fopen() is not a syscall, it's part of stdio which is in libc (in user mode). The nearest POSIX syscall equivalent is open(2), but stdio does things like buffering and formatting in user mode on top. It's also more portable to non-Unix because stdio is in the C language spec.

I had heard this phrase before as simply "it competes with fopen()".

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

#44

Post by c-smile »

If to speak about desktop applications then any embedded DB will be unbeatable.

So I am speaking about embeddable DBs here.

Konstantin Knizhnik have implemented impressive set of various embedded DBs: http://garret.ru/databases.html

Like his POST++ has direct mapping to C++ classes so if you use C++ then you don't need any ORM.

In my Sciter[1] Engine I am using his DyBase library [3] as a bult-in persistence for Sciter's script [2] (JavaScript++).

With the DyBase in script you have features similar to MongoDB (noSQL free-form DB) but without any need for ORM and DAL - you can declare some root object as be persistable and access those data trees as if they are JavaScript objects. The engine pumps objects from DB into memory when they are needed:

  var storage = Storage.open(...);
  var dataRoot = storage.root; // all things inside are persitable
  dataRoot.topics = []; // flat persistable list
  dataRoot.topics.push({ foo:1, bar:2 }); // storing object
  /* create indexed collection with string keys, keys can be unique or not */ 
  dataRoot.titles = storage.createIndex(#string);
DyBase has Python bindings too.

[1] http://sciter.com - multiplatform HTML/CSS UI Engine for Desktop and Mobile Application

[2] TIScript - http://www.codeproject.com/Articles/33662/TIScript-Language-...

[3] DyBase - http://www.garret.ru/dybase.html

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

#45

Post by tedmiston »

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.

This is helpful; it's possible I'm assuming too much. The corruption did happen only once and was also during a time period that the server (a PaaS) was running maintenance including some downtime.

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

#46

Post by corysama »

jtakkala wrote: post #13
Feneric wrote: post #9

SQLite also does remarkably well with recovering from all manner of power loss / crashes / worst case scenarios. We created a "power loss" rig just to test this facility for one particular system. Really SQLite's biggest weakness is concurrency, and if your app needs that in any serious amount you probably ought to look elsewhere. If you're just dealing with occasional concurrency though SQLite shouldn't be dismissed…

I gained a newfound respect for SQLite recently after reading Dan Luu's post on file consistency ( http://danluu.com/file-consistency/ ). I had always thought of SQLite as a bit of a toy database, but having read that post I was surprised by how rigorously it appears to have been developed.

Reading that post makes me want to investigate LMDB as an alternative "competition for fopen()". I'd be very interested if anyone had opinions/experience on that idea.

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

#47

Post by Animats »

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.

If they don't need indices, almost anything will work. Few people have a big enough music library that the data won't fit in memory.

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

#48

Post by c-smile »

petercooper wrote: post #23
StavrosK wrote: post #15

Earlier quoted context omitted.

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

Consider multi-user wordpress site for example. You can have one SQLite DB per user - there is simply no information to share between users. If so you can split all your users between multiple backend machines and do per user request routing - like one machine serving users from A to F. SQLlite has cheap DB loading sequence so you can open/close it for each page request. That would be perfect horizontal scaling as all your users will not fight for single DB access.

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

#49

Post by ww520 »

chjj wrote: post #41

Premature optimization is evil, but preemptive optimization is necessary unless you want to paint yourself into a corner. I realized this after implementing a bitcoin full node. In my bitcoin implementation, as an experiment, I tried storing the blockchain in sqlite, postgres, and leveldb. I gathered up a bunch of data from the first ~200k blocks of the blockchain and benchmarked all three databases. I queried for so…

I'm curious to know how come Sqlite performed so bad on couple millions records.

Can you share what the table schema is? What the queries are? And what indices built for the Postgres and Sqlite tables?

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

#50

Post by panic »

chjj wrote: post #41

Premature optimization is evil, but preemptive optimization is necessary unless you want to paint yourself into a corner. I realized this after implementing a bitcoin full node. In my bitcoin implementation, as an experiment, I tried storing the blockchain in sqlite, postgres, and leveldb. I gathered up a bunch of data from the first ~200k blocks of the blockchain and benchmarked all three databases. I queried for so…

What operations were you doing in your benchmark? What was SQLite actually doing (CPU?, disk I/O?) while taking 30 seconds to finish? This would be a lot more useful and less FUDdy with more detail.
Post reply on HN