I wish HTML5 storage standardized on Sqlite. The inconsistent story on HTML5 storage across browsers is kind of sad.
We’re pretty happy with SQLite and not urgently interested in a fancier DBMS
51–60 of 149 posts
Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS
#52I 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…
Also, I don't even want to try to imagine shipping an installable desktop application for non-technical users that relies on MySQL!
It's really not that hard to imagine. There is an embedded library version of MySQL called libmysqld which is made for this very purpose. Of course as you point out it's quite overkill for something that would work perfectly well with Berkeley DBs.
Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS
#53Earlier quoted context omitted.
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 al…
well, you kinda just move the problem to another layer. You'll face same horizontal scaling issues once you need to scale on each user.
Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS
#54I have written several "Use Sqlite!" posts that have made the rounds on hackernews... reading this watered down post, which is devoid of any new, surprising or usable info, it strikes me that repping SQLite has achieved meme status.
If you want tangible info you can actually use, read sqlites documentation. There's a wealth of information there.
Here are some of posts, for the Python crowd:
http://charlesleifer.com/blog/five-reasons-you-should-use-sq...
http://charlesleifer.com/blog/using-the-sqlite-json1-and-fts...
http://charlesleifer.com/blog/my-list-of-python-and-sqlite-r...
Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS
#55Premature 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.
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 against in terms of data management. This is primarily why I didn't post them in the first place. This was several months ago. I'll try to find the code that ran them and put them up on github if I can, but I doubt it will be that useful without the actual data, which isn't easy to upload.
So yes, to clarify, don't take my word for it. This is just my experience.
Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS
#56Earlier 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.
The worst thing that I experienced was actually the lack of basic support for routine schema alterations that you find in other DMBS's. I don't have time to recall exactly but relatively mundane things like altering the names or data types of columns required basically dropping and rebuilding the columns / tables from scratch. It really made the ongoing maintenance quite painful compared to other DBMS's.
Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS
#57Premature 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've found sqlite's performance on bulk insertions to be massively (100x) improved by wrapping the bulk insertion in a transaction.
fsync()ing a couple hundred thousand individual INSERTs isn't fast.
Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS
#58Premature 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.
Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS
#59I didn't know beet, but it looks exactly like what I've been wanting for years.
Re: We’re pretty happy with SQLite and not urgently interested in a fancier DBMS
#60Premature 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've found sqlite's performance on bulk insertions to be massively (100x) improved by wrapping the bulk insertion in a transaction. fsync()ing a couple hundred thousand individual INSERTs isn't fast.
Batching inserts, wrapping the batches in transactions, waiting til after to add indexes: best way to go.