Forums intermittently going down?

[H. S. Teoh]

On Wed, Sep 26, 2018 at 02:33:27AM +0000, Vladimir Panteleev via Digitalmars-d wrote:
> On Wednesday, 26 September 2018 at 01:52:31 UTC, H. S. Teoh wrote:
[...]
> > 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:
[...]

Hmm.  I wonder if it might help if you separated the subscription queue
into its own database.  You're right that SQLite locks the entire
database when writing, so if there's a lot of write activity going on,
readers will be frequently blocked.  Separating part of the data into
its own DB may help increase the parallelizability of the system.

In my experience in working with SQLite, I find that generally you want
to design your schema so that writes are as short as possible -- the
global DB write lock can be a big bottleneck, as you said, so the less
time you spend holding the write lock, the better. If it's possible to
split up data for different functionalities into different DBs, that
might help improve performance by avoiding waiting for the global write
lock on a single DB all the time.

Now glancing over your schema, I wonder if it might make a difference if
you used the implicit rowId for your 'ID' fields instead of strings. The
rowId in SQLite is special, because it exists for every table
implicitly, is always unique, and AFAIK allows fast lookups (or faster
lookups than strings, AIUI).  It may not be practical to do that now,
given the large amount of data already stored with string IDs, but it
could potentially make a difference.  Of course, you may need to map it
to strings somewhere, so I'm not sure if the tradeoff is worth it, but
it might be instructive to experiment with it in an offline system to
see if you could gain some performance that way.


T

-- 
We are in class, we are supposed to be learning, we have a teacher... Is it too much that I expect him to teach me??? -- RL