Definitely SQLite will be a bottle neck in this system – not great for writing to – both Pg or MySQL would be an almost certainly better solution for repeated writing to.
You could get some simple gains by splitting the database up so that there is a database per site rather than a database for all 10 sites – unless there is data to be shared between them – as you would have less database lock contention on the individual databases.
SQLlite is not designed to be used in a situation where there are lots of write locks. And does not scale very well as the database gets larger…
Also, just trying to get round what each visitor triggers in the CGI – and why it is a lot of work for the db – this is where you would need to make things easier for it & yourself, is the information you show useful to the user or could this just be processed
some other way!
From: Tom Browder <tom.browder@xxxxxxxxx>
Sent: 03 October 2020 20:29
To: users@xxxxxxxxxxxxxxxx
Subject: Re: [users@httpd] Re: Alternatives to SSI (server side includes)? [EXT]
Sounds like a job for AJAX, but before throwing out the baby with the bath water I'd seriously consider turning up logging with timestamps on your existing CGI and
That's a good idea, Scott, I've just been too lazy and debugging CGI is such a pain.
The clients are mostly casual browsers, but every visitor triggers the CGI so that's a lot of work for the db. Regarding the db it's currently an SQLite instance but I'm running about 10+ virtual sites off the same db and needing writes
so that alone may be part of the problem.
Also, I do have a good Pg db running I've intended to use but just haven't gotten a "round tuit" yet.
But, could a web socket setup help do you think?
--
The Wellcome Sanger Institute is operated by Genome Research
Limited, a charity registered in England with number 1021457 and a
company registered in England with number 2742969, whose registered
office is 215 Euston Road, London, NW1 2BE.
|
