Forums intermittently going down?

[H. S. Teoh]

On Wed, Sep 26, 2018 at 01:07:29AM +0000, Vladimir Panteleev via Digitalmars-d wrote:
[...]
> One thing possible with a traditional RDBMS that's not possible with
> SQLite is processing several simultaneous requests. The synchronous
> API translates to the synchronous nature of the entire program: when
> the forum hits a request it needs a few seconds to handle, it can't
> process any requests during that time, even those it could answer
> without consulting the database (as much is cached in RAM).
[...]

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.

IME, though, SQLite performance can be greatly improved simply by
indexing columns used for lookup.  Except for row ID, SQLite doesn't
index by default, so if you're filtering your selects by other columns,
you're potentially hitting O(n) table scans per lookup.  I don't know
what your schema looks like, so it's hard to give specifics, but
basically, any column used in a WHERE clause is a candidate for
indexing.  Of course, it's a judgment call which columns are best for
indexing -- you don't want to index everything since the overhead might
make it even slower than without indexing.  You might have to play
around a bit to find the best candidates to index.  Usually, though, as
is typical for performance optimizations, there's just a small number of
columns that are causing a bottleneck; once they are indexed, it should
yield much improved performance.


T

-- 
Fact is stranger than fiction.