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

[Mark Ito]

Folks,

No objections were lodged. The switch has been made (a few minutes 
early). The change should be transparent to default-build_scripts users, 
so let me know if you see any problems.

   -- Mark

On 4/29/19 11:57 AM, Mark Ito wrote:
>
> 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
>
>
> _______________________________________________
> Halld-offline mailing list
> Halld-offline at jlab.org
> https://mailman.jlab.org/mailman/listinfo/halld-offline
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mailman.jlab.org/pipermail/halld-offline/attachments/20190429/66e5d04d/attachment-0002.html>