[Halld-offline] Make CCDB Use SQLite Again (MCUSA)

[Mark Ito]

Folks,

You will recall that we switched the default choice for the database 
engine for CCDB at JLab from SQLite to MySQL back on March 12 
(https://mailman.jlab.org/pipermail/halld-offline/2019-March/003561.html). 
Since then we have been studying usage patterns and CCDB performance on 
MySQL. And, not unexpectedly, we have experienced "storms" where the 
MySQL server is overloaded with CCDB requests from farm jobs and 
performance is severely degraded. Jobs don't crash but they take a long 
time to read in constants before starting to analyze events, up to two 
hours in the worst cases. These have been occurring once every day or 
two and last for a few hours.

We have learned a lot. The storms are definitely coming from farm jobs 
when many of them start at the same time. The queries have been analyzed 
(no big inefficiencies found). These things were invisible to us when we 
were running with the SQLite default. As a result IT is putting up a new 
beefier server for us to use for the farm.

Until the new server comes online (a week or two), *I am proposing that 
we switch back to using SQLite as the default for CCDB*, but with two 
differences from how we used SQLite at JLab in the past.

(1) SQLite will be used *for the farm only*. Interactive users at JLab 
will continue to use the MySQL server.
(2) Rather than accessing the ccdb.sqlite file on the group disk, farm 
jobs will access one of 100 copies of ccdb.sqlite, located on the work 
disk, chosen randomly for each job.

The idea is to separate farm demand for CCDB constants from other 
functions of our main database server while we wait for the new server 
that will do just that. Other functions include the writing of 
calibration constants to the CCDB, RCDB access, and MCwrapper job 
control functions.

Note again that this change only affects processes using the defaults 
from the build_scripts-based environment set-up. Those that set their 
own JANA_CALIB_URL environment variable explicitly will get what they 
ask for, as always.

*The proposed switch-over time is COB today.* I will make the change 
unless I hear objections.

   -- Mark

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mailman.jlab.org/pipermail/halld-offline/attachments/20190429/b49b2038/attachment.html>