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

81–90 of 149 posts

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

#81

Post by chjj »

orf wrote: post #58
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 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. I'm sorry to say but you're almost certainly doing something wrong then.

It's possible, but it was a very simple database layout. Pretty hard to screw up. Then again, I screw up simple things all the time. But whatever I screwed up would likely have been duplicated on the postgres side since the sqlite and postgres setups were nearly identical. Postgres performed fine compared to sqlite. It was a bit behind leveldb, but I figure the overhead of flushing the data to a socket has an added cost. Leveldb has the advantage of being in the same process.

If I remember right: Querying for leveldb was iteration from [20-byte-hash][lo-utxo-id] to [20-byte-hash][hi-utxo-id] and subsequent lookups on the keys containing the actual data. The sql table setup was `id` as a char primary key and `address` as a char with an index. The query was a simple `select data from utxos where address = ?`. The actual `data` blob was on average maybe 60 bytes in size.

Maybe there was something I could have done better there. I'm not sure. This was just my experience. This is all beside the point anyway. My point was whatever database was better, it was worth testing each one, and I don't consider it to be premature optimization.

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

#82

Post by combatentropy »

ww520 wrote: post #51

I wish HTML5 storage standardized on Sqlite. The inconsistent story on HTML5 storage across browsers is kind of sad.

Implementing WebSQL was so much work that all the browser vendors just basically bundled SQLite and called it a day. The problem was that the working group required at least one other implementation, before it became a standard. But that was such a monstrous project that none of the browser vendors wanted to take it on.

What the browser vendors agreed on is that they would rather have IndexedDB. I've never used it, but they say it is a lower-level API than SQL, and using it you could build your own SQL abstraction layer above it.

So we have as standards the key-value-based localStorage and then, halfway between that and SQL, is IndexedDB.

More: https://hacks.mozilla.org/2010/06/beyond-html5-database-apis...

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

#83

Post by chjj »

vonklaus wrote: post #63
chjj wrote: post #55

Earlier quoted context omitted.

I'll clarify here: these weren't very scientific benchmarks, as I alluded to. So, don't take my word for it. I didn't measure ops per second, I didn't take into account system load or cpu usage, etc. This is all anecdotal evidence. I'm not saying this an announcement that "you shouldn't use sqlite". I measured the time of one query. It was a benchmark I ran just to personally give me a general idea of what I was up a…

I would be interested in a repo or post about your setup for the actual project; downloading, storing and using blockchain data. Sounds super interesting so if it(article, repo, site) isn't private & exists, would be keen. Cheers.

The project itself an alternative bitcoin full node, one of the few (along with btcd, bitcoinj, etc), and the only one that runs in the browser: http://bcoin.io/browser.html. I've posted it here before but no one seemed to be interested.

It's probably not good if you want to index anything more than addresses, but you could easily modify it to index other things if you wanted to. The actual blockchain database resides here: https://github.com/bcoin-org/bcoin/blob/coinviews5/lib/bcoin...

^ That's the current branch I'm working on which, as it happens, optimizes a lot of the storage of utxos. Before, it was storing them the messy bitcoinj way. Now it uses coin viewpoints similar to bitcoind and btcd.

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

#84

Post by strcat »

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…

It works very well for concurrent readers and can handle one concurrent writer when set to WAL mode. Saying that it's no good at concurrency is selling it short. It's blazing fast as long as the use case doesn't require multiple concurrent writers. In fact, it will be faster than client-server databases when you aren't hitting a weak point like that, since it has no IPC overhead.

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

#85

Post by stevekemp »

petercooper wrote: post #4
niftich wrote: post #3

I was unfamiliar with this project and assumed it was a hosted service at first. Not so, this is a local application, so an embedded database makes sense. It took until the very last paragraph for the blog post to make that point.

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.

Yeah I've written many small websites which, such as a dynamic DNS service[1], which use SQLite as their sole storage.

Providing there aren't too many updates at once, such that locking becomes a problem, it works really really well.

[1] - http://dhcp.io/

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

#86

Post by mgkimsal »

kefka_p wrote: post #65

Some people are unfamiliar with the phrase "right tool for the job". As the developers behind the project, I'd have to think the authors are in the best position to make the determination about which tool is appropriate.

"right tool for the job" ends up being almost meaningless unless the players involved have enough experience with multiple tools to make a useful judgement. Too often "right tool" is "whatever I've already used before". :/

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

#87

Post by mianos »

SwellJoe wrote: post #21

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

So many times I see people handing out a root password to edit /etc/password. Sure, it may be crazy for performance but, even a trivial number of users and aliases are more easily managed with a remote mysql client. Everyone that has used postfix in a production environment knows the pain otherwise, the rest feel free to vote down.

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

#88

Post by voiper1 »

ams6110 wrote: post #61
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…

With relational databases, you absolutely can see subsecond to 30+ second ranges for the same query on the same data, if you don't have proper indexing or stats on your tables.

Indeed! An index can be the difference between a 2-5minute full table scan and a subsecond query.

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

#89

Post by thawkins »

SwellJoe wrote: post #21

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

Its mostly about concurrency, sqllite serialises all writes, for an embedded database in a single user application that is ok, but for a web application that may have a lot of concurrent writes going on its a performance disaster area. Note that for good perfomamce on a write heavy web application even mysql can be a problem unless you use innodb. As myisam has the same write table locking behaviour.

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

#90

Post by keeperofdakeys »

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.

I thought sqlite didn't allow any readers while a write was in progress, but apparently that is no longer the case https://www.sqlite.org/wal.html
Post reply on HN