Forums intermittently going down?

[Vladimir Panteleev]

On Wednesday, 26 September 2018 at 01:52:31 UTC, H. S. Teoh wrote:
> What version of SQLite are you using?  AFAIK, SQLite itself 
> does support concurrent access.  Though it does have to be 
> explicitly compiled with that option, otherwise it will only 
> issue a runtime error.  Of course, locking is not as 
> fine-grained, so if one request locks one table then it will 
> block everything else.

Yep, well, it's not very good at it (as it wasn't designed for 
it). It locks the entire database when writing, and when the lock 
is held, you get an exception or have to retry on a timer. So, 
it's "supported" but not actually scalable.

> I don't know what your schema looks like, so it's hard to give 
> specifics,

I posted a link to the schema earlier in the thread.

> but basically, any column used in a WHERE clause is a candidate 
> for indexing.

Yep, I think we're past that already.

The last issue I ran into was subscriptions. Some people seem to 
be creating subscriptions to collect and email them frequently, 
sometimes on every post - not that those work well, because the 
forum stops emailing people as soon as they have unread messages 
in their subscriptions, but they still get saved to the queue. 
Still, the longer the forum was online, the more subscriptions 
have accumulated, and every new post resulted in all those 
subscriptions getting triggered. Now, every time a subscription 
with an email action was triggered, we had to check if there are 
any unread messages in their subscription queue, and there can be 
a lot of messages in there - thus, this caused something like an 
O(m*n) database operation (with the underlying database 
implementation also not having a constant execution time of 
course). I fixed this by limiting the check to the first unread 
post instead of reusing a function to count all unread messages 
in the subscription queue:

https://github.com/cybershadow/DFeed/commit/9cfcab2