News for hackers. Stuff that matters.
Re:SQLite 3.8.0 Release with next-generation query planner by leokun on Monday August 26, 2013 @09:05PM (#6280850)
Unless you want to use it with multiple users and connections, in which case it stops working. It's good for embedding data storage in clients and testing.

http://howfuckedismydatabase.com/sqlite/

Re:SQLite 3.8.0 Release with next-generation query planner by eksith on Monday August 26, 2013 @09:16PM (#6280880)
Until 2010, I ran a forum on SQLite with 600 users/day + around 1000 posts/day on a single OpenBSD machine with 2gb RAM and 7200RPM HDD. We moved to Postgres when we passed 1600 posts/day.

There was some minor slowdown noticeable around 6AM - 10AM, but besides that, we never had major issues. The bottleneck was usually the network, not the DB.

Edit: Anyone interested in playing with SQLite should give the SQLite Manager plugin for FF a try : https://addons.mozilla.org/en-US/firefox/addon/sqlite-manage...

(I'm not affiliated with the developer, I just like the tool)

Re:SQLite 3.8.0 Release with next-generation query planner by vidarh on Tuesday August 27, 2013 @05:19AM (#6282364)
600 users/day and 1000/posts a day is miniscule.

You can run something like that with no noticeable performance issues on a classic 120MHz Pentium and flat files (I'm speaking from experience of running a USENET news server handling 30,000 groups for an ISP on a machine like that, which was shared with lots of other functions), so if the bottleneck had been anything other than the network, it'd have been shocking.

Re:SQLite 3.8.0 Release with next-generation query planner by dchest on Tuesday August 27, 2013 @05:04AM (#6282321)
To add to this, since 2010 SQLite improved concurrency a bit with write-ahead loging: http://www.sqlite.org/draft/wal.html
Re:SQLite 3.8.0 Release with next-generation query planner by guelo on Monday August 26, 2013 @10:11PM (#6281053)
I thought SQLite locked the entire DB when doing an INSERT. Seems like that would slow a forum app considerably. Though I guess at 1600 INSERTs per day, which averages to about 1 per minute assuming even distribution throughout the day, you won't have that many lock collisions.
Re:SQLite 3.8.0 Release with next-generation query planner by abtinf on Monday August 26, 2013 @10:22PM (#6281089)
The sqlite locking model can eat 1600 inserts before breakfast with only minor lock contention. The whole file lock doesn't take effect until the moment the write is ready to go to disk.

http://www.sqlite.org/lockingv3.html

Re:SQLite 3.8.0 Release with next-generation query planner by eksith on Monday August 26, 2013 @10:22PM (#6281092)
Writes were generally quite fast so the next write rarely got queued. Also we ran it single-threaded. When we moved to Postgres, that also gave us the opportunity to safely switch to phpfpm without worrying about the lock issue.

The nice thing about Postgres as opposed to SQLite is that we finally got "searching" as a feature. ;)

Re:SQLite 3.8.0 Release with next-generation query planner by jeltz on Tuesday August 27, 2013 @08:09PM (#6287198)
Not if you use it in the WAL mode.
Re:SQLite 3.8.0 Release with next-generation query planner by acmecorps on Monday August 26, 2013 @09:54PM (#6281005)
Really? I thought SQLite is for development purpose only. but 1000posts/day? Wow..
Re:SQLite 3.8.0 Release with next-generation query planner by onedognight on Monday August 26, 2013 @11:22PM (#6281279)
> but 1000posts/day? Wow..

While a forum that gets 1000 posts/day is quite active, a database that does an INSERT ever 80s is not even moving.

Re:SQLite 3.8.0 Release with next-generation query planner by vidarh on Tuesday August 27, 2013 @05:28AM (#6282392)
For comparison, 8 years ago I wrote a queuing system that did either in-memory queues, or wrote to sqlite for durability. With a little bit of tuning, the Sqlite queues could handle at the very least hundreds of thousands of messages a day - we never pushed it to it's limit.
Re:SQLite 3.8.0 Release with next-generation query planner by eurleif on Tuesday August 27, 2013 @04:08AM (#6282121)
Presumably there were many SELECTs for every INSERT.
Re:SQLite 3.8.0 Release with next-generation query planner by eksith on Tuesday August 27, 2013 @04:48AM (#6282252)
Yes. Yes there were. :)

We always limited transactions for UPDATES and INSERTs that involved more than one table.

It was quite the learning experience since you do have to think of different ways of working with data. You get to learn very quickly the difference between what like to store vs. what you actually need to store to get a particular functionality. But that turned to being a boon in the end because we got a much simpler forum as a result. Fewer bells and whistles meant users focused on actual discussion and not ancillary, shiny bits and bobs.

Re:SQLite 3.8.0 Release with next-generation query planner by vidarh on Tuesday August 27, 2013 @05:22AM (#6282372)
But on a machine with 2GB of RAM running a forum that small, pretty much none of those selects should ever need to hit disk - it should all pretty much be in the buffer cache, unless there were lots of full text searches of the entire posting history.

EDIT: And that is without any app specific caching.

Re:SQLite 3.8.0 Release with next-generation query planner by Groxx on Tuesday August 27, 2013 @02:20AM (#6281792)
Put simply, you thought wrong. SQLite is one of the most-used pieces of software out there, absolutely including production software. http://www.sqlite.org/mostdeployed.html
Re:SQLite 3.8.0 Release with next-generation query planner by eksith on Monday August 26, 2013 @10:02PM (#6281030)
I should add, that we ran nothing else on the box. Just the forum and it was text only (no image uploads etc...).

Backups were pretty easy. ;)

Re:SQLite 3.8.0 Release with next-generation query planner by corresation on Monday August 26, 2013 @10:08PM (#6281045)
Please clarify "for development purpose only"? SQLite is used in a lot of production software you rely upon daily, and is one of the most robust, useful pieces of code going.

http://www.sqlite.org/famous.html

Now it's an embedded database, which is why leokun's comment is out of place. However you can absolutely drop a queue in front of it, for instance, and serve many "simultaneous" users for a purpose such as a web forum.

Re:SQLite 3.8.0 Release with next-generation query planner by nraynaud on Monday August 26, 2013 @09:55PM (#6281009)
I'm really glad to read that, and I was stupid not to do it.
Re:SQLite 3.8.0 Release with next-generation query planner by asperous on Monday August 26, 2013 @09:09PM (#6280858)
You basically just said your car is reliable unless you need a semi-truck, in which case your car is worthless.
Re:SQLite 3.8.0 Release with next-generation query planner by leokun on Monday August 26, 2013 @11:22PM (#6281281)
Apparently I'm wrong! Too late to edit. Good to know though, not afraid of saying something and getting downvotes if I'll learn something. :)
Re:SQLite 3.8.0 Release with next-generation query planner by eksith on Tuesday August 27, 2013 @01:50AM (#6281699)
I don't think you were downvoted because you were wrong. I like to think the HN community isn't that shallow.

Reasons for downvoting is usually due to posts containing no new information, excessive snark, flippant attitude etc... Users and moderators are extra touchy about avoiding the Reddit ambiance here. Basically if it doesn't contribute to the discussion, then it may be downvoted. That said, I didn't downvote you because what you said allowed me to add to the discussion.

I try to follow this when I vote : http://news.ycombinator.com/item?id=1065084